[
https://issues.apache.org/jira/browse/CALCITE-4446?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4446:
---------------------------------
Description:
Implement three-valued logic for SEARCH operator.
Consider the expression {{x IN (10, 20)}}, which we might represent as
{{SEARCH(x, Sarg(10, 20))}}. Suppose we invoke this with a value of {{NULL}}
for {{x}}. Do we want it to return UNKNOWN, FALSE or TRUE? The answer is: all
of the above.
Here are the 3 variants:
* {{Sarg(10, 20; NULL AS TRUE)}} returns TRUE if x is NULL, and is equivalent
to SQL {{x IS NULL OR x IN (10, 20)}}
* {{Sarg(10, 20)}} returns UNKNOWN if x is NULL, and is equivalent to SQL {{x
IN (10, 20)}}
* {{Sarg(10, 20; NULL AS FALSE)}} returns FALSE if x is NULL, and is equivalent
to SQL {{x IS NOT NULL AND (x IN (10, 20))}}
Currently {{class Sarg}} has a field {{boolean containsNull}} which deals with
the first two cases. Changing {{boolean containsNull}} to {{RexUnknownAs
nullAs}} (which has 3 values) will allow us to represent the third. The new
representation is symmetrical under negation, which de Morgan's law suggests is
a good thing.
We also introduce 6 special values (6 because of the 3 truth values multiplied
by all/none ranges):
* FALSE returns FALSE for all null and not-null values;
* IS_NOT_NULL returns TRUE for all not-null values, FALSE for null;
* IS_NULL returns FALSE for all not-null values, TRUE for null;
* TRUE returns TRUE for all null and not-null values;
* NOT_EQUALS returns FALSE for all not-null values, UNKNOWN for null;
* EQUALS returns TRUE for all not-null values, UNKNOWN for null.
was:
Implement three-valued logic for SEARCH operator.
Consider the expression {{x IN (10, 20)}}, which we might represent as
{{SEARCH(x, Sarg(10, 20))}}. Suppose we invoke this with a value of {{NULL}}
for {{x}}. Do we want it to return UNKNOWN, FALSE or TRUE? The answer is: all
of the above.
Here are the 3 variants:
* {{Sarg(10, 20, UNKNOWN AS TRUE)}}: {{x IS NULL OR x IN (10, 20)}} → TRUE
* {{Sarg(10, 20, UNKNOWN AS UNKNOWN)}}: {{x IN (10, 20)}} → UNKNOWN
* {{Sarg(10, 20, UNKNOWN AS FALSE)}}: {{x IS NOT NULL AND (x IN (10, 20))}}
→ FALSE
Currently {{class Sarg}} has a field {{boolean containsNull}} which deals with
the first two cases. Changing {{boolean containsNull}} to {{RexUnknownAs
unknownAs}} (which has 3 values) will allow us to represent the third. The new
representation is symmetrical under negation, which de Morgan's law suggests is
a good thing.
> Implement three-valued logic for SEARCH operator
> -------------------------------------------------
>
> Key: CALCITE-4446
> URL: https://issues.apache.org/jira/browse/CALCITE-4446
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Blocker
> Labels: pull-request-available
> Fix For: 1.27.0
>
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> Implement three-valued logic for SEARCH operator.
> Consider the expression {{x IN (10, 20)}}, which we might represent as
> {{SEARCH(x, Sarg(10, 20))}}. Suppose we invoke this with a value of {{NULL}}
> for {{x}}. Do we want it to return UNKNOWN, FALSE or TRUE? The answer is: all
> of the above.
> Here are the 3 variants:
> * {{Sarg(10, 20; NULL AS TRUE)}} returns TRUE if x is NULL, and is equivalent
> to SQL {{x IS NULL OR x IN (10, 20)}}
> * {{Sarg(10, 20)}} returns UNKNOWN if x is NULL, and is equivalent to SQL
> {{x IN (10, 20)}}
> * {{Sarg(10, 20; NULL AS FALSE)}} returns FALSE if x is NULL, and is
> equivalent to SQL {{x IS NOT NULL AND (x IN (10, 20))}}
> Currently {{class Sarg}} has a field {{boolean containsNull}} which deals
> with the first two cases. Changing {{boolean containsNull}} to {{RexUnknownAs
> nullAs}} (which has 3 values) will allow us to represent the third. The new
> representation is symmetrical under negation, which de Morgan's law suggests
> is a good thing.
> We also introduce 6 special values (6 because of the 3 truth values
> multiplied by all/none ranges):
> * FALSE returns FALSE for all null and not-null values;
> * IS_NOT_NULL returns TRUE for all not-null values, FALSE for null;
> * IS_NULL returns FALSE for all not-null values, TRUE for null;
> * TRUE returns TRUE for all null and not-null values;
> * NOT_EQUALS returns FALSE for all not-null values, UNKNOWN for null;
> * EQUALS returns TRUE for all not-null values, UNKNOWN for null.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
