Implement 3-Valued logic for Null handling

[nishantmonu51]

Description

As per the SQL Standard, predicates/expressions handling NULLs should follow 3-valued logic. However in current druid filters only return true/false which can lead to inconsistent results involving NOT/AND/OR and null values.

Motivation

Some queries returns incorrect results as per SQL standard->

SELECT dim1, not(dim1 like 'abc'), (dim1 like 'abc') FROM druid.foo
returns row #0: [null, true, false]

SELECT dim1 FROM druid.foo WHERE dim1 > 1 OR dim1 < 2
returns - row #0: [null]

Proposal

the proposal is to implement 3-valued logic wherein predicates/expressions return one of [true/false/unknown] values and follows below logic for AND/NOT/OR -

NOT

• NOT ( true ) is false
• NOT ( false ) is true
• NOT ( unknown ) is unknown

AND

AND	true	false	unknown
true	true	false	unknown
false	false	false	false
unknown	unknown	false	unknown

OR

OR	true	false	unknown
true	true	true	true
false	true	false	unknown
unknown	true	unknown	unknown

TODO: I havn't figured out the implementation details yet.

[gianm]

I believe this was addressed in #11184. @clintropolis could you confirm please?

[clintropolis]

I believe this was addressed in #11184. @clintropolis could you confirm please?

Yeah, I believe so.

Semi related, there are still a handful of filters which have non-sql compliant behavior, including the NotFilter, e.g. x != 'a' will take the x = 'a' bitmap and then invert it, which means that it matches null values, which is incorrect. This should be fixed by probably pushing the inversion into the child index computation so that child filters can do whatever is appropriate to invert the filter, such as filtering out nulls for the selector, etc.

Talking about filters, the bound filter is also not sql compliant, since it treats a missing lower bound as a "should match null values", which is hard to reason about. In the super near future I would like to add new equality, null, and range filters to replace selector and bound filters to try to be more sql compliant, and have a switch in the planner to use these better behaved filters without impacting the existing behavior.

[github-actions]

This issue has been marked as stale due to 280 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If this issue is still
relevant, please simply write any comment. Even if closed, you can still revive the
issue at any time or discuss it on the dev@druid.apache.org list.
Thank you for your contributions.

[clintropolis]

This has resolved the rest of the way by #15058. Full 3VL behavior can be had with druid.generic.useDefaultValueForNull=false, druid.expressions.useStrictBooleans=true, and druid.generic.useThreeValueLogicForNativeFilters=true, all of which are the defaults.