[CALCITE-4805] Calcite should convert a small IN-list as if the user had written OR, even if the IN-list contains NULL
Since [CALCITE-373], Calcite has converted "x IN (1, 2)" to
"x = 1 OR x = 2" but it still converts "x IN (1, NULL)" to
"x IN (VALUES 1, NULL)". Now that some bugs have been fixed,
there's no longer any reason to treat lists with NULL any
differently.
Close apache/calcite#2545
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 7738a29..e1e1e1f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1139,8 +1139,7 @@
if (query instanceof SqlNodeList) {
SqlNodeList valueList = (SqlNodeList) query;
- if (!containsNullLiteral(valueList)
- && valueList.size() < config.getInSubQueryThreshold()) {
+ if (valueList.size() < config.getInSubQueryThreshold()) {
// We're under the threshold, so convert to OR.
subQuery.expr =
convertInToOr(
@@ -1414,18 +1413,6 @@
}
}
- private static boolean containsNullLiteral(SqlNodeList valueList) {
- for (SqlNode node : valueList) {
- if (node instanceof SqlLiteral) {
- SqlLiteral lit = (SqlLiteral) node;
- if (lit.getValue() == null) {
- return true;
- }
- }
- }
- return false;
- }
-
/**
* Determines if a sub-query is non-correlated and if so, converts it to a
* constant.
diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index 24e3662..84084b6 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3083,4 +3083,178 @@
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-4805] Calcite should convert a small IN-list as if the
+# user had written OR, even if the IN-list contains NULL.
+
+# The IN-list contains partial null value.
+select * from "scott".emp where comm in (300, 500, null);
+
++-------+-------+----------+------+------------+---------+--------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+----------+------+------------+---------+--------+--------+
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
++-------+-------+----------+------+------------+---------+--------+--------+
+(2 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[Sarg[300:DECIMAL(7, 2), 500:DECIMAL(7, 2)]:DECIMAL(7, 2)], expr#9=[SEARCH($t6, $t8)], proj#0..7=[{exprs}], $condition=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Previous, as scalar sub-query.
+select *, comm in (300, 500, null) as i from "scott".emp;
+
++-------+--------+-----------+------+------------+---------+---------+--------+------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | I |
++-------+--------+-----------+------+------------+---------+---------+--------+------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | true |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
++-------+--------+-----------+------+------------+---------+---------+--------+------+
+(14 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[Sarg[300:DECIMAL(7, 2), 500:DECIMAL(7, 2)]:DECIMAL(7, 2)], expr#9=[SEARCH($t6, $t8)], expr#10=[null:BOOLEAN], expr#11=[OR($t9, $t10)], proj#0..7=[{exprs}], I=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# As above, but NOT IN.
+select * from "scott".emp where comm not in (300, 500, null);
+
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableValues(tuples=[[]])
+!plan
+
+# Previous, as scalar sub-query.
+select *, comm not in (300, 500, null) as i from "scott".emp;
+
++-------+--------+-----------+------+------------+---------+---------+--------+-------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | I |
++-------+--------+-----------+------+------------+---------+---------+--------+-------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
++-------+--------+-----------+------+------------+---------+---------+--------+-------+
+(14 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[Sarg[(-∞..300:DECIMAL(7, 2)), (300:DECIMAL(7, 2)..500:DECIMAL(7, 2)), (500:DECIMAL(7, 2)..+∞)]:DECIMAL(7, 2)], expr#9=[SEARCH($t6, $t8)], expr#10=[null:BOOLEAN], expr#11=[AND($t9, $t10)], proj#0..7=[{exprs}], I=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# The IN-list only contains null value.
+select * from "scott".emp where empno in (null);
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableValues(tuples=[[]])
+!plan
+
+# Previous, as scalar sub-query.
+select *, empno in (null) as i from "scott".emp;
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | I |
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+(14 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[null:BOOLEAN], proj#0..8=[{exprs}])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# As above, but NOT IN.
+select * from "scott".emp where empno not in (null);
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableValues(tuples=[[]])
+!plan
+
+# Previous, as scalar sub-query.
+select *, empno not in (null) as i from "scott".emp;
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | I |
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
++-------+--------+-----------+------+------------+---------+---------+--------+---+
+(14 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[null:BOOLEAN], proj#0..8=[{exprs}])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# End sub-query.iq
