[jira] [Commented] (CALCITE-4446) Implement three-valued logic for SEARCH operator

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4446?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17256678#comment-17256678 ] 

Julian Hyde commented on CALCITE-4446:
--------------------------------------

After this change, there will be 3 special Sarg values (implemented by {{class SpecialSarg}}, a private sub-class of {{class Sarg}}) whose range is empty and therefore return {{FALSE}} for all non-{{NULL}} values, and respectively return {{FALSE}}, {{TRUE}}, {{UNKNOWN}} for the {{NULL}} value. Similarly, there are 3 special Sarg values whose range is everything and therefore return {{TRUE}} for all non-{{NULL}} values, and respectively return {{FALSE}}, {{TRUE}}, {{UNKNOWN}} for the {{NULL}} value.

Consider the expression "{{x < 3 AND x > 5}}". It evaluates to {{FALSE}} for all non-{{NULL}} values of {{x}}, and {{UNKNOWN}} if {{x}} is {{NULL}}. We simplify to one of the aforementioned special Sarg values.

How should we convert that Sarg value back to SQL? We could generate "{{CASE WHEN x IS NULL THEN UKNOWN ELSE FALSE END}}" but we instead generate the more concise {{"x <> x"}}.

> 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: Major
>
> 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)}} &rarr; TRUE
> * {{Sarg(10, 20, UNKNOWN AS UNKNOWN)}}: {{x IN (10, 20)}} &rarr; UNKNOWN
> * {{Sarg(10, 20, UNKNOWN AS FALSE)}}: {{x IS NOT NULL AND (x IN (10, 20))}} &rarr; 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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)