maria-developers team mailing list archive

Thread
Date
Re: [Commits] b1ddc7d: MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops with UNION in ALL subquery

To: maria-developers@xxxxxxxxxxxxxxxxxxx
From: Sergey Petrunia <sergey@xxxxxxxxxxx>
Date: Wed, 4 May 2016 18:59:31 +0300
Cc: commits@xxxxxxxxxxx
In-reply-to: <56ca283a.0772c20a.d5b22.7db5@mx.google.com>
User-agent: Mutt/1.5.21 (2010-09-15)
Hi Sanja,

I have one comment: the patch introduces JOIN::in_optimize which can have
values of 1) not initialized and 2) false. This is clearly redundant.

Ok to push after the above is addressed.

On Sun, Feb 21, 2016 at 10:12:25PM +0100, OleksandrByelkin wrote:
> revision-id: b1ddc7d546e6b147838af72dd03f86a8b272fdf0 (mariadb-10.1.11-18-gb1ddc7d)
> parent(s): fd8e846a3b049903706267d58e6d8e61eea97df8
> committer: Oleksandr Byelkin
> timestamp: 2016-02-21 22:12:25 +0100
> message:
> 
> 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.
> 
> ---
>  mysql-test/r/derived_view.result              |  6 +++---
>  mysql-test/r/subselect.result                 | 20 +++++++++++++++++---
>  mysql-test/r/subselect_no_exists_to_in.result | 20 +++++++++++++++++---
>  mysql-test/r/subselect_no_mat.result          | 20 +++++++++++++++++---
>  mysql-test/r/subselect_no_opts.result         | 20 +++++++++++++++++---
>  mysql-test/r/subselect_no_scache.result       | 20 +++++++++++++++++---
>  mysql-test/r/subselect_no_semijoin.result     | 20 +++++++++++++++++---
>  mysql-test/r/type_year.result                 |  1 +
>  mysql-test/t/subselect.test                   | 11 +++++++++++
>  sql/item_subselect.cc                         | 20 ++++++++++++++++----
>  sql/sql_select.h                              |  2 ++
>  11 files changed, 135 insertions(+), 25 deletions(-)
> 
> diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
> index 639942f..5783247 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 75c8597..90d8f9f 100644
> --- a/mysql-test/r/subselect.result
> +++ b/mysql-test/r/subselect.result
> @@ -6805,7 +6805,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6817,8 +6819,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7133,3 +7135,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 e6238af..dcceb61 100644
> --- a/mysql-test/r/subselect_no_exists_to_in.result
> +++ b/mysql-test/r/subselect_no_exists_to_in.result
> @@ -6805,7 +6805,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6817,8 +6819,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7133,6 +7135,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 70edc64..4e9f750 100644
> --- a/mysql-test/r/subselect_no_mat.result
> +++ b/mysql-test/r/subselect_no_mat.result
> @@ -6800,7 +6800,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6812,8 +6814,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7126,6 +7128,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 c89fd13..4b09fd2 100644
> --- a/mysql-test/r/subselect_no_opts.result
> +++ b/mysql-test/r/subselect_no_opts.result
> @@ -6796,7 +6796,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6808,8 +6810,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7124,4 +7126,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 b12bf21..322e64d 100644
> --- a/mysql-test/r/subselect_no_scache.result
> +++ b/mysql-test/r/subselect_no_scache.result
> @@ -6811,7 +6811,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6823,8 +6825,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7139,6 +7141,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 54f145d..d380528 100644
> --- a/mysql-test/r/subselect_no_semijoin.result
> +++ b/mysql-test/r/subselect_no_semijoin.result
> @@ -6796,7 +6796,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR alias1.a = 'y'
>  HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
> +1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a ) AS field
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> @@ -6808,8 +6810,8 @@ SELECT MAX( alias2.a )
>  FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
>  WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
> -1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
> +1	PRIMARY	alias1	index	a	a	19	NULL	11	Using index
> +1	PRIMARY	alias2	index	a	a	19	NULL	11	Using where; Using index; Using join buffer (flat, BNL join)
>  1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
>  2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
>  SELECT MAX( alias2.a )
> @@ -7124,5 +7126,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 842a16e..204cec2 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 a862870..f71c215 100644
> --- a/mysql-test/t/subselect.test
> +++ b/mysql-test/t/subselect.test
> @@ -5989,3 +5989,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 d4e3e6c..f45d83d 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 || cur_join->in_optimize)
> +      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())
> diff --git a/sql/sql_select.h b/sql/sql_select.h
> index 9f90473..e57844fd 100644
> --- a/sql/sql_select.h
> +++ b/sql/sql_select.h
> @@ -1292,6 +1292,7 @@ class JOIN :public Sql_alloc
>                                   OPTIMIZATION_IN_PROGRESS=1,
>                                   OPTIMIZATION_DONE=2};
>    bool optimized; ///< flag to avoid double optimization in EXPLAIN
> +  bool in_optimize;
>    bool initialized; ///< flag to avoid double init_execution calls
>  
>    Explain_select *explain;
> @@ -1380,6 +1381,7 @@ class JOIN :public Sql_alloc
>      ref_pointer_array_size= 0;
>      zero_result_cause= 0;
>      optimized= 0;
> +    in_optimize= 0;
>      have_query_plan= QEP_NOT_PRESENT_YET;
>      initialized= 0;
>      cleaned= 0;

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Follow ups

Re: [Commits] b1ddc7d: MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops with UNION in ALL subquery
From: Vicențiu Ciorbaru, 2016-05-04