Merge 10.2 into 10.3

1 files changed, 163 insertions, 7 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 0eace3d21a0..0dfc2df7b61 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/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(h_id,w_id) as (/* select#2 */ 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(id,name,dob,father,mother) as (/* select#3 */ 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#4 */ 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#5 */ 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#1 */ 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
@@ -3370,13 +3370,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(a) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ 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#5 */ 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#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2`
 prepare stmt from "with recursive
@@ -3435,6 +3435,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(a) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ 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#5 */ 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#1 */ 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;
 #
@@ -3853,7 +3912,104 @@ 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	5	const	0	0.00	
+1	PRIMARY	<derived4>	ref	key0	key0	5	const	0	0.00	
+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#2 */ 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#3 */ 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#4 */ 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#5 */ 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#1 */ select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where `cte3`.`YEAR` = 2018 and `cte2`.`YEAR` = 2018
+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
+#
 #
 # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
 #