Hi All, We are seeing peculiar behavior when trying to omit rows with NULL values for a particular column. Query: SELECT COL1, SUM(HITS) FROM TABLE_NAME WHERE COL1 IS NOT NULL AND COL1 != 'VALUE1' GROUP BY 1 The results contain a row for NULL value of COL1 but not 'VALUE1'. COL1 is not an ARRAY or Multi-value column. If we just keep the condition "COL1 IS NOT NULL", then the results don't contain any row for NULL value. But the minute we add the AND condition, it appears. Why is this happening? Is this an expected behavior? Thanks, AR.

Are you running Druid 27 or earlier? (https://druid.apache.org/docs/latest/release-info/upgrade-notes#sql-compatibility) I just tried it on a recent version with no issues, but I my version is using the ANSI NULL handling behavior.

Hi John, We are running v27.0.0. The document is referring to differences in querying empty strings and nulls. We don't have any empty strings in the data. Also, we have set

druid.generic.useDefaultValueForNull

to false from the beginning. So it should not be mixing up empty strings & null values. It seems to be handling null values correctly because when the query containts only the "is not null" condition, it is working properly. Only when we add another "AND" clause on the same column that the results become incorrect. Thanks, AR.

Ok, yes it looks like the older releases have the issue ... I'm testing on an Imply release that is circa Druid 27 ... it has the probem regardless of whether

druid.generic.useDefaultValueForNull

is set to

true

or

false

. The latest version does not have this issue ... I suggest you try out Druid 29.

Thank you John. For now, we are using a workaround using the

LENGTH

function in the query to eliminate the NULL rows from the result. SELECT COL1, COUNT(*) FROM TABLE_NAME WHERE LENGTH(TRIM(COL1)) > 0 AND COL1 != 'VAL'; We just upgraded to v27.0.0 a couple of months ago. Looks like we will have to plan for another upgrade very soon. 😞 Regards, AR.

@John Kowtko Did you tested on v29? We see this issue in version 29 too. According to this Github issue https://github.com/apache/druid/issues/17116 seems like the reporter also saw it on 29.0.1.

My Imply version might be circa Druid 30 then ... unfortunately I think you will just have to try it out to see when it was fixed ... unless you can find the specific PR ...