[jira] [Reopened] (CALCITE-5528) RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one

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

     [ https://issues.apache.org/jira/browse/CALCITE-5528?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pinhan Zhao reopened CALCITE-5528:
----------------------------------

> 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
>            Priority: Blocker
>
> [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)