[jira] [Commented] (CALCITE-5516) RelOptRulesTest AggregateReduceFunctionsRule optimized plan not semantically equivalent to the original one after conversion

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17684981#comment-17684981 ] 

Julian Hyde commented on CALCITE-5516:
--------------------------------------

The aggregate functions are different -- one has a {{WITHIN DISTINCT}} clause. Maybe the only bug here is the translation to Postgres dialect. Postgres doesn't support {{WITHIN DISTINCT}} so probably translation should fail.

> RelOptRulesTest AggregateReduceFunctionsRule 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
>          Components: core
>    Affects Versions: 1.32.0
>            Reporter: Pinhan Zhao
>            Priority: Blocker
>
> After converting the "plan before" and "plan after" of testReduceWithNonTypePredicate (https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/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}
>  
> The code we used to convert the plans to PostgreSQL queries is:
> {code:java}
> converter.visitRoot(rel).asStatement().toSqlString(PostgresqlSqlDialect.DEFAULT).getSql().replace('\n', ''){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)