[jira] [Created] (CALCITE-5527) RelOptRulesTest testAnyInProjectNullable optimized query not equivalent to the original one

["Pinhan Zhao (Jira)" <ji...@apache.org>]

Pinhan Zhao created CALCITE-5527:
------------------------------------

             Summary: RelOptRulesTest testAnyInProjectNullable optimized query not equivalent to the original one
                 Key: CALCITE-5527
                 URL: https://issues.apache.org/jira/browse/CALCITE-5527
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.32.0
            Reporter: Pinhan Zhao


https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L6114

After converting the testAnyInProjectNullable's original plan and optimized plan to PostgreSQL using
{code:java}
converter.visitRoot(rel).asStatement().toSqlString(PostgresqlSqlDialect.DEFAULT).getSql().replace('\n', '');
{code}
, we have queries:
 
{code:sql}
SELECT DEPTNO, NAME IN (SELECT MGR FROM EMP) FROM DEPT{code}

and

{code:sql}
SELECT DEPT0.DEPTNO, t5.i IS NOT NULL AND t2.c <> 0 OR t2.ck < t2.c AND NULL AND t2.c <> 0 AND t5.i IS NULL FROM DEPT AS DEPT0 CROSS JOIN (SELECT COUNT(*) AS c, COUNT(MGR) AS ck FROM EMP) AS t2 LEFT JOIN (SELECT EMP1.MGR, t3.i FROM EMP AS EMP1, (VALUES (TRUE)) AS t3 (i) GROUP BY EMP1.MGR, t3.i) AS t5 ON DEPT0.NAME = t5.MGR{code}

However, they are semantically different with the following counterexample being able to distinguish them:
{code:sql}
--------DEPT----------
CREATE TABLE DEPT (
DEPTNO INTEGER PRIMARY KEY,
NAME VARCHAR(20)
);
INSERT INTO DEPT VALUES (0,NULL);
INSERT INTO DEPT VALUES (-1,'2');
--------EMP----------
CREATE TABLE EMP (
EMPNO INTEGER PRIMARY KEY,
DEPTNO INTEGER REFERENCES DEPT(DEPTNO),
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR VARCHAR(20),
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
SLACKER BOOLEAN
);
INSERT INTO EMP VALUES (0 , -1 , '0' , '0' , 1 , '2000-01-01' , 0 , 0 , True);
INSERT INTO EMP VALUES (1 , -1 , '0' , '0' , 0 , NULL , 0 , NULL , False);{code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)