diff options
Diffstat (limited to 'mysql-test/main')
25 files changed, 53 insertions, 48 deletions
diff --git a/mysql-test/main/compress.result b/mysql-test/main/compress.result index 788eb7ab13b..21154aaa7c9 100644 --- a/mysql-test/main/compress.result +++ b/mysql-test/main/compress.result @@ -612,8 +612,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index 5708cf88934..553b640b93d 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -634,7 +634,7 @@ a 2 explain select a from t2 where b is null order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range b b 9 NULL 3 Using where; Using filesort +1 SIMPLE t2 index b PRIMARY 8 NULL 2 Using where select a from t2 where b is null order by a desc limit 2; a 3 diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index adf65780d21..2b40fd67678 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -172,7 +172,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where DROP TABLE t1; # diff --git a/mysql-test/main/join_cardinality.test b/mysql-test/main/join_cardinality.test index b24debfb66e..92b8a1b1c87 100644 --- a/mysql-test/main/join_cardinality.test +++ b/mysql-test/main/join_cardinality.test @@ -1,4 +1,5 @@ --source include/have_sequence.inc +--source include/not_embedded.inc # Default settings for all following tests SET @save_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 2ff966fdfd3..6926d632f69 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -2446,7 +2446,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 index c1_idx PRIMARY 4 NULL 4 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -2456,11 +2456,9 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead # Status of "equivalent" SELECT query execution: Variable_name Value -Handler_read_key 7 -Handler_read_next 2 -Sort_priority_queue_sorts 1 -Sort_range 1 -Sort_rows 2 +Handler_read_key 6 +Handler_read_last 1 +Handler_read_prev 3 # Status of testing query execution: Variable_name Value Handler_read_key 7 @@ -2492,7 +2490,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 index c1_idx PRIMARY 4 NULL 4 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -2502,11 +2500,9 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead # Status of "equivalent" SELECT query execution: Variable_name Value -Handler_read_key 7 -Handler_read_next 2 -Sort_priority_queue_sorts 1 -Sort_range 1 -Sort_rows 2 +Handler_read_key 6 +Handler_read_last 1 +Handler_read_prev 3 # Status of testing query execution: Variable_name Value Handler_delete 2 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index f059c3f95e5..c597d00d725 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -165,7 +165,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where DROP TABLE t1; # diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index c0e4bccb71e..53fd270be6a 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1641,7 +1641,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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,cost_based_order_by_limit=on optimizer-trace optimizer-trace-max-mem-size 1048576 optimizer-use-condition-selectivity 4 diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result index 72983254340..73d6ed43558 100644 --- a/mysql-test/main/mysqltest_tracking_info.result +++ b/mysql-test/main/mysqltest_tracking_info.result @@ -38,7 +38,7 @@ SET @@session.session_track_system_variables='optimizer_switch'; set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES -- optimizer_switch --- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,cost_based_order_by_limit=off +-- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,cost_based_order_by_limit=on Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result index 9fc7abd79f5..c7e082c5c21 100644 --- a/mysql-test/main/named_pipe.result +++ b/mysql-test/main/named_pipe.result @@ -606,8 +606,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 59404af5154..da4790bb7bb 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1899,6 +1899,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK set optimizer_trace='enabled=on'; +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='cost_based_order_by_limit=off'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where @@ -2244,6 +2246,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { } ] } 0 0 +set optimizer_switch=@save_optimizer_switch; drop table t1,ten,one_k; # # TABLE ELIMINATION diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 314e5825c08..3f221717279 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -155,8 +155,12 @@ update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; set optimizer_trace='enabled=on'; + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='cost_based_order_by_limit=off'; explain select * from t1 where a=1 and b=2 order by c limit 1; select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +set optimizer_switch=@save_optimizer_switch; drop table t1,ten,one_k; --echo # diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 27004fa4596..170ee4bb19c 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -2555,7 +2555,7 @@ INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +1 SIMPLE t1 range b,b_2 b 4 NULL 49 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 @@ -2645,7 +2645,7 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 ORDER BY 1 LIMIT 10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE r range PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index +1 SIMPLE r range PRIMARY PRIMARY 4 NULL 10 100.00 Using where; Using index 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index Warnings: Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where `test`.`s`.`a` = `test`.`r`.`a` and (`test`.`r`.`a` in (2,9) or `test`.`r`.`a` < 100 and `test`.`r`.`a` <> 0) order by 1 limit 10 @@ -2712,7 +2712,7 @@ LEFT JOIN t3 ON t2.i2 = t3.i3 ORDER BY t1.i1 LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 Const row not found -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 100.00 Using index +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 12 100.00 Using index 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`i2` = `test`.`t1`.`i1` order by `test`.`t1`.`i1` limit 5 @@ -3189,13 +3189,13 @@ explain select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b from t2, t3 where t2.a=t3.a order by t2.a limit 25; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort +1 SIMPLE t2 index a a 5 NULL 25 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1 explain select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b from t2, t3 where t2.a=t3.a order by t3.a limit 25; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort +1 SIMPLE t2 index a a 5 NULL 25 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1 select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b from t2, t3 where t2.a=t3.a order by t2.a limit 25; @@ -3627,7 +3627,7 @@ test.t4 analyze status Table is already up to date explain extended select * from t4 where b < 5000 order by a limit 600; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 index NULL a 5 NULL 1188 100.00 Using where +1 SIMPLE t4 index NULL a 5 NULL 2352 100.00 Using where Warnings: Note 1003 select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler1` AS `filler1`,`test`.`t4`.`filler2` AS `filler2` from `test`.`t4` where `test`.`t4`.`b` < 5000 order by `test`.`t4`.`a` limit 600 set histogram_size=@tmp_h, histogram_type=@tmp_ht, use_stat_tables=@tmp_u, diff --git a/mysql-test/main/pool_of_threads.result b/mysql-test/main/pool_of_threads.result index d4a7d9092f4..812341f07ef 100644 --- a/mysql-test/main/pool_of_threads.result +++ b/mysql-test/main/pool_of_threads.result @@ -606,8 +606,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index c7bf8948d3a..a6612220e2d 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3278,7 +3278,7 @@ drop table t1,ten,t2; # # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer # -set @@optimizer_switch='not_null_range_scan=on'; +set @@optimizer_switch='not_null_range_scan=on,cost_based_order_by_limit=off'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int); diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 4f228e5dce3..677e1cefbdd 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2247,7 +2247,7 @@ drop table t1,ten,t2; --echo # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer --echo # -set @@optimizer_switch='not_null_range_scan=on'; +set @@optimizer_switch='not_null_range_scan=on,cost_based_order_by_limit=off'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 3c5b7164163..9c937ea426a 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3267,7 +3267,7 @@ drop table t1,ten,t2; # # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer # -set @@optimizer_switch='not_null_range_scan=on'; +set @@optimizer_switch='not_null_range_scan=on,cost_based_order_by_limit=off'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int); diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 7547b5b1347..ae95048fdc2 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -609,8 +609,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 6a8174d124c..b3d88174cb5 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -620,12 +620,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using temporary; Using filesort -1 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort -1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period 9410 diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 7547b5b1347..ae95048fdc2 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -609,8 +609,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 591294aff10..10e1239d998 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1739,7 +1739,7 @@ INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +1 SIMPLE t1 range b,b_2 b 4 NULL 49 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 7a8f837d4e2..c9d3df34f2f 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1749,7 +1749,7 @@ INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +1 SIMPLE t1 range b,b_2 b 4 NULL 49 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test index 6736defd674..74ee0e00bf0 100644 --- a/mysql-test/main/sort_nest.test +++ b/mysql-test/main/sort_nest.test @@ -1,4 +1,5 @@ --source include/have_sequence.inc +--source include/not_embedded.inc set optimizer_switch='cost_based_order_by_limit=on'; set optimizer_switch='rowid_filter=off'; set optimizer_trace=1; @@ -1015,4 +1016,4 @@ SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate')) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; eval $query; -DROP TABLE t0,t1,t2,t3;
\ No newline at end of file +DROP TABLE t0,t1,t2,t3; diff --git a/mysql-test/main/ssl.result b/mysql-test/main/ssl.result index 40a32fdd1dd..d999a56f846 100644 --- a/mysql-test/main/ssl.result +++ b/mysql-test/main/ssl.result @@ -615,8 +615,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/ssl_compress.result b/mysql-test/main/ssl_compress.result index 8c63c798afa..e4e40150f42 100644 --- a/mysql-test/main/ssl_compress.result +++ b/mysql-test/main/ssl_compress.result @@ -612,8 +612,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +1 SIMPLE t1 index period period 4 NULL 1 +1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index period period 4 NULL 1 diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result index 34fd8300423..642a25ca5b7 100644 --- a/mysql-test/main/xtradb_mrr.result +++ b/mysql-test/main/xtradb_mrr.result @@ -402,7 +402,7 @@ id This must show type=index, extra=Using where explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using where +1 SIMPLE t1 index NULL PRIMARY 4 NULL 7 Using where SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; id parent_id name 60 40 F |