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

Implement three-valued logic for SEARCH operator

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Fixed
    • None
    • 1.27.0
    • 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

              Unassigned Unassigned
              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