[ https://issues.apache.org/jira/browse/CALCITE-5637?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17709948#comment-17709948 ]
Jiajun Xie commented on CALCITE-5637: ------------------------------------- The result in `agg.iq` is right {code:java} # [CALCITE-5637] RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one SELECT ANY_VALUE(t.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t ON EMP.SAL = t.SAL; +---------+ | EXPR$0 | +---------+ | 5000.00 | +---------+ (1 row) !ok {code} so the issue is not a bug for Calcite. > RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent > to the original one > ------------------------------------------------------------------------------------------------ > > Key: CALCITE-5637 > URL: https://issues.apache.org/jira/browse/CALCITE-5637 > Project: Calcite > Issue Type: Bug > Components: core > Reporter: Pinhan Zhao > Priority: Major > > [https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L5046] > We first converted plans to SQL queries, resulting in two queries below: > {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, with the DB: > {code:sql} > CREATE TABLE EMP ( > EMPNO INTEGER, > DEPTNO INTEGER, > ENAME VARCHAR(20), > JOB VARCHAR(20), > MGR INTEGER, > HIREDATE DATE, > SAL INTEGER, > COMM INTEGER, > SLACKER INTEGER > ); > INSERT INTO EMP VALUES (-1, 1, '2147483648', '-2147483649', 0, '1970-01-01', > 0, 0, 1); > INSERT INTO EMP VALUES (0, 1, '2147483648', '-2147483649', NULL, > '1970-01-01', 0, 0, 1);{code} > > the outputs of these two queries running on MySQL are different: > {code:sql} > ANY_VALUE(t0.SAL) > 0 > 0{code} > vs > {code:sql} > ANY_VALUE(t4.SAL) > 0{code} > -- This message was sent by Atlassian Jira (v8.20.10#820010)