Is there a way to get a multi valued string field ...
# troubleshooting
m
Is there a way to get a multi valued string field to contain a zero length array ? So if the null support is set to store 'null' for the MV string field I get ['null']. If I do
field is not null
the indexed null correctly registers the field as null. But the length of the array is still 1 and still contains a literal string. This is making string matching a little wonky as there is always a value in the array to be matched. So if a user tried to check if an array contains the text 'null' it returns a value instead of nothing. Is there a way to set my default for MV fields to an empty array to avoid this string matching issue? I know I can just add
field is not null
to all my queries, but I want to see if there is a better answer I am not seeing.
n
Is there a way to set my default for MV fields to an empty array
+1 to the above ^^. We have a similar scenario to set default nulls for MV fields to an empty array, so that the following distinct count doesn't return a non-zero value for nulls/empty arrays. Is it possible to provide this as part of default null override in schema definition for the real-time table?
Copy code
SELECT
      id,
      DISTINCTCOUNTMV(message_ids) as num_unique_messages,
      DISTINCTCOUNTMV(file_ids) as num_unique_files
    FROM real_time_pinot_table
    WHERE
      id in ${IDs}
      AND enterprise_id = {$ENTERPRISE_ID}
      AND date_str >= {$START_DATE_STR_1d}
      AND date_str <= {$END_DATE_STR_1d}
    GROUP BY
       id
cc: @Mayank @ZEBIN KANG @Jessica Stewart
🫢🏻 1
🫢 2
m
m
@Mayank I have column level nulls enabled, is there something I don't understand from the docs?
m
You do need to filter out nulls right?
Null filtering is not implicit
m
it still returns an array with ['null'] as the MV result
which is an array with more than 0 values
m
what's the query
m
the specifics don't matter so much but if I have a MV field where I need to check array member counts, 1 array member and null look the same so to differentiate I also have tag on is not null every time I reference an array column to block the nulls, so my question was is there a way to flag the value as null and make sure the array is truly empty?
I'm guessing no from my reading of the docs since it sounds like storing the values and indexing them in a null index is a bit of a work around to not have traditional nulls
m
I am not asking for specific query, you can give me a made up one, I want to see if it is missing some check or there's a bug
m
2 secs
Copy code
select affinity_all_product_name, 
ARRAYLENGTH(affinity_all_product_name),
affinity_all_product_name is null
from audience_estimate_v1 
where affinity_all_product_name is null
the column is a mv string with column level null enabled and will be using the default null value for MV string
βž• 1
m
Thanks. Will check and get back
❀️ 1
thanks 1
Seems like, this is a current limitation, unfortunately. cc: @Jackie
πŸ‘πŸ» 1
n
Is this limitation specific to a Pinot version?
m
From what I understand, it is not version specific
πŸ‘ 1
z
Hey @Mayank thank you so much for helping here; this is Zebin, @Neeraja Sridharan’s coworker.
We are currently using Pinot 1.0 and we notice that Pinot 1.1 release Support for explicit null handling modes and Fix for arrayAgg null support; my understand is that this will resolve
DISTINCTCOUNTMV
to count null as a distinct element. Please let me know if I have any misunderstanding here πŸ™‡
thanks 1
m
I checked with @Jackie author of the PR you included, he says @mat β€˜s problem is not fixed
πŸ‘ 2
j
@Neeraja Sridharan @mat Can you try this and see if it works:
Copy code
SELECT
      id,
      DISTINCTCOUNTMV(message_ids) FILTER(WHERE message_ids IS NOT NULL) as num_unique_messages,
      DISTINCTCOUNTMV(file_ids) FILTER(WHERE file_ids IS NOT NULL) as num_unique_files
    FROM real_time_pinot_table
    WHERE
      id in ${IDs}
      AND enterprise_id = {$ENTERPRISE_ID}
      AND date_str >= {$START_DATE_STR_1d}
      AND date_str <= {$END_DATE_STR_1d}
    GROUP BY
       id
βœ… 1
πŸ‘€ 1
n
Thanks @Jackie πŸ™‡β€β™€οΈ This ^^ works without MSE enabled (our current Pinot version is 1.0). So, for
IS NOT NULL
check to work, below are sufficient - correct? 1. set
"nullHandlingEnabled": true
in the table config (no column level or any special null handling is needed) 2.
message_ids
&
file_ids
MV columns need to be STRING data type (where the internal default is 'null')?
cc: @Sai Tarun Tadakamalla
thanks 1
j
1 is required, 2 is not needed
n
Oh okay! So, file_ids MV column can be an array of LONG data type and IS NOT NULL would still work? as the internal default null is long min value
j
IS NOT NULL
can apply to any column regardless of its type
n
Good to know thanks
IS NOT NULL
can apply to any column regardless of its type
Would this work if we override with a custom value for nulls?
j
Yes.
IS NOT NULL
only looks at whether the value was
null
when ingested, so it works on any custom default null value
πŸ‘ 2
m
@Jackie thanks for assisting but this has drifted away from my original question. Is there a way to set the MV column settings so that the array is actually empty, not stuck with a placeholder value? As my original question stated, I know I can use
is not null
to manage the query, but it's not ideal to have to do is not null on every field that may show this behaviour. Is there a column setting that will ensure my MV field is actually empty?
if it's not is there a way to use an actual null type instead of a string value
m
@Mayank thanks, I was trying to clarify as @Jackie tagged me in the below which has nothing to do with my actual question, I will assume this is resolved then and its a no https://apache-pinot.slack.com/archives/C011C9JHN7R/p1755891101701499?thread_ts=1755539507.290849&amp;cid=C011C9JHN7R
m
Yes, sorry for the confusion, we had two separate conversations in this thread πŸ˜…
πŸ‘πŸ» 1
n
Sorry about the confusion! I also had a similar question as @mat i.e. how to set an empty array
Is there a column setting that will ensure my MV field is actually empty?
, and hence posted my request in this thread. Once we figured out that's not possible in any Pinot version (as stated here), I just wanted to get a recommended work-around for the same i.e. this.
m
No worries
πŸ‘ 1