diff options
author | Igor Babaev <igor@askmonty.org> | 2018-05-08 23:32:11 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2018-05-08 23:32:29 -0700 |
commit | fc0f5adb7fe181906f48c84f0e6d07e4973740f2 (patch) | |
tree | ffdd859430fabf4b823b4e4dd1cef31638a64814 /mysql-test/main | |
parent | 2deb17fd5422dda1ed7b70817f2225682419e32c (diff) | |
download | mariadb-git-fc0f5adb7fe181906f48c84f0e6d07e4973740f2.tar.gz |
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.
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.result | 33 | ||||
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.test | 23 |
2 files changed, 56 insertions, 0 deletions
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 5a496d2bd4e..326e56b222f 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15164,3 +15164,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 2e2ec69520d..596641299ae 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; |