Pinhan Zhao created CALCITE-5527:
------------------------------------
Summary: RelOptRulesTest testAnyInProjectNullable optimized query
not equivalent to the original one
Key: CALCITE-5527
URL: https://issues.apache.org/jira/browse/CALCITE-5527
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#L6114
After converting the testAnyInProjectNullable'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 DEPTNO, NAME IN (SELECT MGR FROM EMP) FROM DEPT{code}
and
{code:sql}
SELECT DEPT0.DEPTNO, t5.i IS NOT NULL AND t2.c <> 0 OR t2.ck < t2.c AND NULL
AND t2.c <> 0 AND t5.i IS NULL FROM DEPT AS DEPT0 CROSS JOIN (SELECT COUNT(*)
AS c, COUNT(MGR) AS ck FROM EMP) AS t2 LEFT JOIN (SELECT EMP1.MGR, t3.i FROM
EMP AS EMP1, (VALUES (TRUE)) AS t3 (i) GROUP BY EMP1.MGR, t3.i) AS t5 ON
DEPT0.NAME = t5.MGR{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,NULL);
INSERT INTO DEPT VALUES (-1,'2');
--------EMP----------
CREATE TABLE EMP (
EMPNO INTEGER PRIMARY KEY,
DEPTNO INTEGER REFERENCES DEPT(DEPTNO),
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR VARCHAR(20),
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
SLACKER BOOLEAN
);
INSERT INTO EMP VALUES (0 , -1 , '0' , '0' , 1 , '2000-01-01' , 0 , 0 , True);
INSERT INTO EMP VALUES (1 , -1 , '0' , '0' , 0 , NULL , 0 , NULL , False);{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
