[CALCITE-4352] RelBuilder/RexSimplify/Sarg incorrectly transforms complex expressions with IS NULL/IS NOT NULL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available

Environment

All environments

• Assignee: Unassigned
• Reporter: Igor Lozynskyi
• Created: 23/Oct/20 09:00
• Updated: 03/Jun/21 22:31
• Resolved: 01/Nov/20 17:48

Description

Now, the following query with IS NOT NULL:

SELECT *
 FROM emp
 WHERE deptno > 20 AND deptno < 30 AND mgr IS NOT NULL

Is simplified to:

SELECT *
 FROM emp
 WHERE deptno > 20 AND deptno < 30

It seems that the simplification of complex `AND` filters incorrectly simplify `IS NOT NULL` to `TRUE`.
During simplification, `IS NOT NULL` is transformed to `SEARCH($3, Sarg[(-∞..+∞), null])` instead of `SEARCH($3, Sarg[(-∞..+∞)])`.

Also, the following query with IS NULL:

SELECT *
FROM emp
WHERE deptno > 20 AND deptno < 30 AND mgr IS NULL

Is simplified to the following query:

SELECT * 
FROM emp 
WHERE deptno > 20 AND deptno < 30

However, now it is correct when transformed into a Sarg, but incorrect after `RexUtil.expandSearch()` expansion.

 

A simpler query does not show this issue (stays the same):

SELECT *
 FROM emp
 WHERE mgr IS NOT NULL

The patch with the corresponding test cases is in attachments.

 

Issue Links

• is depended upon by: CALCITE-4364 Simplify "a IN (1, 2) AND a = 1" to "a = 1"
• is related to: CALCITE-4173 Add internal SEARCH operator and Sarg literal, replacing use of IN in RexCall
• links to: GitHub Pull Request #2233