revision-id: 0efb1e502f2b77ae483cc7894beaeaab575e9b6f (mariadb-10.3.6-112-g0efb1e5) parent(s): 0bfd45f63419c45b68448cbd210a6e73e5c1ab34 author: Igor Babaev committer: Igor Babaev timestamp: 2018-05-08 23:32:11 -0700 message: MDEV-16104 Server crash in JOIN::fix_all_splittings_in_plan upon select with view and subqueries This bug occurred when a splittable materialized derived/view were used inside another splittable materialized derived/view. The bug happened because the function JOIN::fix_all_splittings_in_plan() was called at the very beginning of the optimization phase 2 at the moment when the plan structure of the embedding derived/view were not valid. The proper position for this call is the very end of the optimization phase 1. --- mysql-test/main/derived_cond_pushdown.result | 33 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 23 +++++++++++++++++++ sql/sql_select.cc | 15 ++++++++++--- 3 files changed, 68 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 82f621c..e178ceb 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15165,3 +15165,36 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort drop table t1; +# +# MDEV-16104: embedded splittable materialized derived/views +# +CREATE TABLE t1 (f int PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 +VALUES (3), (7), (1), (4), (8), (5), (9); +CREATE ALGORITHM=MERGE VIEW v1 AS +SELECT a2.* +FROM +( SELECT f, COUNT(*) as c FROM t1 GROUP BY f ) AS a1 +JOIN +t1 AS a2 +USING (f); +EXPLAIN EXTENDED +SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00 +2 DERIVED <derived4> ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort +2 DERIVED a2 eq_ref PRIMARY PRIMARY 4 a1.f 1 100.00 Using index +4 DERIVED t1 index PRIMARY PRIMARY 4 NULL 7 100.00 Using index; Using temporary; Using filesort +Warnings: +Note 1003 /* select#1 */ select `s`.`f` AS `f`,`s`.`c` AS `c` from (/* select#2 */ select straight_join `a2`.`f` AS `f`,count(0) AS `c` from ((/* select#4 */ select `test`.`t1`.`f` AS `f`,count(0) AS `c` from `test`.`t1` group by `test`.`t1`.`f`)) `a1` join `test`.`t1` `a2` where `a2`.`f` = `a1`.`f` group by `a2`.`f`) `s` +SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; +f c +1 1 +3 1 +4 1 +5 1 +7 1 +8 1 +9 1 +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 2e2ec69..5966412 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2690,3 +2690,26 @@ eval $q; eval explain $q; drop table t1; + +--echo # +--echo # MDEV-16104: embedded splittable materialized derived/views +--echo # + +CREATE TABLE t1 (f int PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 + VALUES (3), (7), (1), (4), (8), (5), (9); + +CREATE ALGORITHM=MERGE VIEW v1 AS +SELECT a2.* +FROM + ( SELECT f, COUNT(*) as c FROM t1 GROUP BY f ) AS a1 + JOIN + t1 AS a2 + USING (f); + +EXPLAIN EXTENDED +SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; +SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; + +DROP VIEW v1; +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 320e631..c45edf8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1852,6 +1852,18 @@ JOIN::optimize_inner() DBUG_RETURN(1); } + /* + If a splittable materialized derived/view dt_i is embedded into + into another splittable materialized derived/view dt_o then + splitting plans for dt_i and dt_o are evaluated independently. + First the optimizer looks for the best splitting plan sp_i for dt_i. + It happens when non-splitting plans for dt_o are evaluated. + The cost of sp_i is considered as the cost of materialization of dt_i + when evaluating any splitting plan for dt_o. + */ + if (fix_all_splittings_in_plan()) + DBUG_RETURN(1); + setup_subq_exit: with_two_phase_optimization= check_two_phase_optimization(thd); if (with_two_phase_optimization) @@ -9370,9 +9382,6 @@ bool JOIN::get_best_combination() full_join=0; hash_join= FALSE; - if (fix_all_splittings_in_plan()) - DBUG_RETURN(TRUE); - fix_semijoin_strategies_for_picked_join_order(this); JOIN_TAB_RANGE *root_range;