Pinhan Zhao created CALCITE-5528:
------------------------------------
Summary: RelOptRulesTest testPushAggregateThroughJoin7 optimized
query not equivalent to the original one
Key: CALCITE-5528
URL: https://issues.apache.org/jira/browse/CALCITE-5528
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Pinhan Zhao
[https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L5046]
After converting the testPushAggregateThroughJoin7's original plan and
optimized plan to PostgreSQL using
{code:java}
converter.visitRoot(rel).asStatement().toSqlString(PostgresqlSqlDialect.DEFAULT).getSql().replace('\n',
'');
{code}
, we have queries:
{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, they are semantically different with the following counterexample
being able to distinguish them:
{code:sql}
--------DEPT----------
CREATE TABLE DEPT (
DEPTNO INTEGER PRIMARY KEY,
NAME VARCHAR(20)
);
INSERT INTO DEPT VALUES (0,'0');
INSERT INTO DEPT VALUES (-1,'0');
--------EMP----------
CREATE TABLE EMP (
EMPNO INTEGER PRIMARY KEY,
DEPTNO INTEGER REFERENCES DEPT(DEPTNO),
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INTEGER,
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
SLACKER BOOLEAN
);
INSERT INTO EMP VALUES (0 , -1 , NULL , NULL , NULL , NULL , 0 , 0 , True);
INSERT INTO EMP VALUES (-1 , 0 , NULL , '0' , NULL , NULL , 0 , 0 ,
False);{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
