[jira] [Commented] (CALCITE-5527) RelOptRulesTest testAnyInProjectNullable optimized query not equivalent to the original one

["Benchao Li (Jira)" <ji...@apache.org>]

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

Benchao Li commented on CALCITE-5527:
-------------------------------------

[~pinhan] {{RelOptRulesTest}} is used to test whether the {{RelNode}} is correct after applying some rules. You are converting the {{RelNode}} back to SQL now, it involves another important step: {{RelToSqlConverter}}. 

Could you make this more specific, is the plan correct in {{RelOptRulesTest}}? Is this just a {{RelToSqlConverter}} problem?

The same to CALCITE-5528.

> 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
>            Priority: Blocker
>
> 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)