You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2021/09/30 19:25:15 UTC
[calcite] branch master updated: [CALCITE-4805] Calcite should
convert a small IN-list as if the user had written OR,
even if the IN-list contains NULL
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 5b8ce9c [CALCITE-4805] Calcite should convert a small IN-list as if the user had written OR, even if the IN-list contains NULL
5b8ce9c is described below
commit 5b8ce9c477dcf6ea4848b14982a11bd2e6ebe556
Author: NobiGo <no...@gmail.com>
AuthorDate: Sat Sep 25 23:05:54 2021 +0800
[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
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 15 +-
core/src/test/resources/sql/sub-query.iq | 174 +++++++++++++++++++++
2 files changed, 175 insertions(+), 14 deletions(-)
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 @@ public class SqlToRelConverter {
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 @@ public class SqlToRelConverter {
}
}
- 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 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t3)], proj#0..2=[{expr
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