# Implement three-valued logic for SEARCH operator

XMLWordPrintableJSON

#### Details

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

#### People

Unassigned
Julian Hyde
0 Vote for this issue
Watchers:
4 Start watching this issue

#### Dates

Created:
Updated:
Resolved:

#### Time Tracking

Estimated:
Not Specified
Remaining:
0h
Logged:
2h 50m