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

["Pinhan Zhao (Jira)" <ji...@apache.org>]

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

Pinhan Zhao edited comment on CALCITE-5516 at 2/7/23 12:07 AM:
---------------------------------------------------------------

The pertinent method originally in RelOptRulesTest is testReduceWithNonTypePredicate(), which I can copy here:
{code:java}
@Test void testReduceWithNonTypePredicate() {
  // Make sure we can reduce with more specificity than just agg function type.
  final RelOptRule rule = AggregateReduceFunctionsRule.Config.DEFAULT
      .withExtraCondition(call -> call.distinctKeys != null)
      .toRule();
  final String sql = "select avg(sal), avg(sal) within distinct (deptno)\n"
      + "from emp";
  sql(sql).withRule(rule).check();
}{code}


was (Author: JIRAUSER298853):
The pertinent method name originally in RelOptRulesTest is testReduceWithNonTypePredicate(), which I can copy here:
{code:java}
@Test void testReduceWithNonTypePredicate() {
  // Make sure we can reduce with more specificity than just agg function type.
  final RelOptRule rule = AggregateReduceFunctionsRule.Config.DEFAULT
      .withExtraCondition(call -> call.distinctKeys != null)
      .toRule();
  final String sql = "select avg(sal), avg(sal) within distinct (deptno)\n"
      + "from emp";
  sql(sql).withRule(rule).check();
}{code}

> 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
>          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/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}
>  
> 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)