1 files changed, 52 insertions, 0 deletions
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test
index 584bde2d75d..006bdaea5ef 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -2400,4 +2400,56 @@ DROP PROCEDURE p1;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-27212: 2-nd execution of PS for select with embedded derived tables
+--echo #             and correlated subquery in select list of outer derived
+--echo #
+create table t1 ( id int, id2 int ) engine=myisam;
+create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam;
+insert into t1 values (3, 2), (4, 2), (3, 4);
+insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1);
+
+let $q=
+select id from t1
+               join
+               ( select dt2.x1,
+                        ( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m
+                 from ( select x1 from t2 u where  x3 = 1 ) dt2
+               ) dt
+               on t1.id = dt.x1
+where t1.id2 < dt.m;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+eval create procedure sp1() $q;
+call sp1();
+call sp1();
+
+create view v2 as select x1 from t2 u where  x3 = 1;
+create view v as
+select v2.x1,
+       ( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2;
+
+let $q=
+select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+eval create procedure sp2() $q;
+call sp2();
+call sp2();
+
+drop procedure sp1;
+drop procedure sp2;
+
+drop view v, v2;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests