Hi Sanja, Sergey, I think Sergey might have mean the same thing but it is not clear to me. in_optimize never gets set to true. By mentioning that it is redundant that means that it can be removed from the current code right? Also, I would put the "return true" conditions after the "return false" conditions. Makes the code slightly easier to follow. Vicentiu On Wed, 4 May 2016 at 18:59 Sergey Petrunia <sergey@mariadb.com> wrote:
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.
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
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
+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
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
+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
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
+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
On Sun, Feb 21, 2016 at 10:12:25PM +0100, OleksandrByelkin wrote: possible_keys key key_len ref rows filtered Extra trigcond(<is_not_null_test>(5)))))))) possible_keys key key_len ref rows filtered Extra trigcond(<is_not_null_test>(5)))))))) possible_keys key key_len ref rows filtered Extra trigcond(<is_not_null_test>(5)))))))) 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp