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)