diff options
18 files changed, 91 insertions, 45 deletions
diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc index 3912aa34026..df42745b4fc 100644 --- a/mysql-test/include/index_merge_ror_cpk.inc +++ b/mysql-test/include/index_merge_ror_cpk.inc @@ -79,8 +79,11 @@ select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; # Verify that CPK is always used for index intersection scans # (this is because it is used as a filter, not for retrieval) explain select * from t1 where badkey=1 and key1=10; +set @tmp_index_merge_ror_cpk=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; --replace_column 9 ROWS explain select * from t1 where pk1 < 7500 and key1 = 10; +set optimizer_switch=@tmp_index_merge_ror_cpk; # Verify that keys with 'tails' of PK members are ok. explain select * from t1 where pktail1ok=1 and key1=10; diff --git a/mysql-test/r/index_intersect_innodb.result b/mysql-test/r/index_intersect_innodb.result index d28b3a0bf92..33f2247e5d1 100644 --- a/mysql-test/r/index_intersect_innodb.result +++ b/mysql-test/r/index_intersect_innodb.result @@ -463,29 +463,29 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,3 NULL # Using sort_intersect(PRIMARY,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,7 NULL # Using sort_intersect(PRIMARY,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City USE INDEX () WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population @@ -739,13 +739,13 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -1033,7 +1033,7 @@ EXPLAIN SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,35 NULL # Using sort_intersect(PRIMARY,f4); Using where +1 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,39 NULL # Using sort_intersect(PRIMARY,f4); Using where SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; f1 f4 f5 diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index b93d15f7bef..5202c79f3c7 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -580,9 +580,12 @@ pk1 pk2 explain select * from t1 where badkey=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1 key1 4 const 100 Using where +set @tmp_index_merge_ror_cpk=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; explain select * from t1 where pk1 < 7500 and key1 = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where +set optimizer_switch=@tmp_index_merge_ror_cpk; explain select * from t1 where pktail1ok=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 953e9d926dd..5971a103d0a 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1092,7 +1092,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on optimizer-use-condition-selectivity 1 performance-schema FALSE performance-schema-events-waits-history-long-size 10000 diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 424b1009a97..1b12a9f5512 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -797,6 +797,8 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 41 Using index condition +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -985,6 +987,7 @@ ID Name Country Population 3798 Phoenix USA 1321045 DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; EXPLAIN SELECT * FROM City WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR diff --git a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff index ecae2c809c1..4bc23e87c85 100644 --- a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff +++ b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff @@ -1,5 +1,5 @@ ---- ./r/range_vs_index_merge_innodb.result 2012-11-21 19:35:14.000000000 +0100 -+++ ./r/range_vs_index_merge_innodb,innodb_plugin.reject 2012-11-21 20:56:00.000000000 +0100 +--- ./r/range_vs_index_merge_innodb.result 2014-02-24 23:52:36.050552475 +0400 ++++ ./r/range_vs_index_merge_innodb,innodb_plugin.reject 2014-02-24 23:55:59.338560727 +0400 @@ -50,14 +50,14 @@ WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR (Population < 100000 OR Name Like 'T%') AND Country='ARG'; @@ -222,10 +222,10 @@ id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 71 Using index condition +1 SIMPLE City range Name Name 35 NULL 71 Using where + set @tmp_range_vs_index_merge=@@optimizer_switch; + set optimizer_switch='extended_keys=off'; EXPLAIN - SELECT * FROM City - WHERE ((Population > 101000 AND Population < 102000) OR -@@ -818,7 +818,7 @@ +@@ -820,7 +820,7 @@ ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra @@ -234,7 +234,7 @@ SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' -@@ -950,14 +950,14 @@ +@@ -952,14 +952,14 @@ ID BETWEEN 3500 AND 3800) AND Country='USA' AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra @@ -251,7 +251,7 @@ SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' -@@ -1077,7 +1077,7 @@ +@@ -1080,7 +1080,7 @@ (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra @@ -260,7 +260,7 @@ SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR -@@ -1164,7 +1164,7 @@ +@@ -1167,7 +1167,7 @@ (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra @@ -269,7 +269,7 @@ SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR -@@ -1346,7 +1346,7 @@ +@@ -1349,7 +1349,7 @@ AND (Population >= 100000 AND Population < 120000) ORDER BY Population LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 67e341192da..8428936d25f 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -65,7 +65,7 @@ Country IN ('CAN', 'ARG') AND ID < 3800 OR Country < 'U' AND Name LIKE 'Zhu%' OR ID BETWEEN 3800 AND 3810; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 125 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,7,4 NULL 123 Using sort_union(Name,Country,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); @@ -362,7 +362,7 @@ WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 900 AND 1500) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 681 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 39,3,4 NULL 680 Using sort_union(Name,Country,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -798,6 +798,8 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 71 Using index condition +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -986,6 +988,7 @@ ID Name Country Population 3798 Phoenix USA 1321045 DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; EXPLAIN SELECT * FROM City WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR diff --git a/mysql-test/r/stat_tables_par_innodb.result b/mysql-test/r/stat_tables_par_innodb.result index e0c00c482a0..620ebcc50b9 100644 --- a/mysql-test/r/stat_tables_par_innodb.result +++ b/mysql-test/r/stat_tables_par_innodb.result @@ -157,11 +157,15 @@ dbt3_s001 lineitem PRIMARY 1 4.0033 dbt3_s001 lineitem PRIMARY 2 1.0000 dbt3_s001 lineitem i_l_commitdate 1 2.7160 dbt3_s001 lineitem i_l_orderkey 1 4.0033 -dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_partkey 1 30.0250 dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.5000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 @@ -176,7 +180,11 @@ dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 dbt3_s001 lineitem i_l_partkey 1 30.0250 dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.5000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 @@ -202,15 +210,29 @@ db_name table_name index_name prefix_arity avg_frequency dbt3_s001 lineitem PRIMARY 1 4.0027 dbt3_s001 lineitem PRIMARY 2 1.0000 dbt3_s001 lineitem i_l_commitdate 1 2.7155 +dbt3_s001 lineitem i_l_commitdate 2 1.0364 +dbt3_s001 lineitem i_l_commitdate 3 1.0000 dbt3_s001 lineitem i_l_orderkey 1 4.0027 +dbt3_s001 lineitem i_l_orderkey 2 1.0000 dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0027 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_orderkey_quantity 3 1.0000 dbt3_s001 lineitem i_l_partkey 1 30.0200 +dbt3_s001 lineitem i_l_partkey 2 1.0089 +dbt3_s001 lineitem i_l_partkey 3 1.0000 dbt3_s001 lineitem i_l_receiptdate 1 2.6473 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 dbt3_s001 lineitem i_l_shipdate 1 2.6496 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.4000 +dbt3_s001 lineitem i_l_suppkey 2 1.2073 +dbt3_s001 lineitem i_l_suppkey 3 1.0000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0200 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5771 +dbt3_s001 lineitem i_l_suppkey_partkey 3 1.0030 +dbt3_s001 lineitem i_l_suppkey_partkey 4 1.0000 set @save_global_use_stat_tables=@@global.use_stat_tables; set global use_stat_tables='preferably'; set debug_sync='RESET'; @@ -224,6 +246,8 @@ use dbt3_s001; select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68; db_name table_name index_name prefix_arity avg_frequency l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment dbt3_s001 lineitem i_l_shipdate 1 2.6496 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +dbt3_s001 lineitem i_l_shipdate 2 1.0149 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +dbt3_s001 lineitem i_l_shipdate 3 1.0000 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s set debug_sync='RESET'; set global use_stat_tables=@save_global_use_stat_tables; DROP DATABASE dbt3_s001; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index c5a996552e4..2d27d55f7aa 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -163,7 +163,7 @@ SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index 1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index -2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where +2 SUBQUERY t1 const PRIMARY,a PRIMARY 4 const 1 Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; pk a b 0 4 4 @@ -172,7 +172,7 @@ SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index 1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index -2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where +2 SUBQUERY t1 const PRIMARY,a PRIMARY 4 const 1 Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; pk a b 0 4 4 diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 11a2321b0f4..e6e81e3da69 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2535,6 +2535,8 @@ KEY idx1 (f2,f5,f4), KEY idx2 (f2,f4) ) ENGINE=InnoDB; LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1; +set @tmp_innodb_mysql= @@optimizer_switch; +set optimizer_switch='extended_keys=off'; SELECT * FROM t1 WHERE f1 IN (3305028,3353871,3772880,3346860,4228206,3336022, 3470988,3305175,3329875,3817277,3856380,3796193, @@ -2558,6 +2560,7 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where +set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # # Bug#51431 Wrong sort order after import of dump file diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index 3ae5be3aa30..101781000c2 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -728,6 +728,8 @@ CREATE TABLE t1 ( LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1; +set @tmp_innodb_mysql= @@optimizer_switch; +set optimizer_switch='extended_keys=off'; SELECT * FROM t1 WHERE f1 IN (3305028,3353871,3772880,3346860,4228206,3336022, 3470988,3305175,3329875,3817277,3856380,3796193, @@ -739,6 +741,7 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN 3470988,3305175,3329875,3817277,3856380,3796193, 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; +set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result index 937a3b164fa..64028e537bf 100644 --- a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result +++ b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result @@ -9,7 +9,7 @@ SELECT @global_start_value; select @old_session_opt_switch:=@@session.optimizer_switch, @old_global_opt_switch:=@@global.optimizer_switch; @old_session_opt_switch:=@@session.optimizer_switch @old_global_opt_switch:=@@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off '#--------------------FN_DYNVARS_028_01------------------------#' SET @@session.engine_condition_pushdown = 0; Warnings: @@ -212,7 +212,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@session.engine_condition_pushdown = TRUE; Warnings: Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead @@ -220,7 +220,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@session.engine_condition_pushdown = FALSE; Warnings: Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead @@ -228,7 +228,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@global.engine_condition_pushdown = TRUE; Warnings: Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead @@ -236,7 +236,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@global.engine_condition_pushdown = FALSE; Warnings: Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead @@ -244,31 +244,31 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@session.optimizer_switch = "engine_condition_pushdown=on"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@session.optimizer_switch = "engine_condition_pushdown=off"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@global.optimizer_switch = "engine_condition_pushdown=on"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set @@global.optimizer_switch = "engine_condition_pushdown=off"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off SET @@session.engine_condition_pushdown = @session_start_value; Warnings: Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead @@ -287,4 +287,4 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index 8a9c49def85..99b1d6296a8 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,25 +1,25 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@ -67,4 +67,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result index be4da117c2b..b658281f5a8 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result @@ -2,4 +2,4 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result index d1c5516d9ed..c36147b605c 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result @@ -2,4 +2,4 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off +0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 97bfbbe4eaf..32f053bcd36 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -52,7 +52,7 @@ a b c a b c 1 -1 -1 1 -1 -1 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 const c c 5 const 1 +1 SIMPLE t3 ref c c 5 const 1 Using index condition 1 SIMPLE t1 ref c c 5 const 2 Using index condition # select_type=PRIMARY, type=index,ALL select * from t1 where b in (select c from t3); diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index fb8fd778559..7ecca454f4c 100644 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -454,6 +454,8 @@ SELECT * FROM City WHERE Name LIKE 'Pa%'; # index merge retrievals over: # 2. key1 and key3 # 3. key2 and key3 +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City @@ -578,6 +580,7 @@ SELECT * FROM City DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; # The pattern of the WHERE condition used in the following query is # (key1|2_p1=c AND range(key1_p2)) OR (key1|2_p1=c AND range(key2_p2)) diff --git a/sql/sql_priv.h b/sql/sql_priv.h index 9891cf1b24e..c86b585fc45 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -238,6 +238,7 @@ template <class T> bool valid_buffer_range(T jump, OPTIMIZER_SWITCH_DERIVED_MERGE | \ OPTIMIZER_SWITCH_DERIVED_WITH_KEYS | \ OPTIMIZER_SWITCH_TABLE_ELIMINATION | \ + OPTIMIZER_SWITCH_EXTENDED_KEYS | \ OPTIMIZER_SWITCH_IN_TO_EXISTS | \ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ |