Pinhan Zhao created CALCITE-5528: ------------------------------------ Summary: RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one Key: CALCITE-5528 URL: https://issues.apache.org/jira/browse/CALCITE-5528 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#L5046] After converting the testPushAggregateThroughJoin7'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 ANY_VALUE(t0.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t0 ON EMP.SAL = t0.SAL{code} and {code:sql} SELECT ANY_VALUE(t4.SAL) FROM (SELECT SAL FROM EMP GROUP BY SAL) AS t3 INNER JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t4 ON t3.SAL = t4.SAL{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,'0'); INSERT INTO DEPT VALUES (-1,'0'); --------EMP---------- CREATE TABLE EMP ( EMPNO INTEGER PRIMARY KEY, DEPTNO INTEGER REFERENCES DEPT(DEPTNO), ENAME VARCHAR(20), JOB VARCHAR(20), MGR INTEGER, HIREDATE DATE, SAL INTEGER, COMM INTEGER, SLACKER BOOLEAN ); INSERT INTO EMP VALUES (0 , -1 , NULL , NULL , NULL , NULL , 0 , 0 , True); INSERT INTO EMP VALUES (-1 , 0 , NULL , '0' , NULL , NULL , 0 , 0 , False);{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)