# 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

# End sub-query.iq