[CALCITE-5169] 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Benchao Li
• Reporter: Benchao Li
• Created: 29/May/22 07:32
• Updated: 03/Aug/22 16:21
• Resolved: 15/Jun/22 01:42

Description

As described in the title, xx < 1 OR xx > 1 should be simplified to xx <> 1.

See discussion in ML: https://lists.apache.org/thread/hsdgx0jb3214v6k9p7slbkk3xsbl0b7f
The original problem is that ename <> '' and ename <> '3' could be simplified to Sarg while (ename < '' or ename > '') and (ename < '3' or ename > '3') cannot.
Since < and <> have different SqlOperandTypeChecker.Consistency, the type inference result is different for these two cases. We suspect that the type inference result leads to the difference of simplification.
However, the real reason is that xx < 1 OR xx > 1 cannot be simplified to xx <> 1 in simplification, which prevents (ename < '' or ename > '') and (ename < '3' or ename > '3') being simplified to ename <> '' and ename <> '3'.

Issue Links

• links to: GitHub Pull Request #2821