diff options
| -rw-r--r-- | mysql-test/r/selectivity.result | 28 | ||||
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/selectivity.test | 27 | ||||
| -rw-r--r-- | sql/opt_range.cc | 7 |
4 files changed, 89 insertions, 1 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index e26d2481820..781df332f77 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -867,4 +867,32 @@ a set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4372: exists subquery in WHERE +# with optimizer_use_condition_selectivity=3 +# +set use_stat_tables = PREFERABLY; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES +(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),(1),(3),(8),(8); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4),(5),(2),(5),(1),(1),(2); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +FLUSH TABLES; +SET optimizer_use_condition_selectivity=3; +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 100.00 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((exists(select 1 from `test`.`t1` join `test`.`t2`) and (`test`.`t1`.`a` <> `test`.`t2`.`b`)) or (`test`.`t1`.`a` <= 4)) +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1,t2; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 5f4ddf76383..e65e61af4c1 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -875,6 +875,34 @@ a set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4372: exists subquery in WHERE +# with optimizer_use_condition_selectivity=3 +# +set use_stat_tables = PREFERABLY; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES +(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),(1),(3),(8),(8); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4),(5),(2),(5),(1),(1),(2); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +FLUSH TABLES; +SET optimizer_use_condition_selectivity=3; +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 100.00 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((exists(select 1 from `test`.`t1` join `test`.`t2`) and (`test`.`t1`.`a` <> `test`.`t2`.`b`)) or (`test`.`t1`.`a` <= 4)) +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1,t2; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index ccd5b09ce68..f601d4f656a 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -467,5 +467,32 @@ DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-4372: exists subquery in WHERE +--echo # with optimizer_use_condition_selectivity=3 +--echo # + +set use_stat_tables = PREFERABLY; + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES + (1),(7),(4),(7),(0),(2),(9),(4),(0),(9),(1),(3),(8),(8); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4),(5),(2),(5),(1),(1),(2); + +ANALYZE TABLE t1, t2; +FLUSH TABLES; + +SET optimizer_use_condition_selectivity=3; + +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1,t2; + +set use_stat_tables=@save_use_stat_tables; + set use_stat_tables=@save_use_stat_tables; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 215c85cbdf5..035ea3635e2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3368,7 +3368,12 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) rows= 0; table->reginfo.impossible_range= 1; goto free_alloc; - } + } + else if (tree->type == SEL_TREE::MAYBE) + { + rows= table_records; + goto free_alloc; + } for (key= tree->keys, end= key + param.keys; key != end; key++, idx++) { |