[
https://issues.apache.org/jira/browse/CALCITE-7006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17950672#comment-17950672
]
Zhen Chen commented on CALCITE-7006:
------------------------------------
I executed your SQL in join.iq and the result is as follows:
{code:java}
SELECT *
FROM (
SELECT EMPNO, COMM, COMM + 1000 AS e1
FROM EMP
) e_outer
LEFT JOIN (
SELECT e0, TRUE AS EXISTS_FLAG
FROM (
SELECT t1.EMPNO, t1.e1, t2.e0
FROM (
SELECT EMPNO, COMM + 100 AS e1, COMM
FROM EMP
) t1
JOIN (
SELECT COMM + 1000 AS e0
FROM EMP
GROUP BY COMM + 1000
) t2
ON t1.COMM IS NULL OR t1.e1 > t2.e0
) j
GROUP BY e0
) subq
ON e_outer.e1 is not distinct from subq.e0;
EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($2, $3)],
joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6, $t8)],
EMPNO=[$t0], COMM=[$t6], E1=[$t9])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
EnumerableAggregate(group=[{3}])
EnumerableNestedLoopJoin(condition=[OR(IS NULL($2), >($1, $3))],
joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[100], expr#9=[+($t6,
$t8)], EMPNO=[$t0], E1=[$t9], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6,
$t8)], E0=[$t9])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+---------+---------+---------+-------------+
| EMPNO | COMM | E1 | E0 | EXISTS_FLAG |
+-------+---------+---------+---------+-------------+
| 7369 | | | | true |
| 7499 | 300.00 | 1300.00 | 1300.00 | true |
| 7521 | 500.00 | 1500.00 | 1500.00 | true |
| 7566 | | | | true |
| 7654 | 1400.00 | 2400.00 | 2400.00 | true |
| 7698 | | | | true |
| 7782 | | | | true |
| 7788 | | | | true |
| 7839 | | | | true |
| 7844 | 0.00 | 1000.00 | 1000.00 | true |
| 7876 | | | | true |
| 7900 | | | | true |
| 7902 | | | | true |
| 7934 | | | | true |
+-------+---------+---------+---------+-------------+
(14 rows)
!ok {code}
> Incorrect left join results with IS NOT DISTINCT FROM under specific plan
> -------------------------------------------------------------------------
>
> Key: CALCITE-7006
> URL: https://issues.apache.org/jira/browse/CALCITE-7006
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> Priority: Major
> Labels: pull-request-available
>
> I forcibly applied the EnumerableNestedLoopJoin rule to make the execution
> plans identical for {{=}} and {{{}is not distinct from{}}}. Here are test in
> Quidem:
> {code:java}
> SELECT *
> FROM (
> SELECT EMPNO, COMM, COMM + 1000 AS e1
> FROM EMP
> ) e_outer
> LEFT JOIN (
> SELECT e0, TRUE AS EXISTS_FLAG
> FROM (
> SELECT t1.EMPNO, t1.e1, t2.e0
> FROM (
> SELECT EMPNO, COMM + 100 AS e1, COMM
> FROM EMP
> ) t1
> JOIN (
> SELECT COMM + 1000 AS e0
> FROM EMP
> GROUP BY COMM + 1000
> ) t2
> ON t1.COMM IS NULL OR t1.e1 > t2.e0
> ) j
> GROUP BY e0
> ) subq
> ON e_outer.e1 = subq.e0;
> EMPNO | COMM | E1 | E0 | EXISTS_FLAG
> -------+---------+---------+---------+-------------
> 7369 | | | | null
> 7499 | 300.00 | 1300.00 | 1300.00 | true
> 7521 | 500.00 | 1500.00 | 1500.00 | true
> 7566 | | | | null
> 7654 | 1400.00 | 2400.00 | 2400.00 | true
> 7698 | | | | null
> 7782 | | | | null
> 7788 | | | | null
> 7839 | | | | null
> 7844 | 0.00 | 1000.00 | 1000.00 | true
> 7876 | | | | null
> 7900 | | | | null
> 7902 | | | | null
> 7934 | | | | null
> (14 rows)
> EnumerableNestedLoopJoin(condition=[=($2, $3)], joinType=[left])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6, $t8)],
> EMPNO=[$t0], COMM=[$t6], E1=[$t9])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
> EnumerableAggregate(group=[{3}])
> EnumerableNestedLoopJoin(condition=[OR(IS NULL($2), >($1, $3))],
> joinType=[inner])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[100], expr#9=[+($t6,
> $t8)], EMPNO=[$t0], E1=[$t9], COMM=[$t6])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableAggregate(group=[{0}])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6,
> $t8)], E0=[$t9])
> EnumerableTableScan(table=[[scott, EMP]]) {code}
> Replace = with is not distinct from:
> {code:java}
> SELECT *
> FROM (
> SELECT EMPNO, COMM, COMM + 1000 AS e1
> FROM EMP
> ) e_outer
> LEFT JOIN (
> SELECT e0, TRUE AS EXISTS_FLAG
> FROM (
> SELECT t1.EMPNO, t1.e1, t2.e0
> FROM (
> SELECT EMPNO, COMM + 100 AS e1, COMM
> FROM EMP
> ) t1
> JOIN (
> SELECT COMM + 1000 AS e0
> FROM EMP
> GROUP BY COMM + 1000
> ) t2
> ON t1.COMM IS NULL OR t1.e1 > t2.e0
> ) j
> GROUP BY e0
> ) subq
> ON e_outer.e1 is not distinct from subq.e0;
> EMPNO | COMM | E1 | E0 | EXISTS_FLAG
> -------+---------+---------+----+-------------
> 7369 | | | | true
> 7499 | 300.00 | 1300.00 | | null
> 7521 | 500.00 | 1500.00 | | null
> 7566 | | | | true
> 7654 | 1400.00 | 2400.00 | | null
> 7698 | | | | true
> 7782 | | | | true
> 7788 | | | | true
> 7839 | | | | true
> 7844 | 0.00 | 1000.00 | | null
> 7876 | | | | true
> 7900 | | | | true
> 7902 | | | | true
> 7934 | | | | true
> (14 rows)
> EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($2, $3)],
> joinType=[left])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6, $t8)],
> EMPNO=[$t0], COMM=[$t6], E1=[$t9])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
> EnumerableAggregate(group=[{3}])
> EnumerableNestedLoopJoin(condition=[OR(IS NULL($2), >($1, $3))],
> joinType=[inner])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[100], expr#9=[+($t6,
> $t8)], EMPNO=[$t0], E1=[$t9], COMM=[$t6])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableAggregate(group=[{0}])
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1000], expr#9=[+($t6,
> $t8)], E0=[$t9])
> EnumerableTableScan(table=[[scott, EMP]]) {code}
> From the plan, aside from IS NOT DISTINCT FROM, the plans are identical.
> However, the IS NOT DISTINCT FROM plan failed to join non-NULL data
> successfully. I tried simplifying the SQL but couldn't reproduce the issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
