diff options
Diffstat (limited to 'mysql-test/t')
33 files changed, 332 insertions, 10 deletions
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 2c9f84e823a..49e535aad53 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -15,6 +15,12 @@ -- source include/have_innodb.inc +set @innodb_test_tmp=@@optimizer_switch; +set optimizer_switch = + if(@innodb_test_dont_touch_optimizer_switch, + @@optimizer_switch, + 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'); + # Save the original values of some variables in order to be able to # estimate how much they have changed during the tests. Previously this # test assumed that e.g. rows_deleted is 0 here and after deleting 23 @@ -2549,6 +2555,8 @@ SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig; -- enable_query_log +set optimizer_switch=@innodb_test_tmp; + ####################################################################### # # # Please, DO NOT TOUCH this file as well as the innodb.result file. # diff --git a/mysql-test/t/innodb_icp.test b/mysql-test/t/innodb_icp.test index 3a4ecd3a7de..0fb42355f96 100644 --- a/mysql-test/t/innodb_icp.test +++ b/mysql-test/t/innodb_icp.test @@ -7,7 +7,11 @@ set @save_storage_engine= @@storage_engine; set storage_engine=InnoDB; +set @innodb_icp_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + --source include/icp_tests.inc +set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; diff --git a/mysql-test/t/innodb_mrr.test b/mysql-test/t/innodb_mrr.test index 2ae8744bca0..7afa18b43ef 100644 --- a/mysql-test/t/innodb_mrr.test +++ b/mysql-test/t/innodb_mrr.test @@ -7,6 +7,9 @@ drop table if exists t1,t2,t3,t4; set @save_storage_engine= @@storage_engine; set storage_engine=InnoDB; +set @innodb_mrr_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + --source include/mrr_tests.inc set storage_engine= @save_storage_engine; @@ -454,4 +457,5 @@ select count(*) from ( ) X; set join_cache_level=@_save_join_cache_level; +set optimizer_switch= @innodb_mrr_tmp; drop table t1; diff --git a/mysql-test/t/innodb_mrr_cpk.test b/mysql-test/t/innodb_mrr_cpk.test index 69eeef9618f..a157ddd792f 100644 --- a/mysql-test/t/innodb_mrr_cpk.test +++ b/mysql-test/t/innodb_mrr_cpk.test @@ -17,6 +17,9 @@ drop table if exists t0,t1,t2,t3; --enable_warnings +set @innodb_mrr_cpk_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + set @save_join_cache_level=@@join_cache_level; set join_cache_level=6; @@ -133,5 +136,6 @@ drop table t1,t2; set @@join_cache_level= @save_join_cache_level; set storage_engine=@save_storage_engine; +set optimizer_switch=@innodb_mrr_cpk_tmp; drop table t0; diff --git a/mysql-test/t/innodb_no_mrricp.test b/mysql-test/t/innodb_no_mrricp.test new file mode 100644 index 00000000000..df0a3e44de5 --- /dev/null +++ b/mysql-test/t/innodb_no_mrricp.test @@ -0,0 +1,10 @@ + +set @innodb_with_mrricp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @innodb_test_dont_touch_optimizer_switch=1; + +--source t/innodb.test + +set @innodb_test_dont_touch_optimizer_switch=NULL; +set optimizer_switch=@innodb_with_mrricp; + diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index aac55dcb27b..9ccba27e4ee 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -7,7 +7,9 @@ set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; set names utf8; CREATE DATABASE world; @@ -2981,7 +2983,7 @@ SELECT * FROM t1,t2 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); SET SESSION join_cache_level = DEFAULT; -SET SESSION optimizer_switch = DEFAULT; +SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; DROP TABLE t1,t2; @@ -3007,7 +3009,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; -SET SESSION optimizer_switch = DEFAULT; +SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; SET SESSION optimizer_switch = 'index_condition_pushdown=on'; EXPLAIN SELECT * FROM t1,t2 @@ -3015,7 +3017,7 @@ EXPLAIN SELECT * FROM t1,t2 SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; -SET SESSION optimizer_switch = DEFAULT; +SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; DROP TABLE t1,t2; @@ -3134,7 +3136,5 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1, t2; -SET SESSION optimizer_switch=default; - # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test index 5737cfe115f..3c6476e1205 100644 --- a/mysql-test/t/join_nested_jcl6.test +++ b/mysql-test/t/join_nested_jcl6.test @@ -5,6 +5,7 @@ set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test index be98e7503ad..0b7399ff92b 100644 --- a/mysql-test/t/join_outer_jcl6.test +++ b/mysql-test/t/join_outer_jcl6.test @@ -5,6 +5,7 @@ set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/maria_icp.test b/mysql-test/t/maria_icp.test index beb13544ced..d8af34daf2e 100644 --- a/mysql-test/t/maria_icp.test +++ b/mysql-test/t/maria_icp.test @@ -6,8 +6,12 @@ set @save_storage_engine= @@storage_engine; set storage_engine=Maria; +set @maria_icp_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source include/icp_tests.inc set storage_engine= @save_storage_engine; +set optimizer_switch=@maria_icp_tmp; + diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test index 233186fe5bd..939deac6bb7 100644 --- a/mysql-test/t/maria_mrr.test +++ b/mysql-test/t/maria_mrr.test @@ -7,6 +7,9 @@ drop table if exists t1,t2,t3,t4; --enable_warnings +set @maria_mrr_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + set @mrr_buffer_size_save= @@mrr_buffer_size; set @save_storage_engine= @@storage_engine; @@ -201,4 +204,6 @@ WHERE set join_cache_level=@_save_join_cache_level; set join_buffer_size=@_save_join_buffer_size; +set optimizer_switch=@maria_mrr_tmp; + drop table t1; diff --git a/mysql-test/t/mrr_icp_extra.test b/mysql-test/t/mrr_icp_extra.test new file mode 100644 index 00000000000..2f137845cfc --- /dev/null +++ b/mysql-test/t/mrr_icp_extra.test @@ -0,0 +1,242 @@ + +set @mrr_icp_extra_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET NAMES latin1; +CREATE TABLE t1 +(s1 char(10) COLLATE latin1_german1_ci, + s2 char(10) COLLATE latin1_swedish_ci, + KEY(s1), + KEY(s2)); + +INSERT INTO t1 VALUES ('a','a'); +INSERT INTO t1 VALUES ('b','b'); +INSERT INTO t1 VALUES ('c','c'); +INSERT INTO t1 VALUES ('d','d'); +INSERT INTO t1 VALUES ('e','e'); +INSERT INTO t1 VALUES ('f','f'); +INSERT INTO t1 VALUES ('g','g'); +INSERT INTO t1 VALUES ('h','h'); +INSERT INTO t1 VALUES ('i','i'); +INSERT INTO t1 VALUES ('j','j'); + +EXPLAIN SELECT * FROM t1 WHERE s1='a'; +EXPLAIN SELECT * FROM t1 WHERE s2='a'; +EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; + +EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; + +EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); +EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); + +EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; + +DROP TABLE t1; + +--echo # +--echo # + +CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, +UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); +CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); +CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); +INSERT INTO t3 SELECT * FROM t1; +EXPLAIN +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # +create table t1(a int, b int, index(b)); +insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); +explain select * from t1 where b=1 or b is null order by a; +select * from t1 where b=1 or b is null order by a; +explain select * from t1 where b=2 or b is null order by a; +select * from t1 where b=2 or b is null order by a; +drop table t1; + +--echo # +--echo # +CREATE TABLE t1 ( +FieldKey varchar(36) NOT NULL default '', +LongVal bigint(20) default NULL, +StringVal mediumtext, +KEY FieldKey (FieldKey), +KEY LongField (FieldKey,LongVal), +KEY StringField (FieldKey,StringVal(32)) +); +INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); +EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal; +EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; +SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; +DROP TABLE t1; +--echo # +--echo # +CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); +INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4); +create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); +INSERT into t2 values (1,1,1), (2,2,2); +optimize table t1; +explain select * from t1 force index (a) where a=0 or a=2; +select * from t1 force index (a) where a=0 or a=2; +drop table t1; +--echo # +--echo # +create table t1 +( + pk1 int not null, + pk2 int not null, + + key1 int not null, + key2 int not null, + + pktail1ok int not null, + pktail2ok int not null, + pktail3bad int not null, + pktail4bad int not null, + pktail5bad int not null, + + pk2copy int not null, + badkey int not null, + + filler1 char (200), + filler2 char (200), + key (key1), + key (key2), + + /* keys with tails from CPK members */ + key (pktail1ok, pk1), + key (pktail2ok, pk1, pk2), + key (pktail3bad, pk2, pk1), + key (pktail4bad, pk1, pk2copy), + key (pktail5bad, pk1, pk2, pk2copy), + + primary key (pk1, pk2) +); + +--disable_query_log +set autocommit=0; +let $1=10000; +while ($1) +{ + eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2'); + dec $1; +} +set autocommit=1; +--enable_query_log +explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; +select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; +drop table t1; + +--echo # +--echo # +CREATE TABLE t1 ( +f1 int, +f4 varchar(32), +f5 int, +PRIMARY KEY (f1), +KEY (f4) +); +INSERT INTO t1 VALUES +(5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6), +(530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1), +(535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2), +(540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0), +(956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0), +(961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL), +(966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0), +(971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0), +(976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7), +(981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1), +(986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7), +(991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4), +(996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2); +EXPLAIN +SELECT * FROM t1 +WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; +SELECT * FROM t1 +WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; +drop table t1; + +--echo # +--echo # +--source include/varchar.inc + +--echo # +--echo # +--disable_warnings +drop database if exists world; +--enable_warnings +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +explain +SELECT * FROM City +WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); + +SELECT * FROM City +WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); + +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) +AND (Population > 101000 AND Population < 102000); + +explain +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) +AND (Population > 101000 AND Population < 102000); + +explain +SELECT * FROM City +WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); + +SELECT * FROM City +WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); + +SELECT * FROM City +WHERE Name LIKE 'M%' AND Population > 7000000; + +explain +SELECT * FROM City +WHERE Name LIKE 'M%' AND Population > 7000000; + +explain +SELECT * FROM City +WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; +SELECT * FROM City +WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; + +explain +SELECT * FROM City +WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; +SELECT * FROM City +WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; + +drop database world; +use test; + +set @mrr_icp_extra_tmp=@@optimizer_switch; + diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 30a8b208230..7cc35a25e91 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -4,6 +4,9 @@ --source include/icp_tests.inc +set @myisam_icp_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + --disable_warnings drop table if exists t0, t1, t1i, t1m; --enable_warnings @@ -192,3 +195,4 @@ drop table if exists t0, t1, t1i, t1m; drop table t0, t1, t1i, t1m; --enable_parsing +set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test index 82457ee4366..9c4c7be4fa2 100644 --- a/mysql-test/t/myisam_mrr.test +++ b/mysql-test/t/myisam_mrr.test @@ -6,6 +6,8 @@ drop table if exists t0, t1, t2, t3; --enable_warnings +set @myisam_mrr_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @mrr_buffer_size_save= @@mrr_buffer_size; set mrr_buffer_size=79; @@ -216,5 +218,6 @@ where set join_cache_level= @save_join_cache_level; set join_buffer_size= @save_join_buffer_size; +set optimizer_switch= @myisam_mrr_tmp; drop table t1; diff --git a/mysql-test/t/range_mrr_icp.test b/mysql-test/t/range_mrr_icp.test new file mode 100644 index 00000000000..724da8aea3b --- /dev/null +++ b/mysql-test/t/range_mrr_icp.test @@ -0,0 +1,7 @@ +set @mrr_icp_extra_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + +--source t/range.test + +set optimizer_switch=@mrr_icp_extra_tmp; + diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 3cebb8fffd0..470572fa7c7 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4146,7 +4146,8 @@ INSERT INTO t3 VALUES (4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'), (9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'), (4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz'); - + +set @tmp= @@optimizer_switch; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1; @@ -4166,7 +4167,7 @@ SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; -SET SESSION optimizer_switch=DEFAULT; +SET SESSION optimizer_switch=@tmp; DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test index 9f9a3a40e0f..ef461b89e1c 100644 --- a/mysql-test/t/select_jcl6.test +++ b/mysql-test/t/select_jcl6.test @@ -5,6 +5,7 @@ set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d6aa73e8010..c06eee73b79 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -15,6 +15,7 @@ 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,partial_match_rowid_merge=off,partial_match_table_scan=off"); +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); explain extended select (select 2); diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index b6b1388be1c..5f819ed39ba 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -9,7 +9,7 @@ drop table if exists t1, t2, t3, t4; --enable_warnings set @subselect2_test_tmp=@@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on'; +set optimizer_switch='semijoin=on,firstmatch=on,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; CREATE TABLE t1 ( diff --git a/mysql-test/t/subselect3_jcl6.test b/mysql-test/t/subselect3_jcl6.test index 6d9611f83f3..5867886df6a 100644 --- a/mysql-test/t/subselect3_jcl6.test +++ b/mysql-test/t/subselect3_jcl6.test @@ -5,6 +5,7 @@ set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 1cc3a7314a3..a745d74e56c 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -6,6 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6; set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --echo # --echo # Bug #46791: Assertion failed:(table->key_read==0),function unknown diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 573fe0c1810..da7524c1427 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -1,5 +1,7 @@ -- source include/have_innodb.inc +set @subselect_innodb_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --disable_warnings drop table if exists t1,t2,t3; --enable_warnings @@ -238,3 +240,6 @@ call p1(); call p1(); drop procedure p1; drop tables t1,t2,t3; + +set optimizer_switch=@subselect_innodb_tmp; + diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test index 172ef651407..bfdb2554de1 100644 --- a/mysql-test/t/subselect_mat.test +++ b/mysql-test/t/subselect_mat.test @@ -11,6 +11,7 @@ set @subselect_mat_test_optimizer_switch_value='materialization=on,in_to_exists= set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # # Test that the contents of the temp table of a materialized subquery is # cleaned up between PS re-executions. diff --git a/mysql-test/t/subselect_mat_cost.test b/mysql-test/t/subselect_mat_cost.test index 45133f32513..8a0d1ac702d 100644 --- a/mysql-test/t/subselect_mat_cost.test +++ b/mysql-test/t/subselect_mat_cost.test @@ -13,6 +13,9 @@ # switches, data distribution, available indexes, and typical queries. # +set @subselect_mat_cost=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + -- echo TEST GROUP 1: -- echo Typical cases of in-to-exists and materialization subquery strategies @@ -415,3 +418,5 @@ drop database world; #(IN_EXIST transformation is applied to it) # inner subqyery is materialized while the outer subquery is not ( #IN_EXIST transformation is applied to it) + +set optimizer_switch=@subselect_mat_cost; diff --git a/mysql-test/t/subselect_no_mat.test b/mysql-test/t/subselect_no_mat.test index 5fbbef5caed..0265ec91e88 100644 --- a/mysql-test/t/subselect_no_mat.test +++ b/mysql-test/t/subselect_no_mat.test @@ -3,6 +3,7 @@ # select @@optimizer_switch like '%materialization=on%'; set optimizer_switch='materialization=off'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source t/subselect.test diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test index a29fa5a27ac..005b2f041fa 100644 --- a/mysql-test/t/subselect_no_opts.test +++ b/mysql-test/t/subselect_no_opts.test @@ -2,7 +2,8 @@ # Run subselect.test without semi-join and materialization optimizations # (test in-to-exists) -set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off'; + +set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source t/subselect.test set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test index b8bcb6c4600..c836c12ec50 100644 --- a/mysql-test/t/subselect_no_semijoin.test +++ b/mysql-test/t/subselect_no_semijoin.test @@ -1,7 +1,7 @@ # # Run subselect.test without semi-join optimization (test materialize) # -set @optimizer_switch_for_subselect_test='semijoin=off'; +set @optimizer_switch_for_subselect_test='semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source t/subselect.test diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index e454d3e49b9..2a0197a3047 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -9,6 +9,7 @@ drop procedure if exists p1; set @subselect_sj_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # The 'default' value within the scope of this test: set @save_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 7caf5da7b8b..285146481f4 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -5,6 +5,7 @@ set @subselect_sj2_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --disable_warnings drop table if exists t0, t1, t2, t3; drop view if exists v1; diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 5292a56f266..529c906b917 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -5,6 +5,7 @@ set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index 643a287a897..fdfa0f311d3 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -2,6 +2,7 @@ # Run subselect_sj2.test with subquery materialization. # set optimizer_switch='materialization=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source t/subselect_sj2.test diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index fb5b7ab5655..f7d94409ab5 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -7,6 +7,7 @@ set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; set @@optimizer_switch='join_cache_hashed=off'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index db798e27037..dcabc166a3b 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -5,6 +5,7 @@ set @subselect_sj_mat_tmp= @@optimizer_switch; set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; --disable_warnings diff --git a/mysql-test/t/subselect_sj_nonmerged.test b/mysql-test/t/subselect_sj_nonmerged.test index f60871c9dd9..4f50b4cbc4d 100644 --- a/mysql-test/t/subselect_sj_nonmerged.test +++ b/mysql-test/t/subselect_sj_nonmerged.test @@ -7,6 +7,7 @@ drop table if exists t0, t1, t2, t3, t4; set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on,materialization=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |