[CALCITE-1794] Expressions with numeric comparisons are not simplified when CAST is present

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.29.0
• Fix Version/s: 1.30.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: Remus Rusanu
• Created: 16/May/17 15:21
• Updated: 27/Feb/24 22:23
• Resolved: 03/Mar/22 03:23

Description

For example:

x = 1 and x <> 2

In optimizer procedure, We pull up "x = 1" first, then when we encounter "x <> 2", can be simplified to "x = 1". So the result will be "x = 1"

x <> 2 and x = 1

When we pull up "x <> 2" first, it can't optimize the "x = 1".  So we need SARG to optimize it again.

Because EQUALS and NOT-EQUALS operator will make the left RexNode become [CAST($t0): INTEGER NOT NULL] when the left node and right node have a least restrictive type.  So we need to handle the CAST operator.

Issue Links

• is related to: CALCITE-4173 Add internal SEARCH operator and Sarg literal, replacing use of IN in RexCall
• is related to: CALCITE-1790 Simplify CASE P1 THEN <boolean> P@ THEN <booleans> ... ELSE TRUE/FALSE
• links to: GitHub Pull Request #2727
• links to: GitHub Pull Request #2736