[
https://issues.apache.org/jira/browse/CALCITE-5528?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17700904#comment-17700904
]
Benchao Li commented on CALCITE-5528:
-------------------------------------
[~pinhan] If have marked "fixed" for this issue. Could you clarify which
version/commit fixes this? If the problem does not exist, we need to mark it as
"Not a problem".
> 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)
