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().

9 files changed, 123 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 79b2810d529..b074fb371a5 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7211,4 +7211,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_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 7b2054f4a67..d5aa16a2ce9 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7211,6 +7211,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 '%exists_to_in=off%';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 51c5263ad8a..aff68bd6729 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7204,6 +7204,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 574af7b4d1f..f1181785a5c 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7202,5 +7202,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 03285cf2968..6cefce21c20 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7217,6 +7217,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 6669c27ede7..884374a74b1 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7202,6 +7202,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 8c9a4893be2..390d36361b2 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6096,4 +6096,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 b111d74057c..7c95239b242 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1275,6 +1275,11 @@ public:
   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 43b8d2b8636..19a727f696b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1207,6 +1207,13 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S
   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);