core/src/test/resources/sql/blank.iq

# blank.iq - Queries that start from a blank schema and create their own tables
#
# 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 blank
!set outputformat mysql

create table foo (i int not null, j int);
(0 rows modified)

!update
insert into foo values (1, 0);
(1 row modified)

!update
insert into foo values (0, 2);
(1 row modified)

!update
select * from foo;
+---+---+
| I | J |
+---+---+
| 0 | 2 |
| 1 | 0 |
+---+---+
(2 rows)

!ok

create table bar as select i, i + j as k from foo;
(0 rows modified)

!update

select * from bar;
+---+---+
| I | K |
+---+---+
| 0 | 2 |
| 1 | 1 |
+---+---+
(2 rows)

!ok

# Correlated non-equi IN
select * from foo as f where i in (
  select j from foo where i > f.i);
+---+---+
| I | J |
+---+---+
| 0 | 2 |
+---+---+
(1 row)

!ok

# [CALCITE-1493] Wrong plan for NOT IN correlated queries
create table table1(i int, j int);
(0 rows modified)

!update
create table table2(i int, j int);
(0 rows modified)

!update
insert into table1 values (1, 2), (1, 3);
(2 rows modified)

!update
insert into table2 values (NULL, 1), (2, 1);
(2 rows modified)

!update
# Checked on Oracle
!set lateDecorrelate true
select i, j from table1 where table1.j NOT IN (select i from table2 where table1.i=table2.j);
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)], expr#10=[IS NULL($t1)], expr#11=[IS NOT NULL($t7)], expr#12=[<($t4, $t3)], expr#13=[OR($t10, $t11, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[OR($t9, $t14)], proj#0..1=[{exprs}], $condition=[$t15])
  EnumerableMergeJoin(condition=[AND(=($0, $6), =($1, $5))], joinType=[left])
    EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
      EnumerableMergeJoin(condition=[=($0, $2)], joinType=[left])
        EnumerableSort(sort0=[$0], dir0=[ASC])
          EnumerableTableScan(table=[[BLANK, TABLE1]])
        EnumerableSort(sort0=[$0], dir0=[ASC])
          EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
            EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], proj#0..1=[{exprs}], $condition=[$t2])
              EnumerableTableScan(table=[[BLANK, TABLE2]])
    EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
      EnumerableAggregate(group=[{0, 1}], i=[LITERAL_AGG(true)])
        EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], expr#3=[IS NOT NULL($t0)], expr#4=[AND($t2, $t3)], proj#0..1=[{exprs}], $condition=[$t4])
          EnumerableTableScan(table=[[BLANK, TABLE2]])
!plan
+---+---+
| I | J |
+---+---+
+---+---+
(0 rows)

!ok

select * from table1 where j not in (select i from table2);
+---+---+
| I | J |
+---+---+
+---+---+
(0 rows)

!ok

select * from table1 where j not in (select i from table2) or i = 1;
+---+---+
| I | J |
+---+---+
| 1 | 2 |
| 1 | 3 |
+---+---+
(2 rows)

!ok

select * from table1 where j not in (select i from table2) or j = 2;
+---+---+
| I | J |
+---+---+
| 1 | 2 |
+---+---+
(1 row)

!ok

select * from table1 where j not in (select i from table2) or j = 3;
+---+---+
| I | J |
+---+---+
| 1 | 3 |
+---+---+
(1 row)

!ok

# End blank.iq