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

["Igor Lozynskyi (Jira)" <ji...@apache.org>]

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)