diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 288 |
1 files changed, 248 insertions, 40 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3d7050ebc25..cb43eb9f646 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -15,7 +15,16 @@ drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + +if (`select @join_cache_level_for_subselect_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_test; +} + set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); explain extended select (select 2); @@ -94,8 +103,11 @@ explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); @@ -3235,6 +3247,10 @@ DROP TABLE t1, t2, t3; # Bug#30788 Inconsistent retrieval of char/varchar # +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; + CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); INSERT INTO t1 VALUES ('a', 'aa'); INSERT INTO t1 VALUES ('a', 'aaa'); @@ -3256,6 +3272,7 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); DROP TABLE t1,t2; +SET optimizer_switch= @save_optimizer_switch; # # Bug#32400 Complex SELECT query returns correct result only on some occasions @@ -3404,8 +3421,11 @@ drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); +SET join_cache_level=@save_join_cache_level; DROP TABLE t1; --echo # @@ -3486,39 +3506,39 @@ ORDER BY outr.pk; DROP TABLE t1,t2; -#--echo # -#--echo # Bug#12329653 -#--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY -#--echo # -# -#CREATE TABLE t1(a1 int); -#INSERT INTO t1 VALUES (1),(2); -# -#SELECT @@session.sql_mode INTO @old_sql_mode; -#SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; -# -### First a simpler query, illustrating the transformation -### '1 < some (...)' => '1 < max(...)' -#SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); -# -### The query which made the server crash. -#PREPARE stmt FROM -#'SELECT 1 UNION ALL -#SELECT 1 FROM t1 -#ORDER BY -#(SELECT 1 FROM t1 AS t1_0 -# WHERE 1 < SOME (SELECT a1 FROM t1) -#)' ; -# -#--error ER_SUBQUERY_NO_1_ROW -#EXECUTE stmt ; -#--error ER_SUBQUERY_NO_1_ROW -#EXECUTE stmt ; -# -#SET SESSION sql_mode=@old_sql_mode; -# -#DEALLOCATE PREPARE stmt; -#DROP TABLE t1; +--echo # +--echo # Bug#12329653 +--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## First a simpler query, illustrating the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); + +## The query which made the server crash. +PREPARE stmt FROM +'SELECT 1 UNION ALL +SELECT 1 FROM t1 +ORDER BY +(SELECT 1 FROM t1 AS t1_0 + WHERE 1 < SOME (SELECT a1 FROM t1) +)' ; + +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; + +SET SESSION sql_mode=@old_sql_mode; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; --echo End of 5.0 tests. @@ -4729,8 +4749,12 @@ INSERT INTO t2 VALUES (11,1); INSERT INTO t2 VALUES (12,2); INSERT INTO t2 VALUES (15,4); + +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); +SET join_cache_level=@save_join_cache_level; DROP table t1,t2; @@ -4741,7 +4765,8 @@ DROP table t1,t2; CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); - +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) @@ -4750,7 +4775,7 @@ SELECT b FROM t1 SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; - +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; --echo # @@ -4895,6 +4920,9 @@ INSERT INTO t2 VALUES (20,9),(20,9); create table t3 (d int, e int); insert into t3 values (2, 9), (3,10); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + EXPLAIN SELECT t2.b , t1.c FROM t2 LEFT JOIN t1 ON t1.c < 3 @@ -4904,12 +4932,12 @@ SELECT t2.b , t1.c FROM t2 LEFT JOIN t1 ON t1.c < 3 WHERE (t2.b, t1.c) NOT IN (SELECT * from t3); +SET optimizer_switch=@save_optimizer_switch; + drop table t1, t2, t3; --echo End of 5.3 tests ---echo End of 5.5 tests. - --echo # --echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER --echo # @@ -4943,7 +4971,10 @@ CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); --echo +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; +set optimizer_switch=@tmp_optimizer_switch; --echo EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); @@ -5071,6 +5102,183 @@ create table t1 (a int not null, b char(10) not null); insert into t1 values (1, 'a'); set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); +set @@optimizer_switch=@subselect_tmp; +drop table t1; + +--echo # +--echo # LP BUG#894397 Wrong result with in_to_exists, constant table , semijoin=OFF,materialization=OFF +--echo # + +CREATE TABLE t1 (a varchar(3)); +INSERT INTO t1 VALUES ('AAA'),('BBB'); +CREATE TABLE t2 (a varchar(3)); +INSERT INTO t2 VALUES ('CCC'); +set @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquery_cache=off'; +SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.a < 'ZZZ'); +set @@optimizer_switch=@subselect_tmp; +drop table t1, t2; + +--echo # +--echo # LP bug #859375: Assertion `0' failed in st_select_lex_unit::optimize +--echo # with view , UNION and prepared statement (rewriting fake_select +--echo # condition). +--echo # + +CREATE TABLE t1 ( f1 int NOT NULL, f4 varchar(1) NOT NULL) ; +INSERT INTO t1 VALUES (6,'d'),(7,'y'); + +CREATE TABLE t2 ( f1 int NOT NULL, f2 int NOT NULL) ; +INSERT INTO t2 VALUES (10,7); + +CREATE VIEW v2 AS SELECT * FROM t2; + +PREPARE st1 FROM " + SELECT * + FROM t1 + LEFT JOIN v2 ON ( v2.f2 = t1.f1 ) + WHERE v2.f1 NOT IN ( + SELECT 1 UNION + SELECT 247 + ) +"; + +EXECUTE st1; +deallocate prepare st1; + +DROP VIEW v2; +DROP TABLE t1,t2; + +--echo # +--echo # LP bug #887458 Crash in subselect_union_engine::no_rows with +--echo # double UNION and join_cache_level=3,8 +--echo # (IN/ALL/ANY optimizations should not be applied to fake_select) + +CREATE TABLE t2 ( a int, b varchar(1)) ; +INSERT IGNORE INTO t2 VALUES (8,'y'),(8,'y'); + +CREATE TABLE t1 ( b varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (NULL),(NULL); + +set @save_join_cache_level=@@join_cache_level; +SET SESSION join_cache_level=3; + +SELECT * +FROM t1, t2 +WHERE t2.b IN ( + SELECT 'm' UNION + SELECT 'm' +) OR t1.b <> SOME ( + SELECT 'v' UNION + SELECT 't' +); + +set @@join_cache_level= @save_join_cache_level; +drop table t1,t2; + + +--echo # +--echo # LP bug #885162 Got error 124 from storage engine with UNION inside +--echo # subquery and join_cache_level=3..8 +--echo # (IN/ALL/ANY optimizations should not be applied to fake_select) +--echo # + +CREATE TABLE t1 ( + f1 varchar(1) DEFAULT NULL + ); +INSERT INTO t1 VALUES ('c'); +set @save_join_cache_level=@@join_cache_level; +SET SESSION join_cache_level=8; +SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' ); +set @@join_cache_level= @save_join_cache_level; drop table t1; + +--echo # +--echo # LP BUG#747278 incorrect values of the NULL (no rows) single +--echo # row subquery requested via element_index() interface +--echo # + +CREATE TABLE t1 (f1a int, f1b int) ; +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 ( f2 int); +INSERT IGNORE INTO t2 VALUES (3),(4); +CREATE TABLE t3 (f3a int default 1, f3b int default 2); +INSERT INTO t3 VALUES (1,1),(2,2); + +# check different IN with switches where the bug was found +set @old_optimizer_switch = @@session.optimizer_switch; +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; + +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); + +set @@session.optimizer_switch=@old_optimizer_switch; + +# check different IN with default switches +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); + +# other row operation with NULL single row subquery also should work +select (null, null) = (null, null); +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); + +drop tables t1,t2,t3; + +--echo # +--echo # LP BUG#825051 Wrong result with date/datetime and subquery with GROUP BY and in_to_exists +--echo # + +CREATE TABLE t1 (a date, KEY (a)) ; +INSERT INTO t1 VALUES ('2009-01-01'),('2009-02-02'); +set @old_optimizer_switch = @@optimizer_switch; +SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +set @@optimizer_switch=@old_optimizer_switch; +drop table t1; + +--echo # +--echo # LP BUG#908269 incorrect condition in case of subqueries depending +--echo # on constant tables +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); + +# t2 must be MyISAM or Aria and contain 1 row +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); + +SET optimizer_switch='subquery_cache=off'; + +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +# This query just for example, it should return the same as above (1 and NULL) +SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1; + +# example with "random" +SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; + + +drop table t1,t2,t3; + + +--echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; |