[CALCITE-4173] Add internal SEARCH operator and Sarg literal, replacing use of IN in RexCall

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.26.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 13/Aug/20 07:49
• Updated: 03/May/24 15:25
• Resolved: 04/Sep/20 19:45

Description

Add internal SEARCH operator and Sarg literal, replacing use of IN in RexCall.

Recently we started to allow IN in RexCalls to represent lists of constant values; previously it had only been allowed in SqlCall. The use is confusing, because a RexSubQuery is a sub-class of RexCall that may also use IN as its operator.

More important, we would like to be able to represent more general search arguments in a single RexCall. Examples:

• x BETWEEN 3 AND 10
• x > 3 AND x <= 10
• x NOT BETWEEN 3 AND 10
• x IS NULL OR x BETWEEN 3 AND 100 AND x != 50 OR x IN (200, 300, 400)
• x > 5 AND x < 10 AND x IN (3, 8, 10, 20)

All of these can be converted to sets of ranges, where each range has a lower or upper bound (or both), bounds can be open or closed, plus a flag to say whether NULL is an allowed value. Guava's RangeSet is an efficient implementation of range sets.

This change would create a new class Sarg to represent a range set as a literal. The new internal SEARCH operator tests whether an operand belongs to the range set.

A RexCall to SEARCH is converted back to SQL, typically an IN or OR.

This change would obsolete the use of IN in RexCall to represent a fixed list of values. (This is a breaking change, but when we first allowed IN in RexCall, in CALCITE-2444, it was only intended to be for RelToSqlConverter. Most people still believe that IN is not allowed.)

Issue Links

• causes: CALCITE-4237 AssertionError in SqlTypeFactoryImpl.leastRestrictive when running slow tests
• causes: CALCITE-4279 SEARCH operator cannot be pushed into Druid
• is related to: CALCITE-2444 Handle IN expressions when converting SqlNode to SQL
• is related to: CALCITE-2792 Stackoverflow while evaluating filter with large number of OR conditions
• is related to: CALCITE-3747 Constructing BETWEEN with RelBuilder throws class cast exception
• is related to: CALCITE-4155 Simplify IN expression of discrete values
• is related to: CALCITE-2630 Convert SqlInOperator to In-Expression
• is related to: HIVE-27102 Upgrade Calcite to 1.33.0 and Avatica to 1.23.0
• relates to: CALCITE-4405 SEARCH operator (with Sarg literal) handles UNKNOWN incorrectly
• relates to: CALCITE-4325 RexSimplify/Sarg incorrectly simplifies complex expressions with NULL
• relates to: CALCITE-4352 RelBuilder/RexSimplify/Sarg incorrectly transforms complex expressions with IS NULL/IS NOT NULL
• relates to: CALCITE-1794 Expressions with numeric comparisons are not simplified when CAST is present
• links to: GitHub Pull Request #2124
• links to: GitHub Pull Request #2141