[
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)
