revision-id: 32be26bdc419be8383fecf315e2c66f81646620a (mariadb-10.2.16-41-g32be26b) parent(s): 969939e89c29bb3f3d91f8b4ab539601b5daa4d8 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-25 11:57:57 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2ed431..1c75aa6 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7240,6 +7240,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 6306cff..0bd14a5 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7240,6 +7240,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 0db1b03..a3b7871 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7233,6 +7233,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 1fa6f38..9dcc690 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7231,6 +7231,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 986542b..7b3c001 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7246,6 +7246,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d5fcc17..a062524 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7231,6 +7231,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 7e9e5a7..1048e5a 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6102,6 +6102,21 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests --echo # End of 10.0 tests diff --git a/sql/item.h b/sql/item.h index ce07f99..8fad8da 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1614,6 +1614,11 @@ class Item: public Value_source, virtual bool limit_index_condition_pushdown_processor(void *arg) { return 0; } virtual bool exists2in_processor(void *arg) { return 0; } virtual bool find_selective_predicates_list_processor(void *arg) { return 0; } + bool cleanup_is_expensive_cache_processor(void *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* TRUE if the expression depends only on the table indicated by tab_map diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1fa2143..4cabf5c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1335,6 +1335,13 @@ JOIN::optimize_inner() if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (void *) 0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (void *) 0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);