Pinhan Zhao created CALCITE-5516:
------------------------------------
Summary: RelOptRulesTest optimized plan not semantically
equivalent to the original one after conversion
Key: CALCITE-5516
URL: https://issues.apache.org/jira/browse/CALCITE-5516
Project: Calcite
Issue Type: Bug
Reporter: Pinhan Zhao
After converting the "plan before" and "plan after" of
testReduceWithNonTypePredicate
([https://github.com/apache/calcite/blob/main/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L6820])
to PostgreSQL queries, we obtained two queries:
{code:java}
SELECT AVG(SAL), AVG(SAL) FROM EMP{code}
and
{code:java}
SELECT AVG(SAL), CAST(CASE WHEN COUNT(*) = 0 THEN NULL ELSE COALESCE(SUM(SAL),
0) END / COUNT(*) AS INTEGER) FROM EMP{code}
However, these queries are not semantically equivalent. With a counterexample
below:
{code:java}
--------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,0,'1','0',0,'2000-01-01',NULL,0,True);
INSERT INTO EMP VALUES (-1,1,'0','0',0,'2000-01-01',NULL,0,True);{code}
we can have two queries produce different output:
{code:java}
avg | avg
-----+-----
|
(1 row) {code}
(output of the plan before optimization, with converted to PostgreSQL query)
{code:java}
avg | int4
-----+------
| 0
(1 row)
{code}
(output of the plan after optimization, with converted to PostgreSQL query)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
