summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorVarun Gupta <varun.gupta@mariadb.com>2019-10-27 01:48:00 +0530
committerVarun Gupta <varun.gupta@mariadb.com>2019-11-01 17:22:05 +0530
commit8afe4bba2ecd9ebc762a80598a8465a201d4feee (patch)
tree0e216f48970ed977041469fdb1468ffe0602019f
parent6f86150ab3c9059a9379fcc68cb90da78e59e5cb (diff)
downloadmariadb-git-8afe4bba2ecd9ebc762a80598a8465a201d4feee.tar.gz
MDEV-20424: New default value for optimizer_use_condition-selectivity leads to bad plan
In the function prev_record_reads where one finds the different row combinations for a subset of partial join, it did not take into account the selectivity of tables involved in the subset of partial join.
-rw-r--r--mysql-test/r/selectivity.result57
-rw-r--r--mysql-test/r/selectivity_innodb.result57
-rw-r--r--mysql-test/t/selectivity.test29
-rw-r--r--sql/sql_select.cc3
4 files changed, 146 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index d0bbb46cb0a..5fe6986e9ff 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1753,4 +1753,61 @@ 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 9 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 9 9.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
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 719156a77de..70ddd5bf1a9 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1763,6 +1763,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 optimizer_switch=@save_optimizer_switch_for_selectivity_test;
set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 0deacc390db..a93ad5efb07 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1205,5 +1205,34 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit
DROP TABLE t1;
+--echo #
+--echo # MDEV-20424: New default value for optimizer_use_condition-selectivity
+--echo # leads to bad plan
+--echo #
+
+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;
+
+let $query= 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;
+
+set optimizer_use_condition_selectivity=1;
+eval explain extended $query;
+eval $query;
+
+set optimizer_use_condition_selectivity=2;
+eval explain extended $query;
+eval $query;
+set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+
+drop table t1,t2,t3;
+
--echo # End of 10.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c6e70c2430c..dfc9f729118 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8470,7 +8470,10 @@ prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
#max_nested_outer_joins=64-1) will not make it any more precise.
*/
if (pos->records_read)
+ {
found= COST_MULT(found, pos->records_read);
+ found*= pos->cond_selectivity;
+ }
}
}
return found;