MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops with UNION in ALL subquery

Do not mark subquery as inexpensive when it is not optimized.

10 files changed, 103 insertions, 7 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 639942f0da9..5783247b673 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1101,7 +1101,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a	b
@@ -1115,7 +1115,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a	b
@@ -1129,7 +1129,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ac874f9943c..6a531997d79 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7147,3 +7147,15 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 6a5bec062c7..aa6843409c0 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7147,6 +7147,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%exists_to_in=off%';
 @@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 a574319b175..754aec1db20 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7140,6 +7140,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 62eecd568f0..e05dd4d140d 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7138,4 +7138,16 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 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 174c791489c..71ade62b423 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7153,6 +7153,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@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 e0d62ac6d14..43d191b1225 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7138,5 +7138,17 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 842a16e3b4a..204cec2cc66 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -387,6 +387,7 @@ a
 00
 select a from t1 where a=(select 2000 from dual where 1);
 a
+00
 select a from t1 where a=y2k();
 a
 00
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7a7c01e78bd..3599b523d91 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6000,3 +6000,14 @@ SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
 
 drop view v2;
 drop table t1,t2;
+
+--echo #
+--echo # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+--echo # with UNION in ALL subquery
+--echo #
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+drop table t1;
+SET NAMES default;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index dea58bf8e0c..1812110b1e6 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -561,22 +561,34 @@ bool Item_subselect::is_expensive()
   for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
   {
     JOIN *cur_join= sl->join;
+
+    /* not optimized subquery */
     if (!cur_join)
-      continue;
+      return true;
+
+    /* very simple subquery */
+    if (!cur_join->tables_list && !sl->first_inner_unit())
+      return false;
+
+    /*
+      If the subquery is not optimised or in the process of optimization
+      it supposed to be expensive
+    */
+    if (!cur_join->optimized)
+      return true;
 
     /*
       Subqueries whose result is known after optimization are not expensive.
       Such subqueries have all tables optimized away, thus have no join plan.
     */
-    if (cur_join->optimized &&
-        (cur_join->zero_result_cause || !cur_join->tables_list))
+    if ((cur_join->zero_result_cause || !cur_join->tables_list))
       return false;
 
     /*
       If a subquery is not optimized we cannot estimate its cost. A subquery is
       considered optimized if it has a join plan.
     */
-    if (!(cur_join->optimized && cur_join->join_tab))
+    if (!cur_join->join_tab)
       return true;
 
     if (sl->first_inner_unit())