mat
08/18/2025, 5:51 PMfield 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.Neeraja Sridharan
08/20/2025, 3:51 AMIs 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?
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 StewartMayank
mat
08/21/2025, 5:27 PMMayank
Mayank
mat
08/21/2025, 5:28 PMmat
08/21/2025, 5:28 PMMayank
mat
08/21/2025, 5:31 PMmat
08/21/2025, 5:34 PMMayank
mat
08/21/2025, 5:34 PMmat
08/21/2025, 5:38 PMselect 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 stringMayank
Mayank
Neeraja Sridharan
08/21/2025, 9:26 PMMayank
ZEBIN KANG
08/22/2025, 2:39 PMZEBIN KANG
08/22/2025, 2:43 PMDISTINCTCOUNTMV to count null as a distinct element. Please let me know if I have any misunderstanding here πMayank
Jackie
08/22/2025, 7:31 PMSELECT
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
idNeeraja Sridharan
08/22/2025, 8:35 PMIS 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')?Neeraja Sridharan
08/22/2025, 8:35 PMJackie
08/22/2025, 8:37 PMNeeraja Sridharan
08/22/2025, 8:38 PMJackie
08/22/2025, 8:39 PMIS NOT NULL can apply to any column regardless of its typeNeeraja Sridharan
08/22/2025, 8:40 PMNeeraja Sridharan
08/22/2025, 8:41 PMWould this work if we override with a custom value for nulls?can apply to any column regardless of its typeIS NOT NULL
Jackie
08/22/2025, 8:42 PMIS NOT NULL only looks at whether the value was null when ingested, so it works on any custom default null valuemat
08/23/2025, 6:30 AMis 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?mat
08/23/2025, 6:36 AMmat
08/23/2025, 12:44 PMMayank
Neeraja Sridharan
08/24/2025, 12:20 AMIs 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.Mayank