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