Merge branch '10.3' into 10.4

1 files changed, 43 insertions, 0 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 1441989e2cc..152a130b8af 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -1,3 +1,4 @@
+--source include/have_sequence.inc
 --source include/default_optimizer_switch.inc
 let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_derived=off' for;
 set @@join_buffer_size=256*1024;
@@ -3328,6 +3329,48 @@ set join_cache_level=default;
 
 DROP TABLE t1,t2;
 
+
+--echo #
+--echo # MDEV-21614: potentially splittable materialized derived/view
+--echo #             within materialized semi-join
+--echo #
+
+create table t1 (
+  id int not null auto_increment primary key,
+  a int not null
+) engine=myisam;
+
+create table t2 (
+  id int not null auto_increment primary key,
+  ro_id int not null,
+  flag int not null, key (ro_id)
+) engine=myisam;
+
+insert into t1(a) select seq+100 from seq_1_to_20;
+insert into t2(ro_id,flag) select seq, 1 from seq_1_to_20;
+insert into t2(ro_id,flag) select seq, 0 from seq_1_to_20;
+
+create view v1 as
+select t1.* from t1 left join t2 on (t1.id = t2.ro_id AND t2.flag = 1)
+  group by t1.id;
+
+let $q1=
+select id, a from t1 where  id in (select id from v1);
+eval $q1;
+eval explain extended $q1;
+
+let $q2=
+select id, a from t1
+  where id in (select id
+               from (select t1.* from t1 left join t2
+                                      on (t1.id = t2.ro_id AND t2.flag = 1)
+                     group by t1.id) dt);
+eval $q2;
+eval explain extended $q2;
+
+drop view v1;
+drop table t1,t2;
+
 --echo # End of 10.3 tests
 
 --echo #