diff options
Diffstat (limited to 'mysql-test/main/subselect3.test')
| -rw-r--r-- | mysql-test/main/subselect3.test | 1316 |
1 files changed, 4 insertions, 1312 deletions
diff --git a/mysql-test/main/subselect3.test b/mysql-test/main/subselect3.test index 0221315eb35..79034f976a8 100644 --- a/mysql-test/main/subselect3.test +++ b/mysql-test/main/subselect3.test @@ -1,1318 +1,10 @@ --disable_warnings drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; --enable_warnings +--source include/default_optimizer_switch.inc -set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; -if (`select @join_cache_level_for_subselect3_test is null`) -{ - set join_cache_level=1; -} -if (`select @join_cache_level_for_subselect3_test is not null`) -{ - set join_cache_level=@join_cache_level_for_subselect3_test; -} +set join_cache_level=1; -# -# 1. Subquery with GROUP/HAVING -# -create table t1 (oref int, grp int, ie int) ; -insert into t1 (oref, grp, ie) values - (1, 1, 1), - (1, 1, 1), - (1, 2, NULL), +--source subselect3.inc - (2, 1, 3), - - (3, 1, 4), - (3, 2, NULL); - -# Ok, for -# select max(ie) from t1 where oref=PARAM group by grp -# we'll have: -# PARAM subquery result -# 1 -> {(1), (NULL)} matching + NULL -# 2 -> {(3)} non-matching -# 3 -> {(3), (NULL)} non-matching + NULL -# 4 -> {} empty set - -create table t2 (oref int, a int); -insert into t2 values - (1, 1), - (2, 2), - (3, 3), - (4, NULL), - (2, NULL); - -# true, false, null, false, null -select a, oref, a in (select max(ie) - from t1 where oref=t2.oref group by grp) Z from t2; - -# This must have a trigcond -explain extended -select a, oref, a in (select max(ie) - from t1 where oref=t2.oref group by grp) Z from t2; - -# This must not have a trigcond: -explain extended -select a, oref from t2 -where a in (select max(ie) from t1 where oref=t2.oref group by grp); -select a, oref, a in ( - select max(ie) from t1 where oref=t2.oref group by grp union - select max(ie) from t1 where oref=t2.oref group by grp - ) Z from t2; - -# Non-correlated subquery, 2 NULL evaluations -create table t3 (a int); -insert into t3 values (NULL), (NULL); -flush status; -select a in (select max(ie) from t1 where oref=4 group by grp) from t3; -show status like 'Handler_read_rnd_next'; -select ' ^ This must show 11' Z; - -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; - -# This must show trigcond: -explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; - -set @@optimizer_switch=@save_optimizer_switch; -drop table t1, t2, t3; - -# -# 2. Subquery handled with 'index_subquery': -# -create table t1 (a int, oref int, key(a)); -insert into t1 values - (1, 1), - (1, NULL), - (2, 3), - (2, NULL), - (3, NULL); -insert into t1 values (5, 7), (8, 9), (4, 1); - -create table t2 (a int, oref int); -insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); - -select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; - -# The next explain shows "using index" but that is just incorrect display -# (there is a bug filed about this). -explain extended -select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; - -flush status; -select oref, a from t2 where a in (select a from t1 where oref=t2.oref); -# This will only show access to t2: -show status like '%Handler_read_rnd_next'; - -# Check that repeated NULL-scans are not cached (subq. is not correlated): -delete from t2; -insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); - -set optimizer_switch='subquery_cache=off'; -flush status; -select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; -show status like '%Handler_read%'; -select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; -set @@optimizer_switch=@save_optimizer_switch; - -drop table t1, t2; - -# -# 3. Subquery handled with 'unique_index_subquery': -# -create table t1 (a int, b int, primary key (a)); -insert into t1 values (1,1), (3,1),(100,1); - -create table t2 (a int, b int); -insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); - -select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; - -drop table t1, t2; - -# -# 4. Subquery that is a join, with ref access -# -create table t1 (a int, b int, key(a)); -insert into t1 values - (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); - -create table t2 like t1; -insert into t2 select * from t1; -update t2 set b=1; - -create table t3 (a int, oref int); -insert into t3 values (1, 1), (NULL,1), (NULL,0); -select a, oref, - t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z -from t3; - -# This must have trigcond in WHERE and HAVING: -explain extended -select a, oref, - t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z -from t3; - -drop table t1, t2, t3; - - -# -# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)" -# - -# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc -create table t1 (a int NOT NULL, b int NOT NULL, key(a)); -insert into t1 values - (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); - -create table t2 like t1; -insert into t2 select * from t1; -update t2 set b=1; - -create table t3 (a int, oref int); -insert into t3 values (1, 1), (NULL,1), (NULL,0); -select a, oref, - t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z -from t3; - ---echo This must show a trig_cond: -explain extended -select a, oref, - t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z -from t3; -drop table t1,t2,t3; - - -# case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY -create table t1 (oref int, grp int); -insert into t1 (oref, grp) values - (1, 1), - (1, 1); - -# Ok, for -# select count(*) from t1 group by grp having grp=PARAM -# we'll have: -# PARAM subuqery result -# 1 -> {(2)} -# 2 -> {} - empty set -create table t2 (oref int, a int); -insert into t2 values - (1, NULL), - (2, NULL); - -select a, oref, - a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; - ---echo This must show a trig_cond: -explain extended -select a, oref, - a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; - -drop table t1, t2; - -create table t1 (a int, b int, primary key (a)); -insert into t1 values (1,1), (3,1),(100,1); -create table t2 (a int, b int); -insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); - -select a,b, a in (select a from t1 where t1.b = t2.b union select a from -t1 where t1.b = t2.b) Z from t2 ; -select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; -drop table t1, t2; - - -# -# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side. -# -create table t3 (a int); -insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, b int, oref int); -insert into t2 values (NULL,1, 100), (NULL,2, 100); - -create table t1 (a int, b int, c int, key(a,b)); -insert into t1 select 2*A, 2*A, 100 from t3; - -# First test index subquery engine -explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; -select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; - -# Then check that we do turn off 'ref' scans in the subquery -create table t4 (x int); -insert into t4 select A.a + 10*B.a from t1 A, t1 B; -explain extended - select a,b, oref, - (a,b) in (select a,b from t1,t4 where c=t2.oref) Z - from t2; -select a,b, oref, - (a,b) in (select a,b from t1,t4 where c=t2.oref) Z -from t2; - -drop table t1,t2,t3,t4; - -# More tests for tricky multi-column cases, where some of pushed-down -# equalities are used for index lookups and some arent. -create table t1 (oref char(4), grp int, ie1 int, ie2 int); -insert into t1 (oref, grp, ie1, ie2) values - ('aa', 10, 2, 1), - ('aa', 10, 1, 1), - ('aa', 20, 2, 1), - ('bb', 10, 3, 1), - ('cc', 10, 4, 2), - ('cc', 20, 3, 2), - - ('ee', 10, 2, 1), - ('ee', 10, 1, 2), - - ('ff', 20, 2, 2), - ('ff', 20, 1, 2); -create table t2 (oref char(4), a int, b int); -insert into t2 values - ('ee', NULL, 1), - ('bb', 2, 1), - ('ff', 2, 2), - ('cc', 3, NULL), - ('bb', NULL, NULL), - ('aa', 1, 1), - ('dd', 1, NULL); -alter table t1 add index idx(ie1,ie2); - -# cc 3 NULL NULL -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ; -insert into t2 values ('new1', 10,10); -insert into t1 values ('new1', 1234, 10, NULL); -# new1, 10, 10, NULL, -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; -explain extended -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; -drop table t1, t2; - -# Now test different column types: -create table t1 (oref char(4), grp int, ie int); -insert into t1 (oref, grp, ie) values - ('aa', 10, 2), - ('aa', 10, 1), - ('aa', 20, NULL), - - ('bb', 10, 3), - - ('cc', 10, 4), - ('cc', 20, NULL), - - ('ee', 10, NULL), - ('ee', 10, NULL), - - ('ff', 20, 2), - ('ff', 20, 1); - -create table t2 (oref char(4), a int); -insert into t2 values - ('ee', NULL), - ('bb', 2), - ('ff', 2), - ('cc', 3), - ('aa', 1), - ('dd', NULL), - ('bb', NULL); - -select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); - -select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); - - -select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; - -select oref, a from t2 where - a in (select min(ie) from t1 where oref=t2.oref group by grp); - -select oref, a from t2 where - a not in (select min(ie) from t1 where oref=t2.oref group by grp); - -# -update t1 set ie=3 where oref='ff' and ie=1; - -select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by -grp) Z from t2; - - -select oref, a from t2 where a in (select min(ie) from t1 where -oref=t2.oref group by grp); - -select oref, a from t2 where a not in (select min(ie) from t1 where -oref=t2.oref group by grp); - -select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by -grp having min(ie) > 1) Z from t2; - -select oref, a from t2 where a in (select min(ie) from t1 where -oref=t2.oref group by grp having min(ie) > 1); - -select oref, a from t2 where a not in (select min(ie) from t1 where -oref=t2.oref group by grp having min(ie) > 1); - -# -alter table t1 add index idx(ie); - -explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); - -select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); - - -alter table t1 drop index idx; -alter table t1 add index idx(oref,ie); - -explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); - -select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); - -explain -select oref, a, - a in (select min(ie) from t1 where oref=t2.oref - group by grp having min(ie) > 1) Z -from t2; - -select oref, a, - a in (select min(ie) from t1 where oref=t2.oref - group by grp having min(ie) > 1) Z -from t2; - -select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref - group by grp having min(ie) > 1); - -select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref - group by grp having min(ie) > 1); - -drop table t1,t2; - -create table t1 (oref char(4), grp int, ie1 int, ie2 int); -insert into t1 (oref, grp, ie1, ie2) values - ('aa', 10, 2, 1), - ('aa', 10, 1, 1), - ('aa', 20, 2, 1), - - ('bb', 10, 3, 1), - - ('cc', 10, 4, 2), - ('cc', 20, 3, 2), - - ('ee', 10, 2, 1), - ('ee', 10, 1, 2), - - ('ff', 20, 2, 2), - ('ff', 20, 1, 2); - -create table t2 (oref char(4), a int, b int); -insert into t2 values - ('ee', NULL, 1), - ('bb', 2, 1), - ('ff', 2, 2), - ('cc', 3, NULL), - ('bb', NULL, NULL), - ('aa', 1, 1), - ('dd', 1, NULL); - -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; - -select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); - -select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); - -select oref, a, b, - (a,b) in (select min(ie1),max(ie2) from t1 - where oref=t2.oref group by grp) Z -from t2; - -select oref, a, b from t2 where - (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); - -select oref, a, b from t2 where - (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); - -alter table t1 add index idx(ie1,ie2); - -explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; - -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; - -select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); - -select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); - -explain extended -select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; - -drop table t1,t2; - -create table t1 (oref char(4), grp int, ie int primary key); -insert into t1 (oref, grp, ie) values - ('aa', 10, 2), - ('aa', 10, 1), - - ('bb', 10, 3), - - ('cc', 10, 4), - ('cc', 20, 5), - ('cc', 10, 6); - -create table t2 (oref char(4), a int); -insert into t2 values - ('ee', NULL), - ('bb', 2), - ('cc', 5), - ('cc', 2), - ('cc', NULL), - ('aa', 1), - ('bb', NULL); - -explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; - -select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); - -select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); - -explain -select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; - -select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; - -drop table t1,t2; - -# -# BUG#24420: row-based IN suqueries with aggregation when the left operand -# of the subquery predicate may contain NULL values -# - -create table t1 (a int, b int); -insert into t1 values (0,0), (2,2), (3,3); -create table t2 (a int, b int); -insert into t2 values (1,1), (3,3); - -select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; - -insert into t2 values (NULL,4); -select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; - -drop table t1,t2; - -# -# Bug #24484: Aggregate function used in column list subquery gives erroneous -# error -# -CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); -INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), - (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), - (1,9,'m'); -CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); -INSERT INTO t2 SELECT * FROM t1; - -# Gives error, but should work since it is (a, b) is the PK so only one -# given match possible -SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) - as test FROM t1 GROUP BY a; -SELECT * FROM t1 GROUP by t1.a - HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c - HAVING MAX(t2.b+t1.a) < 10)); - -SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; - -SELECT a, MAX(b), - (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) - LIMIT 1) - as cnt, - (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) - as t_b, - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) - as t_b, - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) - as t_b - FROM t1 GROUP BY a; - -SELECT a, MAX(b), - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test - FROM t1 GROUP BY a; - - -DROP TABLE t1, t2; - -# The next three test cases must be executed with the IN=>EXISTS strategy -set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; - -# -# Bug #27870: crash of an equijoin query with WHERE condition containing -# a subquery predicate of the form <join attr> NOT IN (SELECT ...) -# - -CREATE TABLE t1 (a int); -CREATE TABLE t2 (b int, PRIMARY KEY(b)); -INSERT INTO t1 VALUES (1), (NULL), (4); -INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6); - -EXPLAIN EXTENDED -SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); -SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); -SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); - -DROP TABLE t1,t2; - -# -# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL -# - -CREATE TABLE t1 (id int); -CREATE TABLE t2 (id int PRIMARY KEY); -CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); -INSERT INTO t1 VALUES (2), (NULL), (3), (1); -INSERT INTO t2 VALUES (234), (345), (457); -INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); - -EXPLAIN -SELECT * FROM t1 - WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 - WHERE t3.name='xxx' AND t2.id=t3.id); -SELECT * FROM t1 - WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 - WHERE t3.name='xxx' AND t2.id=t3.id); - -SELECT (t1.id IN (SELECT t2.id FROM t2,t3 - WHERE t3.name='xxx' AND t2.id=t3.id)) AS x - FROM t1; - -DROP TABLE t1,t2,t3; - -# -# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated -# subquery -# -CREATE TABLE t1 (a INT NOT NULL); -INSERT INTO t1 VALUES (1),(-1), (65),(66); - -CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); -INSERT INTO t2 VALUES (65),(66); - -SELECT a FROM t1 WHERE a NOT IN (65,66); -SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); -EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); - -DROP TABLE t1, t2; - -set @@optimizer_switch=@save_optimizer_switch; - -# -# Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer: -# Assertion failed, unexpected error message: -# ERROR 1247 (42S22): Reference '<list ref>' not supported (forward -# reference in item list) -# -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES(1); - -CREATE TABLE t2 (placeholder CHAR(11)); -INSERT INTO t2 VALUES("placeholder"); - -SELECT ROW(1, 2) IN (SELECT t1.a, 2) FROM t1 GROUP BY t1.a; -SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a; - -DROP TABLE t1, t2; - -# -# Bug #36005: crash in subselect with single row -# (subselect_single_select_engine::exec) -# - -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2),(3); -CREATE TABLE t2 SELECT * FROM t1; - -SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0); - -DROP TABLE t1, t2; - -# -# Bug 2198 -# - -create table t1 (a int, b decimal(13, 3)); -insert into t1 values (1, 0.123); -select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; -delete from t1; -load data infile "subselect.out.file.1" into table t1; -select * from t1; -drop table t1; -let $datadir=`select @@datadir`; ---remove_file $datadir/test/subselect.out.file.1 - -# -# Bug #37894: Assertion in init_read_record_seq in handler.h line 1444 -# - -CREATE TABLE t1 ( - pk INT PRIMARY KEY, - int_key INT, - varchar_key VARCHAR(5) UNIQUE, - varchar_nokey VARCHAR(5) -); -INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p'); - -SELECT varchar_nokey -FROM t1 -WHERE NULL NOT IN ( - SELECT INNR.pk FROM t1 AS INNR2 - LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key ) - WHERE INNR.varchar_key > 'n{' -); - -DROP TABLE t1; - -# -# Bug #39069: <row constructor> IN <table-subquery> seriously messed up -# - -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1), (2), (11); - ---echo # 2nd and 3rd columns should be same -SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; -SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; -SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1; -SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1; - -# The x alias is used below to workaround bug #40674. -# Regression tests for sum function on outer column in subselect from dual: -SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1; ---echo # 2nd and 3rd columns should be same -EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; -SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; - -DROP TABLE t1; - ---echo # both columns should be same -SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL); -SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL); -SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2); -SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); -SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); -SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); - -# -# Bug #37362 Crash in do_field_eq -# -# Note that currently this test produces wrong output, see MBug#430669. -# -CREATE TABLE t1 (a INT, b INT, c INT); -INSERT INTO t1 VALUES (1,1,1), (1,1,1); - ---error 1054 -EXPLAIN EXTENDED - SELECT c FROM - ( SELECT - (SELECT COUNT(a) FROM - (SELECT COUNT(b) FROM t1) AS x GROUP BY c - ) FROM t1 GROUP BY b - ) AS y; -SHOW WARNINGS; - -DROP TABLE t1; - - ---echo End of 5.0 tests - ---echo # ---echo # BUG#36896: Server crash on SELECT FROM DUAL ---echo # -create table t1 (a int); -select 1 as res from dual where (1) in (select * from t1); -drop table t1; - -# -# BUG#36135 "void Diagnostics_area::set_eof_status(THD*): Assertion `!is_set()' failed." -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - -create table t1 ( - a int(11) default null, - b int(11) default null, - key (a) -); -# produce numbers 0..999 -insert into t1 select A.a+10*(B.a+10*C.a),A.a+10*(B.a+10*C.a) from t0 A, t0 B, t0 C; - -create table t2 (a int(11) default null); -insert into t2 values (0),(1); - -create table t3 (a int(11) default null); -insert into t3 values (0),(1); - -create table t4 (a int(11) default null); -insert into t4 values (0),(1); - -create table t5 (a int(11) default null); -insert into t5 values (0),(1),(0),(1); - -# this must not fail assertion ---error 1242 -select * from t2, t3 -where - t2.a < 10 and - t3.a+1 = 2 and - t3.a in (select t1.b from t1 - where t1.a+1=t1.a+1 and - t1.a < (select t4.a+10 - from t4, t5 limit 2)); - -drop table t0, t1, t2, t3, t4, t5; - ---echo # ---echo # BUG#48177 - SELECTs with NOT IN subqueries containing NULL ---echo # values return too many records ---echo # - -CREATE TABLE t1 ( - i1 int DEFAULT NULL, - i2 int DEFAULT NULL -) ; - -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (2, 3); -INSERT INTO t1 VALUES (4, NULL); -INSERT INTO t1 VALUES (4, 0); -INSERT INTO t1 VALUES (NULL, NULL); - -CREATE TABLE t2 ( - i1 int DEFAULT NULL, - i2 int DEFAULT NULL -) ; - -INSERT INTO t2 VALUES (4, NULL); -INSERT INTO t2 VALUES (5, 0); - ---echo ---echo Data in t1 -SELECT i1, i2 FROM t1; - ---echo ---echo Data in subquery (should be filtered out) -SELECT i1, i2 FROM t2 ORDER BY i1; - -FLUSH STATUS; -set @save_optimizer_switch2=@@optimizer_switch; -set optimizer_switch='subquery_cache=off'; - ---echo -SELECT i1, i2 -FROM t1 -WHERE (i1, i2) - NOT IN (SELECT i1, i2 FROM t2); - ---echo ---echo # Check that the subquery only has to be evaluated once ---echo # for all-NULL values even though there are two (NULL,NULL) records ---echo # Baseline: -SHOW STATUS LIKE '%Handler_read_rnd_next'; - ---echo -INSERT INTO t1 VALUES (NULL, NULL); -FLUSH STATUS; - ---echo -SELECT i1, i2 -FROM t1 -WHERE (i1, i2) - NOT IN (SELECT i1, i2 FROM t2); - ---echo ---echo # Handler_read_rnd_next should be one more than baseline ---echo # (read record from t1, but do not read from t2) -SHOW STATUS LIKE '%Handler_read_rnd_next'; - -set @@optimizer_switch=@save_optimizer_switch2; -DROP TABLE t1,t2; - ---echo End of 5.1 tests - -# -# Test for the problem with using sj-materialization when subquery's select -# list element SCOL is covered by equality propagation and has preceding equal -# column PCOL which belongs to a table within the the semi-join nest: SJM-Scan -# process should unpack column value not to SCOL but rather to PCOL, as -# substitute_best_equal has made all conditions to refer to PCOL. -# -CREATE TABLE t1 ( - a int(11) NOT NULL, - b int(11) NOT NULL, - c datetime default NULL, - PRIMARY KEY (a), - KEY idx_bc (b,c) -); - -INSERT INTO t1 VALUES -(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'), -(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'), -(406994,67,'2006-02-27 11:26:46'), (256,67,NULL), -(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL), -(406988,67,'2006-02-23 17:07:22'), (255,67,NULL), -(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'), -(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'), -(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'), -(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'), -(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'), -(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'), -(223456,67,NULL),(4101,67,NULL),(1133,67,NULL), -(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'), -(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'), -(154503,67,'2005-10-28 11:52:38'); - -create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc; -create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc; -create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc; -create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc; - -set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch='materialization=off'; - -update t22 set c = '2005-12-08 15:58:27' where a = 255; -explain select t21.* from t21,t22 where t21.a = t22.a and -t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; -select t21.* from t21,t22 where t21.a = t22.a and -t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; - -set @@optimizer_switch=@save_optimizer_switch; - -drop table t1, t11, t12, t21, t22; - -# -# Test sj-materialization re-execution. The test isn't meaningful (materialized -# table stays the same across all executions) because it's hard to create a -# dataset that would verify correct re-execution without hitting BUG#31480 -# -create table t1(a int); -insert into t1 values (0),(1); - -set @@optimizer_switch='firstmatch=off,materialization=off'; -explain -select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; -select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; -set @@optimizer_switch=@save_optimizer_switch; - -drop table t1; - -# -# Test confluent duplicate weedout -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 as select * from t0; -insert into t1 select a+10 from t0; -set @@optimizer_switch='firstmatch=off,materialization=off'; -insert into t0 values(2); -explain select * from t1 where 2 in (select a from t0); -select * from t1 where 2 in (select a from t0); -set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch='materialization=off'; -explain select * from t1 where 2 in (select a from t0); -select * from t1 where 2 in (select a from t0); -set @@optimizer_switch=@save_optimizer_switch; - - -set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch='materialization=off'; - -# -# FirstMatch referring to a derived table -# -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='derived_merge=off,derived_with_keys=off'; -explain select * from (select a from t0) X where a in (select a from t1); -drop table t0, t1; -set optimizer_switch=@tmp_optimizer_switch; - -# -# LooseScan: Check if we can pick it together with range access -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - -create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); -insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; -insert into t1 select * from t1 where kp1 < 20; - -create table t3 (a int); -insert into t3 select A.a + 10*B.a from t0 A, t0 B; - -explain select * from t3 where a in (select kp1 from t1 where kp1<20); - -create table t4 (pk int primary key); -insert into t4 select a from t3; - -explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 -and t4.pk=t1.c); - -drop table t1, t3, t4; - -set @@optimizer_switch=@save_optimizer_switch; - -# -# Test if we handle duplicate elimination temptable overflowing to disk -# -create table t1 (a int) as select * from t0 where a < 5; - -set @save_max_heap_table_size=@@max_heap_table_size; -set @@optimizer_switch='firstmatch=off,materialization=off'; -set @@max_heap_table_size= 16384; - -explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); -flush status; -select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); -show status like 'Created_tmp_disk_tables'; -set @save_max_heap_table_size=@@max_heap_table_size; -set @@optimizer_switch=@save_optimizer_switch; -drop table t0, t1; - -# -# Materialize + Scan + ref access to the subsequent table based on scanned -# value -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2(a int); -insert into t2 values (1),(2); -create table t3 ( a int , filler char(100), key(a)); -insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; -explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); -select * from t3 where a in (select a from t2); - -drop table t0, t2, t3; - -# -# DATETIME type checks -# -set @@optimizer_switch='firstmatch=off,materialization=off'; -create table t1 (a date); -insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01'); -create table t2 (a int); -insert into t2 values (1),(2); -create table t3 (a char(10)); -insert into t3 select * from t1; -insert into t3 values (1),(2); -explain select * from t2 where a in (select a from t1); -explain select * from t2 where a in (select a from t2); -explain select * from t2 where a in (select a from t3); -explain select * from t1 where a in (select a from t3); -drop table t1, t2, t3; -create table t1 (a decimal); -insert into t1 values (1),(2); -explain select * from t1 where a in (select a from t1); -drop table t1; -set @@optimizer_switch=@save_optimizer_switch; - -set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch='materialization=off'; - -# -# SJ-Materialization-scan for non-first table -# -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 as select * from t1; -create table t3 (a int, b int, filler char(100), key(a)); -insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C; -explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; - -# -# Verify that straight_join modifier in parent or child prevents flattening -# -explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); -explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); -explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); -explain select straight_join * from t2 X, t2 Y -where X.a in (select straight_join A.a from t1 A, t1 B); - -# -# SJ-Materialization scan + first table being system const table -# -create table t0 (a int, b int); -insert into t0 values(1,1); -explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); -create table t4 as select a as x, a as y from t1; -explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); -drop table t0,t1,t2,t3,t4; - -# -# LooseScan with ref access -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, b int, filler char(100), key(a,b)); -insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; -create table t2 as select * from t1; - -explain select * from t2 where a in (select b from t1 where a=3); -explain select * from t2 where (b,a) in (select a,b from t1 where a=3); - -drop table t1,t2; - -set @@optimizer_switch=@save_optimizer_switch; - -# -# Multi-column sj-materialization with lookups -# -create table t1 (a int, b int); -insert into t1 select a,a from t0; -create table t2 (a int, b int); -insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; - -set @@optimizer_switch='firstmatch=off,materialization=off'; -explain select * from t1 where (a,b) in (select a,b from t2); - -# A smallish test if find_best() still works for semi-join optimization: -set @save_optimizer_search_depth=@@optimizer_search_depth; -set @@optimizer_search_depth=63; -explain select * from t1 where (a,b) in (select a,b from t2); -set @@optimizer_search_depth=@save_optimizer_search_depth; -set @@optimizer_switch=@save_optimizer_switch; - -drop table t0, t1, t2; - -set @@optimizer_switch='materialization=off'; - -# -# Primitive SJ-Materialization tests for DECIMAL and DATE -# -create table t0 (a decimal(4,2)); -insert into t0 values (10.24), (22.11); -create table t1 as select * from t0; -insert into t1 select * from t0; -explain select * from t0 where a in (select a from t1); -select * from t0 where a in (select a from t1); -drop table t0, t1; - -create table t0(a date); -insert into t0 values ('2008-01-01'),('2008-02-02'); -create table t1 as select * from t0; -insert into t1 select * from t0; -explain select * from t0 where a in (select a from t1); -select * from t0 where a in (select a from t1); -drop table t0, t1; - -# -# Fix a trivial crash with SJ-Materialization lookup, multiple tables in the -# subquery, and a condition on some of inner tables but not others -# -create table t0(a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 as select a as a, a as b, a as c from t0 where a < 3; -create table t2 as select a as a, a as b from t0 where a < 3; -insert into t2 select * from t2; - -explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); - -drop table t0,t1,t2; - -set @@optimizer_switch=@save_optimizer_switch; - ---echo ---echo BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 ---echo -CREATE TABLE t1 ( - `pk` int(11) NOT NULL AUTO_INCREMENT, - `int_key` int(11) DEFAULT NULL, - PRIMARY KEY (`pk`), - KEY `int_key` (`int_key`) -) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1,9),(2,3),(3,8),(4,6),(5,9),(6,5),(7,5),(8,9),(9,1),(10,10); -SELECT `pk` FROM t1 AS OUTR WHERE `int_key` = ALL ( - SELECT `int_key` FROM t1 AS INNR WHERE INNR . `pk` >= 9 -); -DROP TABLE t1; - ---echo ---echo BUG#40118 Crash when running Batched Key Access and requiring one match for each key ---echo -create table t0(a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, key(a)); -insert into t1 select * from t0; -alter table t1 add b int not null, add filler char(200); -insert into t1 select * from t1; -insert into t1 select * from t1; - -set @save_join_cache_level=@@join_cache_level; -set join_cache_level=6; -select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); -set join_cache_level=@save_join_cache_level; -drop table t0, t1; - ---echo # ---echo # BUG#32665 Query with dependent subquery is too slow ---echo # -create table t1 ( - idIndividual int primary key -); -insert into t1 values (1),(2); - -create table t2 ( - idContact int primary key, - contactType int, - idObj int -); -insert into t2 values (1,1,1),(2,2,2),(3,3,3); - -create table t3 ( - idAddress int primary key, - idContact int, - postalStripped varchar(100) -); - -insert into t3 values (1,1, 'foo'), (2,2,'bar'); - ---echo The following must be converted to a semi-join: -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='materialization=off'; -explain extended SELECT a.idIndividual FROM t1 a -WHERE a.idIndividual IN - ( SELECT c.idObj FROM t3 cona - INNER JOIN t2 c ON c.idContact=cona.idContact - WHERE cona.postalStripped='T2H3B2' - ); -set @@optimizer_switch=@save_optimizer_switch; -drop table t1,t2,t3; - ---echo # ---echo # BUG#47367 Crash in Name_resolution_context::process_error ---echo # - -SET SESSION optimizer_switch = 'semijoin=off'; -CREATE TABLE t1 (f1 INTEGER); -CREATE TABLE t2 LIKE t1; -delimiter |; -CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| -delimiter ;| -CALL p1; -ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; -CALL p1; -DROP PROCEDURE p1; ---echo # Restore the original column list of table t2: -ALTER TABLE t2 CHANGE COLUMN my_column f1 INT; - -SET SESSION optimizer_switch = 'semijoin=on'; -delimiter |; ---echo # Recreate procedure so that we eliminate any caching effects -CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| -delimiter ;| -CALL p1; -ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; ---error ER_BAD_FIELD_ERROR -CALL p1; -DROP PROCEDURE p1; -DROP TABLE t1, t2; - ---echo # ---echo # fix of lp:824425 (prohibiting subqueries in row in ---echo # left part of IN/ALL/ANY) ---echo # - -CREATE TABLE t1 ( a int) ; -INSERT INTO t1 VALUES (20),(30); - -CREATE TABLE t2 (a int) ; -INSERT INTO t2 VALUES (3),(9); - -CREATE TABLE t3 ( a int, b int) ; -INSERT INTO t3 VALUES (20,5),(30,6); - -set @optimizer_switch_save=@@optimizer_switch; -SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; - -SELECT * FROM t1 -WHERE ( - ( SELECT a FROM t2 WHERE a = 9 ) -) NOT IN ( - SELECT b - FROM t3 -); -explain extended -SELECT * FROM t1 -WHERE ( - ( SELECT a FROM t2 WHERE a = 9 ) -) NOT IN ( - SELECT b - FROM t3 -); - ---error ER_NOT_SUPPORTED_YET -SELECT * FROM t1 -WHERE ( - ( SELECT a FROM t2 WHERE a = 9 ), - ( SELECT a FROM t2 WHERE a = 3 ) -) NOT IN ( - SELECT b , a - FROM t3 -); -set optimizer_switch=@optimizer_switch_save; - -drop table t1,t2,t3; - ---echo End of 5.3 tests - ---echo # ---echo # BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery ---echo # in the select list ---echo # - ---echo -CREATE TABLE t1 ( - i int(11) DEFAULT NULL, - v varchar(1) DEFAULT NULL -); - ---echo -INSERT INTO t1 VALUES (8,'v'); -INSERT INTO t1 VALUES (9,'r'); -INSERT INTO t1 VALUES (NULL,'y'); - ---echo -CREATE TABLE t2 ( - i int(11) DEFAULT NULL, - v varchar(1) DEFAULT NULL, - KEY i_key (i) -); - ---echo -INSERT INTO t2 VALUES (NULL,'r'); -INSERT INTO t2 VALUES (0,'c'); -INSERT INTO t2 VALUES (0,'o'); -INSERT INTO t2 VALUES (2,'v'); -INSERT INTO t2 VALUES (7,'c'); - ---echo -SELECT i, v, (SELECT COUNT(DISTINCT i) - FROM t1 - WHERE v = t2.v) as subsel -FROM t2; - ---echo -EXPLAIN EXTENDED -SELECT i, v, (SELECT COUNT(DISTINCT i) - FROM t1 - WHERE v = t2.v) as subsel -FROM t2; - -DROP TABLE t1,t2; - ---echo End of 5.6 tests - -# The following command must be the last one in the file -set @@optimizer_switch=@subselect3_tmp; +--echo # end of 10.2 test |