You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Pinhan Zhao (Jira)" <ji...@apache.org> on 2023/04/07 20:56:00 UTC
[jira] [Created] (CALCITE-5637) RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one
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)