1 files changed, 47 insertions, 0 deletions
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 9436665bfee..a092a161277 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -882,3 +882,50 @@ union all
   where c1.term is null);
 
 drop table t1,t2;
+
+--echo #
+--echo # MDEV-14969: view using subquery with attached CTE
+--echo #
+
+create table region (
+  r_regionkey int,
+  r_name char(25),
+  primary key (r_regionkey)
+);
+insert into region values
+(0,'AFRICA'), (1,'AMERICA'), (2,'ASIA'), (3,'EUROPE'), (4,'MIDDLE EAST');
+
+create table nation (
+  n_nationkey int,
+  n_name char(25),
+  n_regionkey int,
+  primary key (n_nationkey),
+  key i_n_regionkey (n_regionkey)
+);
+insert into nation values
+(0,'ALGERIA',0), (1,'ARGENTINA',1), (2,'BRAZIL',1), (3,'CANADA',1),
+(4,'EGYPT',4), (5,'ETHIOPIA',0), (6,'FRANCE',3), (7,'GERMANY',3),
+(8,'INDIA',2), (9,'INDONESIA',2), (10,'IRAN',4), (11,'IRAQ',4),
+(12,'JAPAN',2), (13,'JORDAN',4), (14,'KENYA',0), (15,'MOROCCO',0),
+(16,'MOZAMBIQUE',0), (17,'PERU',1), (18,'CHINA',2), (19,'ROMANIA',3),
+(20,'SAUDI ARABIA',4), (21,'VIETNAM',2), (22,'RUSSIA',3),
+(23,'UNITED KINGDOM',3), (24,'UNITED STATES',1);
+
+select * from nation n ,region r
+  where n.n_regionkey = r.r_regionkey and
+        r.r_regionkey in
+        (with t as (select * from region where r_regionkey <= 3 )
+           select r_regionkey from t where r_name <> "ASIA");
+
+create view v as
+select * from nation n ,region r
+  where n.n_regionkey = r.r_regionkey and
+        r.r_regionkey in
+        (with t as (select * from region where r_regionkey <= 3)
+           select r_regionkey from t where r_name <> "ASIA");
+
+show create view v;
+select * from v;
+
+drop view v;
+drop table region, nation;