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)