[Commits] 83b46d0e18d: MDEV-18255: Server crashes in Bitmap<64u>::intersect
revision-id: 83b46d0e18da954033cfe7ba807ef6a6a8bbf257 (mariadb-10.0.37-41-g83b46d0e18d) parent(s): d0d0f88f2cd4da23c2c2da702da51fb533e7fb8a author: Varun Gupta committer: Varun Gupta timestamp: 2019-01-19 20:12:00 +0530 message: MDEV-18255: Server crashes in Bitmap<64u>::intersect The server crashes here because we try to update condition comprising of a non-merged semi-join which was cleaned up due to the IMPOSSIBLE WHERE in the parent. So the approach to fix this is for a certain select $X: at the end of its JOIN::optimize() call we call JOIN::optimize_unflattened_subqueries (this causes children of select $X be optimized) then for the current select we update_used_tables() inside JOIN::optimize_unflattened_subqueries Updated few test results --- mysql-test/r/subselect.result | 6 +++--- mysql-test/r/subselect_mat.result | 18 +++++++++++++++++- mysql-test/r/subselect_no_exists_to_in.result | 6 +++--- mysql-test/r/subselect_no_mat.result | 6 +++--- mysql-test/r/subselect_no_opts.result | 6 +++--- mysql-test/r/subselect_no_scache.result | 6 +++--- mysql-test/r/subselect_no_semijoin.result | 6 +++--- mysql-test/r/subselect_sj.result | 2 +- mysql-test/r/subselect_sj_jcl6.result | 2 +- mysql-test/r/subselect_sj_mat.result | 2 +- mysql-test/t/subselect_mat.test | 13 +++++++++++++ sql/opt_subselect.cc | 6 +++++- sql/sql_lex.cc | 1 - 13 files changed, 56 insertions(+), 24 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index b074fb371a5..dbfc33639be 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4584,7 +4584,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6656,7 +6656,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6664,7 +6664,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index aa0ac73abd2..cceda9f8599 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1896,7 +1896,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (0 or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b @@ -2822,3 +2822,19 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 ); f DROP TABLE t1, t2; +# +# MDEV-18255: Server crashes in Bitmap<64u>::intersect +# +create table t1 (v1 varchar(1)) engine=myisam ; +create table t2 (v1 varchar(1)) engine=myisam ; +explain +select 1 from t1 where exists +(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +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 no matching row in const table +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +select 1 from t1 where exists +(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +1 +drop table t1,t2; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index d5aa16a2ce9..664ffe015d1 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -4586,7 +4586,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6656,7 +6656,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6664,7 +6664,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index aff68bd6729..1ad808a7e8a 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -4584,7 +4584,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6651,7 +6651,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6659,7 +6659,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index f1181785a5c..d33d561bc3d 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -4580,7 +4580,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6647,7 +6647,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6655,7 +6655,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 6cefce21c20..e2762cde548 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -4590,7 +4590,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6662,7 +6662,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6670,7 +6670,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 884374a74b1..d812b84cbd9 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -4580,7 +4580,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6647,7 +6647,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6655,7 +6655,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index fd9a66d8ef1..6f94b6236aa 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3132,7 +3132,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1 +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and 0)) where 1 # mdev-12820 SELECT * FROM t1 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 71493df594f..e8a7b789bb8 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3146,7 +3146,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1 +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and 0)) where 1 # mdev-12820 SELECT * FROM t1 diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index b48be32441a..2cb99ae450b 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1934,7 +1934,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (0 or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test index 5211f35b48b..66a6cc97acb 100644 --- a/mysql-test/t/subselect_mat.test +++ b/mysql-test/t/subselect_mat.test @@ -267,3 +267,16 @@ explain SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 ); SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 ); DROP TABLE t1, t2; + +--echo # +--echo # MDEV-18255: Server crashes in Bitmap<64u>::intersect +--echo # +create table t1 (v1 varchar(1)) engine=myisam ; +create table t2 (v1 varchar(1)) engine=myisam ; + +explain +select 1 from t1 where exists + (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +select 1 from t1 where exists + (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +drop table t1,t2; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f757823be7c..8a8e390ade3 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5298,7 +5298,11 @@ static void remove_subq_pushed_predicates(JOIN *join, Item **where) bool JOIN::optimize_unflattened_subqueries() { - return select_lex->optimize_unflattened_subqueries(false); + bool val= select_lex->optimize_unflattened_subqueries(false); + if (val) + return val; + select_lex->update_used_tables(); + return false; } /** diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 08c169c5999..891cf9987c6 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3551,7 +3551,6 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) inner_join->select_options|= SELECT_DESCRIBE; } res= inner_join->optimize(); - sl->update_used_tables(); sl->update_correlated_cache(); is_correlated_unit|= sl->is_correlated; inner_join->select_options= save_options;
participants (1)
-
Varun