diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/order_by.result | 145 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/optimizer_switch_basic.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 86 |
3 files changed, 232 insertions, 1 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 294142737d9..2e1475a8bd0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2949,3 +2949,148 @@ explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 li id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer drop table t1,t2; +# +# MDEV-8989: ORDER BY optimizer ignores equality propagation +# +set @tmp_8989=@@optimizer_switch; +set optimizer_switch='orderby_uses_equalities=on'; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; +create table t3 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; +insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; +# The optimizer produces an order of 't2,t3' for this join +# +# Case #1 (from the bug report): +# Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting +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 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index a a 5 NULL 5 Using where +1 SIMPLE t3 ref a a 5 test.t2.a 1 +# +# This is Q2 which used to have "Using temporary; using filesort" but +# has the same query plan as Q1: +# +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 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index a a 5 NULL 5 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 5; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +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 5; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +# +# Case #2: here, only "Using temporary" is removed. "Using filesort" remains. +# +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 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 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; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +194 806 806 194 806 806 +193 807 807 193 807 807 +192 808 808 192 808 808 +191 809 809 191 809 809 +190 810 810 190 810 810 +189 811 811 189 811 811 +188 812 812 188 812 812 +187 813 813 187 813 813 +186 814 814 186 814 814 +185 815 815 185 815 815 +184 816 816 184 816 816 +183 817 817 183 817 817 +182 818 818 182 818 818 +181 819 819 181 819 819 +180 820 820 180 820 820 +179 821 821 179 821 821 +178 822 822 178 822 822 +177 823 823 177 823 823 +176 824 824 176 824 824 +175 825 825 175 825 825 +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; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +194 806 806 194 806 806 +193 807 807 193 807 807 +192 808 808 192 808 808 +191 809 809 191 809 809 +190 810 810 190 810 810 +189 811 811 189 811 811 +188 812 812 188 812 812 +187 813 813 187 813 813 +186 814 814 186 814 814 +185 815 815 185 815 815 +184 816 816 184 816 816 +183 817 817 183 817 817 +182 818 818 182 818 818 +181 819 819 181 819 819 +180 820 820 180 820 820 +179 821 821 179 821 821 +178 822 822 178 822 822 +177 823 823 177 823 823 +176 824 824 176 824 824 +175 825 825 175 825 825 +# +# Case #3: single table access (the code that decides whether we need +# "Using temporary" is not invoked) +# +explain select * from t3 where b=a order by a limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL a 5 NULL 10 Using where +# This must not use filesort. The query plan should be like the query above: +explain select * from t3 where b=a order by b limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL a 5 NULL 10 Using where +drop table t0,t1,t2,t3; +set @@optimizer_switch=@tmp_8989; +set optimizer_switch='orderby_uses_equalities=on'; 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 141ab31ea82..a3830d2d16a 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -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=on,exists_to_in=on +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=on,orderby_uses_equalities=off diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index cf6a4d473c3..84a5b151da4 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1958,3 +1958,89 @@ select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C; --echo # Should show rows=2, not rows=100 explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; drop table t1,t2; + +--echo # +--echo # MDEV-8989: ORDER BY optimizer ignores equality propagation +--echo # +set @tmp_8989=@@optimizer_switch; +set optimizer_switch='orderby_uses_equalities=on'; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t2 ( + pk int primary key, + a int, b int, + filler char(200), + key(a) +); +insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; + +create table t3 ( + pk int primary key, + a int, b int, + filler char(200), + key(a) +); +insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; +insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; + +--echo # The optimizer produces an order of 't2,t3' for this join +--echo # +--echo # Case #1 (from the bug report): +--echo # Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting +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 5; + +--echo # +--echo # This is Q2 which used to have "Using temporary; using filesort" but +--echo # has the same query plan as Q1: +--echo # +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 5; + +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 5; + +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 5; + + +--echo # +--echo # Case #2: here, only "Using temporary" is removed. "Using filesort" remains. +--echo # +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; + +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; + + +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; + + +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; + +--echo # +--echo # Case #3: single table access (the code that decides whether we need +--echo # "Using temporary" is not invoked) +--echo # +explain select * from t3 where b=a order by a limit 10; + +--echo # This must not use filesort. The query plan should be like the query above: +explain select * from t3 where b=a order by b limit 10; +drop table t0,t1,t2,t3; + +set @@optimizer_switch=@tmp_8989; + +set optimizer_switch='orderby_uses_equalities=on'; + + |