#At file:///home/rl136806/mysql/repo/mysql-6.0-exists2in/
2719 Roy Lyseng 2008-11-14
WL#4389 Convert EXISTS to semijoin under certain conditions
EXISTS is converted to semijoin just like IN queries are.
Both correlated and non-correlated queries are covered.
The WHERE clause of an EXISTS subquery is converted to a semi-join predicate for the parent join nest.
added:
mysql-test/r/subselect_systematic.result
mysql-test/t/subselect_systematic.test
modified:
mysql-test/r/group_min_max.result
mysql-test/r/subselect.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/t/subselect.test
sql/item_subselect.h
sql/sql_select.cc
sql/sql_select.h
sql/table.h
per-file messages:
mysql-test/r/group_min_max.result
Changed EXPLAIN output
mysql-test/r/subselect.result
Changed EXPLAIN output
mysql-test/r/subselect_no_mat.result
Changed EXPLAIN output
mysql-test/r/subselect_no_opts.result
Changed EXPLAIN output
mysql-test/r/subselect_no_semijoin.result
Changed EXPLAIN output
mysql-test/r/subselect_systematic.result
Added test for systematic testing of EXISTS/IN queries
mysql-test/t/subselect.test
Different execution strategy caused changes in sorting of output
mysql-test/t/subselect_systematic.test
Added test for systematic testing of EXISTS/IN queries
sql/item_subselect.h
Some fields moved from Item_in_subselect to Item_exists_subselect because they are needed for both IN and EXISTS queries.
sql/sql_select.cc
JOIN::prepare():
- EXISTS queries are selected as candidates for conversion to semijoin like IN.
subquery_types_allow_materialization():
- Simple handling of types for EXISTS queries - always allow materialization
convert_subq_to_sj()
- WHERE clause of EXISTS subquery is added to parent's WHERE or ON clause
- For non-correlated queries a TRUE predicate is added.
JOIN::flatten_subqueries():
- Handles item classes of type Item_exists_subselect to cover both EXISTS and IN queries
- substitution is always NULL for EXISTS, so treatment of substitution field is skipped
sql/sql_select.h
Type change, see item_subselect.h
sql/table.h
Type change, see item_subselect.h
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2008-08-28 11:17:29 +0000
+++ b/mysql-test/r/group_min_max.result 2008-11-14 09:46:50 +0000
@@ -1358,14 +1358,14 @@ explain select a1,a2,b,min(c),max(c) fro
where exists ( select * from t2 where t2.c = t1.c )
group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
+1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index; Using temporary; Using filesort
+1 PRIMARY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index; FirstMatch(t1)
explain select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c > 'b1' )
group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
-2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
+1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index; Using temporary; Using filesort
+1 PRIMARY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index; FirstMatch(t1)
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2008-10-29 20:35:16 +0000
+++ b/mysql-test/r/subselect.result 2008-11-14 09:46:50 +0000
@@ -328,11 +328,11 @@ patient_uq clinic_uq
2 2
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
+1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index
+1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer
Warnings:
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
+Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where (`test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`)
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1,t2,t3;
@@ -2270,11 +2270,11 @@ a b
3 4
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(up)
Warnings:
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
+Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`up`.`a`)
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4053,8 +4053,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3)
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
id st
-3 FL
1 GA
+3 FL
7 FL
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4273,11 +4273,11 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-10-15 23:14:03 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-11-14 09:46:50 +0000
@@ -332,11 +332,11 @@ patient_uq clinic_uq
2 2
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
+1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index
+1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer
Warnings:
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
+Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where (`test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`)
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1,t2,t3;
@@ -2274,11 +2274,11 @@ a b
3 4
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(up)
Warnings:
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
+Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`up`.`a`)
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4057,8 +4057,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3)
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
id st
-3 FL
1 GA
+3 FL
7 FL
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4277,11 +4277,11 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2008-10-15 23:14:03 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-11-14 09:46:50 +0000
@@ -4057,8 +4057,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3)
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
id st
-3 FL
1 GA
+3 FL
7 FL
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4277,11 +4277,11 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2008-10-15 23:14:03 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-11-14 09:46:50 +0000
@@ -4057,8 +4057,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3)
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
id st
-3 FL
1 GA
+3 FL
7 FL
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4277,11 +4277,11 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
=== added file 'mysql-test/r/subselect_systematic.result'
--- a/mysql-test/r/subselect_systematic.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/subselect_systematic.result 2008-11-14 09:46:50 +0000
@@ -0,0 +1,2362 @@
+-- SUBQUERY tests working on the following tables:
+-- empty - Table with no rows
+-- nulls - Table containing a single row, with a null value
+-- t1 - contains data useful for subqueries
+-- t2 - contains data useful for subqueries
+-- t1n - contains data useful for subqueries (with null values)
+-- t2n - contains data useful for subqueries (with null values)
+drop table if exists empty;
+drop table if exists nulls;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t1n;
+drop table if exists t2n;
+create table empty(id integer primary key, v integer);
+create table nulls(id integer primary key, v integer, l char(1), r char(1));
+insert into nulls values(0, null, 'l', 'r');
+create table t1(
+id integer primary key,
+u integer not null unique,
+v integer not null,
+vi integer not null, index(vi),
+s char(1));
+create table t2(
+id integer primary key,
+u integer not null unique,
+v integer not null,
+vi integer not null, index(vi),
+s char(1));
+insert into t1 values(10, 10, 10, 10, 'l');
+insert into t2 values(10, 10, 10, 10, 'r');
+insert into t1 values(20, 20, 20, 20, 'l');
+insert into t2 values(30, 30, 30, 30, 'r');
+insert into t1 values(40, 40, 40, 40, 'l');
+insert into t1 values(41, 41, 40, 40, 'l');
+insert into t1 values(50, 50, 50, 50, 'l');
+insert into t1 values(51, 51, 50, 50, 'l');
+insert into t2 values(50, 50, 50, 50, 'r');
+insert into t1 values(60, 60, 60, 60, 'l');
+insert into t1 values(61, 61, 60, 60, 'l');
+insert into t2 values(60, 60, 60, 60, 'r');
+insert into t2 values(61, 61, 60, 60, 'r');
+insert into t1 values(70, 70, 70, 70, 'l');
+insert into t2 values(70, 70, 70, 70, 'r');
+insert into t2 values(71, 71, 70, 70, 'r');
+insert into t2 values(80, 80, 80, 80, 'r');
+insert into t2 values(81, 81, 80, 80, 'r');
+create table t1n(
+id integer primary key,
+u integer unique,
+g integer,
+v integer,
+vi integer, index(vi),
+s char(1));
+create table t2n(
+id integer primary key,
+u integer unique,
+g integer,
+v integer,
+vi integer, index(vi),
+s char(1));
+insert into t1n values(10, 10, 10, 10, 10, 'l');
+insert into t2n values(10, 10, 10, 10, 10, 'r');
+insert into t1n values(20, 20, 20, 20, 20, 'l');
+insert into t2n values(30, 30, 30, 30, 30, 'r');
+insert into t1n values(40, 40, 40, 40, 40, 'l');
+insert into t1n values(41, 41, 40, null, null, 'l');
+insert into t1n values(50, 50, 50, 50, 50, 'l');
+insert into t1n values(51, 51, 50, null, null, 'l');
+insert into t2n values(50, 50, 50, 50, 50, 'r');
+insert into t1n values(60, 60, 60, 60, 60, 'l');
+insert into t1n values(61, 61, 60, null, null, 'l');
+insert into t2n values(60, 60, 60, 60, 60, 'r');
+insert into t2n values(61, 61, 60, null, null, 'r');
+insert into t1n values(70, 70, 70, 70, 70, 'l');
+insert into t2n values(70, 70, 70, 70, 70, 'r');
+insert into t2n values(71, 71, 70, null, null, 'r');
+insert into t2n values(80, 80, 80, 80, 80, 'r');
+insert into t2n values(81, 81, 80, null, null, 'r');
+insert into t1n values(90, 90, 90, null, null, 'l');
+insert into t2n values(100,100,100,null, null, 'r');
+-- Tests on Empty table
+-- non-key field
+-- [NOT] IN, =ANY, <>ALL, [NOT] EXISTS
+-- IN & Empty table -> 0 rows
+select *
+from empty
+where v in(select v from t2);
+id v
+-- NOT IN & Empty table -> 0 rows
+select *
+from empty
+where v not in(select v from t2);
+id v
+-- =ANY & Empty table -> 0 rows
+select *
+from empty
+where v=any(select v from t2);
+id v
+-- <>ALL & Empty table -> 0 rows
+select *
+from empty
+where v<>all(select v from t2);
+id v
+-- Uncorrelated EXISTS & Empty table -> 0 rows
+select *
+from empty
+where exists(select v from t2);
+id v
+-- Uncorrelated NOT EXISTS & Empty table -> 0 rows
+select *
+from empty
+where not exists(select v from t2);
+id v
+-- Correlated EXISTS & Empty table -> 0 rows
+select *
+from empty
+where exists(select v from t2
+where empty.v=t2.v);
+id v
+-- Correlated NOT EXISTS & Empty table -> 0 rows
+select *
+from empty
+where not exists(select v from t2
+where empty.v=t2.v);
+id v
+-- Tests on Nulls table
+-- non-key field
+-- [NOT] IN, =ANY, <>ALL, [NOT] EXISTS
+-- IN & Nulls table -> 0 rows
+select *
+from nulls
+where v in(select v from t2);
+id v l r
+-- IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v in(select v from t2) is unknown;
+id v l r
+0 NULL l r
+-- NOT IN & Nulls table -> 0 rows
+select *
+from nulls
+where v not in(select v from t2);
+id v l r
+-- NOT IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v not in(select v from t2) is unknown;
+id v l r
+0 NULL l r
+-- IN & Nulls table -> 0 rows
+select *
+from t1
+where v in(select v from nulls);
+id u v vi s
+-- IN & Nulls table & UNKNOWN -> 9 rows
+select *
+from t1
+where v in(select v from nulls) is unknown;
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- NOT IN & Nulls table -> 0 rows
+select *
+from t1
+where v not in(select v from nulls);
+id u v vi s
+-- NOT IN & Nulls table & UNKNOWN -> 9 rows
+select *
+from t1
+where v not in(select v from nulls) is unknown;
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & Nulls table -> 0 rows
+select *
+from nulls
+where v in(select v from nulls);
+id v l r
+-- IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v in(select v from nulls) is unknown;
+id v l r
+0 NULL l r
+-- NOT IN & Nulls table -> 0 rows
+select *
+from nulls
+where v not in(select v from nulls);
+id v l r
+-- NOT IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v not in(select v from nulls) is unknown;
+id v l r
+0 NULL l r
+-- =ANY & Nulls table -> 0 rows
+select *
+from nulls
+where v=any(select v from t2);
+id v l r
+-- <>ALL & Nulls table -> 0 rows
+select *
+from nulls
+where v<>all(select v from t2);
+id v l r
+-- Uncorrelated EXISTS & Nulls table -> 1 rows
+select *
+from nulls
+where exists(select v from t2);
+id v l r
+0 NULL l r
+-- Uncorrelated NOT EXISTS & Nulls table -> 0 rows
+select *
+from nulls
+where not exists(select v from t2);
+id v l r
+-- Correlated EXISTS & Nulls table -> 0 rows
+select *
+from nulls
+where exists(select v from t2
+where nulls.v=t2.v);
+id v l r
+-- Correlated NOT EXISTS & Nulls table -> 1 rows
+select *
+from nulls
+where not exists(select v from t2
+where nulls.v=t2.v);
+id v l r
+0 NULL l r
+-- Tests on tables without nullable columns
+-- non-key field, indexed field, primary key field, unique field
+-- [NOT] IN, <op>ANY, <op>ALL, [NOT] EXISTS
+-- IN & t1 table & non-key field -> 6 rows
+select *
+from t1
+where v in(select v from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & non-key field & TRUE -> 6 rows
+select *
+from t1
+where v in(select v from t2) is true;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & non-key field & UNKNOWN -> 0 rows
+select *
+from t1
+where v in(select v from t2) is unknown;
+id u v vi s
+-- IN & t1 table & non-key field & FALSE -> 3 rows
+select *
+from t1
+where v in(select v from t2) is false;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & non-key field -> 3 rows
+select *
+from t1
+where v not in(select v from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & non-key field & TRUE -> 3 rows
+select *
+from t1
+where v not in(select v from t2) is true;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & non-key field & UNKNOWN -> 0 rows
+select *
+from t1
+where v not in(select v from t2) is unknown;
+id u v vi s
+-- NOT IN & t1 table & non-key field & FALSE -> 6 rows
+select *
+from t1
+where v not in(select v from t2) is false;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- =ANY & t1 table & non-key field -> 6 rows
+select *
+from t1
+where v=any(select v from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ALL & t1 table & non-key field -> 3 rows
+select *
+from t1
+where v<>all(select v from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- <ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<any(select v from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <=ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<=any(select v from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<>any(select v from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >ANY & t1 table & non-key field -> 8 rows
+select *
+from t1
+where id>any(select v from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >=ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id>=any(select v from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id<all(select v from t2);
+id u v vi s
+-- <=ALL & t1 table & non-key field -> 1 rows
+select *
+from t1
+where id<=all(select v from t2);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id=all(select v from t2);
+id u v vi s
+-- >ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id>all(select v from t2);
+id u v vi s
+-- >=ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id>=all(select v from t2);
+id u v vi s
+-- Uncorrelated EXISTS & t1 table & non-key field -> 9 rows
+select *
+from t1
+where exists(select v
+from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Uncorrelated NOT EXISTS & t1 table & non-key field -> 0 rows
+select *
+from t1
+where not exists(select v
+from t2);
+id u v vi s
+-- Correlated EXISTS & t1 table & non-key field -> 6 rows
+select *
+from t1
+where exists(select v from t2
+where t1.v=t2.v);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Correlated NOT EXISTS & t1 table & non-key field -> 3 rows
+select *
+from t1
+where not exists(select v from t2
+where t1.v=t2.v);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- IN & t1 table & indexed field -> 6 rows
+select *
+from t1
+where vi in(select vi from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & indexed field & TRUE -> 6 rows
+select *
+from t1
+where vi in(select vi from t2) is true;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & indexed field & UNKNOWN -> 0 rows
+select *
+from t1
+where vi in(select vi from t2) is unknown;
+id u v vi s
+-- IN & t1 table & indexed field & FALSE -> 3 rows
+select *
+from t1
+where vi in(select vi from t2) is false;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & indexed field -> 3 rows
+select *
+from t1
+where vi not in(select vi from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & indexed field & TRUE -> 3 rows
+select *
+from t1
+where vi not in(select vi from t2) is true;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- NOT IN & t1 table & indexed field & UNKNOWN -> 0 rows
+select *
+from t1
+where vi not in(select vi from t2) is unknown;
+id u v vi s
+-- NOT IN & t1 table & indexed field & FALSE -> 6 rows
+select *
+from t1
+where vi not in(select vi from t2) is false;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- =ANY & t1 table & indexed field -> 6 rows
+select *
+from t1
+where vi=any(select vi from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ALL & t1 table & indexed field -> 3 rows
+select *
+from t1
+where vi<>all(select vi from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- <ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<any(select vi from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <=ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<=any(select vi from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<>any(select vi from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >ANY & t1 table & indexed field -> 8 rows
+select *
+from t1
+where id>any(select vi from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >=ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id>=any(select vi from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id<all(select vi from t2);
+id u v vi s
+-- <=ALL & t1 table & indexed field -> 1 rows
+select *
+from t1
+where id<=all(select vi from t2);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id=all(select vi from t2);
+id u v vi s
+-- >ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id>all(select vi from t2);
+id u v vi s
+-- >=ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id>=all(select vi from t2);
+id u v vi s
+-- Uncorrelated EXISTS & t1 table & indexed field -> 9 rows
+select *
+from t1
+where exists(select vi
+from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Uncorrelated NOT EXISTS & t1 table & indexed field -> 0 rows
+select *
+from t1
+where not exists(select vi
+from t2);
+id u v vi s
+-- Correlated EXISTS & t1 table & indexed field -> 6 rows
+select *
+from t1
+where exists(select *
+from t2
+where t1.vi = t2.vi);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Correlated NOT EXISTS & t1 table & indexed field -> 3 rows
+select *
+from t1
+where not exists(select *
+from t2
+where t1.vi = t2.vi);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+-- IN & t1 table & primary key field -> 5 rows
+select *
+from t1
+where id in(select id from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & primary key field & TRUE -> 5 rows
+select *
+from t1
+where id in(select id from t2) is true;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & primary key field & UNKNOWN -> 0 rows
+select *
+from t1
+where id in(select id from t2) is unknown;
+id u v vi s
+-- IN & t1 table & primary key field & FALSE -> 4 rows
+select *
+from t1
+where id in(select id from t2) is false;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & primary key field -> 4 rows
+select *
+from t1
+where id not in(select id from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & primary key field & TRUE -> 4 rows
+select *
+from t1
+where id not in(select id from t2) is true;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & primary key field & UNKNOWN -> 0 rows
+select *
+from t1
+where id not in(select id from t2) is unknown;
+id u v vi s
+-- NOT IN & t1 table & primary key field & FALSE -> 5 rows
+select *
+from t1
+where id not in(select id from t2) is false;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- =ANY & t1 table & primary key field -> 5 rows
+select *
+from t1
+where id=any(select id from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ALL & t1 table & primary key field -> 4 rows
+select *
+from t1
+where id<>all(select id from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- <ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<any(select id from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <=ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<=any(select id from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<>any(select id from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >ANY & t1 table & primary key field -> 8 rows
+select *
+from t1
+where id>any(select id from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >=ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id>=any(select id from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id<all(select id from t2);
+id u v vi s
+-- <=ALL & t1 table & primary key field -> 1 rows
+select *
+from t1
+where id<=all(select id from t2);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id=all(select id from t2);
+id u v vi s
+-- >ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id>all(select id from t2);
+id u v vi s
+-- >=ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id>=all(select id from t2);
+id u v vi s
+-- Uncorrelated EXISTS & t1 table & primary key field -> 9 rows
+select *
+from t1
+where exists(select id
+from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Uncorrelated NOT EXISTS & t1 table & primary field -> 0 rows
+select *
+from t1
+where not exists(select id
+from t2);
+id u v vi s
+-- Correlated EXISTS & t1 table & primary key field -> 5 rows
+select *
+from t1
+where exists(select *
+from t2
+where t1.id = t2.id);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Correlated NOT EXISTS & t1 table & primary key field -> 4 rows
+select *
+from t1
+where not exists(select *
+from t2
+where t1.id = t2.id);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- IN & t1 table & unique field -> 5 rows
+select *
+from t1
+where u in(select u from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & unique field & TRUE -> 5 rows
+select *
+from t1
+where u in(select u from t2) is true;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- IN & t1 table & unique field & UNKNOWN -> 0 rows
+select *
+from t1
+where u in(select u from t2) is unknown;
+id u v vi s
+-- IN & t1 table & unique field & FALSE -> 4 rows
+select *
+from t1
+where u in(select u from t2) is false;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & unique field -> 4 rows
+select *
+from t1
+where u not in(select u from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & unique field & TRUE -> 4 rows
+select *
+from t1
+where u not in(select u from t2) is true;
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- NOT IN & t1 table & unique field & UNKNOWN -> 0 rows
+select *
+from t1
+where u not in(select u from t2) is unknown;
+id u v vi s
+-- NOT IN & t1 table & unique field & FALSE -> 5 rows
+select *
+from t1
+where u not in(select u from t2) is false;
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- =ANY & t1 table & unique field -> 5 rows
+select *
+from t1
+where u=any(select u from t2);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ALL & t1 table & unique field -> 4 rows
+select *
+from t1
+where u<>all(select u from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- <ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<any(select u from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <=ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<=any(select u from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <>ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<>any(select u from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >ANY & t1 table & unique field -> 8 rows
+select *
+from t1
+where u>any(select u from t2);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- >=ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u>=any(select u from t2);
+id u v vi s
+10 10 10 10 l
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+50 50 50 50 l
+51 51 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- <ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u<all(select u from t2);
+id u v vi s
+-- <=ALL & t1 table & unique field -> 1 rows
+select *
+from t1
+where u<=all(select u from t2);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u=all(select u from t2);
+id u v vi s
+-- >ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u>all(select u from t2);
+id u v vi s
+-- >=ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u>=all(select u from t2);
+id u v vi s
+-- Correlated EXISTS & t1 table & unique field -> 5 rows
+select *
+from t1
+where exists(select *
+from t2
+where t1.u = t2.u);
+id u v vi s
+10 10 10 10 l
+50 50 50 50 l
+60 60 60 60 l
+61 61 60 60 l
+70 70 70 70 l
+-- Correlated NOT EXISTS & t1 table & unique field -> 4 rows
+select *
+from t1
+where not exists(select *
+from t2
+where t1.u = t2.u);
+id u v vi s
+20 20 20 20 l
+40 40 40 40 l
+41 41 40 40 l
+51 51 50 50 l
+-- Tests on tables with nullable columns
+-- non-key field, indexed field, unique field
+-- [NOT] IN, <op>ANY, <op>ALL, [NOT] EXISTS
+-- IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v in(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v in(select v from t2n) is true;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN & t1n table & non-key field & UNKNOWN -> 6 rows
+select *
+from t1n
+where v in(select v from t2n) is unknown;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- IN & t1n table & non-key field & FALSE -> 0 rows
+select *
+from t1n
+where v in(select v from t2n) is false;
+id u g v vi s
+-- NOT IN & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n);
+id u g v vi s
+-- NOT IN & t1n table & non-key field & TRUE -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n) is true;
+id u g v vi s
+-- NOT IN & t1n table & non-key field & UNKNOWN -> 6 rows
+select *
+from t1n
+where v not in(select v from t2n) is unknown;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- NOT IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v not in(select v from t2n) is false;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- =ANY & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v=any(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <>ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v<>all(select v from t2n);
+id u g v vi s
+-- <ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<any(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <=ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<=any(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <>ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<>any(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- >ANY & t1n table & non-key field -> 5 rows
+select *
+from t1n
+where v>any(select v from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- >=ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v>=any(select v from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v<all(select v from t2n);
+id u g v vi s
+-- <=ALL & t1n table & non-key field -> 1 rows
+select *
+from t1
+where v<=all(select v from t2n);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v=all(select v from t2n);
+id u g v vi s
+-- >ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v>all(select v from t2n);
+id u g v vi s
+-- >=ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v>=all(select v from t2n);
+id u g v vi s
+-- Uncorrelated EXISTS & t1n table & non-key field -> 10 rows
+select *
+from t1n
+where exists(select v
+from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- Uncorrelated NOT EXISTS & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where not exists(select *
+from t2n);
+id u g v vi s
+-- Correlated EXISTS & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where exists (select *
+from t2n
+where t1n.v = t2n.v);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated NOT EXISTS & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where t1n.v = t2n.v);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- Correlated IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v in(select v
+from t2n
+where t1n.g=t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v in(select v
+from t2n
+where t1n.g=t2n.g) is true;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated IN & t1n table & non-key field & UNKNOWN -> 2 rows
+-- ERROR: MySQL reports 4 rows
+select *
+from t1n
+where v in(select v
+from t2n
+where t1n.g=t2n.g) is unknown;
+id u g v vi s
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- Correlated IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v in(select v
+from t2n
+where t1n.g=t2n.g) is false;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- Correlated NOT IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v not in(select v
+from t2n
+where t1n.g=t2n.g);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- Correlated NOT IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v not in(select v
+from t2n
+where t1n.g=t2n.g) is true;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- Correlated NOT IN & t1n table & non-key field & UNKNOWN -> 2 rows
+select *
+from t1n
+where v not in(select v
+from t2n
+where t1n.g=t2n.g) is unknown;
+id u g v vi s
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+-- Correlated NOT IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v not in(select v
+from t2n
+where t1n.g=t2n.g) is false;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated EXISTS & t1n table & 2 non-key fields -> 4 rows
+select *
+from t1n
+where exists (select *
+from t2n
+where t1n.v = t2n.v
+and t1n.g = t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated NOT EXISTS & t1n table & 2 non-key fields -> 6 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where t1n.v = t2n.v
+and t1n.g = t2n.g);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- IN & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where vi in(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN & t1n table & indexed field & TRUE -> 4 rows
+select *
+from t1n
+where vi in(select vi from t2n) is true;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN & t1n table & indexed field & UNKNOWN -> 6 rows
+select *
+from t1n
+where vi in(select vi from t2n) is unknown;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- IN & t1n table & indexed field & FALSE -> 0 rows
+select *
+from t1n
+where vi in(select vi from t2n) is false;
+id u g v vi s
+-- NOT IN & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi not in(select vi from t2n);
+id u g v vi s
+-- NOT IN & t1n table & indexed field & TRUE -> 0 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is true;
+id u g v vi s
+-- NOT IN & t1n table & indexed field & UNKNOWN -> 6 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is unknown;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- NOT IN & t1n table & indexed field & FALSE -> 4 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is false;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- =ANY & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where vi=any(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <>ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi<>all(select vi from t2n);
+id u g v vi s
+-- <ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<any(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <=ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<=any(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <>ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<>any(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- >ANY & t1n table & indexed field -> 5 rows
+select *
+from t1n
+where vi>any(select vi from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- >=ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi>=any(select vi from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- <ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi<all(select vi from t2n);
+id u g v vi s
+-- <=ALL & t1n table & indexed field -> 1 rows
+select *
+from t1
+where vi<=all(select vi from t2n);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi=all(select vi from t2n);
+id u g v vi s
+-- >ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi>all(select vi from t2n);
+id u g v vi s
+-- >=ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi>=all(select vi from t2n);
+id u g v vi s
+-- Uncorrelated EXISTS & t1n table & indexed field -> 10 rows
+select *
+from t1n
+where exists(select vi
+from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- Uncorrelated NOT EXISTS & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where not exists(select vi
+from t2n);
+id u g v vi s
+-- Correlated EXISTS & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where exists (select *
+from t2n
+where t1n.vi = t2n.vi);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated NOT EXISTS & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where t1n.vi = t2n.vi);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- IN & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u in(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- IN & t1n table & unique field & TRUE -> 5 rows
+select *
+from t1n
+where u in(select u from t2n) is true;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- IN & t1n table & unique field & UNKNOWN -> 0 rows
+select *
+from t1n
+where u in(select u from t2n) is unknown;
+id u g v vi s
+-- IN & t1n table & unique field & FALSE -> 5 rows
+select *
+from t1n
+where u in(select u from t2n) is false;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- NOT IN & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- NOT IN & t1n table & unique field & TRUE -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n) is true;
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- NOT IN & t1n table & unique field & UNKNOWN -> 0 rows
+select *
+from t1n
+where u not in(select u from t2n) is unknown;
+id u g v vi s
+-- NOT IN & t1n table & unique field & FALSE -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n) is false;
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- =ANY & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u=any(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- <>ALL & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u<>all(select u from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- <ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<any(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- <=ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<=any(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- <>ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<>any(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- >ANY & t1n table & unique field -> 9 rows
+select *
+from t1n
+where u>any(select u from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- >=ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u>=any(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- <ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u<all(select u from t2n);
+id u g v vi s
+-- <=ALL & t1n table & unique field -> 1 rows
+select *
+from t1
+where u<=all(select u from t2n);
+id u v vi s
+10 10 10 10 l
+-- =ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u=all(select u from t2n);
+id u g v vi s
+-- >ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u>all(select u from t2n);
+id u g v vi s
+-- >=ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u>=all(select u from t2n);
+id u g v vi s
+-- Uncorrelated EXISTS & t1n table & unique field -> 10 rows
+select *
+from t1n
+where exists(select u
+from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- Uncorrelated NOT EXISTS & t1n table & unique field -> 0 rows
+select *
+from t1n
+where not exists(select u
+from t2n);
+id u g v vi s
+-- Correlated EXISTS & t1n table & unique field -> 5 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.u = t2n.u);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Correlated NOT EXISTS & t1n table & unique field -> 5 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where t1n.u = t2n.u);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- Tests with ANDed subqueries
+-- Two IN queries -> 4 rows
+select *
+from t1n
+where v in(select v from t2n)
+and g in(select g from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+70 70 70 70 70 l
+-- Three IN queries -> 4 rows
+select *
+from t1n
+where v in(select v from t2n)
+and g in(select g from t2n)
+and u in(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Two NOT IN queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+and g not in(select g from t2n);
+id u g v vi s
+-- Three NOT IN queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+and g not in(select g from t2n)
+and u not in(select u from t2n);
+id u g v vi s
+-- IN/NOT IN queries -> 0 rows
+select *
+from t1n
+where v in(select v from t2n)
+and g not in(select g from t2n);
+id u g v vi s
+-- NOT IN/NOT queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+and g in(select g from t2n);
+id u g v vi s
+-- Two correlated EXISTS queries -> 4 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+and exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+70 70 70 70 70 l
+-- Three correlated EXISTS queries -> 4 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+and exists(select * from t2n where t1n.g=t2n.g)
+and exists(select * from t2n where t1n.u=t2n.u);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Two correlated NOT EXISTS queries -> 4 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+and not exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- Three correlated NOT EXISTS queries -> 4 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+and not exists(select * from t2n where t1n.g=t2n.g)
+and not exists(select * from t2n where t1n.u=t2n.u);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- EXISTS/NOT EXISTS queries -> 0 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+and not exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+-- NOT EXISTS/EXISTS queries -> 2 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+and exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+-- Tests with ORed subqueries
+-- Two IN queries -> 6 rows
+select *
+from t1n
+where v in(select v from t2n)
+or g in(select g from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Three IN queries -> 6 rows
+select *
+from t1n
+where v in(select v from t2n)
+or g in(select g from t2n)
+or u in(select u from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Two NOT IN queries -> 4 rows
+select *
+from t1n
+where v not in(select v from t2n)
+or g not in(select g from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+90 90 90 NULL NULL l
+-- Three NOT IN queries -> 5 rows
+select *
+from t1n
+where v not in(select v from t2n)
+or g not in(select g from t2n)
+or u not in(select u from t2n);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+90 90 90 NULL NULL l
+-- IN/NOT IN queries -> 8 rows
+select *
+from t1n
+where v in(select v from t2n)
+or g not in(select g from t2n);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- NOT IN/IN queries -> 6 rows
+select *
+from t1n
+where v not in(select v from t2n)
+or g in(select g from t2n);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Two correlated EXISTS queries -> 6 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+or exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Three correlated EXISTS queries -> 6 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+or exists(select * from t2n where t1n.g=t2n.g)
+or exists(select * from t2n where t1n.u=t2n.u);
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Two correlated NOT EXISTS queries -> 6 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+or not exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- Three correlated NOT EXISTS queries -> 6 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+or not exists(select * from t2n where t1n.g=t2n.g)
+or not exists(select * from t2n where t1n.u=t2n.u);
+id u g v vi s
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+51 51 50 NULL NULL l
+61 61 60 NULL NULL l
+90 90 90 NULL NULL l
+-- EXISTS/NOT EXISTS queries -> 8 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+or not exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- NOT EXISTS/EXISTS queries -> 10 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+or exists(select * from t2n where t1n.g=t2n.g);
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- Subqueries in three levels
+-- Uncorrelated EXISTS/EXISTS -> 10 rows
+-- Uncorrelated NOT EXISTS/EXISTS -> 0 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where exists(select *
+from t1n));
+id u g v vi s
+-- Uncorrelated EXISTS/NOT EXISTS -> 0 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where not exists(select *
+from t1n));
+id u g v vi s
+-- Uncorrelated NOT EXISTS/NOT EXISTS -> 10 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where not exists(select *
+from t1n));
+id u g v vi s
+10 10 10 10 10 l
+20 20 20 20 20 l
+40 40 40 40 40 l
+41 41 40 NULL NULL l
+50 50 50 50 50 l
+51 51 50 NULL NULL l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+90 90 90 NULL NULL l
+-- Correlated EXISTS primary key/EXISTS unique key-> 5 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.id=t2n.id
+and exists(select *
+from t1n as t3n
+where t2n.u=t3n.u));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Correlated EXISTS unique key/EXISTS primary key -> 5 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.u=t2n.u
+and exists(select *
+from t1n as t3n
+where t2n.id=t3n.id));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- Correlated EXISTS non-unique key/EXISTS non-key-> 4 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.vi=t2n.vi
+and exists(select *
+from t1n as t3n
+where t2n.v=t3n.v));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated EXISTS non-key/EXISTS non-unique key -> 4 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.v=t2n.v
+and exists(select *
+from t1n as t3n
+where t2n.vi=t3n.vi));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN primary key/IN unique key -> 5 rows
+select *
+from t1n
+where id in(select id
+from t2n
+where u in(select u
+from t1n));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- IN unique key/IN primary key -> 5 rows
+select *
+from t1n
+where u in(select u
+from t2n
+where id in(select id
+from t1n));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+61 61 60 NULL NULL l
+70 70 70 70 70 l
+-- IN non-unique key/IN non-key -> 4 rows
+select *
+from t1n
+where vi in(select vi
+from t2n
+where v in(select v
+from t1n));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN non-key/IN non-unique key -> 4 rows
+select *
+from t1n
+where v in(select v
+from t2n
+where vi in(select vi
+from t1n));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Subqueries in four levels
+-- Uncorrelated EXISTS/EXISTS/EXISTS -> 10 rows
+-- Uncorrelated NOT EXISTS/NOT EXISTS/NOT EXISTS -> 0 rows
+select *
+from t1n
+where not exists(select *
+from t2n
+where not exists(select *
+from t1n
+where not exists(select *
+from t2n)));
+id u g v vi s
+-- Correlated EXISTS/EXISTS/EXISTS-> 4 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.id=t2n.id
+and exists(select *
+from t1n as t3n
+where t2n.u=t3n.u
+and exists(select *
+from t2n as t4n
+where t3n.v=t4n.v)));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- Correlated EXISTS/EXISTS/EXISTS -> 4 rows
+select *
+from t1n
+where exists(select *
+from t2n
+where t1n.u=t2n.u
+and exists(select *
+from t1n as t3n
+where t2n.id=t3n.id
+and exists(select *
+from t2n as t4n
+where t3n.vi=t4n.vi)));
+id u g v vi s
+-- IN/IN/IN -> 4 rows
+select *
+from t1n
+where id in(select id
+from t2n
+where u in(select u
+from t1n
+where v in(select v
+from t2n)));
+id u g v vi s
+10 10 10 10 10 l
+50 50 50 50 50 l
+60 60 60 60 60 l
+70 70 70 70 70 l
+-- IN/IN/IN -> 4 rows
+select *
+from t1n
+where u in(select u
+from t2n
+where id in(select id
+from t1n
+where vi in(select vi
+from t2n)));
+id u g v vi s
+-- Drop all created tables
+drop table empty;
+drop table nulls;
+drop table t1;
+drop table t2;
+drop table t1n;
+drop table t2n;
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2008-10-29 20:35:16 +0000
+++ b/mysql-test/t/subselect.test 2008-11-14 09:46:50 +0000
@@ -1464,7 +1464,6 @@ drop table t1;
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root;
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
-
#
# primary query with temporary table and subquery with groupping
#
@@ -2877,14 +2876,18 @@ INSERT INTO t1 VALUES
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
INSERT INTO t2 VALUES (7), (5), (1), (3);
+sorted_result;
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+sorted_result;
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
GROUP BY id;
+sorted_result;
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+sorted_result;
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
GROUP BY id;
=== added file 'mysql-test/t/subselect_systematic.test'
--- a/mysql-test/t/subselect_systematic.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/subselect_systematic.test 2008-11-14 09:46:50 +0000
@@ -0,0 +1,1544 @@
+--echo -- SUBQUERY tests working on the following tables:
+
+--echo -- empty - Table with no rows
+--echo -- nulls - Table containing a single row, with a null value
+--echo -- t1 - contains data useful for subqueries
+--echo -- t2 - contains data useful for subqueries
+--echo -- t1n - contains data useful for subqueries (with null values)
+--echo -- t2n - contains data useful for subqueries (with null values)
+
+--disable_warnings
+drop table if exists empty;
+drop table if exists nulls;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t1n;
+drop table if exists t2n;
+--enable_warnings
+
+create table empty(id integer primary key, v integer);
+create table nulls(id integer primary key, v integer, l char(1), r char(1));
+
+insert into nulls values(0, null, 'l', 'r');
+
+create table t1(
+ id integer primary key,
+ u integer not null unique,
+ v integer not null,
+ vi integer not null, index(vi),
+ s char(1));
+
+create table t2(
+ id integer primary key,
+ u integer not null unique,
+ v integer not null,
+ vi integer not null, index(vi),
+ s char(1));
+
+# Value #t1 #t2
+# 10 1 1
+insert into t1 values(10, 10, 10, 10, 'l');
+insert into t2 values(10, 10, 10, 10, 'r');
+# 20 1 0
+insert into t1 values(20, 20, 20, 20, 'l');
+# 30 0 1
+insert into t2 values(30, 30, 30, 30, 'r');
+# 40 2 0
+insert into t1 values(40, 40, 40, 40, 'l');
+insert into t1 values(41, 41, 40, 40, 'l');
+# 50 2 1
+insert into t1 values(50, 50, 50, 50, 'l');
+insert into t1 values(51, 51, 50, 50, 'l');
+insert into t2 values(50, 50, 50, 50, 'r');
+# 60 2 2
+insert into t1 values(60, 60, 60, 60, 'l');
+insert into t1 values(61, 61, 60, 60, 'l');
+insert into t2 values(60, 60, 60, 60, 'r');
+insert into t2 values(61, 61, 60, 60, 'r');
+# 70 1 2
+insert into t1 values(70, 70, 70, 70, 'l');
+insert into t2 values(70, 70, 70, 70, 'r');
+insert into t2 values(71, 71, 70, 70, 'r');
+# 80 0 2
+insert into t2 values(80, 80, 80, 80, 'r');
+insert into t2 values(81, 81, 80, 80, 'r');
+
+create table t1n(
+ id integer primary key,
+ u integer unique,
+ g integer,
+ v integer,
+ vi integer, index(vi),
+ s char(1));
+
+create table t2n(
+ id integer primary key,
+ u integer unique,
+ g integer,
+ v integer,
+ vi integer, index(vi),
+ s char(1));
+
+# Value #t1 #t2
+# 10 1 1
+insert into t1n values(10, 10, 10, 10, 10, 'l');
+insert into t2n values(10, 10, 10, 10, 10, 'r');
+# 20 1 0
+insert into t1n values(20, 20, 20, 20, 20, 'l');
+# 30 0 1
+insert into t2n values(30, 30, 30, 30, 30, 'r');
+# 40 1+n 0
+insert into t1n values(40, 40, 40, 40, 40, 'l');
+insert into t1n values(41, 41, 40, null, null, 'l');
+# 50 1+n 1
+insert into t1n values(50, 50, 50, 50, 50, 'l');
+insert into t1n values(51, 51, 50, null, null, 'l');
+insert into t2n values(50, 50, 50, 50, 50, 'r');
+# 60 1+n 1+n
+insert into t1n values(60, 60, 60, 60, 60, 'l');
+insert into t1n values(61, 61, 60, null, null, 'l');
+insert into t2n values(60, 60, 60, 60, 60, 'r');
+insert into t2n values(61, 61, 60, null, null, 'r');
+# 70 1 1+n
+insert into t1n values(70, 70, 70, 70, 70, 'l');
+insert into t2n values(70, 70, 70, 70, 70, 'r');
+insert into t2n values(71, 71, 70, null, null, 'r');
+# 80 0 1+n
+insert into t2n values(80, 80, 80, 80, 80, 'r');
+insert into t2n values(81, 81, 80, null, null, 'r');
+# 90 n 0
+insert into t1n values(90, 90, 90, null, null, 'l');
+# 100 0 n
+insert into t2n values(100,100,100,null, null, 'r');
+
+--echo -- Tests on Empty table
+--echo -- non-key field
+--echo -- [NOT] IN, =ANY, <>ALL, [NOT] EXISTS
+
+--echo -- IN & Empty table -> 0 rows
+select *
+from empty
+where v in(select v from t2);
+
+--echo -- NOT IN & Empty table -> 0 rows
+select *
+from empty
+where v not in(select v from t2);
+
+--echo -- =ANY & Empty table -> 0 rows
+select *
+from empty
+where v=any(select v from t2);
+
+--echo -- <>ALL & Empty table -> 0 rows
+select *
+from empty
+where v<>all(select v from t2);
+
+--echo -- Uncorrelated EXISTS & Empty table -> 0 rows
+select *
+from empty
+where exists(select v from t2);
+
+--echo -- Uncorrelated NOT EXISTS & Empty table -> 0 rows
+select *
+from empty
+where not exists(select v from t2);
+
+--echo -- Correlated EXISTS & Empty table -> 0 rows
+select *
+from empty
+where exists(select v from t2
+ where empty.v=t2.v);
+
+--echo -- Correlated NOT EXISTS & Empty table -> 0 rows
+select *
+from empty
+where not exists(select v from t2
+ where empty.v=t2.v);
+
+--echo -- Tests on Nulls table
+--echo -- non-key field
+--echo -- [NOT] IN, =ANY, <>ALL, [NOT] EXISTS
+
+--echo -- IN & Nulls table -> 0 rows
+select *
+from nulls
+where v in(select v from t2);
+
+--echo -- IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v in(select v from t2) is unknown;
+
+--echo -- NOT IN & Nulls table -> 0 rows
+select *
+from nulls
+where v not in(select v from t2);
+
+--echo -- NOT IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v not in(select v from t2) is unknown;
+
+--echo -- IN & Nulls table -> 0 rows
+select *
+from t1
+where v in(select v from nulls);
+
+--echo -- IN & Nulls table & UNKNOWN -> 9 rows
+select *
+from t1
+where v in(select v from nulls) is unknown;
+
+--echo -- NOT IN & Nulls table -> 0 rows
+select *
+from t1
+where v not in(select v from nulls);
+
+--echo -- NOT IN & Nulls table & UNKNOWN -> 9 rows
+select *
+from t1
+where v not in(select v from nulls) is unknown;
+
+--echo -- IN & Nulls table -> 0 rows
+select *
+from nulls
+where v in(select v from nulls);
+
+--echo -- IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v in(select v from nulls) is unknown;
+
+--echo -- NOT IN & Nulls table -> 0 rows
+select *
+from nulls
+where v not in(select v from nulls);
+
+--echo -- NOT IN & Nulls table & UNKNOWN -> 1 rows
+select *
+from nulls
+where v not in(select v from nulls) is unknown;
+
+--echo -- =ANY & Nulls table -> 0 rows
+select *
+from nulls
+where v=any(select v from t2);
+
+--echo -- <>ALL & Nulls table -> 0 rows
+select *
+from nulls
+where v<>all(select v from t2);
+
+--echo -- Uncorrelated EXISTS & Nulls table -> 1 rows
+select *
+from nulls
+where exists(select v from t2);
+
+--echo -- Uncorrelated NOT EXISTS & Nulls table -> 0 rows
+select *
+from nulls
+where not exists(select v from t2);
+
+--echo -- Correlated EXISTS & Nulls table -> 0 rows
+select *
+from nulls
+where exists(select v from t2
+ where nulls.v=t2.v);
+
+--echo -- Correlated NOT EXISTS & Nulls table -> 1 rows
+select *
+from nulls
+where not exists(select v from t2
+ where nulls.v=t2.v);
+
+--echo -- Tests on tables without nullable columns
+--echo -- non-key field, indexed field, primary key field, unique field
+--echo -- [NOT] IN, <op>ANY, <op>ALL, [NOT] EXISTS
+
+--echo -- IN & t1 table & non-key field -> 6 rows
+select *
+from t1
+where v in(select v from t2);
+
+--echo -- IN & t1 table & non-key field & TRUE -> 6 rows
+select *
+from t1
+where v in(select v from t2) is true;
+
+--echo -- IN & t1 table & non-key field & UNKNOWN -> 0 rows
+select *
+from t1
+where v in(select v from t2) is unknown;
+
+--echo -- IN & t1 table & non-key field & FALSE -> 3 rows
+select *
+from t1
+where v in(select v from t2) is false;
+
+--echo -- NOT IN & t1 table & non-key field -> 3 rows
+select *
+from t1
+where v not in(select v from t2);
+
+--echo -- NOT IN & t1 table & non-key field & TRUE -> 3 rows
+select *
+from t1
+where v not in(select v from t2) is true;
+
+--echo -- NOT IN & t1 table & non-key field & UNKNOWN -> 0 rows
+select *
+from t1
+where v not in(select v from t2) is unknown;
+
+--echo -- NOT IN & t1 table & non-key field & FALSE -> 6 rows
+select *
+from t1
+where v not in(select v from t2) is false;
+
+--echo -- =ANY & t1 table & non-key field -> 6 rows
+select *
+from t1
+where v=any(select v from t2);
+
+--echo -- <>ALL & t1 table & non-key field -> 3 rows
+select *
+from t1
+where v<>all(select v from t2);
+
+--echo -- <ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<any(select v from t2);
+
+--echo -- <=ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<=any(select v from t2);
+
+--echo -- <>ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id<>any(select v from t2);
+
+--echo -- >ANY & t1 table & non-key field -> 8 rows
+select *
+from t1
+where id>any(select v from t2);
+
+--echo -- >=ANY & t1 table & non-key field -> 9 rows
+select *
+from t1
+where id>=any(select v from t2);
+
+--echo -- <ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id<all(select v from t2);
+
+--echo -- <=ALL & t1 table & non-key field -> 1 rows
+select *
+from t1
+where id<=all(select v from t2);
+
+--echo -- =ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id=all(select v from t2);
+
+--echo -- >ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id>all(select v from t2);
+
+--echo -- >=ALL & t1 table & non-key field -> 0 rows
+select *
+from t1
+where id>=all(select v from t2);
+
+--echo -- Uncorrelated EXISTS & t1 table & non-key field -> 9 rows
+select *
+from t1
+where exists(select v
+ from t2);
+
+--echo -- Uncorrelated NOT EXISTS & t1 table & non-key field -> 0 rows
+select *
+from t1
+where not exists(select v
+ from t2);
+
+--echo -- Correlated EXISTS & t1 table & non-key field -> 6 rows
+select *
+from t1
+where exists(select v from t2
+ where t1.v=t2.v);
+
+--echo -- Correlated NOT EXISTS & t1 table & non-key field -> 3 rows
+select *
+from t1
+where not exists(select v from t2
+ where t1.v=t2.v);
+
+--echo -- IN & t1 table & indexed field -> 6 rows
+select *
+from t1
+where vi in(select vi from t2);
+
+--echo -- IN & t1 table & indexed field & TRUE -> 6 rows
+select *
+from t1
+where vi in(select vi from t2) is true;
+
+--echo -- IN & t1 table & indexed field & UNKNOWN -> 0 rows
+select *
+from t1
+where vi in(select vi from t2) is unknown;
+
+--echo -- IN & t1 table & indexed field & FALSE -> 3 rows
+select *
+from t1
+where vi in(select vi from t2) is false;
+
+--echo -- NOT IN & t1 table & indexed field -> 3 rows
+select *
+from t1
+where vi not in(select vi from t2);
+
+--echo -- NOT IN & t1 table & indexed field & TRUE -> 3 rows
+select *
+from t1
+where vi not in(select vi from t2) is true;
+
+--echo -- NOT IN & t1 table & indexed field & UNKNOWN -> 0 rows
+select *
+from t1
+where vi not in(select vi from t2) is unknown;
+
+--echo -- NOT IN & t1 table & indexed field & FALSE -> 6 rows
+select *
+from t1
+where vi not in(select vi from t2) is false;
+
+--echo -- =ANY & t1 table & indexed field -> 6 rows
+select *
+from t1
+where vi=any(select vi from t2);
+
+--echo -- <>ALL & t1 table & indexed field -> 3 rows
+select *
+from t1
+where vi<>all(select vi from t2);
+
+--echo -- <ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<any(select vi from t2);
+
+--echo -- <=ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<=any(select vi from t2);
+
+--echo -- <>ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id<>any(select vi from t2);
+
+--echo -- >ANY & t1 table & indexed field -> 8 rows
+select *
+from t1
+where id>any(select vi from t2);
+
+--echo -- >=ANY & t1 table & indexed field -> 9 rows
+select *
+from t1
+where id>=any(select vi from t2);
+
+--echo -- <ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id<all(select vi from t2);
+
+--echo -- <=ALL & t1 table & indexed field -> 1 rows
+select *
+from t1
+where id<=all(select vi from t2);
+
+--echo -- =ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id=all(select vi from t2);
+
+--echo -- >ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id>all(select vi from t2);
+
+--echo -- >=ALL & t1 table & indexed field -> 0 rows
+select *
+from t1
+where id>=all(select vi from t2);
+
+--echo -- Uncorrelated EXISTS & t1 table & indexed field -> 9 rows
+select *
+from t1
+where exists(select vi
+ from t2);
+
+--echo -- Uncorrelated NOT EXISTS & t1 table & indexed field -> 0 rows
+select *
+from t1
+where not exists(select vi
+ from t2);
+
+--echo -- Correlated EXISTS & t1 table & indexed field -> 6 rows
+select *
+from t1
+where exists(select *
+ from t2
+ where t1.vi = t2.vi);
+
+--echo -- Correlated NOT EXISTS & t1 table & indexed field -> 3 rows
+select *
+from t1
+where not exists(select *
+ from t2
+ where t1.vi = t2.vi);
+
+--echo -- IN & t1 table & primary key field -> 5 rows
+select *
+from t1
+where id in(select id from t2);
+
+--echo -- IN & t1 table & primary key field & TRUE -> 5 rows
+select *
+from t1
+where id in(select id from t2) is true;
+
+--echo -- IN & t1 table & primary key field & UNKNOWN -> 0 rows
+select *
+from t1
+where id in(select id from t2) is unknown;
+
+--echo -- IN & t1 table & primary key field & FALSE -> 4 rows
+select *
+from t1
+where id in(select id from t2) is false;
+
+--echo -- NOT IN & t1 table & primary key field -> 4 rows
+select *
+from t1
+where id not in(select id from t2);
+
+--echo -- NOT IN & t1 table & primary key field & TRUE -> 4 rows
+select *
+from t1
+where id not in(select id from t2) is true;
+
+--echo -- NOT IN & t1 table & primary key field & UNKNOWN -> 0 rows
+select *
+from t1
+where id not in(select id from t2) is unknown;
+
+--echo -- NOT IN & t1 table & primary key field & FALSE -> 5 rows
+select *
+from t1
+where id not in(select id from t2) is false;
+
+--echo -- =ANY & t1 table & primary key field -> 5 rows
+select *
+from t1
+where id=any(select id from t2);
+
+--echo -- <>ALL & t1 table & primary key field -> 4 rows
+select *
+from t1
+where id<>all(select id from t2);
+
+--echo -- <ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<any(select id from t2);
+
+--echo -- <=ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<=any(select id from t2);
+
+--echo -- <>ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id<>any(select id from t2);
+
+--echo -- >ANY & t1 table & primary key field -> 8 rows
+select *
+from t1
+where id>any(select id from t2);
+
+--echo -- >=ANY & t1 table & primary key field -> 9 rows
+select *
+from t1
+where id>=any(select id from t2);
+
+--echo -- <ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id<all(select id from t2);
+
+--echo -- <=ALL & t1 table & primary key field -> 1 rows
+select *
+from t1
+where id<=all(select id from t2);
+
+--echo -- =ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id=all(select id from t2);
+
+--echo -- >ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id>all(select id from t2);
+
+--echo -- >=ALL & t1 table & primary key field -> 0 rows
+select *
+from t1
+where id>=all(select id from t2);
+
+--echo -- Uncorrelated EXISTS & t1 table & primary key field -> 9 rows
+select *
+from t1
+where exists(select id
+ from t2);
+
+--echo -- Uncorrelated NOT EXISTS & t1 table & primary field -> 0 rows
+select *
+from t1
+where not exists(select id
+ from t2);
+
+--echo -- Correlated EXISTS & t1 table & primary key field -> 5 rows
+select *
+from t1
+where exists(select *
+ from t2
+ where t1.id = t2.id);
+
+--echo -- Correlated NOT EXISTS & t1 table & primary key field -> 4 rows
+select *
+from t1
+where not exists(select *
+ from t2
+ where t1.id = t2.id);
+
+--echo -- IN & t1 table & unique field -> 5 rows
+select *
+from t1
+where u in(select u from t2);
+
+--echo -- IN & t1 table & unique field & TRUE -> 5 rows
+select *
+from t1
+where u in(select u from t2) is true;
+
+--echo -- IN & t1 table & unique field & UNKNOWN -> 0 rows
+select *
+from t1
+where u in(select u from t2) is unknown;
+
+--echo -- IN & t1 table & unique field & FALSE -> 4 rows
+select *
+from t1
+where u in(select u from t2) is false;
+
+--echo -- NOT IN & t1 table & unique field -> 4 rows
+select *
+from t1
+where u not in(select u from t2);
+
+--echo -- NOT IN & t1 table & unique field & TRUE -> 4 rows
+select *
+from t1
+where u not in(select u from t2) is true;
+
+--echo -- NOT IN & t1 table & unique field & UNKNOWN -> 0 rows
+select *
+from t1
+where u not in(select u from t2) is unknown;
+
+--echo -- NOT IN & t1 table & unique field & FALSE -> 5 rows
+select *
+from t1
+where u not in(select u from t2) is false;
+
+--echo -- =ANY & t1 table & unique field -> 5 rows
+select *
+from t1
+where u=any(select u from t2);
+
+--echo -- <>ALL & t1 table & unique field -> 4 rows
+select *
+from t1
+where u<>all(select u from t2);
+
+--echo -- <ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<any(select u from t2);
+
+--echo -- <=ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<=any(select u from t2);
+
+--echo -- <>ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u<>any(select u from t2);
+
+--echo -- >ANY & t1 table & unique field -> 8 rows
+select *
+from t1
+where u>any(select u from t2);
+
+--echo -- >=ANY & t1 table & unique field -> 9 rows
+select *
+from t1
+where u>=any(select u from t2);
+
+--echo -- <ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u<all(select u from t2);
+
+--echo -- <=ALL & t1 table & unique field -> 1 rows
+select *
+from t1
+where u<=all(select u from t2);
+
+--echo -- =ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u=all(select u from t2);
+
+--echo -- >ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u>all(select u from t2);
+
+--echo -- >=ALL & t1 table & unique field -> 0 rows
+select *
+from t1
+where u>=all(select u from t2);
+
+--echo -- Correlated EXISTS & t1 table & unique field -> 5 rows
+select *
+from t1
+where exists(select *
+ from t2
+ where t1.u = t2.u);
+
+--echo -- Correlated NOT EXISTS & t1 table & unique field -> 4 rows
+select *
+from t1
+where not exists(select *
+ from t2
+ where t1.u = t2.u);
+
+--echo -- Tests on tables with nullable columns
+--echo -- non-key field, indexed field, unique field
+--echo -- [NOT] IN, <op>ANY, <op>ALL, [NOT] EXISTS
+
+--echo -- IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v in(select v from t2n);
+
+--echo -- IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v in(select v from t2n) is true;
+
+--echo -- IN & t1n table & non-key field & UNKNOWN -> 6 rows
+select *
+from t1n
+where v in(select v from t2n) is unknown;
+
+--echo -- IN & t1n table & non-key field & FALSE -> 0 rows
+select *
+from t1n
+where v in(select v from t2n) is false;
+
+--echo -- NOT IN & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n);
+
+--echo -- NOT IN & t1n table & non-key field & TRUE -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n) is true;
+
+--echo -- NOT IN & t1n table & non-key field & UNKNOWN -> 6 rows
+select *
+from t1n
+where v not in(select v from t2n) is unknown;
+
+--echo -- NOT IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v not in(select v from t2n) is false;
+
+--echo -- =ANY & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v=any(select v from t2n);
+
+--echo -- <>ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v<>all(select v from t2n);
+
+--echo -- <ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<any(select v from t2n);
+
+--echo -- <=ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<=any(select v from t2n);
+
+--echo -- <>ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v<>any(select v from t2n);
+
+--echo -- >ANY & t1n table & non-key field -> 5 rows
+select *
+from t1n
+where v>any(select v from t2n);
+
+--echo -- >=ANY & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where v>=any(select v from t2n);
+
+--echo -- <ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v<all(select v from t2n);
+
+--echo -- <=ALL & t1n table & non-key field -> 1 rows
+select *
+from t1
+where v<=all(select v from t2n);
+
+--echo -- =ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v=all(select v from t2n);
+
+--echo -- >ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v>all(select v from t2n);
+
+--echo -- >=ALL & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where v>=all(select v from t2n);
+
+--echo -- Uncorrelated EXISTS & t1n table & non-key field -> 10 rows
+select *
+from t1n
+where exists(select v
+ from t2n);
+
+--echo -- Uncorrelated NOT EXISTS & t1n table & non-key field -> 0 rows
+select *
+from t1n
+where not exists(select *
+ from t2n);
+
+--echo -- Correlated EXISTS & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where exists (select *
+ from t2n
+ where t1n.v = t2n.v);
+
+--echo -- Correlated NOT EXISTS & t1n table & non-key field -> 6 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where t1n.v = t2n.v);
+
+--echo -- Correlated IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v in(select v
+ from t2n
+ where t1n.g=t2n.g);
+
+--echo -- Correlated IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v in(select v
+ from t2n
+ where t1n.g=t2n.g) is true;
+
+--echo -- Correlated IN & t1n table & non-key field & UNKNOWN -> 2 rows
+--echo -- ERROR: MySQL reports 4 rows
+select *
+from t1n
+where v in(select v
+ from t2n
+ where t1n.g=t2n.g) is unknown;
+
+--echo -- Correlated IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v in(select v
+ from t2n
+ where t1n.g=t2n.g) is false;
+
+--echo -- Correlated NOT IN & t1n table & non-key field -> 4 rows
+select *
+from t1n
+where v not in(select v
+ from t2n
+ where t1n.g=t2n.g);
+
+--echo -- Correlated NOT IN & t1n table & non-key field & TRUE -> 4 rows
+select *
+from t1n
+where v not in(select v
+ from t2n
+ where t1n.g=t2n.g) is true;
+
+--echo -- Correlated NOT IN & t1n table & non-key field & UNKNOWN -> 2 rows
+select *
+from t1n
+where v not in(select v
+ from t2n
+ where t1n.g=t2n.g) is unknown;
+
+--echo -- Correlated NOT IN & t1n table & non-key field & FALSE -> 4 rows
+select *
+from t1n
+where v not in(select v
+ from t2n
+ where t1n.g=t2n.g) is false;
+
+--echo -- Correlated EXISTS & t1n table & 2 non-key fields -> 4 rows
+select *
+from t1n
+where exists (select *
+ from t2n
+ where t1n.v = t2n.v
+ and t1n.g = t2n.g);
+
+--echo -- Correlated NOT EXISTS & t1n table & 2 non-key fields -> 6 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where t1n.v = t2n.v
+ and t1n.g = t2n.g);
+
+--echo -- IN & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where vi in(select vi from t2n);
+
+--echo -- IN & t1n table & indexed field & TRUE -> 4 rows
+select *
+from t1n
+where vi in(select vi from t2n) is true;
+
+--echo -- IN & t1n table & indexed field & UNKNOWN -> 6 rows
+select *
+from t1n
+where vi in(select vi from t2n) is unknown;
+
+--echo -- IN & t1n table & indexed field & FALSE -> 0 rows
+select *
+from t1n
+where vi in(select vi from t2n) is false;
+
+--echo -- NOT IN & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi not in(select vi from t2n);
+
+--echo -- NOT IN & t1n table & indexed field & TRUE -> 0 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is true;
+
+--echo -- NOT IN & t1n table & indexed field & UNKNOWN -> 6 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is unknown;
+
+--echo -- NOT IN & t1n table & indexed field & FALSE -> 4 rows
+select *
+from t1n
+where vi not in(select vi from t2n) is false;
+
+--echo -- =ANY & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where vi=any(select vi from t2n);
+
+--echo -- <>ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi<>all(select vi from t2n);
+
+--echo -- <ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<any(select vi from t2n);
+
+--echo -- <=ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<=any(select vi from t2n);
+
+--echo -- <>ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi<>any(select vi from t2n);
+
+--echo -- >ANY & t1n table & indexed field -> 5 rows
+select *
+from t1n
+where vi>any(select vi from t2n);
+
+--echo -- >=ANY & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where vi>=any(select vi from t2n);
+
+--echo -- <ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi<all(select vi from t2n);
+
+--echo -- <=ALL & t1n table & indexed field -> 1 rows
+select *
+from t1
+where vi<=all(select vi from t2n);
+
+--echo -- =ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi=all(select vi from t2n);
+
+--echo -- >ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi>all(select vi from t2n);
+
+--echo -- >=ALL & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where vi>=all(select vi from t2n);
+
+--echo -- Uncorrelated EXISTS & t1n table & indexed field -> 10 rows
+select *
+from t1n
+where exists(select vi
+ from t2n);
+
+--echo -- Uncorrelated NOT EXISTS & t1n table & indexed field -> 0 rows
+select *
+from t1n
+where not exists(select vi
+ from t2n);
+
+--echo -- Correlated EXISTS & t1n table & indexed field -> 4 rows
+select *
+from t1n
+where exists (select *
+ from t2n
+ where t1n.vi = t2n.vi);
+
+--echo -- Correlated NOT EXISTS & t1n table & indexed field -> 6 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where t1n.vi = t2n.vi);
+
+--echo -- IN & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u in(select u from t2n);
+
+--echo -- IN & t1n table & unique field & TRUE -> 5 rows
+select *
+from t1n
+where u in(select u from t2n) is true;
+
+--echo -- IN & t1n table & unique field & UNKNOWN -> 0 rows
+select *
+from t1n
+where u in(select u from t2n) is unknown;
+
+--echo -- IN & t1n table & unique field & FALSE -> 5 rows
+select *
+from t1n
+where u in(select u from t2n) is false;
+
+--echo -- NOT IN & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n);
+
+--echo -- NOT IN & t1n table & unique field & TRUE -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n) is true;
+
+--echo -- NOT IN & t1n table & unique field & UNKNOWN -> 0 rows
+select *
+from t1n
+where u not in(select u from t2n) is unknown;
+
+--echo -- NOT IN & t1n table & unique field & FALSE -> 5 rows
+select *
+from t1n
+where u not in(select u from t2n) is false;
+
+--echo -- =ANY & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u=any(select u from t2n);
+
+--echo -- <>ALL & t1n table & unique field -> 5 rows
+select *
+from t1n
+where u<>all(select u from t2n);
+
+--echo -- <ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<any(select u from t2n);
+
+--echo -- <=ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<=any(select u from t2n);
+
+--echo -- <>ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u<>any(select u from t2n);
+
+--echo -- >ANY & t1n table & unique field -> 9 rows
+select *
+from t1n
+where u>any(select u from t2n);
+
+--echo -- >=ANY & t1n table & unique field -> 10 rows
+select *
+from t1n
+where u>=any(select u from t2n);
+
+--echo -- <ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u<all(select u from t2n);
+
+--echo -- <=ALL & t1n table & unique field -> 1 rows
+select *
+from t1
+where u<=all(select u from t2n);
+
+--echo -- =ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u=all(select u from t2n);
+
+--echo -- >ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u>all(select u from t2n);
+
+--echo -- >=ALL & t1n table & unique field -> 0 rows
+select *
+from t1n
+where u>=all(select u from t2n);
+
+--echo -- Uncorrelated EXISTS & t1n table & unique field -> 10 rows
+select *
+from t1n
+where exists(select u
+ from t2n);
+
+--echo -- Uncorrelated NOT EXISTS & t1n table & unique field -> 0 rows
+select *
+from t1n
+where not exists(select u
+ from t2n);
+
+--echo -- Correlated EXISTS & t1n table & unique field -> 5 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.u = t2n.u);
+
+--echo -- Correlated NOT EXISTS & t1n table & unique field -> 5 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where t1n.u = t2n.u);
+
+--echo -- Tests with ANDed subqueries
+
+--echo -- Two IN queries -> 4 rows
+-- *** ERROR - reports 6 rows ***
+select *
+from t1n
+where v in(select v from t2n)
+ and g in(select g from t2n);
+
+--echo -- Three IN queries -> 4 rows
+select *
+from t1n
+where v in(select v from t2n)
+ and g in(select g from t2n)
+ and u in(select u from t2n);
+
+--echo -- Two NOT IN queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ and g not in(select g from t2n);
+
+--echo -- Three NOT IN queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ and g not in(select g from t2n)
+ and u not in(select u from t2n);
+
+--echo -- IN/NOT IN queries -> 0 rows
+select *
+from t1n
+where v in(select v from t2n)
+ and g not in(select g from t2n);
+
+--echo -- NOT IN/NOT queries -> 0 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ and g in(select g from t2n);
+
+--echo -- Two correlated EXISTS queries -> 4 rows
+-- *** ERROR - reports 6 rows ***
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ and exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Three correlated EXISTS queries -> 4 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ and exists(select * from t2n where t1n.g=t2n.g)
+ and exists(select * from t2n where t1n.u=t2n.u);
+
+--echo -- Two correlated NOT EXISTS queries -> 4 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ and not exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Three correlated NOT EXISTS queries -> 4 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ and not exists(select * from t2n where t1n.g=t2n.g)
+ and not exists(select * from t2n where t1n.u=t2n.u);
+
+--echo -- EXISTS/NOT EXISTS queries -> 0 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ and not exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- NOT EXISTS/EXISTS queries -> 2 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ and exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Tests with ORed subqueries
+
+--echo -- Two IN queries -> 6 rows
+select *
+from t1n
+where v in(select v from t2n)
+ or g in(select g from t2n);
+
+--echo -- Three IN queries -> 6 rows
+select *
+from t1n
+where v in(select v from t2n)
+ or g in(select g from t2n)
+ or u in(select u from t2n);
+
+--echo -- Two NOT IN queries -> 4 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ or g not in(select g from t2n);
+
+--echo -- Three NOT IN queries -> 5 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ or g not in(select g from t2n)
+ or u not in(select u from t2n);
+
+--echo -- IN/NOT IN queries -> 8 rows
+select *
+from t1n
+where v in(select v from t2n)
+ or g not in(select g from t2n);
+
+--echo -- NOT IN/IN queries -> 6 rows
+select *
+from t1n
+where v not in(select v from t2n)
+ or g in(select g from t2n);
+
+--echo -- Two correlated EXISTS queries -> 6 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ or exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Three correlated EXISTS queries -> 6 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ or exists(select * from t2n where t1n.g=t2n.g)
+ or exists(select * from t2n where t1n.u=t2n.u);
+
+--echo -- Two correlated NOT EXISTS queries -> 6 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ or not exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Three correlated NOT EXISTS queries -> 6 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ or not exists(select * from t2n where t1n.g=t2n.g)
+ or not exists(select * from t2n where t1n.u=t2n.u);
+
+--echo -- EXISTS/NOT EXISTS queries -> 8 rows
+select *
+from t1n
+where exists(select * from t2n where t1n.v=t2n.v)
+ or not exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- NOT EXISTS/EXISTS queries -> 10 rows
+select *
+from t1n
+where not exists(select * from t2n where t1n.v=t2n.v)
+ or exists(select * from t2n where t1n.g=t2n.g);
+
+--echo -- Subqueries in three levels
+--echo -- Uncorrelated EXISTS/EXISTS -> 10 rows
+#select *
+#from t1n
+#where exists(select *
+# from t2n
+# where exists(select *
+# from t1n));
+
+--echo -- Uncorrelated NOT EXISTS/EXISTS -> 0 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where exists(select *
+ from t1n));
+
+--echo -- Uncorrelated EXISTS/NOT EXISTS -> 0 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where not exists(select *
+ from t1n));
+
+--echo -- Uncorrelated NOT EXISTS/NOT EXISTS -> 10 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where not exists(select *
+ from t1n));
+
+--echo -- Correlated EXISTS primary key/EXISTS unique key-> 5 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.id=t2n.id
+ and exists(select *
+ from t1n as t3n
+ where t2n.u=t3n.u));
+
+--echo -- Correlated EXISTS unique key/EXISTS primary key -> 5 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.u=t2n.u
+ and exists(select *
+ from t1n as t3n
+ where t2n.id=t3n.id));
+
+--echo -- Correlated EXISTS non-unique key/EXISTS non-key-> 4 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.vi=t2n.vi
+ and exists(select *
+ from t1n as t3n
+ where t2n.v=t3n.v));
+
+--echo -- Correlated EXISTS non-key/EXISTS non-unique key -> 4 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.v=t2n.v
+ and exists(select *
+ from t1n as t3n
+ where t2n.vi=t3n.vi));
+
+--echo -- IN primary key/IN unique key -> 5 rows
+select *
+from t1n
+where id in(select id
+ from t2n
+ where u in(select u
+ from t1n));
+
+--echo -- IN unique key/IN primary key -> 5 rows
+select *
+from t1n
+where u in(select u
+ from t2n
+ where id in(select id
+ from t1n));
+
+--echo -- IN non-unique key/IN non-key -> 4 rows
+select *
+from t1n
+where vi in(select vi
+ from t2n
+ where v in(select v
+ from t1n));
+
+--echo -- IN non-key/IN non-unique key -> 4 rows
+select *
+from t1n
+where v in(select v
+ from t2n
+ where vi in(select vi
+ from t1n));
+
+--echo -- Subqueries in four levels
+--echo -- Uncorrelated EXISTS/EXISTS/EXISTS -> 10 rows
+#select *
+#from t1n
+#where exists(select *
+# from t2n
+# where exists(select *
+# from t1n
+# where exists(select *
+# from t2n)));
+
+--echo -- Uncorrelated NOT EXISTS/NOT EXISTS/NOT EXISTS -> 0 rows
+select *
+from t1n
+where not exists(select *
+ from t2n
+ where not exists(select *
+ from t1n
+ where not exists(select *
+ from t2n)));
+
+--echo -- Correlated EXISTS/EXISTS/EXISTS-> 4 rows
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.id=t2n.id
+ and exists(select *
+ from t1n as t3n
+ where t2n.u=t3n.u
+ and exists(select *
+ from t2n as t4n
+ where t3n.v=t4n.v)));
+
+--echo -- Correlated EXISTS/EXISTS/EXISTS -> 4 rows
+--- *** ERROR: Reports zero rows using semijoin
+select *
+from t1n
+where exists(select *
+ from t2n
+ where t1n.u=t2n.u
+ and exists(select *
+ from t1n as t3n
+ where t2n.id=t3n.id
+ and exists(select *
+ from t2n as t4n
+ where t3n.vi=t4n.vi)));
+
+--echo -- IN/IN/IN -> 4 rows
+select *
+from t1n
+where id in(select id
+ from t2n
+ where u in(select u
+ from t1n
+ where v in(select v
+ from t2n)));
+
+--echo -- IN/IN/IN -> 4 rows
+--- *** ERROR: Reports zero rows using semijoin
+select *
+from t1n
+where u in(select u
+ from t2n
+ where id in(select id
+ from t1n
+ where vi in(select vi
+ from t2n)));
+
+--echo -- Drop all created tables
+
+drop table empty;
+drop table nulls;
+drop table t1;
+drop table t2;
+drop table t1n;
+drop table t2n;
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2008-11-10 18:36:50 +0000
+++ b/sql/item_subselect.h 2008-11-14 09:46:50 +0000
@@ -233,6 +233,36 @@ protected:
bool value; /* value of this item (boolean: exists/not-exists) */
public:
+ /* Priority of this predicate in the convert-to-semi-join-nest process. */
+ int sj_convert_priority;
+ /*
+ Used by subquery optimizations to keep track about in which clause this
+ subquery predicate is located:
+ (TABLE_LIST*) 1 - the predicate is an AND-part of the WHERE
+ join nest pointer - the predicate is an AND-part of ON expression
+ of a join nest
+ NULL - for all other locations
+ See also THD::emb_on_expr_nest.
+ */
+ TABLE_LIST *emb_on_expr_nest;
+ /*
+ Location of the subquery predicate. It is either
+ - pointer to join nest if the subquery predicate is in the ON expression
+ - (TABLE_LIST*)1 if the predicate is in the WHERE.
+ */
+ TABLE_LIST *expr_join_nest;
+ /*
+ Types of left_expr and subquery's select list allow to perform subquery
+ materialization. Currently, we set this to FALSE when it as well could
+ be TRUE. This is to be properly addressed with fix for BUG#36752.
+ */
+ bool types_allow_materialization;
+
+ /*
+ Same as above, but they also allow to scan the materialized table.
+ */
+ bool sjm_scan_allowed;
+
Item_exists_subselect(st_select_lex *select_lex);
Item_exists_subselect(): Item_subselect() {}
@@ -295,36 +325,6 @@ protected:
public:
/* Used to trigger on/off conditions that were pushed down to subselect */
bool *pushed_cond_guards;
-
- /* Priority of this predicate in the convert-to-semi-join-nest process. */
- int sj_convert_priority;
- /*
- Used by subquery optimizations to keep track about in which clause this
- subquery predicate is located:
- (TABLE_LIST*) 1 - the predicate is an AND-part of the WHERE
- join nest pointer - the predicate is an AND-part of ON expression
- of a join nest
- NULL - for all other locations
- See also THD::emb_on_expr_nest.
- */
- TABLE_LIST *emb_on_expr_nest;
- /*
- Location of the subquery predicate. It is either
- - pointer to join nest if the subquery predicate is in the ON expression
- - (TABLE_LIST*)1 if the predicate is in the WHERE.
- */
- TABLE_LIST *expr_join_nest;
- /*
- Types of left_expr and subquery's select list allow to perform subquery
- materialization. Currently, we set this to FALSE when it as well could
- be TRUE. This is to be properly addressed with fix for BUG#36752.
- */
- bool types_allow_materialization;
-
- /*
- Same as above, but they also allow to scan the materialized table.
- */
- bool sjm_scan_allowed;
/* The method chosen to execute the IN predicate. */
enum enum_exec_method {
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-11-10 20:37:59 +0000
+++ b/sql/sql_select.cc 2008-11-14 09:46:50 +0000
@@ -265,7 +265,7 @@ static int
join_read_record_no_init(JOIN_TAB *tab);
static
bool subquery_types_allow_materialization(THD *thd,
- Item_in_subselect *in_subs,
+ Item_exists_subselect *subs,
bool *scan_allowed);
int do_sj_reset(SJ_TMP_TABLE *sj_tbl);
TABLE *create_duplicate_weedout_tmp_table(THD *thd, uint uniq_tuple_length_arg,
@@ -572,17 +572,37 @@ JOIN::prepare(Item ***rref_pointer_array
if (!thd->lex->view_prepare_mode && // (1)
(subselect= select_lex->master_unit()->item)) // (2)
{
- Item_in_subselect *in_subs= NULL;
bool do_semijoin= !test(thd->variables.optimizer_switch &
OPTIMIZER_SWITCH_NO_SEMIJOIN);
- if (subselect->substype() == Item_subselect::IN_SUBS)
- in_subs= (Item_in_subselect*)subselect;
+ bool do_materialize= !test(thd->variables.optimizer_switch &
+ OPTIMIZER_SWITCH_NO_MATERIALIZATION);
+
+ /* We provide special treatment for IN, =ANY and EXISTS subqueries */
+ Item_exists_subselect *item_subs=
+ (subselect->substype() == Item_subselect::IN_SUBS ||
+ subselect->substype() == Item_subselect::EXISTS_SUBS) ?
+ (Item_exists_subselect*)subselect :
+ NULL;
+
+ /* Small trick to avoid several if tests below. exec_method is
+ not defined for EXISTS queries. */
+ Item_in_subselect::enum_exec_method exec_method=
+ (subselect->substype() == Item_subselect::IN_SUBS) ?
+ ((Item_in_subselect *)item_subs)->exec_method :
+ Item_in_subselect::NOT_TRANSFORMED;
+
+ /* Try the following sequence of query transformations:
+ 1. Convert the query into a semi-join
+ 2. Materialize the subquery
+ */
+
DBUG_PRINT("info", ("Checking if subq can be converted to semi-join"));
+
/*
Check if we're in subquery that is a candidate for flattening into a
semi-join (which is done in flatten_subqueries()). The
requirements are:
- 1. Subquery predicate is an IN/=ANY subq predicate
+ 1. Subquery predicate is an IN/=ANY or EXISTS subquery predicate
2. Subquery is a single SELECT (not a UNION)
3. Subquery does not have GROUP BY or ORDER BY
4. Subquery does not use aggregate functions or HAVING
@@ -595,23 +615,26 @@ JOIN::prepare(Item ***rref_pointer_array
9. Parent select is not a confluent table-less select
*/
if (do_semijoin &&
- in_subs && // 1
+ item_subs && // 1
!select_lex->is_part_of_union() && // 2
!select_lex->group_list.elements && !order && // 3
!having && !select_lex->with_sum_func && // 4
thd->thd_marker.emb_on_expr_nest && // 5
select_lex->outer_select()->join && // 6
select_lex->master_unit()->first_select()->leaf_tables && // 7
- in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED && // 8
+ exec_method == Item_in_subselect::NOT_TRANSFORMED && // 8
select_lex->outer_select()->leaf_tables) // 9
{
DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
- in_subs->types_allow_materialization=
- subquery_types_allow_materialization(thd, in_subs,
- &in_subs->sjm_scan_allowed);
-
- if (thd->stmt_arena->state != Query_arena::PREPARED)
+ item_subs->types_allow_materialization=
+ subquery_types_allow_materialization(thd, item_subs,
+ &item_subs->sjm_scan_allowed);
+
+ /* This step is only applicable to IN/=ANY queries (no EXISTS) */
+ if (subselect->substype() == Item_subselect::IN_SUBS &&
+ thd->stmt_arena->state != Query_arena::PREPARED)
{
+ Item_in_subselect *in_subs= (Item_in_subselect *)item_subs;
SELECT_LEX *current= thd->lex->current_select;
thd->lex->current_select= current->return_after_parsing();
char const *save_where= thd->where;
@@ -640,15 +663,15 @@ JOIN::prepare(Item ***rref_pointer_array
}
}
+ item_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
+
/* Register the subquery for further processing */
- select_lex->outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
- in_subs->expr_join_nest= thd->thd_marker.emb_on_expr_nest;
+ select_lex->outer_select()->join->sj_subselects.append(thd->mem_root, item_subs);
+ item_subs->expr_join_nest= thd->thd_marker.emb_on_expr_nest;
}
else
{
DBUG_PRINT("info", ("Subquery can't be converted to semi-join"));
- bool do_materialize= !test(thd->variables.optimizer_switch &
- OPTIMIZER_SWITCH_NO_MATERIALIZATION);
/*
Check if the subquery predicate can be executed via materialization.
The required conditions are:
@@ -684,14 +707,15 @@ JOIN::prepare(Item ***rref_pointer_array
Item_in_subselect in an Item_in_optimizer.
*/
if (do_materialize &&
- in_subs && // 1
+ subselect->substype() == Item_subselect::IN_SUBS && // 1
!select_lex->is_part_of_union() && // 2
select_lex->master_unit()->first_select()->leaf_tables && // 3
thd->lex->sql_command == SQLCOM_SELECT && // *
select_lex->outer_select()->leaf_tables && // 3A
- subquery_types_allow_materialization(thd, in_subs, NULL))
+ subquery_types_allow_materialization(thd, item_subs, NULL))
{
// psergey-todo: duplicated_subselect_card_check: where it's done?
+ Item_in_subselect *in_subs= (Item_in_subselect *)item_subs;
if (in_subs->is_top_level_item() && // 4
!in_subs->is_correlated && // 5
in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6
@@ -845,7 +869,7 @@ err:
SYNOPSIS
subquery_types_allow_materialization()
thd Thread handle
- in_subs Subquery predicate
+ subs Subquery predicate (IN/=ANY or EXISTS)
scan_allowed OUT If the return value is TRUE:
indicates whether it is possible to use subquery
materialization and scan the materialized table
@@ -888,6 +912,10 @@ err:
* require that compared columns have exactly the same type. This is
a temoporary measure to avoid BUG#36752-type problems.
+ For EXISTS subqueries, no action is currently performed.
+ In the future we will probably add code that performs similar checks
+ for trivially-correlated expressions inside the subquery's WHERE clause
+
RETURN
TRUE Yes, subquery types allow materialization
FALSE No, or this is an invalid subquery
@@ -895,11 +923,18 @@ err:
static
bool subquery_types_allow_materialization(THD *thd,
- Item_in_subselect *in_subs,
+ Item_exists_subselect *subs,
bool *scan_allowed)
{
DBUG_ENTER("subquery_types_allow_materialization");
-
+
+ if (subs->substype() == Item_subselect::EXISTS_SUBS)
+ {
+ *scan_allowed= true;
+ DBUG_RETURN(true);
+ }
+ Item_in_subselect *in_subs= (Item_in_subselect *)subs;
+
/* Fix the left expression if it is not yet fixed */
if (!in_subs->left_expr->fixed)
{
@@ -1113,9 +1148,9 @@ static bool sj_table_is_included(JOIN *j
4. SJ-Materialization.
The join order has "duplicate-generating ranges", and every range is
- served by one strategy or a combination of FirstMatch with with some
+ served by one strategy or a combination of FirstMatch with some
other strategy.
-
+
"Duplicate-generating range" is defined as a range within the join order
that contains all of the inner tables of a semi-join. All ranges must be
disjoint, if tables of several semi-joins are interleaved, then the ranges
@@ -1164,7 +1199,8 @@ static bool sj_table_is_included(JOIN *j
(1) - Prefix that may contain any outer tables. The prefix must contain
all the non-trivially correlated outer tables. (non-trivially means
- that the correlation is not just through the IN-equality).
+ that the correlation is not just through the IN-equality, see below
+ for further explanation).
(2) - Inner table for which the LooseScan scan is performed.
@@ -1185,8 +1221,59 @@ static bool sj_table_is_included(JOIN *j
Optimizer
=========
We have the choice made for us by the join optimizer. The optimizer
- guarantees that applicability conditions for
- loosescan
+ guarantees that applicability conditions for loosescan
+
+ A "trivially-correlated subquery" is defined as a subquery used in an
+ IN/=ANY or EXISTS predicate on the form:
+ (SELECT select-list
+ FROM from_clause
+ WHERE uncorrelated_cond AND
+ correlated_cond1 AND ...
+ correlated_condN)
+ where:
+ 1. from_clause is a list of one or more tables or a join expression
+ 2. GROUP BY, HAVING, ORDER BY and aggregated expressions are disallowed
+ 3. uncorrelated_cond contains references to tables inside the subquery
+ exclusively, and may contain negations and disjunctions.
+ 4. correlated_cond is an equality predicate where one side is an
+ expression that refers tables outside the subquery exclusively and
+ the other side is an expression that refers tables inside the
+ subquery exclusively. correlated_cond may be written as
+ inner_expr=outer_expr
+
+ Notice that it is possible to de-correlate IN queries containing such
+ subqueries from:
+ SELECT ...
+ FROM outer_tables
+ WHERE oe_list IN (SELECT ie_list
+ FROM inner_tables
+ WHERE inner_expr=outer_expr)
+ to
+ SELECT ...
+ FROM outer_tables
+ WHERE (oe_list, outer_expr) IN
+ (SELECT ie_list, inner_expr
+ FROM inner_tables)
+
+ EXISTS queries can be de-correlated from:
+ SELECT ...
+ FROM outer_tables
+ WHERE EXISTS(SELECT whatever
+ FROM inner_tables
+ WHERE inner_expr=outer_expr)
+ to
+ SELECT ...
+ FROM outer_tables
+ WHERE outer_expr IN
+ (SELECT inner_expr
+ FROM inner_tables)
+
+ However, if the correlated_cond may contain NULL values, the semantics
+ of the query is slightly changed. The original query will only return
+ rows where inner_expr=outer_expr is TRUE, whereas the IN query may return
+ UNKNOWN if inner_expr or outer_expr are NULL. Again, this is not a problem
+ when the condition involving the subquery does not contain NOT and does not
+ involve a boolean test other than IS TRUE.
RETURN
FALSE OK
@@ -3059,8 +3146,8 @@ err:
}
-int subq_sj_candidate_cmp(Item_in_subselect* const *el1,
- Item_in_subselect* const *el2)
+int subq_sj_candidate_cmp(Item_exists_subselect* const *el1,
+ Item_exists_subselect* const *el2)
{
return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 :
( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1);
@@ -3107,11 +3194,57 @@ void fix_list_after_tbl_changes(SELECT_L
parent_join Parent join, the one that has subq_pred in its WHERE/ON
clause
subq_pred Subquery predicate to be converted
+ This is either an IN, =ANY or EXISTS predicate
DESCRIPTION
Convert a subquery predicate into a TABLE_LIST semi-join nest. All the
prerequisites are already checked, so the conversion is always successfull.
+ The following transformations are performed:
+
+ 1. IN/=ANY predicates on the form:
+
+ SELECT ...
+ FROM ot1 ... otN
+ WHERE (oe1, ... oeM) IN (SELECT ie1, ..., ieM)
+ FROM it1 ... itK
+ [WHERE inner-cond])
+ [AND outer-cond]
+ [GROUP BY ...] [HAVING ...] [ORDER BY ...]
+
+ are transformed into:
+
+ SELECT ...
+ FROM (ot1 ... otN) SJ (it1 ... itK)
+ ON (oe1, ... oeM) = (ie1, ..., ieM)
+ [AND inner-cond]
+ [WHERE outer-cond]
+ [GROUP BY ...] [HAVING ...] [ORDER BY ...]
+
+ 2. EXISTS predicates on the form:
+
+ SELECT ...
+ FROM ot1 ... otN
+ WHERE EXISTS (SELECT whatever
+ FROM it1 ... itK
+ [WHERE inner-cond])
+ [AND outer-cond]
+ [GROUP BY ...] [HAVING ...] [ORDER BY ...]
+
+ are transformed into:
+
+ SELECT ...
+ FROM (ot1 ... otN) SJ (it1 ... itK)
+ ON inner-cond
+ [WHERE outer-cond]
+ [GROUP BY ...] [HAVING ...] [ORDER BY ...]
+
+ If inner-cond in the EXISTS subquery is empty, it is replaced with TRUE.
+
+ Notice that in the inner-cond may contain correlated and non-correlated
+ expressions. Further transformations will analyze and break up such
+ expressions.
+
Prepared Statements: the transformation is permanent:
- Changes in TABLE_LIST structures are naturally permanent
- Item tree changes are performed on statement MEM_ROOT:
@@ -3127,7 +3260,7 @@ void fix_list_after_tbl_changes(SELECT_L
TRUE Out of memory error
*/
-bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
+bool convert_subq_to_sj(JOIN *parent_join, Item_exists_subselect *subq_pred)
{
SELECT_LEX *parent_lex= parent_join->select_lex;
TABLE_LIST *emb_tbl_nest= NULL;
@@ -3135,6 +3268,12 @@ bool convert_subq_to_sj(JOIN *parent_joi
THD *thd= parent_join->thd;
DBUG_ENTER("convert_subq_to_sj");
+ // Shorthand pointer used for IN subqueries
+ Item_in_subselect *in_subq_pred =
+ subq_pred->substype() == Item_subselect::IN_SUBS ?
+ (Item_in_subselect *)subq_pred :
+ NULL;
+
/*
1. Find out where to put the predicate into.
Note: for "t1 LEFT JOIN t2" this will be t2, a leaf.
@@ -3293,7 +3432,10 @@ bool convert_subq_to_sj(JOIN *parent_joi
/* 3. Remove the original subquery predicate from the WHERE/ON */
// The subqueries were replaced for Item_int(1) earlier
- subq_pred->exec_method= Item_in_subselect::SEMI_JOIN; // for subsequent executions
+ if (subq_pred->substype() == Item_subselect::IN_SUBS)
+ {
+ in_subq_pred->exec_method= Item_in_subselect::SEMI_JOIN; // for subsequent executions
+ }
/*TODO: also reset the 'with_subselect' there. */
/* n. Adjust the parent_join->tables counter */
@@ -3310,23 +3452,25 @@ bool convert_subq_to_sj(JOIN *parent_joi
}
parent_join->tables += subq_lex->join->tables;
- /*
- Put the subquery's WHERE into semi-join's sj_on_expr
- Add the subquery-induced equalities too.
- */
- SELECT_LEX *save_lex= thd->lex->current_select;
- thd->lex->current_select=subq_lex;
- if (!subq_pred->left_expr->fixed &&
- subq_pred->left_expr->fix_fields(thd, &subq_pred->left_expr))
- DBUG_RETURN(TRUE);
- thd->lex->current_select=save_lex;
+ if (subq_pred->substype() == Item_subselect::IN_SUBS)
+ {
+ /*
+ IN subquery: Put the subquery's WHERE into semi-join's sj_on_expr
+ Add the subquery-induced equalities too.
+ */
+ SELECT_LEX *save_lex= thd->lex->current_select;
+ thd->lex->current_select=subq_lex;
+ if (!in_subq_pred->left_expr->fixed &&
+ in_subq_pred->left_expr->fix_fields(thd, &in_subq_pred->left_expr))
+ DBUG_RETURN(TRUE);
+ thd->lex->current_select=save_lex;
- sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables();
- sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() |
- subq_pred->left_expr->used_tables();
- sj_nest->sj_on_expr= subq_lex->where;
+ sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables();
+ sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() |
+ in_subq_pred->left_expr->used_tables();
+ sj_nest->sj_on_expr= subq_lex->where;
- /*
+ /*
Create the IN-equalities and inject them into semi-join's ON expression.
Additionally, for LooseScan strategy
- Record the number of IN-equalities.
@@ -3340,62 +3484,109 @@ bool convert_subq_to_sj(JOIN *parent_joi
pointers to Item_direct_view_refs are guaranteed to be stable as
Item_direct_view_refs doesn't substitute itself with anything in
Item_direct_view_ref::fix_fields.
- */
- sj_nest->sj_in_exprs= subq_pred->left_expr->cols();
- sj_nest->nested_join->sj_outer_expr_list.empty();
-
- if (subq_pred->left_expr->cols() == 1)
- {
- nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr);
+ */
+ sj_nest->sj_in_exprs= in_subq_pred->left_expr->cols();
+ sj_nest->nested_join->sj_outer_expr_list.empty();
- Item_func_eq *item_eq= new Item_func_eq(subq_pred->left_expr,
- subq_lex->ref_pointer_array[0]);
- item_eq->in_equality_no= 0;
- sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
- }
- else
- {
- for (uint i= 0; i < subq_pred->left_expr->cols(); i++)
+ if (in_subq_pred->left_expr->cols() == 1)
{
- nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr->
- element_index(i));
- Item_func_eq *item_eq=
- new Item_func_eq(subq_pred->left_expr->element_index(i),
- subq_lex->ref_pointer_array[i]);
- item_eq->in_equality_no= i;
+ nested_join->sj_outer_expr_list.push_back(in_subq_pred->left_expr);
+
+ Item_func_eq *item_eq= new Item_func_eq(in_subq_pred->left_expr,
+ subq_lex->ref_pointer_array[0]);
+ item_eq->in_equality_no= 0;
sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
}
- }
- /* Fix the created equality and AND */
- sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr);
+ else
+ {
+ for (uint i= 0; i < in_subq_pred->left_expr->cols(); i++)
+ {
+ nested_join->sj_outer_expr_list.push_back(in_subq_pred->left_expr->
+ element_index(i));
+ Item_func_eq *item_eq=
+ new Item_func_eq(in_subq_pred->left_expr->element_index(i),
+ subq_lex->ref_pointer_array[i]);
+ item_eq->in_equality_no= i;
+ sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
+ }
+ }
+ /* Fix the created equality and AND */
+ sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr);
- /*
- Walk through sj nest's WHERE and ON expressions and call
- item->fix_table_changes() for all items.
- */
- sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
- fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
+ /*
+ Walk through sj nest's WHERE and ON expressions and call
+ item->fix_table_changes() for all items.
+ */
+ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
+ fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
- /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
- subq_lex->master_unit()->exclude_level();
+ /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
+ subq_lex->master_unit()->exclude_level();
- DBUG_EXECUTE("where",
- print_where(sj_nest->sj_on_expr,"SJ-EXPR", QT_ORDINARY););
+ DBUG_EXECUTE("where",
+ print_where(sj_nest->sj_on_expr,"SJ-EXPR", QT_ORDINARY););
- /* Inject sj_on_expr into the parent's WHERE or ON */
- if (emb_tbl_nest)
+ /* Inject sj_on_expr into the parent's WHERE or ON */
+ if (emb_tbl_nest)
+ {
+ emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
+ sj_nest->sj_on_expr);
+ emb_tbl_nest->on_expr->fix_fields(parent_join->thd,
+ &emb_tbl_nest->on_expr);
+ }
+ else
+ {
+ /* Inject into the WHERE */
+ parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);
+ parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
+ parent_join->select_lex->where= parent_join->conds;
+ }
+ }
+ else if (subq_pred->substype() == Item_subselect::EXISTS_SUBS)
{
- emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
- sj_nest->sj_on_expr);
- emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);
+ /* Add information about correlated tables to semi-join nest */
+
+ sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables();
+ sj_nest->nested_join->sj_depends_on= subq_pred->used_tables();
+ if (subq_lex->where)
+ {
+ sj_nest->sj_on_expr= subq_lex->where; // Non-empty EXISTS predicate
+ }
+ else
+ {
+ sj_nest->sj_on_expr= new Item_int(1); // Add a "true" predicate, NULL
+ // pointer not supported
+ }
+ /*
+ Walk through sj nest's WHERE and ON expressions and call
+ item->fix_table_changes() for all items.
+ */
+ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
+ fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
+
+ /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
+ subq_lex->master_unit()->exclude_level();
+
+ /* Inject sj_on_expr into the parent's WHERE or ON */
+ if (emb_tbl_nest)
+ {
+ emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
+ sj_nest->sj_on_expr);
+ emb_tbl_nest->on_expr->fix_fields(parent_join->thd,
+ &emb_tbl_nest->on_expr);
+ }
+ else
+ {
+ /* Inject into the WHERE */
+ parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);
+ parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
+ parent_join->select_lex->where= parent_join->conds;
+ }
}
else
{
- /* Inject into the WHERE */
- parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);
- parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
- parent_join->select_lex->where= parent_join->conds;
+ DBUG_ASSERT(false); // All cases covered
}
if (subq_lex->ftfunc_list->elements)
@@ -3460,23 +3651,23 @@ bool convert_subq_to_sj(JOIN *parent_joi
bool JOIN::flatten_subqueries()
{
Query_arena *arena, backup;
- Item_in_subselect **in_subq;
- Item_in_subselect **in_subq_end;
+ Item_exists_subselect **subq; // Covers both IN and EXISTS subqueries
+ Item_exists_subselect **subq_end;
DBUG_ENTER("JOIN::flatten_subqueries");
if (sj_subselects.elements() == 0)
DBUG_RETURN(FALSE);
/* First, convert child join's subqueries. We proceed bottom-up here */
- for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back();
- in_subq != in_subq_end; in_subq++)
+ for (subq= sj_subselects.front(), subq_end= sj_subselects.back();
+ subq != subq_end; subq++)
{
- JOIN *child_join= (*in_subq)->unit->first_select()->join;
+ JOIN *child_join= (*subq)->unit->first_select()->join;
child_join->outer_tables = child_join->tables;
if (child_join->flatten_subqueries())
DBUG_RETURN(TRUE);
- (*in_subq)->sj_convert_priority=
- (*in_subq)->is_correlated * MAX_TABLES + child_join->outer_tables;
+ (*subq)->sj_convert_priority=
+ (*subq)->is_correlated * MAX_TABLES + child_join->outer_tables;
}
// Temporary measure: disable semi-joins when they are together with outer
@@ -3487,7 +3678,7 @@ bool JOIN::flatten_subqueries()
if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr &&
!embedding->embedding)))
{
- in_subq= sj_subselects.front();
+ subq= sj_subselects.front();
arena= thd->activate_stmt_arena_if_needed(&backup);
goto skip_conversion;
}
@@ -3504,65 +3695,71 @@ bool JOIN::flatten_subqueries()
// #tables-in-parent-query + #tables-in-subquery < MAX_TABLES
/* Replace all subqueries to be flattened with Item_int(1) */
arena= thd->activate_stmt_arena_if_needed(&backup);
- for (in_subq= sj_subselects.front();
- in_subq != in_subq_end &&
- tables + (*in_subq)->unit->first_select()->join->tables < MAX_TABLES;
- in_subq++)
- {
- Item **tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
- &conds : &((*in_subq)->emb_on_expr_nest->on_expr);
- if (replace_where_subcondition(this, tree, *in_subq, new Item_int(1),
+ for (subq= sj_subselects.front();
+ subq != subq_end &&
+ tables + (*subq)->unit->first_select()->join->tables < MAX_TABLES;
+ subq++)
+ {
+ Item **tree= ((*subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &conds : &((*subq)->emb_on_expr_nest->on_expr);
+ if (replace_where_subcondition(this, tree, *subq, new Item_int(1),
FALSE))
DBUG_RETURN(TRUE);
}
- for (in_subq= sj_subselects.front();
- in_subq != in_subq_end &&
- tables + (*in_subq)->unit->first_select()->join->tables < MAX_TABLES;
- in_subq++)
+ for (subq= sj_subselects.front();
+ subq != subq_end &&
+ tables + (*subq)->unit->first_select()->join->tables < MAX_TABLES;
+ subq++)
{
- if (convert_subq_to_sj(this, *in_subq))
+ if (convert_subq_to_sj(this, *subq))
DBUG_RETURN(TRUE);
}
skip_conversion:
/* 3. Finalize those we didn't convert */
bool converted= FALSE;
- for (; in_subq!= in_subq_end; in_subq++)
+ for (; subq!= subq_end; subq++)
{
- JOIN *child_join= (*in_subq)->unit->first_select()->join;
+ JOIN *child_join= (*subq)->unit->first_select()->join;
Item_subselect::trans_res res;
- (*in_subq)->changed= 0;
- (*in_subq)->fixed= 0;
+ (*subq)->changed= 0;
+ (*subq)->fixed= 0;
SELECT_LEX *save_select_lex= thd->lex->current_select;
- thd->lex->current_select= (*in_subq)->unit->first_select();
+ thd->lex->current_select= (*subq)->unit->first_select();
converted= TRUE;
- res= (*in_subq)->select_transformer(child_join);
+ res= (*subq)->select_transformer(child_join);
thd->lex->current_select= save_select_lex;
if (res == Item_subselect::RES_ERROR)
DBUG_RETURN(TRUE);
- (*in_subq)->changed= 1;
- (*in_subq)->fixed= 1;
+ (*subq)->changed= 1;
+ (*subq)->fixed= 1;
+
+ Item *substitute= (*subq)->substitution;
+
+ // Temporary measure: This check may be removed when semijoins can be
+ // combined with outer joins (see exception code above)
+ if (substitute == NULL)
+ continue;
- Item *substitute= (*in_subq)->substitution;
- bool do_fix_fields= !(*in_subq)->substitution->fixed;
- Item **tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
- &conds : &((*in_subq)->emb_on_expr_nest->on_expr);
- if (replace_where_subcondition(this, tree, *in_subq, substitute,
+ bool do_fix_fields= !(*subq)->substitution->fixed;
+ Item **tree= ((*subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &conds : &((*subq)->emb_on_expr_nest->on_expr);
+ if (replace_where_subcondition(this, tree, *subq, substitute,
do_fix_fields))
DBUG_RETURN(TRUE);
- (*in_subq)->substitution= NULL;
+ (*subq)->substitution= NULL;
if (!thd->stmt_arena->is_conventional())
{
- tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
- &select_lex->prep_where : &((*in_subq)->emb_on_expr_nest->prep_on_expr);
+ tree= ((*subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &select_lex->prep_where : &((*subq)->emb_on_expr_nest->prep_on_expr);
- if (replace_where_subcondition(this, tree, *in_subq, substitute,
+ if (replace_where_subcondition(this, tree, *subq, substitute,
FALSE))
DBUG_RETURN(TRUE);
}
@@ -9484,7 +9681,8 @@ bool setup_sj_materialization(JOIN_TAB *
for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
{
- tab_ref->items[i]= emb_sj_nest->sj_subq_pred->left_expr->element_index(i);
+ tab_ref->items[i]= ((Item_in_subselect *)emb_sj_nest->sj_subq_pred)->
+ left_expr->element_index(i);
int null_count= test(cur_key_part->field->real_maybe_null());
*ref_key= new store_key_item(thd, cur_key_part->field,
/* TODO:
@@ -9516,7 +9714,7 @@ bool setup_sj_materialization(JOIN_TAB *
remove_sj_conds(&tab[i].select->cond);
}
if (!(sjm->in_equality= create_subq_in_equalities(thd, sjm,
- emb_sj_nest->sj_subq_pred)))
+ (Item_in_subselect*)emb_sj_nest->sj_subq_pred)))
DBUG_RETURN(TRUE);
}
else
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2008-11-10 20:37:59 +0000
+++ b/sql/sql_select.h 2008-11-14 09:46:50 +0000
@@ -715,7 +715,7 @@ public:
bool union_part; ///< this subselect is part of union
bool optimized; ///< flag to avoid double optimization in EXPLAIN
- Array<Item_in_subselect> sj_subselects;
+ Array<Item_exists_subselect> sj_subselects; // Covers IN and EXISTS subqueries
/* Temporary tables used to weed-out semi-join duplicates */
List<TABLE> sj_tmp_tables;
=== modified file 'sql/table.h'
--- a/sql/table.h 2008-10-29 20:35:16 +0000
+++ b/sql/table.h 2008-11-14 09:46:50 +0000
@@ -1006,7 +1006,7 @@ public:
class SJ_MATERIALIZATION_INFO;
class Index_hint;
-class Item_in_subselect;
+class Item_exists_subselect;
/*
@@ -1089,7 +1089,7 @@ struct TABLE_LIST
table_map sj_inner_tables;
/* Number of IN-compared expressions */
uint sj_in_exprs;
- Item_in_subselect *sj_subq_pred;
+ Item_exists_subselect *sj_subq_pred;
SJ_MATERIALIZATION_INFO *sj_mat_info;
/*
| Thread |
|---|
| • bzr commit into mysql-6.0-opt-subqueries branch (RoyLysengroy.lyseng:2719)WL#4389 | RoyLysengroy.lyseng | 14 Nov |
