[ https://issues.apache.org/jira/browse/CALCITE-4560?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17332519#comment-17332519 ]
Julian Hyde commented on CALCITE-4560: -------------------------------------- This PR, and the PR for CALCITE-4574, look great. Some very minor comments: * “Affects version” should be 1.26. 1.27 is not released yet. * Can you add <p> and <blockquote> tags to your javadoc? Otherwise it will flow together when rendered as HTML. * In addition to the planner tests, add at least one query in an existing .iq file. It is useful to make sure that these queries run and produce correct results. > Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate > ---------------------------------------------------------------------------- > > Key: CALCITE-4560 > URL: https://issues.apache.org/jira/browse/CALCITE-4560 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.27.0 > Reporter: Stamatis Zampetakis > Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The problem can be seen by adding the following test in > {{SqlToRelConverterTest}}. > {code:java} > @Test void testExistsCorrelatedDecorrelate01() { > final String sql = "select e1.empno from empnullables e1 where exists (\n" > + " select 1 from empnullables e2 where > COALESCE(e1.ename,'M')=COALESCE(e2.ename,'M'))"; > sql(sql).decorrelate(true).ok(); > } > {code} > The plan after decorrelation is shown below: > {noformat} > LogicalProject(EMPNO=[$0]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], ENAME0=[$9], > $f1=[CAST($10):BOOLEAN]) > LogicalJoin(condition=[=($1, $9)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ENAME0=[$9], $f0=[true]) > LogicalJoin(condition=[=(CASE(IS NOT NULL($9), $9, > 'M':VARCHAR(20)), CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT NULL, > 'M':VARCHAR(20)))], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) > LogicalAggregate(group=[{0}]) > LogicalProject(ENAME=[$1]) > LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) > {noformat} > The problem lies in the {{LogicalJoin(condition=[=($1, $9)], > joinType=[inner])}} operator. If there are rows with {{NULL}} values in the > {{ENAME}} column these are going to be incorrectly removed from the result > set. The COALESCE operator is present in the SQL query to ensure that rows > with NULL values are retained in the result. -- This message was sent by Atlassian Jira (v8.3.4#803005)