Google Git
Sign in
apache / calcite / 5b8ce9c477dcf6ea4848b14982a11bd2e6ebe556 / . / core / src / test / resources / sql / sub-query.iq
blob: 84084b604c9534f49af129e98f71ff54de9fd3e0 [file] [log] [blame]
# sub-query.iq - Queries involving IN and EXISTS sub-queries
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
!use post
!set outputformat psql
# [CALCITE-373]
# the following should return no rows, because the IN list has a null.
# for details on this: see HIVE-784, Dayal's paper from VLDB-87
with
t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)),
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
---
(0 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[IS NULL($t4)], expr#8=[>=($t2, $t1)], expr#9=[IS NOT NULL($t0)], expr#10=[AND($t7, $t8, $t9)], expr#11=[OR($t6, $t10)], X=[$t0], $condition=[$t11])
EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableValues(tuples=[[{ 1 }, { 2 }, { null }]])
EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
EnumerableValues(tuples=[[{ 1 }, { null }]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
EnumerableValues(tuples=[[{ 1 }, { null }]])
!plan
# Use of case is to get around issue with directly specifying null in values
# list. Postgres gives 0 rows.
with
t1(x) as (select * from (values (1),(2),(case when 1 = 1 then null else 3 end)) as t1),
t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t2)
select *
from t1
where t1.x not in (select t2.x from t2);
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
--------+-------
(0 rows)
!ok
select deptno, deptno in (select deptno from emp) from dept;
DEPTNO | EXPR$1
--------+--------
10 | true
20 | true
30 | true
40 | null
(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
(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
--------+-------
(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
(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
(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);
+--------+-------------+
| DEPTNO | DNAME |
+--------+-------------+
| 40 | Empty |
+--------+-------------+
(1 row)
!ok
select deptno, deptno in (select deptno from emp where deptno is not null) from dept;
+--------+--------+
| DEPTNO | EXPR$1 |
+--------+--------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | false |
+--------+--------+
(4 rows)
!ok
select deptno, deptno not in (select deptno from emp where deptno is not null) from dept;
+--------+--------+
| DEPTNO | EXPR$1 |
+--------+--------+
| 10 | false |
| 20 | false |
| 30 | false |
| 40 | true |
+--------+--------+
(4 rows)
!ok
# RHS has no rows
# Even 'NULL NOT IN ...' is TRUE.
select * from dept where deptno not in (select deptno from emp where false);
+--------+-------------+
| DEPTNO | DNAME |
+--------+-------------+
| 10 | Sales |
| 20 | Marketing |
| 30 | Engineering |
| 40 | Empty |
+--------+-------------+
(4 rows)
!ok
select deptno, deptno in (select deptno from emp where false) from dept;
+--------+--------+
| DEPTNO | EXPR$1 |
+--------+--------+
| 10 | false |
| 20 | false |
| 30 | false |
| 40 | false |
+--------+--------+
(4 rows)
!ok
select deptno, deptno not in (select deptno from emp where false) from dept;
+--------+--------+
| DEPTNO | EXPR$1 |
+--------+--------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+--------+
(4 rows)
!ok
# Multiple IN, connected by OR
select * from dept
where deptno in (select deptno from emp where gender = 'F')
or deptno in (select deptno from emp where gender = 'M');
+--------+-------------+
| DEPTNO | DNAME |
+--------+-------------+
| 10 | Sales |
| 20 | Marketing |
| 30 | Engineering |
+--------+-------------+
(3 rows)
!ok
# Mix IN and EXISTS
select * from dept
where deptno in (select deptno from emp where gender = 'F')
or exists (select 99, 101 from emp where gender = 'X');
+--------+-------------+
| DEPTNO | DNAME |
+--------+-------------+
| 10 | Sales |
| 30 | Engineering |
+--------+-------------+
(2 rows)
!ok
# Composite key
select * from dept
where (deptno, deptno) in (select deptno * 2 - deptno, deptno from emp where gender = 'F');
# Composite key, part literal
select * from emp
where (gender, deptno) in (select gender, 10 from emp where gender = 'F');
+-------+--------+--------+
| ENAME | DEPTNO | GENDER |
+-------+--------+--------+
| Jane | 10 | F |
+-------+--------+--------+
(1 row)
!ok
!use scott
# [CALCITE-1155] Support columns for IN list
SELECT empno, ename, mgr FROM "scott".emp WHERE 7782 IN (empno, mgr);
+-------+--------+------+
| EMPNO | ENAME | MGR |
+-------+--------+------+
| 7782 | CLARK | 7839 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
(2 rows)
!ok
# [CALCITE-694] Scan HAVING clause for sub-queries and IN-lists
SELECT count(*) AS c
FROM "scott".emp
GROUP BY emp.deptno
HAVING sum(case when emp.empno in (7369, 7839, 7902) then emp.sal else 0 end)
BETWEEN 5000.0 AND 10000.0;
+---+
| C |
+---+
| 3 |
+---+
(1 row)
!ok
# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING
# clause gives AssertionError
SELECT emp.deptno
FROM "scott".emp
GROUP BY emp.deptno
HAVING max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp);
+--------+
| DEPTNO |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
(3 rows)
!ok
# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING
# clause gives AssertionError
SELECT emp.deptno,
max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp) as bbbb
FROM "scott".emp
GROUP BY emp.deptno;
+--------+------+
| DEPTNO | BBBB |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
+--------+------+
(3 rows)
!ok
# [DRILL-4407] Group by sub-query causes Java NPE
select count(*) as c
from "scott".emp
group by (select deptno from "scott".emp where empno = 10);
+----+
| C |
+----+
| 14 |
+----+
(1 row)
!ok
!if (fixed.calcite1045) {
# Correlated IN sub-query in WHERE clause of JOIN
select empno from "scott".emp as e
join "scott".dept as d using (deptno)
where e.job in (
select e2.job from "scott".emp as e2 where e2.deptno > e.deptno);
EMPNO
-------
7369
7566
7782
7876
7934
(5 rows)
!ok
EnumerableCalc(expr#0..5=[{inputs}], EMPNO=[$t0])
EnumerableHashJoin(condition=[=($2, $5)], joinType=[inner])
EnumerableCalc(expr#0..4=[{inputs}], EMPNO=[$t2], JOB=[$t3], DEPTNO=[$t4], JOB0=[$t0], DEPTNO0=[$t1])
EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], JOB=[$t1], DEPTNO=[$t0])
EnumerableAggregate(group=[{0, 2}])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[>($t3, $t0)], proj#0..3=[{exprs}], $condition=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableAggregate(group=[{7}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
!}
!if (fixed.calcite1045) {
# Correlated NOT IN sub-query in WHERE clause of JOIN
select empno from "scott".emp as e
join "scott".dept as d using (deptno)
where e.job not in (
select e2.job from "scott".emp as e2 where e2.deptno > e.deptno);
!ok
!plan
!}
# Condition that returns a NULL key.
# Tested on Oracle.
select count(*) as c
from "scott".emp
where sal + 100 not in (
select comm
from "scott".emp);
+---+
| C |
+---+
| 0 |
+---+
(1 row)
!ok
# Condition that happens to eliminate all NULL keys.
# The one missing row has {ename: 'MARTIN', comm: 1400}
# Tested on Oracle.
select count(*) as c
from "scott".emp
where sal + 100 not in (
select comm from "scott".emp
where job = 'SALESMAN');
+----+
| C |
+----+
| 13 |
+----+
(1 row)
!ok
# Condition that provably eliminates all NULL keys.
# Tested on Oracle.
select count(*) as c
from "scott".emp
where sal + 100 not in (
select comm
from "scott".emp
where comm < 1000);
+----+
| C |
+----+
| 14 |
+----+
(1 row)
!ok
# Correlated condition in NOT IN.
# Tested on Oracle.
!if (fixed.calcite1513) {
select count(*) as c
from "scott".emp as e
where sal + 100 not in (
select comm
from "scott".emp
where job = e.job);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
!ok
!}
# [CALCITE-356] AssertionError while translating query with WITH and correlated sub-query
with t (a, b) as (select * from (values (1, 2)))
select * from t where exists (select 1 from "scott".emp where deptno = t.a);
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
EnumerableValues(tuples=[[{ 1, 2 }]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[true], expr#9=[CAST($t7):INTEGER], expr#10=[$cor0], expr#11=[$t10.A], expr#12=[=($t9, $t11)], i=[$t8], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Similar query, identical plan
with t as (select * from (values (1, 2)) as t(a, b))
select * from t where exists (select 1 from "scott".emp where deptno = t.a);
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
EnumerableValues(tuples=[[{ 1, 2 }]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[true], expr#9=[CAST($t7):INTEGER], expr#10=[$cor0], expr#11=[$t10.A], expr#12=[=($t9, $t11)], i=[$t8], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Uncorrelated
with t (a, b) as (select * from (values (60, 'b')))
select * from t where a in (select deptno from "scott".dept);
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableValues(tuples=[[{ 60, 'b' }]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
+---+---+
| A | B |
+---+---+
+---+---+
(0 rows)
!ok
# [CALCITE-864] Correlation variable has incorrect row type if it is populated
# by right side of a Join
select *
from "scott".emp as e
join "scott".dept as d using (deptno)
where sal = (
select max(sal)
from "scott".emp as e2
join "scott".dept as d2 using (deptno)
where d2.deptno = d.deptno);
+--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
| DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC |
+--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
| 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | ACCOUNTING | NEW YORK |
| 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | RESEARCH | DALLAS |
| 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | RESEARCH | DALLAS |
| 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | SALES | CHICAGO |
+--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
(4 rows)
!ok
# Simpler test case for [CALCITE-864]
select empno, ename, sal, e.deptno, loc
from "scott".emp as e
join "scott".dept as d using (deptno)
where e.sal = (
select max(sal)
from "scott".emp as e2
where e2.deptno = e.deptno);
+-------+-------+---------+--------+----------+
| EMPNO | ENAME | SAL | DEPTNO | LOC |
+-------+-------+---------+--------+----------+
| 7698 | BLAKE | 2850.00 | 30 | CHICAGO |
| 7788 | SCOTT | 3000.00 | 20 | DALLAS |
| 7839 | KING | 5000.00 | 10 | NEW YORK |
| 7902 | FORD | 3000.00 | 20 | DALLAS |
+-------+-------+---------+--------+----------+
(4 rows)
!ok
# Simpler test case for [CALCITE-864]
select *
from "scott".emp as e
join "scott".dept as d using (deptno)
where d.dname = (
select max(dname)
from "scott".dept as d2
where d2.deptno = d.deptno);
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
| DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC |
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | ACCOUNTING | NEW YORK |
| 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | ACCOUNTING | NEW YORK |
| 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | ACCOUNTING | NEW YORK |
| 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | RESEARCH | DALLAS |
| 20 | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | RESEARCH | DALLAS |
| 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | RESEARCH | DALLAS |
| 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | RESEARCH | DALLAS |
| 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | RESEARCH | DALLAS |
| 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | SALES | CHICAGO |
| 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | SALES | CHICAGO |
| 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | SALES | CHICAGO |
| 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | SALES | CHICAGO |
| 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | SALES | CHICAGO |
| 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | SALES | CHICAGO |
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
(14 rows)
!ok
# Two EXISTS
# [CALCITE-1511] AssertionError while decorrelating query with two EXISTS sub-queries
select *
from "scott".dept as d
where exists (select 1 from "scott".emp where empno > d.deptno)
and exists (select 0 from "scott".emp where deptno = d.deptno and ename = 'SMITH');
+--------+----------+--------+
| DEPTNO | DNAME | LOC |
+--------+----------+--------+
| 20 | RESEARCH | DALLAS |
+--------+----------+--------+
(1 row)
!ok
# Two scalar sub-queries
!if (fixed.calcite1045) {
select deptno,
(select min(1) from "scott".emp where empno > d.deptno) as i0,
(select min(0) from "scott".emp where deptno = d.deptno and ename = 'SMITH') as i1
from "scott".dept as d;
+--------+----+---+
| DEPTNO | I0 | I1|
+--------+----+---+
| 10 | 1 | |
| 20 | 1 | 0 |
| 30 | 1 | |
| 40 | 1 | |
+--------+----+---+
(4 rows)
!ok
!}
# [CALCITE-1494] Inefficient plan for correlated sub-queries
# Plan must have only one scan each of emp and dept.
select sal
from "scott".emp
where empno IN (
select deptno
from "scott".dept
where emp.job = dept.dname);
+-----+
| SAL |
+-----+
+-----+
(0 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], SAL=[$t3])
EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $2))], joinType=[inner])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)], proj#0..1=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t2):VARCHAR(14)], EMPNO=[$t0], SAL=[$t5], JOB0=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# As above, but for EXISTS
select *
from "scott".dept as d
where exists (
select 0 from "scott".emp
where deptno = d.deptno
and ename = 'SMITH');
+--------+----------+--------+
| DEPTNO | DNAME | LOC |
+--------+----------+--------+
| 20 | RESEARCH | DALLAS |
+--------+----------+--------+
(1 row)
!ok
EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t1], DNAME=[$t2], LOC=[$t3])
EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=['SMITH':VARCHAR(10)], expr#9=[=($t1, $t8)], expr#10=[IS NOT NULL($t7)], expr#11=[AND($t9, $t10)], DEPTNO=[$t7], $condition=[$t11])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# [DRILL-5644]
select TJOIN1.RNUM, TJOIN1.C1,
case when 10 in ( select C1 from ( values (1) ) T(C1) ) then 'yes' else 'no' end C3
from (
values (0, 10, 15),
(1, 20, 25),
(2, cast(NULL as integer), 50)) TJOIN1 (RNUM, C1, C2);
+------+----+-----+
| RNUM | C1 | C3 |
+------+----+-----+
| 0 | 10 | no |
| 1 | 20 | no |
| 2 | | no |
+------+----+-----+
(3 rows)
!ok
# [CALCITE-2028] Un-correlated IN sub-query should be converted into a Join
# rather than a Correlate without correlation variables
SELECT *
FROM "scott".emp
WHERE job in (select job from "scott".emp ee where ee.hiredate = DATE '1980-12-17')
AND EXISTS (select * from "scott".emp e where emp.deptno = e.deptno);
+-------+--------+-------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
+-------+--------+-------+------+------------+---------+------+--------+
(4 rows)
!ok
# Variant of [CALCITE-2028] above
SELECT *
FROM "scott".emp
WHERE job in (select job from "scott".emp ee where ee.hiredate = DATE '1980-12-17')
OR EXISTS (select * from "scott".emp e where emp.deptno = e.deptno + 20);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
(9 rows)
!ok
# [CALCITE-2071] Query with IN and OR in WHERE clause returns wrong result
select empno
from "scott".emp
where (empno in (select empno from "scott".emp)
or empno in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25))
and empno in (7876, 7698, 7900);
+-------+
| EMPNO |
+-------+
| 7698 |
| 7876 |
| 7900 |
+-------+
(3 rows)
!ok
# Equivalent to above (by de Morgan's law)
select empno
from "scott".emp
where not (empno not in (select empno from "scott".emp)
and empno not in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25))
and empno in (7876, 7698, 7900);
+-------+
| EMPNO |
+-------+
| 7698 |
| 7876 |
| 7900 |
+-------+
(3 rows)
!ok
# Not equivalent to above, but happens to have same result
select empno
from "scott".emp
where (empno = 12345
or empno in (select empno from "scott".emp)
or not empno in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25))
and empno in (7876, 7698, 7900);
+-------+
| EMPNO |
+-------+
| 7698 |
| 7876 |
| 7900 |
+-------+
(3 rows)
!ok
# Similar to above, but never suffered from [CALCITE-2071] because AND
select empno
from "scott".emp
where (empno in (select empno from "scott".emp)
and empno in (7876, 7698, 7900))
and empno in (7876, 7698, 7900);
+-------+
| EMPNO |
+-------+
| 7698 |
| 7876 |
| 7900 |
+-------+
(3 rows)
!ok
!set outputformat psql
!set expand false
# [CALCITE-2329] Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally
# Test project null IN null
select sal,
cast(null as int) IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal IN null non-correlated
select sal,
123 IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS FALSE($t2)], expr#5=[null:BOOLEAN], expr#6=[IS NOT NULL($t3)], expr#7=[AND($t4, $t5, $t6)], expr#8=[IS NOT NULL($t2)], expr#9=[IS NOT FALSE($t2)], expr#10=[AND($t8, $t6, $t9)], expr#11=[OR($t7, $t10)], SAL=[$t1], EXPR$1=[$t11])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null IN literal non-correlated
select sal,
cast(null as int) IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null IN required
select sal,
cast(null as int) IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null IN nullable
select sal,
cast(null as int) IN (
select case when true 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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal IN required
select sal,
10 IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)], SAL=[$t1], EXPR$1=[$t3])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal IN nullable
select sal,
10 IN (
select case when true 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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS FALSE($t2)], expr#5=[null:BOOLEAN], expr#6=[IS NOT NULL($t3)], expr#7=[AND($t4, $t5, $t6)], expr#8=[IS NOT NULL($t2)], expr#9=[IS NOT FALSE($t2)], expr#10=[AND($t8, $t6, $t9)], expr#11=[OR($t7, $t10)], SAL=[$t1], EXPR$1=[$t11])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null NOT IN null non-correlated
select sal,
cast(null as int) NOT IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[null:BOOLEAN], expr#6=[OR($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal NOT IN null non-correlated
select sal,
123 NOT IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[IS FALSE($t2)], expr#6=[null:BOOLEAN], expr#7=[AND($t5, $t6)], expr#8=[IS NOT FALSE($t2)], expr#9=[IS NULL($t2)], expr#10=[AND($t8, $t9)], expr#11=[OR($t4, $t7, $t10)], SAL=[$t1], EXPR$1=[$t11])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null NOT IN literal non-correlated
select sal,
cast(null as int) NOT IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[null:BOOLEAN], expr#6=[OR($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null NOT IN required
select sal,
cast(null as int) NOT IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[null:BOOLEAN], expr#6=[OR($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null NOT IN nullable
select sal,
cast(null as int) NOT IN (
select case when true 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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[null:BOOLEAN], expr#6=[OR($t4, $t5)], SAL=[$t1], EXPR$1=[$t6])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal NOT IN required
select sal,
10 NOT IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NULL($t2)], SAL=[$t1], EXPR$1=[$t3])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal NOT IN nullable
select sal,
10 NOT IN (
select case when true 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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[IS FALSE($t2)], expr#6=[null:BOOLEAN], expr#7=[AND($t5, $t6)], expr#8=[IS NOT FALSE($t2)], expr#9=[IS NULL($t2)], expr#10=[AND($t8, $t9)], expr#11=[OR($t4, $t7, $t10)], SAL=[$t1], EXPR$1=[$t11])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project null IN required is unknown
select sal,
cast(null as int) IN (
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], expr#7=[IS NULL($t6)], SAL=[$t1], EXPR$1=[$t7])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN null
select sal from "scott".emp
where cast(null as int) IN (
select cast(null as int)
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal IN null non-correlated
select sal from "scott".emp
where 123 IN (
select cast(null as int)
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null IN literal non-correlated
select sal from "scott".emp
where cast(null as int) IN (
select 1
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null IN required
select sal from "scott".emp
where cast(null as int) IN (
select deptno
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null IN nullable
select sal from "scott".emp
where cast(null as int) IN (
select case when true then deptno else null end
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal IN required
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
(14 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal IN nullable
select sal from "scott".emp
where 10 IN (
select case when true 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
(14 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN null non-correlated
select sal from "scott".emp
where cast(null as int) NOT IN (
select cast(null as int)
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], SAL=[$t1], $condition=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN null non-correlated
select sal from "scott".emp
where 123 NOT IN (
select cast(null as int)
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[NOT($t2)], expr#6=[IS NOT NULL($t2)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], expr#9=[OR($t4, $t8)], SAL=[$t1], $condition=[$t9])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN literal non-correlated
select sal from "scott".emp
where cast(null as int) NOT IN (
select 1
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], SAL=[$t1], $condition=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN required
select sal from "scott".emp
where cast(null as int) NOT IN (
select deptno
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], SAL=[$t1], $condition=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN nullable
select sal from "scott".emp
where cast(null as int) NOT IN (
select case when true then deptno else null end
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], SAL=[$t1], $condition=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN required
select sal from "scott".emp
where 10 NOT IN (
select deptno
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[NOT($t2)], expr#6=[IS NOT NULL($t2)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], expr#9=[OR($t4, $t8)], SAL=[$t1], $condition=[$t9])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN nullable
select sal from "scott".emp
where 10 NOT IN (
select case when true then deptno else null end
from "scott".dept);
SAL
-----
(0 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[NOT($t2)], expr#6=[IS NOT NULL($t2)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], expr#9=[OR($t4, $t8)], SAL=[$t1], $condition=[$t9])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN required is unknown
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL($t3)], expr#6=[AND($t4, $t5)], expr#7=[IS NULL($t6)], SAL=[$t1], $condition=[$t7])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
#-------------------------------
# Test filter null IN null correlated
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
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal IN null correlated
select sal from "scott".emp e
where 123 IN (
select cast(null as int)
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null IN literal correlated
select sal from "scott".emp e
where cast(null as int) IN (
select 1
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null IN required correlated
select sal from "scott".emp e
where cast(null as int) IN (
select deptno
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal IN null liter with query that can not be trivially simplified
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
-----
(0 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[RAND()], expr#6=[CAST($t5):INTEGER NOT NULL], expr#7=[2], expr#8=[MOD($t6, $t7)], expr#9=[3], expr#10=[=($t8, $t9)], expr#11=[IS NOT NULL($t4)], expr#12=[AND($t4, $t11)], expr#13=[OR($t10, $t12)], SAL=[$t1], $condition=[$t13])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], DEPTNO=[$t0], $f1=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN nullable correlated
select sal from "scott".emp e
where cast(null as int) IN (
select case when true then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal IN required correlated
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
(3 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)], DEPTNO=[$t0], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Test filter literal IN nullable correlated
select sal from "scott".emp e
where 10 IN (
select case when true then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
---------
1300.00
2450.00
5000.00
(3 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)], DEPTNO=[$t0], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Test filter null NOT IN null correlated
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
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal NOT IN null correlated
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
-----
(0 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1], $condition=[$t8])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], DEPTNO=[$t0], $f1=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN literal correlated
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
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null NOT IN required correlated
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
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter null NOT IN nullable correlated
select sal from "scott".emp e
where cast(null as int) NOT IN (
select case when true then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
(0 rows)
!ok
EnumerableValues(tuples=[[]])
!plan
# Test filter literal NOT IN required correlated
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
(11 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1], $condition=[$t8])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN nullable correlated
select sal from "scott".emp e
where 10 NOT IN (
select case when true 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
(11 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1], $condition=[$t8])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t0, $t4)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN required is unknown correlated
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
(14 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project constant IN an expression that is sometimes null
select sal,
20 IN (
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
(14 rows)
!ok
# Test project constant IN an nullable expression in an empty relation
select sal,
20 IN (
select case when deptno > 10 then deptno else null end
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
(14 rows)
!ok
# Test project null IN an nullable expression in an empty relation
select sal,
cast(null as integer) IN (
select case when deptno > 10 then deptno else null end
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
(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
(6 rows)
!ok
EnumerableHashJoin(condition=[=($7, $9)], joinType=[semi])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], EXPR$0=[MAX($7)])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# 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
(14 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], EXPR$0=[MAX($1)])
EnumerableHashJoin(condition=[=($1, $9)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableTableScan(table=[[scott, EMP]])
!plan
!use scott
# [CALCITE-1513] Correlated NOT IN query throws AssertionError
select count(*) as c
from "scott".emp as e
where sal + 100 not in (
select deptno
from dept
where dname = e.ename);
C
----
14
(1 row)
!ok
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t1, $t8)], expr#10=[IS NULL($t0)], expr#11=[IS NOT NULL($t6)], expr#12=[<($t2, $t1)], expr#13=[OR($t10, $t11, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[OR($t9, $t14)], proj#0..7=[{exprs}], $condition=[$t15])
EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $7))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[100], expr#8=[+($t2, $t7)], expr#9=[CAST($t1):VARCHAR(14)], SAL=[$t2], c=[$t4], ck=[$t5], $f5=[$t8], ENAME0=[$t9])
EnumerableMergeJoin(condition=[=($3, $6)], joinType=[left])
EnumerableSort(sort0=[$3], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t1):VARCHAR(14)], proj#0..1=[{exprs}], SAL=[$t5], ENAME0=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT], expr#4=[IS NOT NULL($t1)], c=[$t3], ck=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$0], sort1=[$2], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Correlated ANY sub-query
select empno from "scott".emp as e
where e.empno > ANY(
select 2 from "scott".dept e2 where e2.deptno = e.deptno) ;
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[>($t0, $t2)], expr#8=[IS NULL($t5)], expr#9=[0], expr#10=[=($t3, $t9)], expr#11=[OR($t8, $t10)], expr#12=[IS NOT TRUE($t11)], expr#13=[AND($t7, $t12)], expr#14=[IS NOT TRUE($t7)], expr#15=[>($t3, $t4)], expr#16=[IS NOT TRUE($t15)], expr#17=[AND($t7, $t12, $t14, $t16)], expr#18=[OR($t13, $t17)], EMPNO=[$t0], $condition=[$t18])
EnumerableMergeJoin(condition=[=($1, $6)], joinType=[left])
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[1:BIGINT], expr#5=[true], m=[$t3], c=[$t4], d=[$t4], trueLiteral=[$t5], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
EMPNO
-------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
(14 rows)
!ok
# # inner query produces empty result therefore ANY should produce 'false'
select empno,
e.deptno > ANY(
select 2 from "scott".dept e2 where e2.deptno = e.empno) from "scott".emp as e;
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[>($t1, $t2)], expr#8=[IS TRUE($t7)], expr#9=[IS NULL($t5)], expr#10=[0], expr#11=[=($t3, $t10)], expr#12=[OR($t9, $t11)], expr#13=[IS NOT TRUE($t12)], expr#14=[AND($t8, $t13)], expr#15=[>($t3, $t4)], expr#16=[IS TRUE($t15)], expr#17=[null:BOOLEAN], expr#18=[IS NOT TRUE($t7)], expr#19=[AND($t16, $t17, $t13, $t18)], expr#20=[IS NOT TRUE($t15)], expr#21=[AND($t7, $t13, $t18, $t20)], expr#22=[OR($t14, $t19, $t21)], EMPNO=[$t0], EXPR$1=[$t22])
EnumerableMergeJoin(condition=[=($0, $6)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], m=[$t1], c=[$t2], d=[$t2], trueLiteral=[$t3], DEPTNO0=[$t0])
EnumerableAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()])
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
(14 rows)
!ok
# [CALCITE-4560] Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate
# The employee KING has no manager (NULL) so before the fix the following query was missing
# this employee from the result set.
select ename
from "scott".emp as e1
where exists
(select 1 from "scott".emp as e2 where coalesce(e1.mgr,0)=coalesce(e2.mgr,0));
# The plan before the fix was wrong but also inefficient since it required the generation of
# a value generator (see RelDecorrelator code). The value generator is not present in the
# following plan (two scans of EMP table instead of three).
EnumerableCalc(expr#0..2=[{inputs}], ENAME=[$t1])
EnumerableHashJoin(condition=[=($2, $3)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)], expr#9=[CAST($t3):INTEGER], expr#10=[0], expr#11=[CASE($t8, $t9, $t10)], proj#0..1=[{exprs}], $f3=[$t11])
EnumerableTableScan(table=[[scott, EMP]])
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
(14 rows)
!ok
!set outputformat mysql
# Correlated SOME sub-query with not equality
# Both sides Not NUll.
select empno
from "scott".emp emp1
where empno <> some (select emp2.empno from "scott".emp emp2 where emp2.empno = emp1.empno);
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[<>($t2, $t1)], expr#7=[1], expr#8=[<=($t2, $t7)], expr#9=[<>($t0, $t3)], expr#10=[IS NULL($t4)], expr#11=[0], expr#12=[=($t1, $t11)], expr#13=[OR($t10, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[AND($t6, $t8, $t9, $t14)], expr#16=[=($t2, $t7)], expr#17=[IS NOT NULL($t2)], expr#18=[AND($t6, $t17)], expr#19=[IS NOT TRUE($t18)], expr#20=[AND($t16, $t9, $t14, $t19)], expr#21=[AND($t6, $t8)], expr#22=[IS NOT TRUE($t21)], expr#23=[IS NOT TRUE($t16)], expr#24=[AND($t14, $t22, $t23)], expr#25=[OR($t15, $t20, $t24)], EMPNO=[$t0], $condition=[$t25])
EnumerableMergeJoin(condition=[=($0, $5)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1:BIGINT], expr#9=[true], c=[$t8], d=[$t8], m=[$t0], trueLiteral=[$t9], EMPNO1=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+
| EMPNO |
+-------+
+-------+
(0 rows)
!ok
# Previous, as scalar sub-query.
select empno, empno <> some (select emp2.empno from "scott".emp emp2 where emp2.empno = emp1.empno) as x
from "scott".emp emp1;
+-------+-------+
| EMPNO | X |
+-------+-------+
| 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
# left side NOT NULL, correlated sub-query nullable.
select *
from "scott".emp emp1
where empno <> some (select comm from "scott".emp where deptno = emp1.deptno);
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t0, $t10)], expr#20=[IS NULL($t11)], expr#21=[0], expr#22=[=($t8, $t21)], expr#23=[OR($t20, $t22)], expr#24=[IS NOT TRUE($t23)], expr#25=[AND($t18, $t19, $t24)], expr#26=[IS NOT TRUE($t18)], expr#27=[AND($t24, $t26)], expr#28=[OR($t25, $t27)], proj#0..7=[{exprs}], $condition=[$t28])
EnumerableMergeJoin(condition=[=($7, $12)], joinType=[left])
EnumerableSort(sort0=[$7], dir0=[ASC])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], c=[$t1], d=[$t2], m=[$t3], trueLiteral=[$t4], DEPTNO=[$t0])
EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], DEPTNO=[$t1], COMM=[$t0], m=[$t2], $g_0=[$t5], $g_1=[$t7])
EnumerableAggregate(group=[{6, 7}], groups=[[{6, 7}, {7}]], m=[MAX($6)], $g=[GROUPING($7, $6)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+--------+----------+------+------------+---------+---------+--------+
| 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
# Previous, as scalar sub-query.
select *, empno <> some (select comm from "scott".emp where deptno = emp1.deptno) as x
from "scott".emp as emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X |
+-------+--------+-----------+------+------------+---------+---------+--------+------+
| 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 | true |
| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | true |
| 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 | true |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
+-------+--------+-----------+------+------------+---------+---------+--------+------+
(14 rows)
!ok
# left side NOT NULL, correlated sub-query empty.
select *
from "scott".emp as emp1
where empno <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno);
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[<>($t0, $t10)], expr#17=[IS NULL($t11)], expr#18=[0], expr#19=[=($t8, $t18)], expr#20=[OR($t17, $t19)], expr#21=[IS NOT TRUE($t20)], expr#22=[AND($t13, $t15, $t16, $t21)], expr#23=[=($t9, $t14)], expr#24=[IS NOT NULL($t9)], expr#25=[AND($t13, $t24)], expr#26=[IS NOT TRUE($t25)], expr#27=[AND($t23, $t16, $t21, $t26)], expr#28=[AND($t13, $t15)], expr#29=[IS NOT TRUE($t28)], expr#30=[IS NOT TRUE($t23)], expr#31=[AND($t21, $t29, $t30)], expr#32=[OR($t22, $t27, $t31)], proj#0..7=[{exprs}], $condition=[$t32])
EnumerableMergeJoin(condition=[=($0, $12)], joinType=[left])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[true], c=[$t1], d=[$t2], m=[$t4], trueLiteral=[$t5], DEPTNO0=[$t0])
EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7])
EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], m=[MAX($1)], $g=[GROUPING($0, $1)])
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 | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)
!ok
# Previous, as scalar sub-query.
select *, empno <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno) as x
from "scott".emp as emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false |
| 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 | false |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false |
| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
(14 rows)
!ok
# left side nullable, correlated sub-query empty.
select *
from "scott".emp as emp1
where comm <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno);
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t6, $t10)], expr#20=[IS NULL($t11)], expr#21=[IS NULL($t6)], expr#22=[0], expr#23=[=($t8, $t22)], expr#24=[OR($t20, $t21, $t23)], expr#25=[IS NOT TRUE($t24)], expr#26=[AND($t18, $t19, $t25)], expr#27=[IS NOT TRUE($t18)], expr#28=[AND($t25, $t27)], expr#29=[OR($t26, $t28)], proj#0..7=[{exprs}], $condition=[$t29])
EnumerableMergeJoin(condition=[=($0, $12)], joinType=[left])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[true], c=[$t1], d=[$t2], m=[$t4], trueLiteral=[$t5], DEPTNO0=[$t0])
EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7])
EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], m=[MAX($1)], $g=[GROUPING($0, $1)])
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 | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)
!ok
# Previous, as scalar sub-query.
select *, comm <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno) as x
from "scott".emp as emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false |
| 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 | false |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false |
| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
(14 rows)
!ok
# left side nullable, correlated sub-query nullable.
select *
from "scott".emp emp1
where emp1.comm <> some (select comm from "scott".emp emp2 where emp2.sal = emp1.sal);
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t6, $t10)], expr#20=[IS NULL($t11)], expr#21=[IS NULL($t6)], expr#22=[0], expr#23=[=($t8, $t22)], expr#24=[OR($t20, $t21, $t23)], expr#25=[IS NOT TRUE($t24)], expr#26=[AND($t18, $t19, $t25)], expr#27=[IS NOT TRUE($t18)], expr#28=[AND($t25, $t27)], expr#29=[OR($t26, $t28)], proj#0..7=[{exprs}], $condition=[$t29])
EnumerableMergeJoin(condition=[=($5, $12)], joinType=[left])
EnumerableSort(sort0=[$5], dir0=[ASC])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], c=[$t1], d=[$t2], m=[$t3], trueLiteral=[$t4], SAL=[$t0])
EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7])
EnumerableAggregate(group=[{5, 6}], groups=[[{5, 6}, {5}]], m=[MAX($6)], $g=[GROUPING($5, $6)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
(2 rows)
!ok
# Previous, as scalar sub-query.
select *, emp1.comm <> some (select comm from "scott".emp where sal = emp1.sal) as x
from "scott".emp emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+
| 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 | true |
| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true |
| 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 | false |
| 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-4486] UNIQUE predicate
!use scott
!set expand false
!set outputformat mysql
# singleton keys have unique value which excludes fully or partially null rows.
select deptno
from "scott".dept
where unique (select comm from "scott".emp where comm is not null);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select comm from "scott".emp where comm is not null) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, but NOT UNIQUE.
select deptno, not unique (select comm from "scott".emp where comm is not null) as u
from "scott".dept;
+--------+-------+
| DEPTNO | U |
+--------+-------+
| 10 | false |
| 20 | false |
| 30 | false |
| 40 | false |
+--------+-------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# singleton keys have unique value which includes partial null rows.
select deptno
from "scott".dept
where unique (select comm from "scott".emp);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select comm from "scott".emp) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# singleton keys which includes fully null rows.
select deptno
from "scott".dept
where unique (select comm from "scott".emp where comm is null);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableValues(tuples=[[]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select comm from "scott".emp where comm is null) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableValues(tuples=[[]])
!plan
# composite keys have unique value which excludes fully or partially null rows.
select deptno
from "scott".dept
where unique (select comm, sal from "scott".emp where comm is not null);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], expr#9=[IS NOT NULL($t5)], expr#10=[AND($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select comm, sal from "scott".emp where comm is not null) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], expr#9=[IS NOT NULL($t5)], expr#10=[AND($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# composite keys have unique value which includes fully or partially null rows.
select deptno
from "scott".dept
where unique (select comm, sal from "scott".emp);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], expr#9=[IS NOT NULL($t5)], expr#10=[AND($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select comm, sal from "scott".emp) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 6}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t6)], expr#9=[IS NOT NULL($t5)], expr#10=[AND($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# singleton keys have duplicate value
select deptno
from "scott".dept
where unique (select deptno from "scott".emp);
+--------+
| DEPTNO |
+--------+
+--------+
(0 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select deptno from "scott".emp) as u
from "scott".dept;
+--------+-------+
| DEPTNO | U |
+--------+-------+
| 10 | false |
| 20 | false |
| 30 | false |
| 40 | false |
+--------+-------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# composite keys have duplicate value.
select deptno
from "scott".dept
where unique (select deptno, sal from "scott".emp where sal = 3000);
+--------+
| DEPTNO |
+--------+
+--------+
(0 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12, 2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select deptno, sal from "scott".emp where sal = 3000) as u
from "scott".dept;
+--------+-------+
| DEPTNO | U |
+--------+-------+
| 10 | false |
| 20 | false |
| 30 | false |
| 40 | false |
+--------+-------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12, 2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, but NOT UNIQUE.
select deptno, not unique (select deptno, sal from "scott".emp where sal = 3000) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1], expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12, 2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# as above, but sub-query empty.
select deptno
from "scott".dept
where unique (select deptno from "scott".emp where deptno = 35);
+--------+
| DEPTNO |
+--------+
| 10 |
| 30 |
| 40 |
| 20 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], $condition=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[35], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select deptno, unique (select deptno from "scott".emp where deptno = 35) as u
from "scott".dept;
+--------+------+
| DEPTNO | U |
+--------+------+
| 10 | true |
| 20 | true |
| 30 | true |
| 40 | true |
+--------+------+
(4 rows)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], DEPTNO=[$t0], U=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[35], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# singleton keys which a uniqueness constraint indicates that the relation is already unique.
select *
from "scott".dept
where unique (select deptno from "scott".dept);
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
(4 rows)
!ok
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# as above, sub-query with limit.
select *
from "scott".dept
where unique (select deptno from "scott".emp limit 1);
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
(4 rows)
!ok
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# as above, sub-query with distinct.
select deptno
from "scott".dept
where unique (select distinct deptno, sal from "scott".emp where sal = 3000);
+--------+
| DEPTNO |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# as above, sub-query with group by.
select deptno
from "scott".dept
where unique (select job from "scott".emp group by job);
+--------+
| DEPTNO |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
+--------+
(4 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Correlated UNIQUE predicate.
select *
from "scott".dept
where unique (
select 1 from "scott".emp where dept.deptno = emp.deptno);
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
(1 row)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NULL($t3)], proj#0..2=[{exprs}], $condition=[$t5])
EnumerableMergeJoin(condition=[=($0, $4)], joinType=[left])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], DEPTNO=[$t0], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
select *, unique (select 1 from "scott".emp where dept.deptno = emp.deptno) as u
from "scott".dept;
+--------+------------+----------+-------+
| DEPTNO | DNAME | LOC | U |
+--------+------------+----------+-------+
| 10 | ACCOUNTING | NEW YORK | false |
| 20 | RESEARCH | DALLAS | false |
| 30 | SALES | CHICAGO | false |
| 40 | OPERATIONS | BOSTON | true |
+--------+------------+----------+-------+
(4 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NULL($t3)], proj#0..2=[{exprs}], U=[$t5])
EnumerableMergeJoin(condition=[=($0, $4)], joinType=[left])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], DEPTNO=[$t0], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
!plan
# as above, but NOT UNIQUE.
select *
from "scott".dept
where not unique (
select 1 from "scott".emp where dept.deptno = emp.deptno);
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
(3 rows)
!ok
EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t1], DNAME=[$t2], LOC=[$t3])
EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t1, $t2)], DEPTNO=[$t0], $condition=[$t3])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Previous, as scalar sub-query.
select *, not unique (select 1 from "scott".emp where dept.deptno = emp.deptno) as u
from "scott".dept;
+--------+------------+----------+-------+
| DEPTNO | DNAME | LOC | U |
+--------+------------+----------+-------+
| 10 | ACCOUNTING | NEW YORK | true |
| 20 | RESEARCH | DALLAS | true |
| 30 | SALES | CHICAGO | true |
| 40 | OPERATIONS | BOSTON | false |
+--------+------------+----------+-------+
(4 rows)
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t3)], proj#0..2=[{exprs}], U=[$t5])
EnumerableMergeJoin(condition=[=($0, $4)], joinType=[left])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[1], expr#4=[>($t1, $t3)], i=[$t2], DEPTNO=[$t0], $condition=[$t4])
EnumerableAggregate(group=[{7}], c=[COUNT()])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
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