From 8d7462ec499812557d0a0eff2ad1792927cb80ab Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 7 Feb 2020 19:42:11 -0800 Subject: MDEV-21614 Wrong query results with optimizer_switch="split_materialized=on" Do not materialize a semi-join nest if it contains a materialized derived table /view that potentially can be subject to the split optimization. Splitting of materialization of such nest would help, but currently there is no code to support this technique. --- mysql-test/main/derived_cond_pushdown.test | 43 ++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'mysql-test/main/derived_cond_pushdown.test') diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index e3a7306f7a0..525122e6cd7 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,4 +3329,46 @@ 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 -- cgit v1.2.1