Added the test case from MDEV-16086 tfixed by the patch for MDEV-15575

2 files changed, 64 insertions, 0 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 70752c7200c..d6dfdf1ec6a 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -3201,3 +3201,35 @@ a
 1
 3
 drop table t1;
+#
+# MDEV-16086: tmp table for CTE is created as ARIA tables
+#
+CREATE TABLE t1 (
+Id int(11) not null AUTO_INCREMENT,
+Parent varchar(15) not null,
+Child varchar(15) not null,
+PRIMARY KEY (Id)
+) ENGINE = MyISAM;
+INSERT INTO t1 (Parent, Child) VALUES
+('123', '456'),('456', '789'),('321', '654'),('654', '987');
+WITH RECURSIVE cte AS
+( SELECT b.Parent,
+b.Child,
+CAST(CONCAT(b.Child,',') AS CHAR(513)) Path
+FROM t1 b
+LEFT OUTER JOIN t1 bc ON b.Child = bc.Parent
+WHERE bc.Id IS NULL
+UNION ALL SELECT c.Parent,
+c.Child,
+CONCAT(p.Path,c.Child,',') Path
+FROM t1 c
+INNER JOIN cte p ON c.Child = p.Parent)
+SELECT *
+FROM cte
+ORDER BY Path;
+Parent	Child	Path
+456	789	789,
+123	456	789,456,
+654	987	987,
+321	654	987,654,
+DROP TABLE t1;
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 332a64b30de..3a8795e114a 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2214,3 +2214,35 @@ with recursive qn as
 select * from qn;
 
 drop table t1;
+
+--echo #
+--echo # MDEV-16086: tmp table for CTE is created as ARIA tables
+--echo #
+
+CREATE TABLE t1 (
+  Id int(11) not null AUTO_INCREMENT,
+  Parent varchar(15) not null,
+  Child varchar(15) not null,
+  PRIMARY KEY (Id)
+) ENGINE = MyISAM;
+
+INSERT INTO t1 (Parent, Child) VALUES
+  ('123', '456'),('456', '789'),('321', '654'),('654', '987');
+
+WITH RECURSIVE cte AS
+  ( SELECT b.Parent,
+           b.Child,
+           CAST(CONCAT(b.Child,',') AS CHAR(513)) Path
+   FROM t1 b
+   LEFT OUTER JOIN t1 bc ON b.Child = bc.Parent
+   WHERE bc.Id IS NULL
+   UNION ALL SELECT c.Parent,
+                    c.Child,
+                    CONCAT(p.Path,c.Child,',') Path
+   FROM t1 c
+   INNER JOIN cte p ON c.Child = p.Parent)
+SELECT *
+FROM cte
+ORDER BY Path;
+
+DROP TABLE t1;