NobiGo commented on a change in pull request #2607:
URL: https://github.com/apache/calcite/pull/2607#discussion_r748787869
##########
File path: core/src/test/resources/sql/sub-query.iq
##########
@@ -3306,4 +3306,28 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7782],
expr#9=[CAST($t0):INTEGER NO
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-4846] IN-list that includes NULL converted to Values throws
exception
+
+select * from "scott".emp where empno not in (null, 7782);
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0:BIGINT], expr#14=[=($t8,
$t13)], expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[AND($t15,
$t16)], expr#18=[OR($t14, $t17)], proj#0..7=[{exprs}], $condition=[$t18])
+ EnumerableMergeJoin(condition=[=($10, $11)], joinType=[left])
+ EnumerableSort(sort0=[$10], dir0=[ASC])
+ EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ EnumerableValues(tuples=[[{ null }, { 7782 }]])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableValues(tuples=[[{ null }, { 7782 }]])
Review comment:
The Join duplication is not related to the fix. When I try to execute:
`select * from "scott".emp where (empno, deptno) not in ((7369, 20), (7499,
30));`
This SQL also has two joins. And return the right answer. So I think this is
the right logic. (Unless we find a wrong unit test).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
