[
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750789#comment-17750789
]
Alessandro Solimando commented on CALCITE-5880:
-----------------------------------------------
[~leepb], it seems we need some help reproducing the problem, we can all agree
that the plans you share seem wrong under the context you describe (nullable
fields), but we need to double check the details, then possibly debug it, and
we can't do it if we can't reproduce the issue.
What [~shenlang] tried seems reasonable to me, but it does not seem to be
enough, I think that sharing a (minimal) reproducer would help moving the
discussion forward.
If you have one in your own downstream fork, you might consider even sharing
that if it's complicated to paste the code here.
> When the association condition of the association subquery is 'is not
> distinct from', the join condition becomes '=' after decorrelation.
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
> Issue Type: Bug
> Reporter: libopeng
> Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png,
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from
> dept.DEPTNO){code}
> before decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
> LogicalFilter(condition=[IS NOT NULL($9)])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], agg#0=[MIN($0)])
> LogicalProject($f0=[true])
> LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)),
> IS TRUE(=($cor0.EMPNO, $0)))]) <--- here is 't1.x is not distinct from
> t2.x'
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> after decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
> LogicalFilter(condition=[IS NOT NULL($9)])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
> LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left])
> <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false],
> EMPNO0=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
> LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
> LogicalJoin(condition=[=($2, $0)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(EMPNO=[$0], $f9=[false])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
>
> {code:java}
> EMP | DEPT
> +---------+ | +---------+
> | EMPNO | | | DEPTNO |
> +---------+ | +---------+
> | null | | | null |
> {code}
> expect result
> {code:java}
> EMPNO
> +-------+
> | null |
> +-------+{code}
> actual result
> {code:java}
> EMPNO
> +-------+
> +-------+ {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
