Merge 10.2 into 10.3

1 files changed, 32 insertions, 0 deletions

diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 0647e6c15c3..9d7f23a2258 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2215,6 +2215,38 @@ 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;
+
 --echo # Start of 10.3 tests
 
 --echo #