revision-id: 9aeda1c3af3fd3f8efe2589ecac578b69eb83bf6 (mariadb-10.4.0-307-g9aeda1c3af3) parent(s): e80bcd7f64fc8ff6f46c1fc0d01e9c0b0fd03064 author: Varun Gupta committer: Varun Gupta timestamp: 2019-02-08 12:02:32 +0530 message: MDEV-18355: Switching the results for plans when there was query plan change with the new optimizer defaults Switched plans for a few tests so that we are still able to regressions. --- mysql-test/main/derived_cond_pushdown.result | 40 +++++++++------- mysql-test/main/derived_cond_pushdown.test | 6 +++ mysql-test/main/range.result | 70 +++++++++++++++------------- mysql-test/main/range.test | 7 +++ mysql-test/main/select_safe.result | 14 ++++-- mysql-test/main/select_safe.test | 6 +++ 6 files changed, 90 insertions(+), 53 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 36a44ce6fba..679c5940ba0 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15157,6 +15157,10 @@ a s m 2 113 23 deallocate prepare stmt; delete from t1 where t1.b between 2 and 5; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set use_stat_tables=1; +set optimizer_use_condition_selectivity=1; set statement optimizer_switch='split_materialized=off' for select t1.a,t.max,t.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t @@ -15180,11 +15184,11 @@ from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t on t1.a=t.a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 9 100.00 Using where -2 DERIVED t2 ALL idx_a NULL NULL NULL 90 100.00 Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 100.00 Using where +2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 2 100.00 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 explain format=json select t1.a,t.max,t.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t @@ -15197,7 +15201,7 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 4, "filtered": 100 }, "table": { @@ -15208,29 +15212,31 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 9, + "rows": 2, "filtered": 100, "attached_condition": "trigcond(trigcond(t1.a is not null))", "materialized": { "query_block": { "select_id": 2, - "filesort": { - "sort_key": "t2.a", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "possible_keys": ["idx_a"], - "rows": 90, - "filtered": 100 - } - } + "outer_ref_condition": "t1.a is not null", + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["idx_a"], + "key": "idx_a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.a"], + "rows": 2, + "filtered": 100 } } } } } } +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables= @save_use_stat_tables; create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; insert into t3 values (8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 076d39c1abd..d85d1c438db 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2869,10 +2869,16 @@ from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t on t1.a=t.a; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set use_stat_tables=NEVER; +set optimizer_use_condition_selectivity=1; eval $no_splitting $q2; eval $q2; eval explain extended $q2; eval explain format=json $q2; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables= @save_use_stat_tables; create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; insert into t3 values diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 2c2f7be096d..d3d74c11fb8 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -2530,11 +2530,15 @@ insert into t2 values (42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l'); # join order: (t1,t2) with ref access of t2 # range access to t1 by 1-component keys for index idx +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set optimizer_use_condition_selectivity=1; +set use_stat_tables= NEVER; explain select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx1,idx2 idx1 5 NULL 8 Using index condition; Using where -1 SIMPLE t1 ref idx idx 5 test.t2.d 8 +1 SIMPLE t1 range idx idx 5 NULL 6 Using index condition +1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where explain format=json select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; EXPLAIN @@ -2542,27 +2546,27 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "t2", + "table_name": "t1", "access_type": "range", - "possible_keys": ["idx1", "idx2"], - "key": "idx1", + "possible_keys": ["idx"], + "key": "idx", "key_length": "5", - "used_key_parts": ["d"], - "rows": 8, - "filtered": 12.5, - "index_condition": "t2.d is not null", - "attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" + "used_key_parts": ["a"], + "rows": 6, + "filtered": 100, + "index_condition": "t1.a is not null" }, "table": { - "table_name": "t1", + "table_name": "t2", "access_type": "ref", - "possible_keys": ["idx"], - "key": "idx", + "possible_keys": ["idx1", "idx2"], + "key": "idx1", "key_length": "5", - "used_key_parts": ["a"], - "ref": ["test.t2.d"], - "rows": 8, - "filtered": 100 + "used_key_parts": ["d"], + "ref": ["test.t1.a"], + "rows": 12, + "filtered": 100, + "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" } } } @@ -2570,16 +2574,16 @@ select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; a b c d e f 3 2 uuuw 3 3 i -3 2 uuua 3 3 i -3 3 zzzz 3 3 i -3 3 zyxw 3 3 i -3 3 zzza 3 3 i -3 3 zyxa 3 3 i 3 2 uuuw 3 3 i 3 2 uuua 3 3 i +3 2 uuua 3 3 i 3 3 zzzz 3 3 i +3 3 zzzz 3 3 i +3 3 zyxw 3 3 i 3 3 zyxw 3 3 i 3 3 zzza 3 3 i +3 3 zzza 3 3 i +3 3 zyxa 3 3 i 3 3 zyxa 3 3 i 7 7 xxxyy 7 7 h 7 7 xxxya 7 7 h @@ -2590,16 +2594,16 @@ where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1"; execute stmt; a b c d e f 3 2 uuuw 3 3 i -3 2 uuua 3 3 i -3 3 zzzz 3 3 i -3 3 zyxw 3 3 i -3 3 zzza 3 3 i -3 3 zyxa 3 3 i 3 2 uuuw 3 3 i 3 2 uuua 3 3 i +3 2 uuua 3 3 i 3 3 zzzz 3 3 i +3 3 zzzz 3 3 i +3 3 zyxw 3 3 i 3 3 zyxw 3 3 i 3 3 zzza 3 3 i +3 3 zzza 3 3 i +3 3 zyxa 3 3 i 3 3 zyxa 3 3 i 7 7 xxxyy 7 7 h 7 7 xxxya 7 7 h @@ -2608,22 +2612,24 @@ a b c d e f execute stmt; a b c d e f 3 2 uuuw 3 3 i -3 2 uuua 3 3 i -3 3 zzzz 3 3 i -3 3 zyxw 3 3 i -3 3 zzza 3 3 i -3 3 zyxa 3 3 i 3 2 uuuw 3 3 i 3 2 uuua 3 3 i +3 2 uuua 3 3 i +3 3 zzzz 3 3 i 3 3 zzzz 3 3 i 3 3 zyxw 3 3 i +3 3 zyxw 3 3 i +3 3 zzza 3 3 i 3 3 zzza 3 3 i 3 3 zyxa 3 3 i +3 3 zyxa 3 3 i 7 7 xxxyy 7 7 h 7 7 xxxya 7 7 h 7 8 xxxxx 7 7 h 7 8 xxxxa 7 7 h deallocate prepare stmt; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables= @save_use_stat_tables; insert into t1 select * from t1; # join order: (t2,t1) with ref access of t1 # range access to t2 by keys for index idx2 diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index bd2299bac5f..b3a2a1abee7 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -1923,6 +1923,11 @@ insert into t2 values let $q5= select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set optimizer_use_condition_selectivity=1; +set use_stat_tables= NEVER; eval explain $q5; eval explain format=json $q5; eval $q5; @@ -1930,6 +1935,8 @@ eval prepare stmt from "$q5"; execute stmt; execute stmt; deallocate prepare stmt; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables= @save_use_stat_tables; insert into t1 select * from t1; diff --git a/mysql-test/main/select_safe.result b/mysql-test/main/select_safe.result index 649e2dc484e..304b086a568 100644 --- a/mysql-test/main/select_safe.result +++ b/mysql-test/main/select_safe.result @@ -67,16 +67,22 @@ test.t1 analyze status OK insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e"); insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v"); +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set use_stat_tables=1; +set optimizer_use_condition_selectivity=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 11 -1 SIMPLE t2 ALL b NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL b NULL NULL NULL 41 Using where +1 SIMPLE t2 ref b b 21 test.t1.b 6 set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 11 -1 SIMPLE t2 ALL b NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL b NULL NULL NULL 41 Using where +1 SIMPLE t2 ref b b 21 test.t1.b 6 SET MAX_SEEKS_FOR_KEY=DEFAULT; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables= @save_use_stat_tables; drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3),(4),(5); diff --git a/mysql-test/main/select_safe.test b/mysql-test/main/select_safe.test index c76e337cd10..1f757003bd9 100644 --- a/mysql-test/main/select_safe.test +++ b/mysql-test/main/select_safe.test @@ -60,10 +60,16 @@ analyze table t1; insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e"); insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v"); +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set use_stat_tables=1; +set optimizer_use_condition_selectivity=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; SET MAX_SEEKS_FOR_KEY=DEFAULT; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables= @save_use_stat_tables; drop table t1;