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

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

Pinhan Zhao created CALCITE-5637:
------------------------------------

             Summary: 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


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