Igor Lozynskyi created CALCITE-4352: ---------------------------------------
Summary: RelBuilder/RexSimplify/Sarg incorrectly transforms complex expressions with IS NULL/IS NOT NULL Key: CALCITE-4352 URL: https://issues.apache.org/jira/browse/CALCITE-4352 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.27.0 Environment: All environments Reporter: Igor Lozynskyi Attachments: rex-simplify-issue-tests-3.patch Now, the following query with IS NOT NULL: {code:java} SELECT * FROM emp WHERE deptno > 20 AND deptno < 30 AND mgr IS NOT NULL{code} Is simplified to: {code:java} SELECT * FROM emp WHERE deptno > 20 AND deptno < 30{code} 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: {code:java} SELECT * FROM emp WHERE deptno > 20 AND deptno < 30 AND mgr IS NULL {code} Is simplified to the following query: {code:java} SELECT * FROM emp WHERE deptno > 20 AND deptno < 30 {code} 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): {code:java} SELECT * FROM emp WHERE mgr IS NOT NULL{code} The patch with the corresponding test cases is in attachments. -- This message was sent by Atlassian Jira (v8.3.4#803005)