[CALCITE-5759] 'SEARCH(1, Sarg[IS NOT NULL])' should be simplified to 'TRUE'

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Runkang He
• Reporter: Runkang He
• Created: 07/Jun/23 00:45
• Updated: 27/Jul/23 10:22
• Resolved: 14/Jun/23 07:13

Description

The minimal reproducer is to construct a RexCall with RexUnknownAs.FALSE as below:

SEARCH(1, Sarg[IS NOT NULL])

After RexSimplify#simplifySearch, the final simplified result is IS NOT NULL(1), which should be TRUE more thoroughly.

Also, the following RexCall with RexUnknownAs.TRUE:

SEARCH(1, Sarg[IS NULL])

Is simplified to IS NULL(1), which should be FALSE.

Another two cases:

The following RexCall with RexUnknownAs.UNKNOWN:

SEARCH(1, Sarg[=])

Is simplified to 1 IS NOT NULL OR NULL, which should be TRUE.

The following RexCall with RexUnknownAs.UNKNOWN:

SEARCH(1, Sarg[<>])

Is simplified to 1 <> 1, which should be FALSE.

Note that current simplified result is inconsistent with RexCall.isAlwaysFalse and isAlwaysTrue.

Therefore, for the above two reasons, I think we need to simplify SEARCH operator with special sarg more thoroughly.

Issue Links

• duplicates: CALCITE-4679 Search/sarg simplification leaves is not null(literal) unsimplified
• links to: GitHub Pull Request #3257