MDEV-15556 MariaDB crash with big_tables=1 and CTE

This bug manifested itself when the optimizer chose an execution plan with
an access of the recursive CTE in a recursive query by key and ARIA/MYISAM
temporary tables were used to store recursive tables.
The problem appeared due to passing an incorrect parameter to the call of
instantiate_tmp_table() in the function With_element::instantiate_tmp_tables().

1 files changed, 87 insertions, 1 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 01443bb6494..e1a52be5a6b 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -3081,7 +3081,7 @@ a
 130
 set big_tables=default;
 #
-# MDEV-1571: Setting user variable in recursive CTE
+# MDEV-15571: using recursive cte with big_tables enabled
 #
 set big_tables=1;
 with recursive qn as
@@ -3093,3 +3093,89 @@ select a*2000 from qn where a<10000000000000000000
 select * from qn;
 ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000'
 set big_tables=default;
+#
+# MDEV-15556: using recursive cte with big_tables enabled
+#             when recursive tables are accessed by key
+#
+SET big_tables=1;
+CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);
+INSERT INTO t1 VALUES
+(1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7),
+(6, "LLLA", NULL, NULL), (7, "RLLA", NULL, NULL), (5, "RLA", 8, 9),
+(8, "LRLA", NULL, NULL), (9, "RRLA", NULL, NULL), (3, "RA", 10, 11),
+(10, "LRA", 12, 13), (11, "RRA", 14, 15), (15, "RRRA", NULL, NULL),
+(16, "B", 17, 18), (17, "LB", NULL, NULL), (18, "RB", NULL, NULL);
+CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();
+WITH RECURSIVE tree_of_a AS
+(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+    UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id)
+FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id)
+FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar)
+SELECT * FROM tree_of_a
+ORDER BY path;
+id	name	leftpar	rightpar	path
+1	A	2	3	1
+2	LA	4	5	1,2
+4	LLA	6	7	1,2,4
+6	LLLA	NULL	NULL	1,2,4,6
+7	RLLA	NULL	NULL	1,2,4,7
+5	RLA	8	9	1,2,5
+8	LRLA	NULL	NULL	1,2,5,8
+9	RRLA	NULL	NULL	1,2,5,9
+3	RA	10	11	1,3
+10	LRA	12	13	1,3,10
+11	RRA	14	15	1,3,11
+15	RRRA	NULL	NULL	1,3,11,15
+EXPLAIN WITH RECURSIVE tree_of_a AS
+(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+    UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id)
+FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id)
+FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar)
+SELECT * FROM tree_of_a
+ORDER BY path;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	
+4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	Using where
+4	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	
+NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+DROP TABLE t1,t2;
+SET big_tables=0;
+#
+# MDEV-15840: recursive tables are accessed by key
+#             (the same problem as for MDEV-15556)
+#
+CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int);
+INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000;
+CREATE PROCEDURE getNums()
+BEGIN
+WITH RECURSIVE cte as
+(
+SELECT * FROM t1
+UNION
+SELECT c.* FROM t1 c JOIN cte p ON c.p1 = p.p2 AND c.k2 = p.k1
+)
+SELECT * FROM cte LIMIT 10;
+END |
+call getNums();
+p1	k2	p2	k1
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+5	5	5	5
+6	6	6	6
+7	7	7	7
+8	8	8	8
+9	9	9	9
+10	10	10	10
+DROP PROCEDURE getNums;
+DROP TABLE t1;