Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4446

Implement three-valued logic for SEARCH operator

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.27.0
    • Component/s: None

      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 internal constant Sarg values to deal with the 6 combinations of empty and full ranges (3 truth values multiplied by {all, none}):

      • 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_EQUAL returns FALSE for all not-null values, UNKNOWN for null;
      • EQUAL returns TRUE for all not-null values, UNKNOWN for null.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 50m
                  2h 50m