This is an automated email from the ASF dual-hosted git repository.
zhenchen pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 05347685da [CALCITE-7381] Parameters modified by !set must be restored
to their default values in Quidem test
05347685da is described below
commit 05347685dade4fcf91c830001af56900bd179798
Author: Zhen Chen <[email protected]>
AuthorDate: Sun Jan 18 21:04:28 2026 +0800
[CALCITE-7381] Parameters modified by !set must be restored to their
default values in Quidem test
---
.../org/apache/calcite/runtime/SqlFunctions.java | 5 +
.../org/apache/calcite/test/JdbcAdapterTest.java | 38 +-
core/src/test/resources/sql/agg.iq | 3 +
core/src/test/resources/sql/misc.iq | 151 ++-
core/src/test/resources/sql/planner.iq | 65 +
core/src/test/resources/sql/scalar.iq | 3 +
core/src/test/resources/sql/set-op.iq | 68 -
core/src/test/resources/sql/some.iq | 5 +-
core/src/test/resources/sql/sub-query.iq | 1303 +++++++++++---------
.../java/org/apache/calcite/test/QuidemTest.java | 11 +
10 files changed, 942 insertions(+), 710 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 28b34513e1..f9a74cec9a 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -232,6 +232,11 @@ public class SqlFunctions {
private static final TryThreadLocal<Map<String, AtomicLong>>
THREAD_SEQUENCES =
TryThreadLocal.withInitial(HashMap::new);
+ /** Resets the sequences in the current thread. */
+ public static void resetThreadSequences() {
+ THREAD_SEQUENCES.get().clear();
+ }
+
/** A byte string consisting of a single byte that is the ASCII space
* character (0x20). */
private static final ByteString SINGLE_SPACE_BYTE_STRING =
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index a94cad903b..989aac3604 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -21,6 +21,7 @@
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.config.Lex;
import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.prepare.Prepare;
import org.apache.calcite.runtime.Hook;
import org.apache.calcite.test.CalciteAssert.AssertThat;
import org.apache.calcite.test.CalciteAssert.DatabaseInstance;
@@ -28,6 +29,7 @@
import org.apache.calcite.test.schemata.hr.HrSchema;
import org.apache.calcite.util.Smalls;
import org.apache.calcite.util.TestUtil;
+import org.apache.calcite.util.TryThreadLocal;
import org.hsqldb.jdbcDriver;
import org.junit.jupiter.api.Test;
@@ -181,23 +183,25 @@ class JdbcAdapterTest {
}
@Test void testInPlan() {
- CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
- .query("select \"store_id\", \"store_name\" from \"store\"\n"
- + "where \"store_name\" in ('Store 1', 'Store 10', 'Store 11',
'Store 15', 'Store 16', 'Store 24', 'Store 3', 'Store 7')")
- .runs()
- .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
- .planHasSql("SELECT \"store_id\", \"store_name\"\n"
- + "FROM \"foodmart\".\"store\"\n"
- + "WHERE \"store_name\" IN ('Store 1', 'Store 10', 'Store 11',"
- + " 'Store 15', 'Store 16', 'Store 24', 'Store 3', 'Store 7')")
- .returns("store_id=1; store_name=Store 1\n"
- + "store_id=3; store_name=Store 3\n"
- + "store_id=7; store_name=Store 7\n"
- + "store_id=10; store_name=Store 10\n"
- + "store_id=11; store_name=Store 11\n"
- + "store_id=15; store_name=Store 15\n"
- + "store_id=16; store_name=Store 16\n"
- + "store_id=24; store_name=Store 24\n");
+ try (TryThreadLocal.Memo ignore =
Prepare.THREAD_INSUBQUERY_THRESHOLD.push(20)) {
+ CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
+ .query("select \"store_id\", \"store_name\" from \"store\"\n"
+ + "where \"store_name\" in ('Store 1', 'Store 10', 'Store 11',
'Store 15', 'Store 16', 'Store 24', 'Store 3', 'Store 7')")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"store_id\", \"store_name\"\n"
+ + "FROM \"foodmart\".\"store\"\n"
+ + "WHERE \"store_name\" IN ('Store 1', 'Store 10', 'Store 11',"
+ + " 'Store 15', 'Store 16', 'Store 24', 'Store 3', 'Store 7')")
+ .returns("store_id=1; store_name=Store 1\n"
+ + "store_id=3; store_name=Store 3\n"
+ + "store_id=7; store_name=Store 7\n"
+ + "store_id=10; store_name=Store 10\n"
+ + "store_id=11; store_name=Store 11\n"
+ + "store_id=15; store_name=Store 15\n"
+ + "store_id=16; store_name=Store 16\n"
+ + "store_id=24; store_name=Store 24\n");
+ }
}
@Test void testEquiJoinPlan() {
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 51e6221f1b..5478f6bb6e 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3710,6 +3710,9 @@ group by
!ok
+# Reset to default value 20
+!set insubquerythreshold 20
+
select
deptno,
case when deptno in (10) then 1 else 2 end as col1,
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 6db6e0bd45..d4d8dd93d7 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2267,40 +2267,68 @@ values atan2(0.5, 2);
!ok
-!set outputformat csv
-
# [CALCITE-1167] OVERLAPS should match even if operands are in (high, low)
order
values ((date '1999-12-01', date '2001-12-31') overlaps (date '2001-01-01' ,
date '2002-11-11'));
-EXPR$0
-true
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
!ok
values ((date '2001-12-31', date '1999-12-01') overlaps (date '2001-01-01' ,
date '2002-11-11'));
-EXPR$0
-true
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
!ok
values ((date '2001-12-31', date '1999-12-01') overlaps (date '2002-11-11',
date '2001-01-01'));
-EXPR$0
-true
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
!ok
values ((date '2001-12-31', date '1999-12-01') overlaps (date '2002-01-01',
date '2002-11-11'));
-EXPR$0
-false
++--------+
+| EXPR$0 |
++--------+
+| false |
++--------+
+(1 row)
+
!ok
# Sub-query returns a MAP, column is renamed, and enclosing query references
the map.
select mycol['b'] as x
from (select map['a', false, 'b', true] from (values (2))) as t(mycol);
-X
-true
++------+
+| X |
++------+
+| true |
++------+
+(1 row)
+
!ok
# JSON
values json_exists('{"foo":"bar"}', 'strict $.foo' false on error);
-EXPR$0
-true
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
!ok
# [CALCITE-2908] Implement SQL LAST_DAY function
@@ -2313,22 +2341,32 @@ with data(c_date, c_timestamp) as (select * from (values
(DATE'2019-06-28', TIMESTAMP '2019-06-28 17:32:01'),
(DATE'2019-12-12', TIMESTAMP '2019-12-12 12:12:01')))
select last_day(c_date), last_day(c_timestamp) from data;
-EXPR$0, EXPR$1
-1965-01-31, 1965-01-31
-2019-01-31, 2019-01-31
-2019-02-28, 2019-02-28
-2019-02-28, 2019-02-28
-2019-03-31, 2019-03-31
-2019-06-30, 2019-06-30
-2019-12-31, 2019-12-31
++------------+------------+
+| EXPR$0 | EXPR$1 |
++------------+------------+
+| 1965-01-31 | 1965-01-31 |
+| 2019-01-31 | 2019-01-31 |
+| 2019-02-28 | 2019-02-28 |
+| 2019-02-28 | 2019-02-28 |
+| 2019-03-31 | 2019-03-31 |
+| 2019-06-30 | 2019-06-30 |
+| 2019-12-31 | 2019-12-31 |
++------------+------------+
+(7 rows)
+
!ok
# [CALCITE-3142] An NPE when rounding a nullable numeric
SELECT ROUND(CAST((X/Y) AS NUMERIC), 2)
FROM (VALUES (1, 2), (NULLIF(5, 5), NULLIF(5, 5))) A(X, Y);
-EXPR$0
-0.00
-null
++--------+
+| EXPR$0 |
++--------+
+| 0.00 |
+| |
++--------+
+(2 rows)
+
!ok
# [CALCITE-3143]Dividing NULLIF clause may cause Division by zero error
@@ -2337,17 +2375,27 @@ FROM (
SELECT SUM("X") / NULLIF(SUM(0),0) AS Z
FROM (VALUES (1.1, 2.5), (4.51, 32.5)) A(X, Y)
GROUP BY "Y");
-EXPR$0
-88
-88
++--------+
+| EXPR$0 |
++--------+
+| 88 |
+| 88 |
++--------+
+(2 rows)
+
!ok
# [CALCITE-3150] NPE in UPPER when repeated and combine with LIKE
SELECT "NAME"
FROM (VALUES ('Bill'), NULLIF('x', 'x'), ('Eric')) A(NAME)
WHERE UPPER("NAME") LIKE 'B%' AND UPPER("NAME") LIKE '%L';
-NAME
-Bill
++------+
+| NAME |
++------+
+| Bill |
++------+
+(1 row)
+
!ok
# [CALCITE-3717] Query fails with "division by zero" exception
@@ -2364,32 +2412,45 @@ FROM (VALUES (0, 2, 4, 8),
(1, 2, 4, 0),
(0, 0, 0, 0),
(1, 2, 4, 8),
- (CAST(null as int), CAST(null as int), CAST(null as int), CAST(null as
int))) AS T(A,B,C,D);
-V
-13.00000000
-9.50000000
-1.75000000
-1.87500000
-null
-0E-8
-14.00000000
+ (CAST(null as int), CAST(null as int), CAST(null as int), CAST(null as
int))) AS T(A,B,C,D) order by V;
++-------------+
+| V |
++-------------+
+| 0E-8 |
+| 1.75000000 |
+| 1.87500000 |
+| 9.50000000 |
+| 13.00000000 |
+| 14.00000000 |
+| |
++-------------+
+(7 rows)
+
!ok
# TIMESTAMP literals without a time part are OK.
SELECT TIMESTAMP '1969-07-20' AS ts;
-TS
-1969-07-20 00:00:00
++---------------------+
+| TS |
++---------------------+
+| 1969-07-20 00:00:00 |
++---------------------+
+(1 row)
+
!ok
# Short TIMESTAMP literals are equivalent to long TIMESTAMP literals
SELECT TIMESTAMP '1969-07-20' + i AS ts
FROM (VALUES (INTERVAL '1' DAY)) AS t (i)
GROUP BY TIMESTAMP '1969-07-20 00:00:00' + i;
-TS
-1969-07-21 00:00:00
-!ok
++---------------------+
+| TS |
++---------------------+
+| 1969-07-21 00:00:00 |
++---------------------+
+(1 row)
-!set outputformat mysql
+!ok
# [CALCITE-5870] Allow literals like DECIMAL '12.3' (consistent with Postgres)
# Test a decimal value between decimal logic for range checking.
diff --git a/core/src/test/resources/sql/planner.iq
b/core/src/test/resources/sql/planner.iq
index 1c90347a21..96aaaf5eed 100644
--- a/core/src/test/resources/sql/planner.iq
+++ b/core/src/test/resources/sql/planner.iq
@@ -541,4 +541,69 @@ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
!plan
!set planner-rules original
+# [CALCITE-7125] Impossible to get a plan with partial aggregate push-down via
IntersectToDistinctRule
+!set planner-rules "
+-EnumerableRules.ENUMERABLE_INTERSECT_RULE,
+-AGGREGATE_REMOVE"
+# AGGREGATE_REMOVE is disabled as it would remove the inner COUNTs
+# as the grouping columns are unique
+
+# Intersect rewrite as aggregation + union with partial aggregation pushdown
+select empno, ename from emp where deptno = 10
+intersect
+select empno, ename from emp where empno >= 150;
+
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2:BIGINT], expr#4=[=($t2, $t3)],
proj#0..1=[{exprs}], $condition=[$t4])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableUnion(all=[true])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[10], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT
NULL], expr#9=[150], expr#10=[>=($t8, $t9)], proj#0..7=[{exprs}],
$condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++-------+--------+
+| EMPNO | ENAME |
++-------+--------+
+| 7782 | CLARK |
+| 7839 | KING |
+| 7934 | MILLER |
++-------+--------+
+(3 rows)
+
+!ok
+!set planner-rules original
+
+!set planner-rules "
+-CoreRules.INTERSECT_TO_DISTINCT,
+-EnumerableRules.ENUMERABLE_INTERSECT_RULE,
++CoreRules.INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN"
+
+# Intersect rewrite as aggregation + union without partial aggregation pushdown
+select empno, ename from emp where deptno = 10
+intersect
+select empno, ename from emp where empno >= 150;
+
+EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[>($t2, $t4)],
expr#6=[>($t3, $t4)], expr#7=[AND($t5, $t6)], proj#0..1=[{exprs}],
$condition=[$t7])
+ EnumerableAggregate(group=[{0, 1}], count_i0=[COUNT() FILTER $2],
count_i1=[COUNT() FILTER $3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[0], expr#4=[=($t2, $t3)],
expr#5=[1], expr#6=[=($t2, $t5)], proj#0..1=[{exprs}], $f3=[$t4], $f4=[$t6])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0],
expr#9=[CAST($t7):INTEGER], expr#10=[10], expr#11=[=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1],
expr#9=[CAST($t0):INTEGER NOT NULL], expr#10=[150], expr#11=[>=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++-------+--------+
+| EMPNO | ENAME |
++-------+--------+
+| 7782 | CLARK |
+| 7839 | KING |
+| 7934 | MILLER |
++-------+--------+
+(3 rows)
+
+!ok
+!set planner-rules original
+
# End planner.iq
diff --git a/core/src/test/resources/sql/scalar.iq
b/core/src/test/resources/sql/scalar.iq
index 4b5e186cc9..91cd88279a 100644
--- a/core/src/test/resources/sql/scalar.iq
+++ b/core/src/test/resources/sql/scalar.iq
@@ -403,4 +403,7 @@ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS
NULL($t9)], expr#11=[0:BIGINT],
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# Reset to default value true
+!set trimfields true
+
# End scalar.iq
diff --git a/core/src/test/resources/sql/set-op.iq
b/core/src/test/resources/sql/set-op.iq
index b52debdf3e..4b3917ad30 100644
--- a/core/src/test/resources/sql/set-op.iq
+++ b/core/src/test/resources/sql/set-op.iq
@@ -47,74 +47,6 @@ intersect
!ok
-!use scott
-
-!set planner-rules "
--EnumerableRules.ENUMERABLE_INTERSECT_RULE,
--AGGREGATE_REMOVE"
-# AGGREGATE_REMOVE is disabled as it would remove the inner COUNTs
-# as the grouping columns are unique
-
-# Intersect rewrite as aggregation + union with partial aggregation pushdown
-select empno, ename from emp where deptno = 10
-intersect
-select empno, ename from emp where empno >= 150;
-
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2:BIGINT], expr#4=[=($t2, $t3)],
proj#0..1=[{exprs}], $condition=[$t4])
- EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
- EnumerableUnion(all=[true])
- EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[10], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT
NULL], expr#9=[150], expr#10=[>=($t8, $t9)], proj#0..7=[{exprs}],
$condition=[$t10])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-+-------+--------+
-| EMPNO | ENAME |
-+-------+--------+
-| 7782 | CLARK |
-| 7839 | KING |
-| 7934 | MILLER |
-+-------+--------+
-(3 rows)
-
-!ok
-!set planner-rules original
-
-!use scott
-
-!set planner-rules "
--CoreRules.INTERSECT_TO_DISTINCT,
--EnumerableRules.ENUMERABLE_INTERSECT_RULE,
-+CoreRules.INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN"
-
-# Intersect rewrite as aggregation + union without partial aggregation pushdown
-select empno, ename from emp where deptno = 10
-intersect
-select empno, ename from emp where empno >= 150;
-
-EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[>($t2, $t4)],
expr#6=[>($t3, $t4)], expr#7=[AND($t5, $t6)], proj#0..1=[{exprs}],
$condition=[$t7])
- EnumerableAggregate(group=[{0, 1}], count_i0=[COUNT() FILTER $2],
count_i1=[COUNT() FILTER $3])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[0], expr#4=[=($t2, $t3)],
expr#5=[1], expr#6=[=($t2, $t5)], proj#0..1=[{exprs}], $f3=[$t4], $f4=[$t6])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0],
expr#9=[CAST($t7):INTEGER], expr#10=[10], expr#11=[=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1],
expr#9=[CAST($t0):INTEGER NOT NULL], expr#10=[150], expr#11=[>=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-+-------+--------+
-| EMPNO | ENAME |
-+-------+--------+
-| 7782 | CLARK |
-| 7839 | KING |
-| 7934 | MILLER |
-+-------+--------+
-(3 rows)
-
-!ok
-!set planner-rules original
-
# Intersect all with null value rows
select * from
(select x, y from (values (cast(NULL as int), cast(NULL as varchar(1))),
diff --git a/core/src/test/resources/sql/some.iq
b/core/src/test/resources/sql/some.iq
index acc61d9c92..02e771241f 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -895,7 +895,10 @@ where sal > some (4000, 2000);
!ok
-# CALCITE-6786: ANY/SOME operator yields multiple rows in correlated queries
+# Reset to default value 20
+!set insubquerythreshold 20
+
+# [CALCITE-6786] ANY/SOME operator yields multiple rows in correlated queries
WITH tb as (select array(SELECT * FROM (VALUES (TRUE), (NULL)) as x(a)) as a)
SELECT TRUE IN (SELECT b FROM UNNEST(a) AS x1(b)) AS test FROM tb;
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 404520f0dc..e981cc15d3 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -16,7 +16,7 @@
# limitations under the License.
#
!use post
-!set outputformat psql
+!set outputformat mysql
# [CALCITE-373]
# the following should return no rows, because the IN list has a null.
@@ -27,8 +27,10 @@ t2(x) as (select * from (values 1,case when 1 = 1 then null
else 3 end))
select *
from t1
where t1.x not in (select t2.x from t2);
- X
----
++---+
+| X |
++---+
++---+
(0 rows)
!ok
@@ -50,16 +52,20 @@ t2(x) as (select * from (values (1),(case when 1 = 1 then
null else 3 end)) as
select *
from t1
where t1.x not in (select t2.x from t2);
- X
----
++---+
+| X |
++---+
++---+
(0 rows)
!ok
# RHS has a mixture of NULL and NOT NULL keys
select * from dept where deptno not in (select deptno from emp);
- DEPTNO | DNAME
---------+-------
++--------+-------+
+| DEPTNO | DNAME |
++--------+-------+
++--------+-------+
(0 rows)
!ok
@@ -70,12 +76,14 @@ SELECT "hr"."emps"."empid", "hr"."emps"."deptno",
FROM "hr"."emps"
WHERE "hr"."emps"."empid"<"hr"."emps"."salary"
ORDER BY 1,2,3;
- empid | deptno | EXPR$2
--------+--------+--------
- 100 | 10 | 0
- 110 | 10 | 0
- 150 | 10 | 0
- 200 | 20 | 2
++-------+--------+--------+
+| empid | deptno | EXPR$2 |
++-------+--------+--------+
+| 100 | 10 | 0 |
+| 110 | 10 | 0 |
+| 150 | 10 | 0 |
+| 200 | 20 | 2 |
++-------+--------+--------+
(4 rows)
!ok
@@ -83,63 +91,73 @@ FROM "hr"."emps"
# [CALCITE-5638] Assertion Failure during planning correlated query
SELECT t1.deptno FROM dept AS t0 JOIN emp AS t1 ON
(t1.deptno = (SELECT inner_t1.deptno FROM emp AS inner_t1 WHERE inner_t1.ENAME
= t0.DNAME));
- DEPTNO
---------
++--------+
+| DEPTNO |
++--------+
++--------+
(0 rows)
!ok
select deptno, deptno in (select deptno from emp) from dept;
- DEPTNO | EXPR$1
---------+--------
- 10 | true
- 20 | true
- 30 | true
- 40 | null
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | true |
+| 20 | true |
+| 30 | true |
+| 40 | |
++--------+--------+
(4 rows)
!ok
select deptno, deptno not in (select deptno from emp) from dept;
- DEPTNO | EXPR$1
---------+--------
- 10 | false
- 20 | false
- 30 | false
- 40 | null
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | false |
+| 20 | false |
+| 30 | false |
+| 40 | |
++--------+--------+
(4 rows)
!ok
# RHS has only NULL keys
select * from dept where deptno not in (select deptno from emp where deptno is
null);
- DEPTNO | DNAME
---------+-------
++--------+-------+
+| DEPTNO | DNAME |
++--------+-------+
++--------+-------+
(0 rows)
!ok
select deptno, deptno in (select deptno from emp where deptno is null)
from dept;
- DEPTNO | EXPR$1
---------+--------
- 10 | null
- 20 | null
- 30 | null
- 40 | null
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | |
+| 20 | |
+| 30 | |
+| 40 | |
++--------+--------+
(4 rows)
!ok
select deptno, deptno not in (select deptno from emp where deptno is null)
from dept;
- DEPTNO | EXPR$1
---------+--------
- 10 | null
- 20 | null
- 30 | null
- 40 | null
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | |
+| 20 | |
+| 30 | |
+| 40 | |
++--------+--------+
(4 rows)
!ok
-!set outputformat mysql
-
# RHS has only NOT NULL keys
select * from dept where deptno not in (select deptno from emp where deptno is
not null);
+--------+-------------+
@@ -1314,8 +1332,6 @@ and empno in (7876, 7698, 7900);
!ok
-!set outputformat psql
-
# [CALCITE-2329] Enhance SubQueryRemoveRule to rewrite IN operator with the
constant from the left side more optimally
# Test project null IN null
select sal,
@@ -1323,22 +1339,24 @@ select sal,
select cast(null as int)
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1359,22 +1377,24 @@ select sal,
select cast(null as int)
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1395,22 +1415,24 @@ select sal,
select 1
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1431,22 +1453,24 @@ select sal,
select deptno
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1467,22 +1491,24 @@ select sal,
select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1503,22 +1529,24 @@ select sal,
select deptno
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | true
- 1250.00 | true
- 1250.00 | true
- 1300.00 | true
- 1500.00 | true
- 1600.00 | true
- 2450.00 | true
- 2850.00 | true
- 2975.00 | true
- 3000.00 | true
- 3000.00 | true
- 5000.00 | true
- 800.00 | true
- 950.00 | true
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | true |
+| 1250.00 | true |
+| 1250.00 | true |
+| 1300.00 | true |
+| 1500.00 | true |
+| 1600.00 | true |
+| 2450.00 | true |
+| 2850.00 | true |
+| 2975.00 | true |
+| 3000.00 | true |
+| 3000.00 | true |
+| 5000.00 | true |
+| 800.00 | true |
+| 950.00 | true |
++---------+--------+
(14 rows)
!ok
@@ -1537,22 +1565,24 @@ select sal,
select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | true
- 1250.00 | true
- 1250.00 | true
- 1300.00 | true
- 1500.00 | true
- 1600.00 | true
- 2450.00 | true
- 2850.00 | true
- 2975.00 | true
- 3000.00 | true
- 3000.00 | true
- 5000.00 | true
- 800.00 | true
- 950.00 | true
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | true |
+| 1250.00 | true |
+| 1250.00 | true |
+| 1300.00 | true |
+| 1500.00 | true |
+| 1600.00 | true |
+| 2450.00 | true |
+| 2850.00 | true |
+| 2975.00 | true |
+| 3000.00 | true |
+| 3000.00 | true |
+| 5000.00 | true |
+| 800.00 | true |
+| 950.00 | true |
++---------+--------+
(14 rows)
!ok
@@ -1573,22 +1603,24 @@ select sal,
select cast(null as int)
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1609,22 +1641,24 @@ select sal,
select cast(null as int)
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1645,22 +1679,24 @@ select sal,
select 1
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1681,22 +1717,24 @@ select sal,
select deptno
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1717,22 +1755,24 @@ select sal,
select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | null
- 1250.00 | null
- 1250.00 | null
- 1300.00 | null
- 1500.00 | null
- 1600.00 | null
- 2450.00 | null
- 2850.00 | null
- 2975.00 | null
- 3000.00 | null
- 3000.00 | null
- 5000.00 | null
- 800.00 | null
- 950.00 | null
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | |
+| 2850.00 | |
+| 2975.00 | |
+| 3000.00 | |
+| 3000.00 | |
+| 5000.00 | |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
(14 rows)
!ok
@@ -1753,22 +1793,24 @@ select sal,
select deptno
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | false
- 1250.00 | false
- 1250.00 | false
- 1300.00 | false
- 1500.00 | false
- 1600.00 | false
- 2450.00 | false
- 2850.00 | false
- 2975.00 | false
- 3000.00 | false
- 3000.00 | false
- 5000.00 | false
- 800.00 | false
- 950.00 | false
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | false |
+| 1250.00 | false |
+| 1250.00 | false |
+| 1300.00 | false |
+| 1500.00 | false |
+| 1600.00 | false |
+| 2450.00 | false |
+| 2850.00 | false |
+| 2975.00 | false |
+| 3000.00 | false |
+| 3000.00 | false |
+| 5000.00 | false |
+| 800.00 | false |
+| 950.00 | false |
++---------+--------+
(14 rows)
!ok
@@ -1787,22 +1829,24 @@ select sal,
select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | false
- 1250.00 | false
- 1250.00 | false
- 1300.00 | false
- 1500.00 | false
- 1600.00 | false
- 2450.00 | false
- 2850.00 | false
- 2975.00 | false
- 3000.00 | false
- 3000.00 | false
- 5000.00 | false
- 800.00 | false
- 950.00 | false
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | false |
+| 1250.00 | false |
+| 1250.00 | false |
+| 1300.00 | false |
+| 1500.00 | false |
+| 1600.00 | false |
+| 2450.00 | false |
+| 2850.00 | false |
+| 2975.00 | false |
+| 3000.00 | false |
+| 3000.00 | false |
+| 5000.00 | false |
+| 800.00 | false |
+| 950.00 | false |
++---------+--------+
(14 rows)
!ok
@@ -1823,22 +1867,24 @@ select sal,
select deptno
from "scott".dept) is unknown
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | true
- 1250.00 | true
- 1250.00 | true
- 1300.00 | true
- 1500.00 | true
- 1600.00 | true
- 2450.00 | true
- 2850.00 | true
- 2975.00 | true
- 3000.00 | true
- 3000.00 | true
- 5000.00 | true
- 800.00 | true
- 950.00 | true
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | true |
+| 1250.00 | true |
+| 1250.00 | true |
+| 1300.00 | true |
+| 1500.00 | true |
+| 1600.00 | true |
+| 2450.00 | true |
+| 2850.00 | true |
+| 2975.00 | true |
+| 3000.00 | true |
+| 3000.00 | true |
+| 5000.00 | true |
+| 800.00 | true |
+| 950.00 | true |
++---------+--------+
(14 rows)
!ok
@@ -1858,8 +1904,10 @@ select sal from "scott".emp
where cast(null as int) IN (
select cast(null as int)
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -1871,8 +1919,10 @@ select sal from "scott".emp
where 123 IN (
select cast(null as int)
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -1884,8 +1934,10 @@ select sal from "scott".emp
where cast(null as int) IN (
select 1
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -1897,8 +1949,10 @@ select sal from "scott".emp
where cast(null as int) IN (
select deptno
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -1910,8 +1964,10 @@ select sal from "scott".emp
where cast(null as int) IN (
select case when deptno > 0 then deptno else null end
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -1923,22 +1979,24 @@ select sal from "scott".emp
where 10 IN (
select deptno
from "scott".dept);
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1300.00
- 1500.00
- 1600.00
- 2450.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 5000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1300.00 |
+| 1500.00 |
+| 1600.00 |
+| 2450.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 5000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(14 rows)
!ok
@@ -1956,22 +2014,24 @@ select sal from "scott".emp
where 10 IN (
select case when deptno > 0 then deptno else null end
from "scott".dept);
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1300.00
- 1500.00
- 1600.00
- 2450.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 5000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1300.00 |
+| 1500.00 |
+| 1600.00 |
+| 2450.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 5000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(14 rows)
!ok
@@ -1989,8 +2049,10 @@ select sal from "scott".emp
where cast(null as int) NOT IN (
select cast(null as int)
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2010,8 +2072,10 @@ select sal from "scott".emp
where 123 NOT IN (
select cast(null as int)
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2031,8 +2095,10 @@ select sal from "scott".emp
where cast(null as int) NOT IN (
select 1
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2052,8 +2118,10 @@ select sal from "scott".emp
where cast(null as int) NOT IN (
select deptno
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2073,8 +2141,10 @@ select sal from "scott".emp
where cast(null as int) NOT IN (
select case when deptno > 0 then deptno else null end
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2094,8 +2164,10 @@ select sal from "scott".emp
where 10 NOT IN (
select deptno
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2115,8 +2187,10 @@ select sal from "scott".emp
where 10 NOT IN (
select case when deptno > 0 then deptno else null end
from "scott".dept);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2136,22 +2210,24 @@ select sal from "scott".emp
where cast(null as int) IN (
select deptno
from "scott".dept) is unknown;
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1300.00
- 1500.00
- 1600.00
- 2450.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 5000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1300.00 |
+| 1500.00 |
+| 1600.00 |
+| 2450.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 5000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(14 rows)
!ok
@@ -2173,8 +2249,10 @@ select sal from "scott".emp e
where cast(null as int) IN (
select cast(null as int)
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2186,8 +2264,10 @@ select sal from "scott".emp e
where 123 IN (
select cast(null as int)
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2199,8 +2279,10 @@ select sal from "scott".emp e
where cast(null as int) IN (
select 1
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2212,8 +2294,10 @@ select sal from "scott".emp e
where cast(null as int) IN (
select deptno
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2225,8 +2309,10 @@ select sal from "scott".emp e
where mod(cast(rand() as int), 2) = 3 OR 123 IN (
select cast(null as int) from "scott".dept d
where d.deptno = e.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2247,8 +2333,10 @@ select sal from "scott".emp e
where cast(null as int) IN (
select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2260,11 +2348,13 @@ select sal from "scott".emp e
where 10 IN (
select deptno
from "scott".dept d where e.deptno=d.deptno);
- SAL
----------
- 1300.00
- 2450.00
- 5000.00
++---------+
+| SAL |
++---------+
+| 1300.00 |
+| 2450.00 |
+| 5000.00 |
++---------+
(3 rows)
!ok
@@ -2281,11 +2371,13 @@ select sal from "scott".emp e
where 10 IN (
select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
- SAL
----------
- 1300.00
- 2450.00
- 5000.00
++---------+
+| SAL |
++---------+
+| 1300.00 |
+| 2450.00 |
+| 5000.00 |
++---------+
(3 rows)
!ok
@@ -2302,8 +2394,10 @@ select sal from "scott".emp e
where cast(null as int) NOT IN (
select cast(null as int)
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2315,8 +2409,10 @@ select sal from "scott".emp e
where 123 NOT IN (
select cast(null as int)
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2337,8 +2433,10 @@ select sal from "scott".emp e
where cast(null as int) NOT IN (
select 1
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2350,8 +2448,10 @@ select sal from "scott".emp e
where cast(null as int) NOT IN (
select deptno
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2363,8 +2463,10 @@ select sal from "scott".emp e
where cast(null as int) NOT IN (
select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
- SAL
------
++-----+
+| SAL |
++-----+
++-----+
(0 rows)
!ok
@@ -2376,19 +2478,21 @@ select sal from "scott".emp e
where 10 NOT IN (
select deptno
from "scott".dept d where e.deptno=d.deptno);
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1500.00
- 1600.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1500.00 |
+| 1600.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(11 rows)
!ok
@@ -2410,19 +2514,21 @@ select sal from "scott".emp e
where 10 NOT IN (
select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1500.00
- 1600.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1500.00 |
+| 1600.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(11 rows)
!ok
@@ -2444,22 +2550,24 @@ select sal from "scott".emp e
where cast(null as int) IN (
select deptno
from "scott".dept d where e.deptno=d.deptno) is unknown;
- SAL
----------
- 1100.00
- 1250.00
- 1250.00
- 1300.00
- 1500.00
- 1600.00
- 2450.00
- 2850.00
- 2975.00
- 3000.00
- 3000.00
- 5000.00
- 800.00
- 950.00
++---------+
+| SAL |
++---------+
+| 1100.00 |
+| 1250.00 |
+| 1250.00 |
+| 1300.00 |
+| 1500.00 |
+| 1600.00 |
+| 2450.00 |
+| 2850.00 |
+| 2975.00 |
+| 3000.00 |
+| 3000.00 |
+| 5000.00 |
+| 800.00 |
+| 950.00 |
++---------+
(14 rows)
!ok
@@ -2474,22 +2582,24 @@ select sal,
select case when deptno > 10 then deptno else null end
from "scott".dept)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | true
- 1250.00 | true
- 1250.00 | true
- 1300.00 | true
- 1500.00 | true
- 1600.00 | true
- 2450.00 | true
- 2850.00 | true
- 2975.00 | true
- 3000.00 | true
- 3000.00 | true
- 5000.00 | true
- 800.00 | true
- 950.00 | true
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | true |
+| 1250.00 | true |
+| 1250.00 | true |
+| 1300.00 | true |
+| 1500.00 | true |
+| 1600.00 | true |
+| 2450.00 | true |
+| 2850.00 | true |
+| 2975.00 | true |
+| 3000.00 | true |
+| 3000.00 | true |
+| 5000.00 | true |
+| 800.00 | true |
+| 950.00 | true |
++---------+--------+
(14 rows)
!ok
@@ -2501,22 +2611,24 @@ select sal,
from "scott".dept
where deptno < 0)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | false
- 1250.00 | false
- 1250.00 | false
- 1300.00 | false
- 1500.00 | false
- 1600.00 | false
- 2450.00 | false
- 2850.00 | false
- 2975.00 | false
- 3000.00 | false
- 3000.00 | false
- 5000.00 | false
- 800.00 | false
- 950.00 | false
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | false |
+| 1250.00 | false |
+| 1250.00 | false |
+| 1300.00 | false |
+| 1500.00 | false |
+| 1600.00 | false |
+| 2450.00 | false |
+| 2850.00 | false |
+| 2975.00 | false |
+| 3000.00 | false |
+| 3000.00 | false |
+| 5000.00 | false |
+| 800.00 | false |
+| 950.00 | false |
++---------+--------+
(14 rows)
!ok
@@ -2528,36 +2640,40 @@ select sal,
from "scott".dept
where deptno < 0)
from "scott".emp;
- SAL | EXPR$1
----------+--------
- 1100.00 | false
- 1250.00 | false
- 1250.00 | false
- 1300.00 | false
- 1500.00 | false
- 1600.00 | false
- 2450.00 | false
- 2850.00 | false
- 2975.00 | false
- 3000.00 | false
- 3000.00 | false
- 5000.00 | false
- 800.00 | false
- 950.00 | false
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | false |
+| 1250.00 | false |
+| 1250.00 | false |
+| 1300.00 | false |
+| 1500.00 | false |
+| 1600.00 | false |
+| 2450.00 | false |
+| 2850.00 | false |
+| 2975.00 | false |
+| 3000.00 | false |
+| 3000.00 | false |
+| 5000.00 | false |
+| 800.00 | false |
+| 950.00 | false |
++---------+--------+
(14 rows)
!ok
# Test nested sub-query in PROJECT within FILTER
select * from emp where deptno IN (select (select max(deptno) from "scott".emp
t1) from "scott".emp t2);
- 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
- 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
- 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30
- 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
- 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
++-------+--------+----------+------+------------+---------+---------+--------+
+| 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 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
++-------+--------+----------+------+------------+---------+---------+--------+
(6 rows)
!ok
@@ -2572,22 +2688,24 @@ EnumerableHashJoin(condition=[=($7, $9)],
joinType=[semi])
# Test nested sub-query in FILTER within PROJECT
select (select max(deptno) from "scott".emp where deptno IN (select deptno
from "scott".emp)) from emp ;
- EXPR$0
---------
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
- 30
++--------+
+| EXPR$0 |
++--------+
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
+| 30 |
++--------+
(14 rows)
!ok
@@ -2606,38 +2724,44 @@ EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])
# [CALCITE-7317] SubQueryRemoveRule should skip NULL-safety checks for IN
subqueries when both keys and subquery columns are NOT NULL
select * from emp as e1 where empno in (select empno from emp e2);
- EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
--------+--------+-----------+------+------------+---------+---------+--------
- 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
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 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
# [CALCITE-7317] SubQueryRemoveRule should skip NULL-safety checks for IN
subqueries when both keys and subquery columns are NOT NULL
select * from emp as e1 where coalesce(deptno, 0) not in (select deptno from
emp e2);
- EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
--------+-------+-----+-----+----------+-----+------+--------
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)
!ok
# [CALCITE-7317] SubQueryRemoveRule should skip NULL-safety checks for IN
subqueries when both keys and subquery columns are NOT NULL
select * from emp as e1 where deptno not in (select coalesce(deptno, 0) from
emp e2);
- EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
--------+-------+-----+-----+----------+-----+------+--------
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)
!ok
@@ -2649,9 +2773,11 @@ where sal + 100 not in (
select deptno
from dept
where dname = e.ename);
- C
-----
- 14
++----+
+| C |
++----+
+| 14 |
++----+
(1 row)
!ok
@@ -2690,22 +2816,24 @@ EnumerableCalc(expr#0..6=[{inputs}], EMPNO=[$t5])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
!plan
- EMPNO
--------
- 7369
- 7499
- 7521
- 7566
- 7654
- 7698
- 7782
- 7788
- 7839
- 7844
- 7876
- 7900
- 7902
- 7934
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
(14 rows)
!ok
@@ -2727,22 +2855,24 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[>($t1,
$t2)], expr#8=[IS TRUE($t7)]
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT NOT
NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
- EMPNO | EXPR$1
--------+--------
- 7369 | false
- 7499 | false
- 7521 | false
- 7566 | false
- 7654 | false
- 7698 | false
- 7782 | false
- 7788 | false
- 7839 | false
- 7844 | false
- 7876 | false
- 7900 | false
- 7902 | false
- 7934 | false
++-------+--------+
+| EMPNO | EXPR$1 |
++-------+--------+
+| 7369 | false |
+| 7499 | false |
+| 7521 | false |
+| 7566 | false |
+| 7654 | false |
+| 7698 | false |
+| 7782 | false |
+| 7788 | false |
+| 7839 | false |
+| 7844 | false |
+| 7876 | false |
+| 7900 | false |
+| 7902 | false |
+| 7934 | false |
++-------+--------+
(14 rows)
!ok
@@ -2764,27 +2894,28 @@ EnumerableCalc(expr#0..2=[{inputs}], ENAME=[$t1])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)],
expr#9=[CAST($t3):INTEGER NOT NULL], expr#10=[0], expr#11=[CASE($t8, $t9,
$t10)], $f8=[$t11])
EnumerableTableScan(table=[[scott, EMP]])
!plan
- ENAME
---------
- ADAMS
- ALLEN
- BLAKE
- CLARK
- FORD
- JAMES
- JONES
- KING
- MARTIN
- MILLER
- SCOTT
- SMITH
- TURNER
- WARD
++--------+
+| ENAME |
++--------+
+| ADAMS |
+| ALLEN |
+| BLAKE |
+| CLARK |
+| FORD |
+| JAMES |
+| JONES |
+| KING |
+| MARTIN |
+| MILLER |
+| SCOTT |
+| SMITH |
+| TURNER |
+| WARD |
++--------+
(14 rows)
!ok
-!set outputformat mysql
# Correlated SOME sub-query with not equality
# Both sides Not NUll.
select empno
@@ -3063,7 +3194,6 @@ FROM tb;
# [CALCITE-4486] UNIQUE predicate
!use scott
-!set outputformat mysql
# singleton keys have unique value which excludes fully or partially null rows.
select deptno
@@ -3941,8 +4071,11 @@ 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
+# Reset to default value 20
+!set insubquerythreshold 20
+# [CALCITE-4846] IN-list that includes NULL converted to Values throws
exception
+!set insubquerythreshold 0
select * from "scott".emp where empno not in (null, 7782);
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
@@ -4069,6 +4202,9 @@ EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0],
expr#16=[=($t8, $t15)], expr#
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
!plan
+# Reset to default value 20
+!set insubquerythreshold 20
+
# [CALCITE-5117] Optimize the EXISTS sub-query by Metadata RowCount
# Test case about sub-query is guaranteed to produce at least one row
@@ -4740,7 +4876,7 @@ WHERE s1.total > (SELECT avg(total) FROM agg_sal s2 WHERE
s1.deptno = s2.deptno)
!ok
# [CALCITE-6506] Type inference for IN list is incorrect
-
+!set insubquerythreshold 0
# Test LHS is not nullable and RHS is not nullable
select empno, empno in (7369, 7499, 7521) from emp;
+-------+--------+
@@ -4852,8 +4988,11 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS
NULL($t1)], expr#8=[null:BOOLEAN
EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null }]])
!plan
-# Test LHS is (not nullable, not nullable) and RHS is (not nullable, not
nullable)
+# Reset to default value 20
+!set insubquerythreshold 20
+# Test LHS is (not nullable, not nullable) and RHS is (not nullable, not
nullable)
+!set insubquerythreshold 0
select empno, (empno, empno) in ((7369, 7369), (7499, 7499), (7521, 7521))
from emp;
+-------+--------+
| EMPNO | EXPR$1 |
@@ -4965,6 +5104,9 @@ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[IS
NULL($t1)], expr#10=[null:BOOLEA
EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }, { null, null }]])
!plan
+# Reset to default value 20
+!set insubquerythreshold 20
+
# [CALCITE-5156] Support implicit integer types cast for IN Sub-query
# Test case about the IN sub-query left operand type is INTEGER and right
operand type is TINYINT
@@ -7649,4 +7791,7 @@ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
!ok
+# Reset to default value 20
+!set trimfields true
+
# End sub-query.iq
diff --git a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
index 632d24cfea..e9d18930d4 100644
--- a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
@@ -85,6 +85,9 @@
import java.util.regex.Pattern;
import java.util.stream.Collectors;
+import static org.apache.calcite.runtime.SqlFunctions.resetThreadSequences;
+import static
org.apache.calcite.sql2rel.SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD;
+
import static org.junit.jupiter.api.Assertions.fail;
import static java.util.Objects.requireNonNull;
@@ -511,9 +514,17 @@ private static String n2u(String s) {
: s;
}
+ private void resetThreadConfig() {
+ resetThreadSequences();
+ Prepare.THREAD_INSUBQUERY_THRESHOLD.push(DEFAULT_IN_SUB_QUERY_THRESHOLD);
+ Prepare.THREAD_EXPAND.push(false);
+ Prepare.THREAD_TRIM.push(true);
+ }
+
@ParameterizedTest
@MethodSource("getPath")
public void test(String path) throws Exception {
+ resetThreadConfig();
final Method method = findMethod(path);
if (method != null) {
try {
