revision-id: 1fde449f1d4e8ebe12e989754bfdc84b123cf0b2 (mariadb-10.1.34-30-g1fde449) parent(s): 57cde8ccd19675dc98e3cbacf0ef5c72cb188e49 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:53:12 -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 40f936f..69db095 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7235,6 +7235,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 a238208..ef3bf7e 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7235,6 +7235,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 baa7430..703572e 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7228,6 +7228,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 039f2fe..39a098b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7226,6 +7226,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 0ce77bb..b9a59bf 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7241,6 +7241,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 574e781..f1df85f 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7226,6 +7226,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 717871d..a4c4c21 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6098,6 +6098,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 139ce40..dcd773f 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1449,6 +1449,11 @@ class Item: public Value_source, virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e3b725c..460a64e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1212,6 +1212,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, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);