[ https://issues.apache.org/jira/browse/CALCITE-5527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17692433#comment-17692433 ]
Pinhan Zhao commented on CALCITE-5527: -------------------------------------- [~libenchao] Thank you for the clarification - we can confirm that given the NOT NULL constraints in this case, the plan is correct. Then the only real problem should be the bug with PSQL translation in [CALCITE-5516|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5516?filter=allopenissues] > 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 > Priority: Blocker > > 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)