summaryrefslogtreecommitdiff
path: root/mysql-test/main/derived_cond_pushdown.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
-rw-r--r--mysql-test/main/derived_cond_pushdown.test43
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 #