diff options
Diffstat (limited to 'mysql-test/main/selectivity_innodb.result')
| -rw-r--r-- | mysql-test/main/selectivity_innodb.result | 57 |
1 files changed, 57 insertions, 0 deletions
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 5c740d71925..c091ce07ff8 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1768,6 +1768,63 @@ a 1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; +# +# MDEV-20424: New default value for optimizer_use_condition-selectivity +# leads to bad plan +# +create table t1(a int, b int, c int, d int, key(a,b)); +insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; +insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; +create table t2(a int, b int, c int, primary key(a)); +insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; +create table t3(a int, b int, c int, primary key(a)); +insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; +set optimizer_use_condition_selectivity=1; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 11 100.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity=2; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 11 11.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +drop table t1,t2,t3; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; |