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

["Jiajun Xie (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5637?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17709947#comment-17709947 ] 

Jiajun Xie commented on CALCITE-5637:
-------------------------------------

I think MySQL result that contain two row is error.

 

If you use other aggregate function in mysql
{code:java}
SELECT MAX(t0.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t0 ON EMP.SAL = t0.SAL; {code}
the result is one row.
||MAX(t0.SAL)||
|0|

 

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