[
https://issues.apache.org/jira/browse/CALCITE-7006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17950629#comment-17950629
]
Mihai Budiu commented on CALCITE-7006:
--------------------------------------
Are you saying that the code generation for IS DISTINCT FROM is wrong for
BigDecimal typed-values only?
[~suibianwanwan33] using a WITH statement infers the column types from the
VALUES statement, and thus does not have the same types as when using a table.
(You can add some CASTs to VALUES to force them to have the right types).
[~suibianwanwan33] do you know how to inspect the generated Java code, shown
here by [~jensen] ?
You can display the value of the variable executable from
RexExecutorImpl.reduce.
> 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
>
> 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)
