diff options
| -rw-r--r-- | mysql-test/r/cte_recursive.result | 170 | ||||
| -rw-r--r-- | mysql-test/t/cte_recursive.test | 70 | ||||
| -rw-r--r-- | sql/sql_cte.cc | 1 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 6 | ||||
| -rw-r--r-- | sql/sql_union.cc | 13 |
5 files changed, 247 insertions, 13 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3fa8ab..640493142bd 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f065e..d1904586ffe 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a7551..fe8e0de71b4 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8e815..39499e6895f 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b03f2..b2198eb2b31 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { |