diff options
author | Igor Babaev <igor@askmonty.org> | 2018-07-24 20:00:28 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2018-07-24 20:00:28 -0700 |
commit | d567f1611e27a17427380e9aae67939792f68ad1 (patch) | |
tree | 96821ecf4292951ce29d26a246e467b958287fec | |
parent | 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af (diff) | |
download | mariadb-git-d567f1611e27a17427380e9aae67939792f68ad1.tar.gz |
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().
-rw-r--r-- | mysql-test/r/subselect.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_mat.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_opts.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_scache.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 16 | ||||
-rw-r--r-- | mysql-test/t/subselect.test | 15 | ||||
-rw-r--r-- | sql/item.h | 5 | ||||
-rw-r--r-- | sql/sql_select.cc | 7 |
8 files changed, 107 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2f2b6521c8..cdedc02f825 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7171,4 +7171,20 @@ 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 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 25ef4a76962..a7291297e7c 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7168,6 +7168,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 set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 074874fbd5b..c41fa1be47b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7166,5 +7166,21 @@ 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 set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index de49585b562..1c181357050 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7177,6 +7177,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 set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 46a46c91ddc..89c671252ff 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7166,6 +7166,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 set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e6233e9de78..4e35032a789 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6060,4 +6060,19 @@ 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 diff --git a/sql/item.h b/sql/item.h index d756cf8301b..1bded7377ee 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1234,6 +1234,11 @@ public: { return FALSE; } + 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 6a64a0e9952..5228dd4f439 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1098,6 +1098,13 @@ JOIN::optimize() 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); |