vlsi commented on a change in pull request #2607:
URL: https://github.com/apache/calcite/pull/2607#discussion_r750219731
##########
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:
When values are non-nullable, then the double join is not really needed.
A single anti-join should be enough.
I have filed https://issues.apache.org/jira/browse/CALCITE-4889
--
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]
