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