1set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); 2set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 3set @optimizer_switch_local_default= @@optimizer_switch; 4set @save_join_cache_level=@@join_cache_level; 5set join_cache_level=1; 6drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; 7drop table if exists columns; 8drop table if exists t1_16, t2_16, t3_16; 9drop view if exists v1, v2, v1m, v2m; 10create table t1 (a1 char(8), a2 char(8)); 11create table t2 (b1 char(8), b2 char(8)); 12create table t3 (c1 char(8), c2 char(8)); 13insert into t1 values ('1 - 00', '2 - 00'); 14insert into t1 values ('1 - 01', '2 - 01'); 15insert into t1 values ('1 - 02', '2 - 02'); 16insert into t2 values ('1 - 01', '2 - 01'); 17insert into t2 values ('1 - 01', '2 - 01'); 18insert into t2 values ('1 - 02', '2 - 02'); 19insert into t2 values ('1 - 02', '2 - 02'); 20insert into t2 values ('1 - 03', '2 - 03'); 21insert into t3 values ('1 - 01', '2 - 01'); 22insert into t3 values ('1 - 02', '2 - 02'); 23insert into t3 values ('1 - 03', '2 - 03'); 24insert into t3 values ('1 - 04', '2 - 04'); 25create table t1i (a1 char(8), a2 char(8)); 26create table t2i (b1 char(8), b2 char(8)); 27create table t3i (c1 char(8), c2 char(8)); 28create index it1i1 on t1i (a1); 29create index it1i2 on t1i (a2); 30create index it1i3 on t1i (a1, a2); 31create index it2i1 on t2i (b1); 32create index it2i2 on t2i (b2); 33create index it2i3 on t2i (b1, b2); 34create index it3i1 on t3i (c1); 35create index it3i2 on t3i (c2); 36create index it3i3 on t3i (c1, c2); 37insert into t1i select * from t1; 38insert into t2i select * from t2; 39insert into t3i select * from t3; 40analyze table t1,t2,t3,t1i,t2i,t3i; 41Table Op Msg_type Msg_text 42test.t1 analyze status Engine-independent statistics collected 43test.t1 analyze status OK 44test.t2 analyze status Engine-independent statistics collected 45test.t2 analyze status OK 46test.t3 analyze status Engine-independent statistics collected 47test.t3 analyze status OK 48test.t1i analyze status Engine-independent statistics collected 49test.t1i analyze status Table is already up to date 50test.t2i analyze status Engine-independent statistics collected 51test.t2i analyze status Table is already up to date 52test.t3i analyze status Engine-independent statistics collected 53test.t3i analyze status Table is already up to date 54set @@optimizer_switch='materialization=on,in_to_exists=off,firstmatch=off'; 55/****************************************************************************** 56* Simple tests. 57******************************************************************************/ 58# non-indexed nullable fields 59explain extended 60select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 61id select_type table type possible_keys key key_len ref rows filtered Extra 621 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 631 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 100.00 642 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where 65Warnings: 66Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b1` > '0' 67select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 68a1 a2 691 - 01 2 - 01 701 - 02 2 - 02 71explain extended 72select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 73id select_type table type possible_keys key key_len ref rows filtered Extra 741 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 751 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 100.00 762 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where 77Warnings: 78Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b1` > '0' 79select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 80a1 a2 811 - 01 2 - 01 821 - 02 2 - 02 83explain extended 84select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 85id select_type table type possible_keys key key_len ref rows filtered Extra 861 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 871 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 882 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where 89Warnings: 90Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b1` > '0' 91select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 92a1 a2 931 - 01 2 - 01 941 - 02 2 - 02 95explain extended 96select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 97id select_type table type possible_keys key key_len ref rows filtered Extra 981 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 991 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 1002 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where; Using temporary 101Warnings: 102Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`b1` > '0' group by `test`.`t2`.`b1`) join `test`.`t1` where `<subquery2>`.`b1` = `test`.`t1`.`a1` and `<subquery2>`.`min(b2)` = `test`.`t1`.`a2` 103select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 104a1 a2 1051 - 01 2 - 01 1061 - 02 2 - 02 107explain extended 108select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 109id select_type table type possible_keys key key_len ref rows filtered Extra 1101 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan 1111 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 112Warnings: 113Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` > '0' 114select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 115a1 a2 1161 - 01 2 - 01 1171 - 02 2 - 02 118explain extended 119select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); 120id select_type table type possible_keys key key_len ref rows filtered Extra 1211 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # 1221 PRIMARY <subquery2> eq_ref distinct_key # 8 # 1 100.00 # 1232 MATERIALIZED t2i range it2i1,it2i3 # 9 # 5 100.00 # 124Warnings: 125Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (/* select#2 */ select max(`test`.`t2i`.`b1`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1i` where `<subquery2>`.`max(b1)` = `test`.`t1i`.`a1` 126select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); 127a1 a2 1281 - 01 2 - 01 1291 - 02 2 - 02 130explain extended 131select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 132id select_type table type possible_keys key key_len ref rows filtered Extra 1331 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan 1341 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 135Warnings: 136Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' 137select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 138a1 a2 1391 - 01 2 - 01 1401 - 02 2 - 02 141explain extended 142select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); 143id select_type table type possible_keys key key_len ref rows filtered Extra 1441 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1451 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 1462 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # 147Warnings: 148Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1i` where `<subquery2>`.`b1` = `test`.`t1i`.`a1` and `<subquery2>`.`max(b2)` = `test`.`t1i`.`a2` 149select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); 150a1 a2 1511 - 01 2 - 01 1521 - 02 2 - 02 153explain extended 154select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 155id select_type table type possible_keys key key_len ref rows filtered Extra 1561 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1571 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 1582 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # 159Warnings: 160Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1i` where `<subquery2>`.`b1` = `test`.`t1i`.`a1` and `<subquery2>`.`min(b2)` = `test`.`t1i`.`a2` 161select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 162a1 a2 1631 - 01 2 - 01 1641 - 02 2 - 02 165create table t2i_c like t2i; 166insert into t2i_c select * from t2i; 167insert into t2i_c select * from t2i; 168insert into t2i_c select * from t2i; 169insert into t2i_c select * from t2i; 170analyze table t2i_c; 171Table Op Msg_type Msg_text 172test.t2i_c analyze status Engine-independent statistics collected 173test.t2i_c analyze status OK 174show create table t2i_c; 175Table Create Table 176t2i_c CREATE TABLE `t2i_c` ( 177 `b1` char(8) DEFAULT NULL, 178 `b2` char(8) DEFAULT NULL, 179 KEY `it2i1` (`b1`), 180 KEY `it2i2` (`b2`), 181 KEY `it2i3` (`b1`,`b2`) 182) ENGINE=MyISAM DEFAULT CHARSET=latin1 183explain extended 184select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); 185id select_type table type possible_keys key key_len ref rows filtered Extra 1861 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1871 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 1882 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 100.00 Using index for group-by 189Warnings: 190Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i_c`.`b1`,max(`test`.`t2i_c`.`b2`) from `test`.`t2i_c` group by `test`.`t2i_c`.`b1`) join `test`.`t1` where `<subquery2>`.`b1` = `test`.`t1`.`a1` and `<subquery2>`.`max(b2)` = `test`.`t1`.`a2` 191select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); 192a1 a2 1931 - 01 2 - 01 1941 - 02 2 - 02 195prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; 196execute st1; 197id select_type table type possible_keys key key_len ref rows Extra 1981 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1991 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2002 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by 201execute st1; 202id select_type table type possible_keys key key_len ref rows Extra 2031 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2041 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2052 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by 206prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; 207execute st2; 208a1 a2 2091 - 01 2 - 01 2101 - 02 2 - 02 211execute st2; 212a1 a2 2131 - 01 2 - 01 2141 - 02 2 - 02 215drop table t2i_c; 216explain extended 217select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 218id select_type table type possible_keys key key_len ref rows filtered Extra 2191 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2201 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2212 MATERIALIZED t2i range it2i1,it2i3 it2i3 9 NULL 5 100.00 Using where; Using index 222Warnings: 223Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1` where `<subquery2>`.`b1` = `test`.`t1`.`a1` and `<subquery2>`.`min(b2)` = `test`.`t1`.`a2` 224select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 225a1 a2 2261 - 01 2 - 01 2271 - 02 2 - 02 228select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); 229ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 230set @local_optimizer_switch=@@optimizer_switch; 231set @@optimizer_switch=@optimizer_switch_local_default; 232set @@optimizer_switch='semijoin=off'; 233prepare st1 from 234"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 235set @@optimizer_switch=@optimizer_switch_local_default; 236set @@optimizer_switch='materialization=off,in_to_exists=on'; 237execute st1; 238a1 a2 2391 - 01 2 - 01 2401 - 02 2 - 02 241set @@optimizer_switch=@optimizer_switch_local_default; 242set @@optimizer_switch='semijoin=off'; 243execute st1; 244a1 a2 2451 - 01 2 - 01 2461 - 02 2 - 02 247set @@optimizer_switch=@optimizer_switch_local_default; 248set @@optimizer_switch='materialization=off,in_to_exists=on'; 249prepare st1 from 250"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 251set @@optimizer_switch=@optimizer_switch_local_default; 252set @@optimizer_switch='semijoin=off'; 253execute st1; 254a1 a2 2551 - 01 2 - 01 2561 - 02 2 - 02 257set @@optimizer_switch=@optimizer_switch_local_default; 258set @@optimizer_switch='materialization=off,in_to_exists=on'; 259execute st1; 260a1 a2 2611 - 01 2 - 01 2621 - 02 2 - 02 263set @@optimizer_switch=@local_optimizer_switch; 264explain extended 265select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 266id select_type table type possible_keys key key_len ref rows filtered Extra 2671 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 2681 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 2692 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 270Warnings: 271Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where 1 272select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 273a1 a2 2741 - 01 2 - 01 2751 - 02 2 - 02 276explain extended 277select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 278id select_type table type possible_keys key key_len ref rows filtered Extra 2791 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 2801 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 test.t2i.b1,test.t2i.b2 1 100.00 Using index 281Warnings: 282Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` 283select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 284a1 a2 2851 - 01 2 - 01 2861 - 02 2 - 02 287/****************************************************************************** 288* Views, UNIONs, several levels of nesting. 289******************************************************************************/ 290# materialize the result of subquery over temp-table view 291create algorithm=merge view v1 as 292select b1, c2 from t2, t3 where b2 > c2; 293create algorithm=merge view v2 as 294select b1, c2 from t2, t3 group by b2, c2; 295Warnings: 296Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 297create algorithm=temptable view v1m as 298select b1, c2 from t2, t3 where b2 > c2; 299create algorithm=temptable view v2m as 300select b1, c2 from t2, t3 group by b2, c2; 301select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null); 302b1 c2 3031 - 02 2 - 01 3041 - 02 2 - 01 3051 - 03 2 - 01 3061 - 03 2 - 02 307select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null); 308b1 c2 3091 - 02 2 - 01 3101 - 02 2 - 01 3111 - 03 2 - 01 3121 - 03 2 - 02 313select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null); 314b1 c2 3151 - 02 2 - 01 3161 - 02 2 - 01 3171 - 03 2 - 01 3181 - 03 2 - 02 319select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null); 320b1 c2 3211 - 02 2 - 01 3221 - 02 2 - 01 3231 - 03 2 - 01 3241 - 03 2 - 02 325drop view v1, v2, v1m, v2m; 326explain extended 327select * from t1 328where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 329(a1, a2) in (select c1, c2 from t3 330where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 331id select_type table type possible_keys key key_len ref rows filtered Extra 3321 PRIMARY t1 ALL NULL NULL NULL NULL 3 98.44 3331 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3341 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3353 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 98.44 Using where 3363 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 3372 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 98.44 Using where 338Warnings: 339Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t2`.`b1` > '0' and `test`.`t3`.`c2` > '0' 340select * from t1 341where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 342(a1, a2) in (select c1, c2 from t3 343where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 344a1 a2 3451 - 01 2 - 01 3461 - 02 2 - 02 347explain extended 348select * from t1i 349where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 350(a1, a2) in (select c1, c2 from t3i 351where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 352id select_type table type possible_keys key key_len ref rows filtered Extra 3531 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 3541 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 3551 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 3561 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 357Warnings: 358Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t3i`.`c1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t3i`.`c2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' and `test`.`t2i`.`b2` > '0' 359select * from t1i 360where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 361(a1, a2) in (select c1, c2 from t3i 362where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 363a1 a2 3641 - 01 2 - 01 3651 - 02 2 - 02 366explain extended 367select * from t1 368where (a1, a2) in (select b1, b2 from t2 369where b2 in (select c2 from t3 where c2 LIKE '%02') or 370b2 in (select c2 from t3 where c2 LIKE '%03')) and 371(a1, a2) in (select c1, c2 from t3 372where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 373id select_type table type possible_keys key key_len ref rows filtered Extra 3741 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 3751 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3761 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3775 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 3785 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 3792 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where 3804 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3813 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 382Warnings: 383Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%02' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery3>`.`c2`)))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3`.`c2` > '0' 384select * from t1 385where (a1, a2) in (select b1, b2 from t2 386where b2 in (select c2 from t3 where c2 LIKE '%02') or 387b2 in (select c2 from t3 where c2 LIKE '%03')) and 388(a1, a2) in (select c1, c2 from t3 389where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 390a1 a2 3911 - 02 2 - 02 392explain extended 393select * from t1 394where (a1, a2) in (select b1, b2 from t2 395where b2 in (select c2 from t3 t3a where c1 = a1) or 396b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 397(a1, a2) in (select c1, c2 from t3 t3c 398where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 399id select_type table type possible_keys key key_len ref rows filtered Extra 4001 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 4011 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4021 PRIMARY t2 ALL NULL NULL NULL NULL 5 99.22 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4035 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 99.22 Using where 4045 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 1 100.00 Using index 4054 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 4063 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 407Warnings: 408Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 409Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2i`.`b1` = `test`.`t3c`.`c1` and `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3c`.`c2` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3c`.`c2` > '0' 410select * from t1 411where (a1, a2) in (select b1, b2 from t2 412where b2 in (select c2 from t3 t3a where c1 = a1) or 413b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 414(a1, a2) in (select c1, c2 from t3 t3c 415where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 416a1 a2 4171 - 01 2 - 01 4181 - 02 2 - 02 419explain extended 420(select * from t1 421where (a1, a2) in (select b1, b2 from t2 422where b2 in (select c2 from t3 where c2 LIKE '%02') or 423b2 in (select c2 from t3 where c2 LIKE '%03') 424group by b1, b2) and 425(a1, a2) in (select c1, c2 from t3 426where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 427UNION 428(select * from t1i 429where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 430(a1, a2) in (select c1, c2 from t3i 431where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 432id select_type table type possible_keys key key_len ref rows filtered Extra 4331 PRIMARY t1 ALL NULL # # # 3 99.22 # 4341 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # 4351 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 4365 MATERIALIZED t3 ALL NULL # # # 4 99.22 # 4375 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 4382 MATERIALIZED t2 ALL NULL # # # 5 99.22 # 4394 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 4403 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 4417 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 4427 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 4437 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 4447 UNION t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 445NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # 446Warnings: 447Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%02' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery3>`.`c2`)))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3`.`c2` > '0') union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t3i`.`c1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t3i`.`c2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' and `test`.`t2i`.`b2` > '0') 448(select * from t1 449where (a1, a2) in (select b1, b2 from t2 450where b2 in (select c2 from t3 where c2 LIKE '%02') or 451b2 in (select c2 from t3 where c2 LIKE '%03') 452group by b1, b2) and 453(a1, a2) in (select c1, c2 from t3 454where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 455UNION 456(select * from t1i 457where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 458(a1, a2) in (select c1, c2 from t3i 459where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 460a1 a2 4611 - 02 2 - 02 4621 - 01 2 - 01 463explain extended 464select * from t1 465where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 466(a1, a2) in (select c1, c2 from t3 467where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 468id select_type table type possible_keys key key_len ref rows filtered Extra 4691 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 4701 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4714 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4724 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 4732 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 4743 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 475NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 476Warnings: 477Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' 478select * from t1 479where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 480(a1, a2) in (select c1, c2 from t3 481where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 482a1 a2 4831 - 01 2 - 01 4841 - 02 2 - 02 485explain extended 486select * from t1, t3 487where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 488(c1, c2) in (select c1, c2 from t3 489where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 490a1 = c1; 491id select_type table type possible_keys key key_len ref rows filtered Extra 4921 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4931 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) 4941 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4954 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4964 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 4972 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 4983 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 499NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 500Warnings: 501Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' 502select * from t1, t3 503where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 504(c1, c2) in (select c1, c2 from t3 505where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 506a1 = c1; 507a1 a2 c1 c2 5081 - 01 2 - 01 1 - 01 2 - 01 5091 - 02 2 - 02 1 - 02 2 - 02 510/****************************************************************************** 511* Negative tests, where materialization should not be applied. 512******************************************************************************/ 513# UNION in a subquery 514explain extended 515select * from t3 516where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 517id select_type table type possible_keys key key_len ref rows filtered Extra 5181 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 5192 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 5203 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 521NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 522Warnings: 523Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c1`>(<in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select `test`.`t1`.`a1` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1` union /* select#3 */ select `test`.`t2`.`b1` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))) 524select * from t3 525where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 526c1 c2 5271 - 01 2 - 01 5281 - 02 2 - 02 5291 - 03 2 - 03 530explain extended 531select * from t1 532where (a1, a2) in (select b1, b2 from t2 533where b2 in (select c2 from t3 t3a where c1 = a1) or 534b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 535(a1, a2) in (select c1, c2 from t3 t3c 536where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 537id select_type table type possible_keys key key_len ref rows filtered Extra 5381 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5391 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 1 100.00 Using index; Start temporary 5401 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) 5411 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 5424 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 5433 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 544Warnings: 545Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 546Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 547Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2i`.`b1` = `test`.`t1`.`a1` and `test`.`t3c`.`c1` = `test`.`t1`.`a1` and `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t1`.`a2` and `test`.`t3c`.`c2` = `test`.`t1`.`a2` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) 548explain extended 549select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 550id select_type table type possible_keys key key_len ref rows filtered Extra 5511 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5522 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 553Warnings: 554Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) 555select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 556a1 a2 5571 - 01 2 - 01 558explain extended 559select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 560id select_type table type possible_keys key key_len ref rows filtered Extra 5611 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5622 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 563Warnings: 564Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) 565select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 566a1 a2 5671 - 01 2 - 01 568/****************************************************************************** 569* Subqueries in other uncovered clauses. 570******************************************************************************/ 571/* SELECT clause */ 572select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; 573((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL 5740 5750 5760 577/* GROUP BY clause */ 578create table columns (col int key); 579insert into columns values (1), (2); 580explain extended 581select * from t1 group by (select col from columns limit 1); 582id select_type table type possible_keys key key_len ref rows filtered Extra 5831 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 5842 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 585Warnings: 586Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1) 587select * from t1 group by (select col from columns limit 1); 588a1 a2 5891 - 00 2 - 00 590explain extended 591select * from t1 group by (a1 in (select col from columns)); 592id select_type table type possible_keys key key_len ref rows filtered Extra 5931 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort 5942 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key 595Warnings: 596Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond(<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`))))) 597select * from t1 group by (a1 in (select col from columns)); 598a1 a2 5991 - 00 2 - 00 600Warnings: 601Warning 1292 Truncated incorrect DOUBLE value: '1 - 00' 602Warning 1292 Truncated incorrect DOUBLE value: '1 - 01' 603Warning 1292 Truncated incorrect DOUBLE value: '1 - 02' 604/* ORDER BY clause */ 605explain extended 606select * from t1 order by (select col from columns limit 1); 607id select_type table type possible_keys key key_len ref rows filtered Extra 6081 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 6092 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 610Warnings: 611Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1) 612select * from t1 order by (select col from columns limit 1); 613a1 a2 6141 - 00 2 - 00 6151 - 01 2 - 01 6161 - 02 2 - 02 617/****************************************************************************** 618* Column types/sizes that affect materialization. 619******************************************************************************/ 620/* 621Test that BLOBs are not materialized (except when arguments of some functions). 622*/ 623# force materialization to be always considered 624set @prefix_len = 6; 625set @blob_len = 16; 626set @suffix_len = @blob_len - @prefix_len; 627create table t1_16 (a1 blob(16), a2 blob(16)); 628create table t2_16 (b1 blob(16), b2 blob(16)); 629create table t3_16 (c1 blob(16), c2 blob(16)); 630insert into t1_16 values 631(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 632insert into t1_16 values 633(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 634insert into t1_16 values 635(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 636insert into t2_16 values 637(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 638insert into t2_16 values 639(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 640insert into t2_16 values 641(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 642insert into t3_16 values 643(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 644insert into t3_16 values 645(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 646insert into t3_16 values 647(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 648insert into t3_16 values 649(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 650explain extended select left(a1,7), left(a2,7) 651from t1_16 652where a1 in (select b1 from t2_16 where b1 > '0'); 653id select_type table type possible_keys key key_len ref rows filtered Extra 6541 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 6551 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 656Warnings: 657Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where `test`.`t2_16`.`b1` = `test`.`t1_16`.`a1` and `test`.`t1_16`.`a1` > '0' 658select left(a1,7), left(a2,7) 659from t1_16 660where a1 in (select b1 from t2_16 where b1 > '0'); 661left(a1,7) left(a2,7) 6621 - 01x 2 - 01x 6631 - 02x 2 - 02x 664explain extended select left(a1,7), left(a2,7) 665from t1_16 666where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); 667id select_type table type possible_keys key key_len ref rows filtered Extra 6681 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 6691 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 670Warnings: 671Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where `test`.`t2_16`.`b1` = `test`.`t1_16`.`a1` and `test`.`t2_16`.`b2` = `test`.`t1_16`.`a2` and `test`.`t1_16`.`a1` > '0' 672select left(a1,7), left(a2,7) 673from t1_16 674where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); 675left(a1,7) left(a2,7) 6761 - 01x 2 - 01x 6771 - 02x 2 - 02x 678explain extended select left(a1,7), left(a2,7) 679from t1_16 680where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); 681id select_type table type possible_keys key key_len ref rows filtered Extra 6821 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 6831 PRIMARY <subquery2> eq_ref distinct_key distinct_key 19 func 1 100.00 Using where 6842 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where 685Warnings: 686Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where `test`.`t2_16`.`b1` > '0' and `test`.`t1_16`.`a1` = substr(`test`.`t2_16`.`b1`,1,16) 687select left(a1,7), left(a2,7) 688from t1_16 689where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); 690left(a1,7) left(a2,7) 6911 - 01x 2 - 01x 6921 - 02x 2 - 02x 693explain extended select left(a1,7), left(a2,7) 694from t1_16 695where a1 in (select group_concat(b1) from t2_16 group by b2); 696id select_type table type possible_keys key key_len ref rows filtered Extra 6971 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 6982 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort 699Warnings: 700Note 1003 /* select#1 */ select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` having <cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ','))))) 701select left(a1,7), left(a2,7) 702from t1_16 703where a1 in (select group_concat(b1) from t2_16 group by b2); 704left(a1,7) left(a2,7) 7051 - 01x 2 - 01x 7061 - 02x 2 - 02x 707set @@group_concat_max_len = 256; 708explain extended select left(a1,7), left(a2,7) 709from t1_16 710where a1 in (select group_concat(b1) from t2_16 group by b2); 711id select_type table type possible_keys key key_len ref rows filtered Extra 7121 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 7131 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_16.a1 1 100.00 Using where 7142 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort 715Warnings: 716Note 1003 /* select#1 */ select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where `test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)` 717select left(a1,7), left(a2,7) 718from t1_16 719where a1 in (select group_concat(b1) from t2_16 group by b2); 720left(a1,7) left(a2,7) 7211 - 01x 2 - 01x 7221 - 02x 2 - 02x 723explain extended 724select * from t1 725where concat(a1,'x') IN 726(select left(a1,8) from t1_16 727where (a1, a2) IN 728(select t2_16.b1, t2_16.b2 from t2_16, t2 729where t2.b2 = substring(t2_16.b2,1,6) and 730t2.b1 IN (select c1 from t3 where c2 > '0'))); 731id select_type table type possible_keys key key_len ref rows filtered Extra 7321 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 7331 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; Using join buffer (flat, BNL join) 7341 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 7351 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) 7361 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join) 737Warnings: 738Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2_16` join `test`.`t2` join `test`.`t1_16`) where `test`.`t2`.`b1` = `test`.`t3`.`c1` and `test`.`t2_16`.`b1` = `test`.`t1_16`.`a1` and `test`.`t2_16`.`b2` = `test`.`t1_16`.`a2` and `test`.`t2`.`b2` = substr(`test`.`t1_16`.`a2`,1,6) and `test`.`t3`.`c2` > '0' and concat(`test`.`t1`.`a1`,'x') = left(`test`.`t1_16`.`a1`,8) 739drop table t1_16, t2_16, t3_16; 740set @blob_len = 512; 741set @suffix_len = @blob_len - @prefix_len; 742create table t1_512 (a1 blob(512), a2 blob(512)); 743create table t2_512 (b1 blob(512), b2 blob(512)); 744create table t3_512 (c1 blob(512), c2 blob(512)); 745insert into t1_512 values 746(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 747insert into t1_512 values 748(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 749insert into t1_512 values 750(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 751insert into t2_512 values 752(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 753insert into t2_512 values 754(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 755insert into t2_512 values 756(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 757insert into t3_512 values 758(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 759insert into t3_512 values 760(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 761insert into t3_512 values 762(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 763insert into t3_512 values 764(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 765explain extended select left(a1,7), left(a2,7) 766from t1_512 767where a1 in (select b1 from t2_512 where b1 > '0'); 768id select_type table type possible_keys key key_len ref rows filtered Extra 7691 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 7701 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 771Warnings: 772Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where `test`.`t2_512`.`b1` = `test`.`t1_512`.`a1` and `test`.`t1_512`.`a1` > '0' 773select left(a1,7), left(a2,7) 774from t1_512 775where a1 in (select b1 from t2_512 where b1 > '0'); 776left(a1,7) left(a2,7) 7771 - 01x 2 - 01x 7781 - 02x 2 - 02x 779explain extended select left(a1,7), left(a2,7) 780from t1_512 781where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); 782id select_type table type possible_keys key key_len ref rows filtered Extra 7831 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 7841 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 785Warnings: 786Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where `test`.`t2_512`.`b1` = `test`.`t1_512`.`a1` and `test`.`t2_512`.`b2` = `test`.`t1_512`.`a2` and `test`.`t1_512`.`a1` > '0' 787select left(a1,7), left(a2,7) 788from t1_512 789where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); 790left(a1,7) left(a2,7) 7911 - 01x 2 - 01x 7921 - 02x 2 - 02x 793explain extended select left(a1,7), left(a2,7) 794from t1_512 795where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); 796id select_type table type possible_keys key key_len ref rows filtered Extra 7971 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 7981 PRIMARY <subquery2> eq_ref distinct_key distinct_key 516 func 1 100.00 Using where 7992 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where 800Warnings: 801Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where `test`.`t2_512`.`b1` > '0' and `test`.`t1_512`.`a1` = substr(`test`.`t2_512`.`b1`,1,512) 802select left(a1,7), left(a2,7) 803from t1_512 804where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); 805left(a1,7) left(a2,7) 8061 - 01x 2 - 01x 8071 - 02x 2 - 02x 808explain extended select left(a1,7), left(a2,7) 809from t1_512 810where a1 in (select group_concat(b1) from t2_512 group by b2); 811id select_type table type possible_keys key key_len ref rows filtered Extra 8121 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 8131 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 8142 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort 815Warnings: 816Note 1003 /* select#1 */ select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where `test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)` 817select left(a1,7), left(a2,7) 818from t1_512 819where a1 in (select group_concat(b1) from t2_512 group by b2); 820left(a1,7) left(a2,7) 821Warnings: 822Warning 1260 Row 1 was cut by GROUP_CONCAT() 823Warning 1260 Row 2 was cut by GROUP_CONCAT() 824Warning 1260 Row 3 was cut by GROUP_CONCAT() 825set @@group_concat_max_len = 256; 826explain extended select left(a1,7), left(a2,7) 827from t1_512 828where a1 in (select group_concat(b1) from t2_512 group by b2); 829id select_type table type possible_keys key key_len ref rows filtered Extra 8301 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 8311 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 8322 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort 833Warnings: 834Note 1003 /* select#1 */ select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where `test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)` 835select left(a1,7), left(a2,7) 836from t1_512 837where a1 in (select group_concat(b1) from t2_512 group by b2); 838left(a1,7) left(a2,7) 839Warnings: 840Warning 1260 Row 1 was cut by GROUP_CONCAT() 841Warning 1260 Row 2 was cut by GROUP_CONCAT() 842Warning 1260 Row 3 was cut by GROUP_CONCAT() 843drop table t1_512, t2_512, t3_512; 844set @blob_len = 1024; 845set @suffix_len = @blob_len - @prefix_len; 846create table t1_1024 (a1 blob(1024), a2 blob(1024)); 847create table t2_1024 (b1 blob(1024), b2 blob(1024)); 848create table t3_1024 (c1 blob(1024), c2 blob(1024)); 849insert into t1_1024 values 850(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 851insert into t1_1024 values 852(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 853insert into t1_1024 values 854(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 855insert into t2_1024 values 856(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 857insert into t2_1024 values 858(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 859insert into t2_1024 values 860(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 861insert into t3_1024 values 862(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 863insert into t3_1024 values 864(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 865insert into t3_1024 values 866(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 867insert into t3_1024 values 868(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 869explain extended select left(a1,7), left(a2,7) 870from t1_1024 871where a1 in (select b1 from t2_1024 where b1 > '0'); 872id select_type table type possible_keys key key_len ref rows filtered Extra 8731 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 8741 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 875Warnings: 876Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where `test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1` and `test`.`t1_1024`.`a1` > '0' 877select left(a1,7), left(a2,7) 878from t1_1024 879where a1 in (select b1 from t2_1024 where b1 > '0'); 880left(a1,7) left(a2,7) 8811 - 01x 2 - 01x 8821 - 02x 2 - 02x 883explain extended select left(a1,7), left(a2,7) 884from t1_1024 885where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); 886id select_type table type possible_keys key key_len ref rows filtered Extra 8871 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 8881 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 889Warnings: 890Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where `test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1` and `test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2` and `test`.`t1_1024`.`a1` > '0' 891select left(a1,7), left(a2,7) 892from t1_1024 893where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); 894left(a1,7) left(a2,7) 8951 - 01x 2 - 01x 8961 - 02x 2 - 02x 897explain extended select left(a1,7), left(a2,7) 898from t1_1024 899where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); 900id select_type table type possible_keys key key_len ref rows filtered Extra 9011 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 9021 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 903Warnings: 904Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where `test`.`t2_1024`.`b1` > '0' and `test`.`t1_1024`.`a1` = substr(`test`.`t2_1024`.`b1`,1,1024) 905select left(a1,7), left(a2,7) 906from t1_1024 907where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); 908left(a1,7) left(a2,7) 9091 - 01x 2 - 01x 9101 - 02x 2 - 02x 911explain extended select left(a1,7), left(a2,7) 912from t1_1024 913where a1 in (select group_concat(b1) from t2_1024 group by b2); 914id select_type table type possible_keys key key_len ref rows filtered Extra 9151 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 9161 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 9172 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort 918Warnings: 919Note 1003 /* select#1 */ select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where `test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)` 920select left(a1,7), left(a2,7) 921from t1_1024 922where a1 in (select group_concat(b1) from t2_1024 group by b2); 923left(a1,7) left(a2,7) 924Warnings: 925Warning 1260 Row 1 was cut by GROUP_CONCAT() 926Warning 1260 Row 2 was cut by GROUP_CONCAT() 927Warning 1260 Row 3 was cut by GROUP_CONCAT() 928set @@group_concat_max_len = 256; 929explain extended select left(a1,7), left(a2,7) 930from t1_1024 931where a1 in (select group_concat(b1) from t2_1024 group by b2); 932id select_type table type possible_keys key key_len ref rows filtered Extra 9331 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 9341 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 9352 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort 936Warnings: 937Note 1003 /* select#1 */ select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where `test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)` 938select left(a1,7), left(a2,7) 939from t1_1024 940where a1 in (select group_concat(b1) from t2_1024 group by b2); 941left(a1,7) left(a2,7) 942Warnings: 943Warning 1260 Row 1 was cut by GROUP_CONCAT() 944Warning 1260 Row 2 was cut by GROUP_CONCAT() 945Warning 1260 Row 3 was cut by GROUP_CONCAT() 946drop table t1_1024, t2_1024, t3_1024; 947set @blob_len = 1025; 948set @suffix_len = @blob_len - @prefix_len; 949create table t1_1025 (a1 blob(1025), a2 blob(1025)); 950create table t2_1025 (b1 blob(1025), b2 blob(1025)); 951create table t3_1025 (c1 blob(1025), c2 blob(1025)); 952insert into t1_1025 values 953(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 954insert into t1_1025 values 955(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 956insert into t1_1025 values 957(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 958insert into t2_1025 values 959(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 960insert into t2_1025 values 961(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 962insert into t2_1025 values 963(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 964insert into t3_1025 values 965(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 966insert into t3_1025 values 967(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 968insert into t3_1025 values 969(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 970insert into t3_1025 values 971(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 972explain extended select left(a1,7), left(a2,7) 973from t1_1025 974where a1 in (select b1 from t2_1025 where b1 > '0'); 975id select_type table type possible_keys key key_len ref rows filtered Extra 9761 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 9771 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 978Warnings: 979Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where `test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1` and `test`.`t1_1025`.`a1` > '0' 980select left(a1,7), left(a2,7) 981from t1_1025 982where a1 in (select b1 from t2_1025 where b1 > '0'); 983left(a1,7) left(a2,7) 9841 - 01x 2 - 01x 9851 - 02x 2 - 02x 986explain extended select left(a1,7), left(a2,7) 987from t1_1025 988where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); 989id select_type table type possible_keys key key_len ref rows filtered Extra 9901 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 9911 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 992Warnings: 993Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where `test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1` and `test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2` and `test`.`t1_1025`.`a1` > '0' 994select left(a1,7), left(a2,7) 995from t1_1025 996where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); 997left(a1,7) left(a2,7) 9981 - 01x 2 - 01x 9991 - 02x 2 - 02x 1000explain extended select left(a1,7), left(a2,7) 1001from t1_1025 1002where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); 1003id select_type table type possible_keys key key_len ref rows filtered Extra 10041 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 10051 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1006Warnings: 1007Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where `test`.`t2_1025`.`b1` > '0' and `test`.`t1_1025`.`a1` = substr(`test`.`t2_1025`.`b1`,1,1025) 1008select left(a1,7), left(a2,7) 1009from t1_1025 1010where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); 1011left(a1,7) left(a2,7) 10121 - 01x 2 - 01x 10131 - 02x 2 - 02x 1014explain extended select left(a1,7), left(a2,7) 1015from t1_1025 1016where a1 in (select group_concat(b1) from t2_1025 group by b2); 1017id select_type table type possible_keys key key_len ref rows filtered Extra 10181 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 10191 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 10202 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort 1021Warnings: 1022Note 1003 /* select#1 */ select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where `test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)` 1023select left(a1,7), left(a2,7) 1024from t1_1025 1025where a1 in (select group_concat(b1) from t2_1025 group by b2); 1026left(a1,7) left(a2,7) 1027Warnings: 1028Warning 1260 Row 1 was cut by GROUP_CONCAT() 1029Warning 1260 Row 2 was cut by GROUP_CONCAT() 1030Warning 1260 Row 3 was cut by GROUP_CONCAT() 1031set @@group_concat_max_len = 256; 1032explain extended select left(a1,7), left(a2,7) 1033from t1_1025 1034where a1 in (select group_concat(b1) from t2_1025 group by b2); 1035id select_type table type possible_keys key key_len ref rows filtered Extra 10361 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 10371 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 10382 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort 1039Warnings: 1040Note 1003 /* select#1 */ select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (/* select#2 */ select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where `test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)` 1041select left(a1,7), left(a2,7) 1042from t1_1025 1043where a1 in (select group_concat(b1) from t2_1025 group by b2); 1044left(a1,7) left(a2,7) 1045Warnings: 1046Warning 1260 Row 1 was cut by GROUP_CONCAT() 1047Warning 1260 Row 2 was cut by GROUP_CONCAT() 1048Warning 1260 Row 3 was cut by GROUP_CONCAT() 1049drop table t1_1025, t2_1025, t3_1025; 1050create table t1bit (a1 bit(3), a2 bit(3)); 1051create table t2bit (b1 bit(3), b2 bit(3)); 1052insert into t1bit values (b'000', b'100'); 1053insert into t1bit values (b'001', b'101'); 1054insert into t1bit values (b'010', b'110'); 1055insert into t2bit values (b'001', b'101'); 1056insert into t2bit values (b'010', b'110'); 1057insert into t2bit values (b'110', b'111'); 1058explain extended select bin(a1), bin(a2) 1059from t1bit 1060where (a1, a2) in (select b1, b2 from t2bit); 1061id select_type table type possible_keys key key_len ref rows filtered Extra 10621 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 10631 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 10642 MATERIALIZED t2bit ALL NULL NULL NULL NULL 3 100.00 1065Warnings: 1066Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where 1 1067select bin(a1), bin(a2) 1068from t1bit 1069where (a1, a2) in (select b1, b2 from t2bit); 1070bin(a1) bin(a2) 10711 101 107210 110 1073drop table t1bit, t2bit; 1074create table t1bb (a1 bit(3), a2 blob(3)); 1075create table t2bb (b1 bit(3), b2 blob(3)); 1076insert into t1bb values (b'000', '100'); 1077insert into t1bb values (b'001', '101'); 1078insert into t1bb values (b'010', '110'); 1079insert into t2bb values (b'001', '101'); 1080insert into t2bb values (b'010', '110'); 1081insert into t2bb values (b'110', '111'); 1082explain extended select bin(a1), a2 1083from t1bb 1084where (a1, a2) in (select b1, b2 from t2bb); 1085id select_type table type possible_keys key key_len ref rows filtered Extra 10861 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00 10871 PRIMARY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1088Warnings: 1089Note 1003 select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where `test`.`t2bb`.`b1` = `test`.`t1bb`.`a1` and `test`.`t2bb`.`b2` = `test`.`t1bb`.`a2` 1090select bin(a1), a2 1091from t1bb 1092where (a1, a2) in (select b1, b2 from t2bb); 1093bin(a1) a2 10941 101 109510 110 1096drop table t1bb, t2bb; 1097drop table t1, t2, t3, t1i, t2i, t3i, columns; 1098/****************************************************************************** 1099* Test the cache of the left operand of IN. 1100******************************************************************************/ 1101# Test that default values of Cached_item are not used for comparison 1102create table t1 (s1 int); 1103create table t2 (s2 int); 1104insert into t1 values (5),(1),(0); 1105insert into t2 values (0), (1); 1106select s2 from t2 where s2 in (select s1 from t1); 1107s2 11080 11091 1110drop table t1, t2; 1111create table t1 (a int not null, b int not null); 1112create table t2 (c int not null, d int not null); 1113create table t3 (e int not null); 1114insert into t1 values (1,10); 1115insert into t1 values (1,20); 1116insert into t1 values (2,10); 1117insert into t1 values (2,20); 1118insert into t1 values (2,30); 1119insert into t1 values (3,20); 1120insert into t1 values (4,40); 1121insert into t2 values (2,10); 1122insert into t2 values (2,20); 1123insert into t2 values (2,40); 1124insert into t2 values (3,20); 1125insert into t2 values (4,10); 1126insert into t2 values (5,10); 1127insert into t3 values (10); 1128insert into t3 values (10); 1129insert into t3 values (20); 1130insert into t3 values (30); 1131explain extended 1132select a from t1 where a in (select c from t2 where d >= 20); 1133id select_type table type possible_keys key key_len ref rows filtered Extra 11341 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 11351 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11362 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 1137Warnings: 1138Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1139select a from t1 where a in (select c from t2 where d >= 20); 1140a 11412 11422 11432 11443 1145create index it1a on t1(a); 1146explain extended 1147select a from t1 where a in (select c from t2 where d >= 20); 1148id select_type table type possible_keys key key_len ref rows filtered Extra 11491 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index 11501 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11512 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 1152Warnings: 1153Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1154select a from t1 where a in (select c from t2 where d >= 20); 1155a 11562 11572 11582 11593 1160insert into t2 values (1,10); 1161explain extended 1162select a from t1 where a in (select c from t2 where d >= 20); 1163id select_type table type possible_keys key key_len ref rows filtered Extra 11641 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index 11651 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11662 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1167Warnings: 1168Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1169select a from t1 where a in (select c from t2 where d >= 20); 1170a 11712 11722 11732 11743 1175explain extended 1176select a from t1 group by a having a in (select c from t2 where d >= 20); 1177id select_type table type possible_keys key key_len ref rows filtered Extra 11781 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 11792 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1180Warnings: 1181Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where `test`.`t2`.`d` >= 20 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`)))) 1182select a from t1 group by a having a in (select c from t2 where d >= 20); 1183a 11842 11853 1186create index iab on t1(a, b); 1187explain extended 1188select a from t1 group by a having a in (select c from t2 where d >= 20); 1189id select_type table type possible_keys key key_len ref rows filtered Extra 11901 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 11912 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1192Warnings: 1193Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where `test`.`t2`.`d` >= 20 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`)))) 1194select a from t1 group by a having a in (select c from t2 where d >= 20); 1195a 11962 11973 1198explain extended 1199select a from t1 group by a 1200having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 1201id select_type table type possible_keys key key_len ref rows filtered Extra 12021 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using index 12032 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where 12043 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 1205Warnings: 1206Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 1207Note 1981 Aggregate function 'max()' of SELECT #3 belongs to SELECT #1 1208Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select `test`.`t3`.`e` from `test`.`t3` where max(`test`.`t1`.`b`) = `test`.`t3`.`e` having <cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`))))) and <cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))) 1209select a from t1 group by a 1210having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 1211a 12122 12133 1214explain extended 1215select a from t1 1216where a in (select c from t2 where d >= some(select e from t3 where b=e)); 1217id select_type table type possible_keys key key_len ref rows filtered Extra 12181 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary 12191 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary 12203 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 1221Warnings: 1222Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 1223Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t2`.`c` and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select `test`.`t3`.`e` from `test`.`t3` where `test`.`t1`.`b` = `test`.`t3`.`e` and <cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))) 1224select a from t1 1225where a in (select c from t2 where d >= some(select e from t3 where b=e)); 1226a 12272 12282 12292 12303 12311 1232drop table t1, t2, t3; 1233create table t2 (a int, b int, key(a), key(b)); 1234insert into t2 values (3,3),(3,3),(3,3); 1235select 1 from t2 where 1236t2.a > 1 1237or 1238t2.a = 3 and not t2.a not in (select t2.b from t2); 12391 12401 12411 12421 1243drop table t2; 1244create table t1 (a1 int key); 1245create table t2 (b1 int); 1246insert into t1 values (5); 1247explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1248id select_type table type possible_keys key key_len ref rows Extra 12491 PRIMARY t1 system NULL NULL NULL NULL 1 12501 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 12512 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1252select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1253min(a1) 1254NULL 1255set @local_optimizer_switch=@@optimizer_switch; 1256set @@optimizer_switch=@optimizer_switch_local_default; 1257set @@optimizer_switch='materialization=off,in_to_exists=on'; 1258explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1259id select_type table type possible_keys key key_len ref rows Extra 12601 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12612 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1262select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1263min(a1) 1264NULL 1265set @@optimizer_switch=@optimizer_switch_local_default; 1266set @@optimizer_switch='semijoin=off'; 1267explain select min(a1) from t1 where 7 in (select b1 from t2); 1268id select_type table type possible_keys key key_len ref rows Extra 12691 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12702 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1271select min(a1) from t1 where 7 in (select b1 from t2); 1272min(a1) 1273NULL 1274set @@optimizer_switch=@optimizer_switch_local_default; 1275set @@optimizer_switch='materialization=off,in_to_exists=on'; 1276# with MariaDB and MWL#90, this particular case is solved: 1277explain select min(a1) from t1 where 7 in (select b1 from t2); 1278id select_type table type possible_keys key key_len ref rows Extra 12791 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1280select min(a1) from t1 where 7 in (select b1 from t2); 1281min(a1) 1282NULL 1283# but when we go around MWL#90 code, the problem still shows up: 1284explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; 1285id select_type table type possible_keys key key_len ref rows Extra 12861 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12872 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1288select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; 1289min(a1) 1290NULL 1291set @@optimizer_switch= @local_optimizer_switch; 1292drop table t1,t2; 1293create table t1 (a char(2), b varchar(10)); 1294insert into t1 values ('a', 'aaa'); 1295insert into t1 values ('aa', 'aaaa'); 1296explain select a,b from t1 where b in (select a from t1); 1297id select_type table type possible_keys key key_len ref rows Extra 12981 PRIMARY t1 ALL NULL NULL NULL NULL 2 12991 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 Using where 13002 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 1301select a,b from t1 where b in (select a from t1); 1302a b 1303prepare st1 from "select a,b from t1 where b in (select a from t1)"; 1304execute st1; 1305a b 1306execute st1; 1307a b 1308drop table t1; 1309# 1310# BUG#49630: Segfault in select_describe() with double 1311# nested subquery and materialization 1312# 1313CREATE TABLE t1 (t1i int); 1314CREATE TABLE t2 (t2i int); 1315CREATE TABLE t3 (t3i int); 1316CREATE TABLE t4 (t4i int); 1317INSERT INTO t1 VALUES (1); 1318INSERT INTO t2 VALUES (1),(2); 1319INSERT INTO t3 VALUES (1),(2); 1320INSERT INTO t4 VALUES (1),(2); 1321 1322EXPLAIN 1323SELECT t1i 1324FROM t1 JOIN t4 ON t1i=t4i 1325WHERE (t1i) IN ( 1326SELECT t2i 1327FROM t2 1328WHERE (t2i) IN ( 1329SELECT max(t3i) 1330FROM t3 1331GROUP BY t3i 1332) 1333); 1334id select_type table type possible_keys key key_len ref rows Extra 13351 PRIMARY t1 system NULL NULL NULL NULL 1 13361 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 const 1 13371 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 13381 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 13393 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using temporary 1340DROP TABLE t1,t2,t3,t4; 1341CREATE TABLE t1 ( 1342pk INTEGER AUTO_INCREMENT, 1343col_int_nokey INTEGER, 1344col_int_key INTEGER, 1345col_varchar_key VARCHAR(1), 1346PRIMARY KEY (pk), 1347KEY (col_int_key), 1348KEY (col_varchar_key, col_int_key) 1349) 1350; 1351INSERT INTO t1 ( 1352col_int_key, col_int_nokey, col_varchar_key 1353) 1354VALUES 1355(2, NULL, 'w'), 1356(9, 7, 'm'), 1357(3, 9, 'm'), 1358(9, 7, 'k'), 1359(NULL, 4, 'r'), 1360(9, 2, 't'), 1361(3, 6, 'j'), 1362(8, 8, 'u'), 1363(8, NULL, 'h'), 1364(53, 5, 'o'), 1365(0, NULL, NULL), 1366(5, 6, 'k'), 1367(166, 188, 'e'), 1368(3, 2, 'n'), 1369(0, 1, 't'), 1370(1, 1, 'c'), 1371(9, 0, 'm'), 1372(5, 9, 'y'), 1373(6, NULL, 'f'), 1374(2, 4, 'd') 1375; 1376SELECT table2.col_varchar_key AS field1, 1377table2.col_int_nokey AS field2 1378FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2 1379ON (table2.col_varchar_key = table1.col_varchar_key ) ) 1380WHERE table1.pk = 6 1381HAVING ( field2 ) IN 1382( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2 1383FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2 1384ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) ) 1385ORDER BY field2 1386; 1387field1 field2 1388t 1 1389t 2 1390drop table t1; 1391# 1392# BUG#53103: MTR test ps crashes in optimize_cond() 1393# when running with --debug 1394# 1395CREATE TABLE t1(track varchar(15)); 1396INSERT INTO t1 VALUES ('CAD'), ('CAD'); 1397PREPARE STMT FROM 1398"SELECT 1 FROM t1 1399 WHERE 1400 track IN (SELECT track FROM t1 1401 GROUP BY track 1402 HAVING track>='CAD')"; 1403EXECUTE STMT ; 14041 14051 14061 1407EXECUTE STMT ; 14081 14091 14101 1411DEALLOCATE PREPARE STMT; 1412DROP TABLE t1; 1413# End of BUG#53103 1414# 1415# BUG#54511 - Assertion failed: cache != 0L in file 1416# sql_select.cc::sub_select_cache on HAVING 1417# 1418CREATE TABLE t1 (i int(11)); 1419CREATE TABLE t2 (c char(1)); 1420CREATE TABLE t3 (c char(1)); 1421INSERT INTO t1 VALUES (1), (2); 1422INSERT INTO t2 VALUES ('a'), ('b'); 1423INSERT INTO t3 VALUES ('x'), ('y'); 1424SELECT COUNT( i ),i 1425FROM t1 1426HAVING ('c') 1427IN (SELECT t2.c FROM (t2 JOIN t3)); 1428COUNT( i ) i 1429DROP TABLE t1,t2,t3; 1430# End BUG#54511 1431# 1432# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 1433# on subquery in FROM 1434# 1435CREATE TABLE t1 (a INTEGER); 1436CREATE TABLE t2 (b INTEGER); 1437INSERT INTO t2 VALUES (1); 1438set @tmp_optimizer_switch=@@optimizer_switch; 1439set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1440explain SELECT a FROM ( 1441SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1442) table1; 1443id select_type table type possible_keys key key_len ref rows Extra 14441 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 14452 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 14463 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1447SELECT a FROM ( 1448SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1449) table1; 1450a 1451set optimizer_switch=@tmp_optimizer_switch; 1452DROP TABLE t1, t2; 1453# End BUG#56367 1454# 1455# Bug#59833 - materialization=on/off leads to different result set 1456# when using IN 1457# 1458CREATE TABLE t1 ( 1459pk int NOT NULL, 1460f1 int DEFAULT NULL, 1461PRIMARY KEY (pk) 1462) ENGINE=MyISAM; 1463CREATE TABLE t2 ( 1464pk int NOT NULL, 1465f1 int DEFAULT NULL, 1466PRIMARY KEY (pk) 1467) ENGINE=MyISAM; 1468INSERT INTO t1 VALUES (10,0); 1469INSERT INTO t2 VALUES (10,0),(11,0); 1470explain SELECT * FROM t1 JOIN t2 USING (f1) 1471WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1472id select_type table type possible_keys key key_len ref rows Extra 14731 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1474SELECT * FROM t1 JOIN t2 USING (f1) 1475WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1476f1 pk pk 1477DROP TABLE t1, t2; 1478# End Bug#59833 1479# 1480# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT 1481# 1482CREATE TABLE t1 ( 1483col_varchar_key varchar(1) DEFAULT NULL, 1484col_varchar_nokey varchar(1) DEFAULT NULL, 1485KEY col_varchar_key (col_varchar_key)) 1486; 1487INSERT INTO t1 VALUES 1488('v','v'),('r','r'); 1489CREATE TABLE t2 ( 1490col_varchar_key varchar(1) DEFAULT NULL, 1491col_varchar_nokey varchar(1) DEFAULT NULL, 1492KEY col_varchar_key(col_varchar_key)) 1493; 1494INSERT INTO t2 VALUES 1495('r','r'),('c','c'); 1496CREATE VIEW v3 AS SELECT * FROM t2; 1497SELECT DISTINCT alias2.col_varchar_key 1498FROM t1 AS alias1 JOIN v3 AS alias2 1499ON alias2.col_varchar_key = alias1.col_varchar_key 1500HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2) 1501; 1502col_varchar_key 1503r 1504DROP TABLE t1, t2; 1505DROP VIEW v3; 1506# End Bug#11852644 1507 1508# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW 1509# INSTEAD OF NULL WHEN MATERIALIZATION ON 1510 1511CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY; 1512CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY; 1513INSERT INTO t2 VALUES (8),(7); 1514CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY; 1515INSERT INTO t3 VALUES (7); 1516SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3 1517FROM t3 1518LEFT JOIN t1 1519ON t1.col_int_nokey 1520WHERE (194, 200) IN ( 1521SELECT SQ4_alias1.col_int_nokey, 1522SQ4_alias2.col_int_nokey 1523FROM t2 AS SQ4_alias1 1524JOIN 1525t2 AS SQ4_alias2 1526ON SQ4_alias2.col_int_nokey = 5 1527) 1528GROUP BY field3 ; 1529MIN(t3.col_int_nokey) field3 1530DROP TABLE t1; 1531DROP TABLE t2; 1532DROP TABLE t3; 1533CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM; 1534INSERT INTO t1 (f1, f2) VALUES (1, 1.789); 1535INSERT INTO t1 (f1, f2) VALUES (13, 1.454); 1536INSERT INTO t1 (f1, f2) VALUES (10, 1.668); 1537CREATE TABLE t2 LIKE t1; 1538INSERT INTO t2 VALUES (1, 1.789); 1539INSERT INTO t2 VALUES (13, 1.454); 1540set @local_optimizer_switch=@@optimizer_switch; 1541set @@optimizer_switch=@optimizer_switch_local_default; 1542SET @@optimizer_switch='semijoin=on,materialization=on'; 1543EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); 1544id select_type table type possible_keys key key_len ref rows Extra 15451 PRIMARY t1 ALL NULL NULL NULL NULL 3 15461 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 15472 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 1548SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); 1549COUNT(*) 15502 1551set @@optimizer_switch= @local_optimizer_switch; 1552DROP TABLE t1, t2; 1553CREATE TABLE t1 ( 1554pk int, 1555a varchar(1), 1556b varchar(4), 1557c varchar(4), 1558d varchar(4), 1559PRIMARY KEY (pk) 1560); 1561INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); 1562CREATE TABLE t2 LIKE t1; 1563INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); 1564set @local_optimizer_switch=@@optimizer_switch; 1565set @@optimizer_switch=@optimizer_switch_local_default; 1566SET @@optimizer_switch='semijoin=on,materialization=on'; 1567EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); 1568id select_type table type possible_keys key key_len ref rows Extra 15691 PRIMARY t1 ALL NULL NULL NULL NULL 2 15701 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 15712 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan 1572SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); 1573pk 15742 1575SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); 1576pk 15772 1578DROP TABLE t1, t2; 1579set optimizer_switch=@local_optimizer_switch; 1580# 1581# BUG#50019: Wrong result for IN-subquery with materialization 1582# 1583create table t1(i int); 1584insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1585create table t2(i int); 1586insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1587create table t3(i int); 1588insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1589select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); 1590i 15911 15922 15933 15944 1595set @local_optimizer_switch=@@optimizer_switch; 1596set session optimizer_switch='materialization=off,in_to_exists=on'; 1597select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); 1598i 15994 16003 16012 16021 1603set session optimizer_switch=@local_optimizer_switch; 1604drop table t1, t2, t3; 1605create table t0 (a int); 1606insert into t0 values (0),(1),(2); 1607create table t1 (a int); 1608insert into t1 values (0),(1),(2); 1609explain select a, a in (select a from t1) from t0; 1610id select_type table type possible_keys key key_len ref rows Extra 16111 PRIMARY t0 ALL NULL NULL NULL NULL 3 16122 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 1613select a, a in (select a from t1) from t0; 1614a a in (select a from t1) 16150 1 16161 1 16172 1 1618prepare s from 'select a, a in (select a from t1) from t0'; 1619execute s; 1620a a in (select a from t1) 16210 1 16221 1 16232 1 1624update t1 set a=123; 1625execute s; 1626a a in (select a from t1) 16270 0 16281 0 16292 0 1630drop table t0, t1; 1631set optimizer_switch='firstmatch=on'; 1632# 1633# MWL#90, review feedback: check what happens when the subquery 1634# looks like candidate for MWL#90 checking at the first glance 1635# but then subselect_hash_sj_engine::init_permanent() discovers 1636# that it's not possible to perform duplicate removal for the 1637# selected datatypes, and so materialization isn't applicable after 1638# all. 1639# 1640set @blob_len = 1024; 1641set @suffix_len = @blob_len - @prefix_len; 1642create table t1_1024 (a1 blob(1024), a2 blob(1024)); 1643create table t2_1024 (b1 blob(1024), b2 blob(1024)); 1644insert into t1_1024 values 1645(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 1646insert into t1_1024 values 1647(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 1648insert into t1_1024 values 1649(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 1650insert into t2_1024 values 1651(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 1652insert into t2_1024 values 1653(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 1654insert into t2_1024 values 1655(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 1656explain select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1,1,1024), count(*) from t2_1024 where b1 > '0'); 1657id select_type table type possible_keys key key_len ref rows Extra 16581 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 Using where 16592 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 Using where 1660select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1,1,1024), count(*) from t2_1024 where b1 > '0'); 1661left(a1,7) left(a2,7) 16621 - 01x 2 - 01x 1663drop table t1_1024, t2_1024; 1664# 1665# BUG##836491: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization 1666# 1667CREATE TABLE t1 (c int, d varchar(1), KEY(d)) ; 1668INSERT INTO t1 VALUES (2,'x'),(2,'x'),(2,'j'),(2,'c'); 1669CREATE TABLE t2 (a int, d varchar(1)) ; 1670INSERT INTO t2 VALUES (1,'x'); 1671CREATE TABLE t3 (d varchar(1)) ; 1672INSERT INTO t3 VALUES ('x'),('x'),('j'),('c'); 1673SELECT t2.a, t1.c 1674FROM t1, t2 1675WHERE t2.d IN ( SELECT d FROM t3 ) 1676AND t1.d = t2.d 1677GROUP BY 1 , 2; 1678a c 16791 2 1680drop table t1,t2,t3; 1681# 1682# BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization 1683# 1684CREATE TABLE t1 (a varchar(1)); 1685INSERT INTO t1 VALUES ('a'),('a'); 1686CREATE TABLE t2 (a varchar(1)); 1687CREATE TABLE t3 (a int); 1688INSERT INTO t3 VALUES (1),(2); 1689CREATE TABLE t4 (a varchar(1)); 1690INSERT INTO t4 VALUES ('a'),('a'); 1691SELECT t1.a 1692FROM t1 1693WHERE t1.a IN ( 1694SELECT t2.a 1695FROM t2, t3 1696) 1697HAVING a IN ( 1698SELECT a 1699FROM t4 1700); 1701a 1702DROP TABLE t1, t2, t3, t4; 1703# 1704# BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization 1705# 1706CREATE TABLE t1 (a int) ; 1707INSERT IGNORE INTO t1 VALUES (1),(1); 1708CREATE TABLE t2 (a int); 1709INSERT INTO t2 VALUES (1); 1710CREATE TABLE t3 (a int); 1711CREATE TABLE t4 (a int); 1712INSERT INTO t4 VALUES (2),(2); 1713CREATE TABLE t5 (a int); 1714INSERT INTO t5 VALUES (1); 1715SELECT * FROM t1 1716WHERE (a) IN ( 1717SELECT t5.a 1718FROM ( 1719t2 1720LEFT JOIN ( t3 , t4 ) 1721ON 1 = 1 1722) 1723JOIN t5 1724); 1725a 17261 17271 1728DROP TABLE t1,t2,t3,t4,t5; 1729# 1730# BUG#836532: Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization 1731# 1732CREATE TABLE t2 (a int); 1733INSERT IGNORE INTO t2 VALUES ('a'),('a'); 1734Warnings: 1735Warning 1366 Incorrect integer value: 'a' for column `test`.`t2`.`a` at row 1 1736Warning 1366 Incorrect integer value: 'a' for column `test`.`t2`.`a` at row 2 1737CREATE TABLE t4 (a varchar(1)); 1738INSERT INTO t4 VALUES ('m'),('o'); 1739CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ; 1740INSERT INTO t3 VALUES ('b','b'); 1741CREATE TABLE t5 (a varchar(1), KEY (a)) ; 1742INSERT INTO t5 VALUES ('d'),('e'); 1743SELECT * 1744FROM t2 1745WHERE t2.a = ALL ( 1746SELECT t4.a 1747FROM t4 1748WHERE t4.a IN ( 1749SELECT t3.a 1750FROM t3 , t5 1751WHERE ( t5.a = t3.b ) 1752) 1753); 1754a 17550 17560 1757DROP TABLE t2,t3,t4,t5; 1758# 1759# BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization 1760# 1761set @tmp_860300=@@optimizer_switch; 1762set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; 1763CREATE TABLE t1 (f2 int); 1764INSERT INTO t1 VALUES (9),(6); 1765CREATE TABLE t3 (f4 int); 1766CREATE TABLE t4 (f6 varchar(1)); 1767SELECT * 1768FROM t3 1769WHERE 'h' IN (SELECT f6 1770FROM t4 1771WHERE 5 IN (SELECT f2 FROM t1) 1772GROUP BY t4.f6); 1773f4 1774DROP TABLE t1,t3,t4; 1775set optimizer_switch=@tmp_860300; 1776# 1777# BUG#860535: Assertion `keypart_map' failed in mi_rkey with semijoin 1778# 1779set @tmp_860535=@@optimizer_switch; 1780set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; 1781CREATE TABLE t1 (f3 int) ; 1782INSERT INTO t1 VALUES (1),(7); 1783CREATE TABLE t2 (f3 int , f5 varchar(1), KEY (f3)) ; 1784INSERT INTO t2 VALUES (7,'b'); 1785CREATE TABLE t3 (f3 int , f4 varchar(1) , KEY(f3), KEY (f4,f3)) ; 1786INSERT INTO t3 VALUES (1,'t'),(7,'g'); 1787CREATE TABLE t4 1788SELECT f3 1789FROM t1 WHERE ( f3 ) NOT IN ( 1790SELECT f3 1791FROM t2 1792WHERE f5 IN ( 1793SELECT f4 1794FROM t3 1795WHERE t3.f3 < 3 1796) 1797); 1798SELECT * FROM t4; 1799f3 18001 18017 1802DROP TABLE t1, t2, t3, t4; 1803set optimizer_switch=@tmp_860535; 1804# 1805# BUG#860553: Crash in create_ref_for_key with semijoin + materialization 1806# 1807CREATE TABLE t1 (f1 int) ; 1808CREATE TABLE t2 (f5 varchar(52) NOT NULL) ; 1809CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)); 1810CREATE TABLE t4 (f3 int, KEY (f3)); 1811INSERT INTO t4 VALUES (17),(20); 1812CREATE TABLE t5 (f2 int); 1813INSERT INTO t5 VALUES (0),(0); 1814SELECT * 1815FROM t1 1816JOIN t2 1817ON ( t2.f5 ) IN ( 1818SELECT t3.f4 1819FROM t3 1820WHERE ( 1 ) IN ( 1821SELECT t4.f3 1822FROM t4 , t5 1823) 1824); 1825f1 f5 1826DROP TABLE t1, t2, t3, t4, t5; 1827# 1828# BUG#868908: Crash in check_simple_equality() with semijoin + materialization + prepared statement 1829# 1830CREATE TABLE t1 ( a int ); 1831CREATE TABLE t3 ( b int, c int) ; 1832CREATE TABLE t2 ( a int ) ; 1833CREATE TABLE t4 ( a int , c int) ; 1834PREPARE st1 FROM " 1835SELECT STRAIGHT_JOIN * 1836FROM t1 1837WHERE ( 3 ) IN ( 1838 SELECT t3.b 1839 FROM t3 1840 LEFT JOIN ( 1841 t2 STRAIGHT_JOIN t4 ON ( t4.c = t2.a ) 1842 ) ON ( t4.a = t3.c ) 1843); 1844"; 1845EXECUTE st1; 1846a 1847EXECUTE st1; 1848a 1849DROP TABLE t1,t2,t3,t4; 1850# 1851# BUG#901032: Wrong result for MIN/MAX on an indexed column with materialization and semijoin 1852# 1853CREATE TABLE t1 ( a INT, KEY(a) ); 1854INSERT INTO t1 VALUES (1); 1855CREATE TABLE t2 ( b INT ); 1856INSERT INTO t2 VALUES (2); 1857CREATE TABLE t3 ( c INT ); 1858INSERT INTO t3 VALUES (2); 1859SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c); 1860MIN(a) 18611 1862DROP TABLE t1,t2,t3; 1863# 1864# 1865# BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread 1866# 1867CREATE TABLE t1 ( a INT ); 1868INSERT INTO t1 VALUES (1), (2); 1869CREATE TABLE t2 ( b INT ); 1870INSERT INTO t2 VALUES (3), (4); 1871CREATE TABLE t3 ( c INT ); 1872INSERT INTO t3 VALUES (5), (6); 1873SELECT * FROM t1 WHERE EXISTS ( 1874SELECT DISTINCT b FROM t2 1875WHERE b <= a 1876AND b IN ( SELECT c FROM t3 GROUP BY c ) 1877); 1878a 1879DROP TABLE t1,t2,t3; 1880# 1881# BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED 1882# 1883CREATE TABLE t1 ( a INT, KEY(a) ); 1884INSERT INTO t1 VALUES (8); 1885EXPLAIN EXTENDED 1886SELECT * FROM t1 1887WHERE a IN ( SELECT MIN(a) FROM t1 ); 1888id select_type table type possible_keys key key_len ref rows filtered Extra 18891 PRIMARY t1 system a NULL NULL NULL 1 100.00 18901 PRIMARY <subquery2> system NULL NULL NULL NULL 1 100.00 18912 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 1892Warnings: 1893Note 1003 /* select#1 */ select 8 AS `a` from dual where 1 1894DROP TABLE t1; 1895# 1896# BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON 1897# 1898CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; 1899INSERT INTO t1 VALUES (4); 1900CREATE TABLE t2 ( b INT NOT NULL, c INT ); 1901INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1); 1902SELECT * FROM t1 LEFT JOIN t2 ON ( a = b ) 1903WHERE a IN ( SELECT c FROM t2 ); 1904a b c 19054 4 2 19064 4 2 19074 4 4 1908DROP TABLE t1,t2; 1909# 1910# BUG#922254: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) 1911# 1912CREATE TABLE t1 ( a VARCHAR(3) ); 1913CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) ); 1914INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron'); 1915EXPLAIN 1916SELECT * FROM 1917( SELECT * FROM t1 ) AS alias1, 1918t2 AS alias2 1919WHERE b = a AND a IN ( 1920SELECT alias3.c 1921FROM t2 AS alias3, t2 AS alias4 1922WHERE alias4.c = alias3.b 1923); 1924id select_type table type possible_keys key key_len ref rows Extra 19251 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1926DROP TABLE t1,t2; 1927# 1928# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result 1929# 1930create table t1 (a int, b int); 1931insert into t1 values (7,5), (3,3), (5,4), (9,3); 1932create table t2 (a int, b int, index i_a(a)); 1933insert into t2 values 1934(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); 1935explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); 1936id select_type table type possible_keys key key_len ref rows Extra 19371 PRIMARY t1 ALL NULL NULL NULL NULL 4 19381 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 19392 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where 1940select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); 1941a b 19427 5 19433 3 1944drop table t1,t2; 1945# 1946# BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE 1947# 1948CREATE TABLE t1 (a INT); 1949INSERT INTO t1 VALUES (0),(8); 1950SELECT STRAIGHT_JOIN MIN(a) FROM t1 1951WHERE a IN ( 1952SELECT a FROM t1 1953WHERE 'condition'='impossible' 1954 ); 1955MIN(a) 1956NULL 1957DROP TABLE t1; 1958# 1959# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT 1960# 1961CREATE TABLE t1(a int); 1962INSERT INTO t1 values(1),(2); 1963CREATE TABLE t2(a int); 1964INSERT INTO t2 values(1),(2); 1965# Should use Materialization: 1966EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); 1967id select_type table type possible_keys key key_len ref rows Extra 19681 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 19691 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 19702 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary 1971flush status; 1972CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); 1973SHOW STATUS LIKE 'Created_tmp_tables'; 1974Variable_name Value 1975Created_tmp_tables 2 1976DROP TABLE t1,t2,t3; 1977# 1978# BUG#939009: Crash with aggregate function in IN subquery 1979# 1980SET @local_optimizer_switch=@@optimizer_switch; 1981SET optimizer_switch='materialization=on,semijoin=on'; 1982CREATE TABLE t1 (a int, b int); 1983INSERT INTO t1 VALUES (7,1), (4,2), (7,7); 1984CREATE TABLE t2 ( c INT ); 1985INSERT INTO t2 VALUES (4), (7), (6); 1986EXPLAIN EXTENDED 1987SELECT * FROM t1 1988WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); 1989id select_type table type possible_keys key key_len ref rows filtered Extra 19901 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 19911 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 19922 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 1993Warnings: 1994Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7) 1995SELECT * FROM t1 1996WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); 1997a b 19987 7 1999EXPLAIN 2000SELECT * FROM t1 2001WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); 2002id select_type table type possible_keys key key_len ref rows Extra 20031 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 20041 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 20052 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2006SELECT * FROM t1 2007WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); 2008a b 2009SET optimizer_switch=@local_optimizer_switch; 2010DROP TABLE t1,t2; 2011# 2012# BUG#946055: Crash with semijoin IN subquery when hash join is used 2013# 2014CREATE TABLE t1 (a int); 2015INSERT INTO t1 VALUES (7); 2016CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); 2017INSERT INTO t2 VALUES 2018(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), 2019(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); 2020SET @local_optimizer_switch=@@optimizer_switch; 2021SET join_cache_level=2; 2022EXPLAIN 2023SELECT a, c FROM t1, t2 2024WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2025WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2026id select_type table type possible_keys key key_len ref rows Extra 20271 PRIMARY t1 system NULL NULL NULL NULL 1 20281 PRIMARY t2 index c c 5 NULL 8 Using index 20291 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 20302 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 20312 MATERIALIZED s1 ALL c NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 20323 SUBQUERY t2 ALL NULL NULL NULL NULL 8 2033SELECT a, c FROM t1, t2 2034WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2035WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2036a c 20377 1 20387 1 20397 1 2040SET optimizer_switch='join_cache_hashed=on'; 2041SET join_cache_level=4; 2042EXPLAIN 2043SELECT a, c FROM t1, t2 2044WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2045WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2046id select_type table type possible_keys key key_len ref rows Extra 20471 PRIMARY t1 system NULL NULL NULL NULL 1 20481 PRIMARY t2 index c c 5 NULL 8 Using index 20491 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 20502 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 20512 MATERIALIZED s1 hash_ALL c #hash#$hj 5 const 8 Using where; Using join buffer (flat, BNLH join) 20523 SUBQUERY t2 ALL NULL NULL NULL NULL 8 2053SELECT a, c FROM t1, t2 2054WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2055WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2056a c 20577 1 20587 1 20597 1 2060SET optimizer_switch=@local_optimizer_switch; 2061SET join_cache_level=@save_join_cache_level; 2062DROP TABLE t1,t2; 2063# 2064# BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization 2065# 2066CREATE TABLE t1 ( a VARCHAR(1) ); 2067INSERT INTO t1 VALUES ('y'),('z'); 2068CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) ); 2069INSERT INTO t2 VALUES ('v','v'),('v','v'); 2070CREATE VIEW v2 AS SELECT * FROM t2; 2071PREPARE ps FROM ' 2072SELECT a FROM t1, v2 2073WHERE ( c, b ) IN ( SELECT b, b FROM t2 ) 2074GROUP BY a '; 2075EXECUTE ps; 2076a 2077y 2078z 2079EXECUTE ps; 2080a 2081y 2082z 2083DROP VIEW v2; 2084DROP TABLE t1, t2; 2085# 2086# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 2087# 2088CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; 2089INSERT INTO t1 VALUES ('b','b'),('e','e'); 2090CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; 2091INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); 2092SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); 2093a1 a2 b1 b2 2094b b v v 2095b b s s 2096b b y y 2097DROP TABLE t1,t2; 2098# 2099# MDEV-4465: Reproducible crash (mysqld got signal 11) in multi_delete::initialize_tables with semijoin+materialization 2100# 2101CREATE TABLE t1 ( 2102id int(11) NOT NULL 2103); 2104CREATE TABLE t2 ( 2105id int(11) NOT NULL, 2106a_id int(11) DEFAULT NULL 2107); 2108insert into t1 values (1), (2), (3); 2109insert into t2 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 3), (7, 3); 2110delete t2 from t2 where a_id in (select * from (select t1.id from t1 limit 2) as x); 2111drop table t1,t2; 2112# This must be at the end: 2113set optimizer_switch=@save_optimizer_switch; 2114set join_cache_level=@save_join_cache_level; 2115# 2116# MDEV-4908: Assertion `((Item_cond *) cond)->functype() == 2117# ((Item_cond *) new_item)->functype()' fails on a query with 2118# IN and equal conditions, AND/OR, materialization+semijoin 2119# 2120SET @local_optimizer_switch=@@optimizer_switch; 2121SET optimizer_switch = 'materialization=on,semijoin=on'; 2122CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; 2123INSERT INTO t1 VALUES (1,3,5),(2,4,6); 2124SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); 2125pk a b 2126drop table t1; 2127SET optimizer_switch=@local_optimizer_switch; 2128# 2129# MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries 2130# 2131CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; 2132INSERT INTO t1 VALUES (1,3,5),(2,4,6); 2133SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); 2134pk a b 2135DROP TABLE t1; 2136# 2137# MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 2138# execution of PS with IN subqueries, materialization+semijoin 2139# 2140CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2141INSERT INTO t1 VALUES (1),(3); 2142CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2143CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; 2144INSERT INTO t2 VALUES (8),(9); 2145PREPARE stmt FROM " 2146SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) 2147"; 2148EXECUTE stmt; 2149a 2150EXECUTE stmt; 2151a 2152DROP TABLE t1, t2; 2153DROP VIEW v2; 2154# 2155# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON 2156# 2157SET @tmp_mdev5811= @@big_tables; 2158SET big_tables = ON; 2159CREATE TABLE t1 (a INT); 2160INSERT INTO t1 VALUES (1),(2); 2161CREATE TABLE t2 (b INT); 2162INSERT INTO t2 VALUES (3),(4); 2163SELECT * FROM t1 AS t1_1, t1 AS t1_2 2164WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); 2165a a 2166DROP TABLE t1,t2; 2167SET big_tables=@tmp_mdev5811; 2168# End of 5.3 tests 2169# 2170# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries 2171# 2172set @tmp_mdev5056=@@join_cache_level; 2173SET join_cache_level = 2; 2174CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM; 2175INSERT INTO t1 VALUES 2176('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'), 2177('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'), 2178('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'), 2179('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM'); 2180CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM; 2181INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN'); 2182SELECT * FROM t1 AS alias1, t1 AS alias2 2183WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 ); 2184c1 c2 c1 c2 2185CA ML CA ML 2186CA ML RO ML 2187DROP TABLE t1,t2; 2188set join_cache_level=@tmp_mdev5056; 2189# 2190# MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 2191# execution of PS with IN subqueries, materialization+semijoin 2192# 2193CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2194INSERT INTO t1 VALUES (1),(3); 2195CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2196CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; 2197INSERT INTO t2 VALUES (8),(9); 2198PREPARE stmt FROM " 2199SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) 2200"; 2201EXECUTE stmt; 2202a 2203EXECUTE stmt; 2204a 2205DROP TABLE t1, t2; 2206DROP VIEW v2; 2207# 2208# MDEV-6289 : Unexpected results when querying information_schema 2209# 2210CREATE TABLE t1 ( 2211id int(11) unsigned NOT NULL AUTO_INCREMENT, 2212db varchar(254) NOT NULL DEFAULT '', 2213PRIMARY KEY (id), 2214UNIQUE KEY db (db) 2215) DEFAULT CHARSET=utf8; 2216INSERT INTO t1 (db) VALUES ('mysqltest1'),('mysqltest2'),('mysqltest3'),('mysqltest4'); 2217drop database if exists mysqltest1; 2218drop database if exists mysqltest2; 2219drop database if exists mysqltest3; 2220drop database if exists mysqltest4; 2221create database mysqltest1; 2222create database mysqltest2; 2223create database mysqltest3; 2224create database mysqltest4; 2225SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; 2226db 2227mysqltest4 2228mysqltest3 2229mysqltest2 2230mysqltest1 2231EXPLAIN EXTENDED 2232SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; 2233id select_type table type possible_keys key key_len ref rows filtered Extra 22341 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort 22351 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index 22362 MATERIALIZED schemata ALL NULL NULL NULL NULL NULL NULL 2237Warnings: 2238Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc 2239drop table t1; 2240drop database mysqltest1; 2241drop database mysqltest2; 2242drop database mysqltest3; 2243drop database mysqltest4; 2244# 2245# MDEV-7810 Wrong result on execution of a query as a PS 2246# (both 1st and further executions) 2247CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; 2248INSERT INTO t1 VALUES (0),(8); 2249SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); 2250a 22510 2252PREPARE stmt FROM " 2253SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) 2254"; 2255execute stmt; 2256a 22570 2258execute stmt; 2259a 22600 2261drop table t1; 2262# 2263# MDEV-12429: IN subquery used in WHERE of EXISTS subquery 2264# 2265CREATE TABLE t1 ( 2266pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; 2267INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); 2268SELECT sq1.f2 FROM t1 AS sq1 2269WHERE EXISTS ( SELECT * FROM t1 AS sq2 2270WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2271f2 2272foo 2273set @local_optimizer_switch= @@optimizer_switch; 2274set optimizer_switch='exists_to_in=off'; 2275EXPLAIN 2276SELECT sq1.f2 FROM t1 AS sq1 2277WHERE EXISTS ( SELECT * FROM t1 AS sq2 2278WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2279id select_type table type possible_keys key key_len ref rows Extra 22801 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where 22812 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 22822 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 22833 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2284# this checks the result set above 2285set optimizer_switch= 'materialization=off,semijoin=off'; 2286SELECT sq1.f2 FROM t1 AS sq1 2287WHERE EXISTS ( SELECT * FROM t1 AS sq2 2288WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2289f2 2290foo 2291set optimizer_switch= @local_optimizer_switch; 2292DROP TABLE t1; 2293# 2294# MDEV-12145: IN subquery used in WHERE of EXISTS subquery 2295# 2296CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; 2297INSERT INTO t1 VALUES (4),(6); 2298CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; 2299INSERT INTO t2 VALUES (8),(7),(1); 2300CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; 2301INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); 2302set @local_optimizer_switch= @@optimizer_switch; 2303set optimizer_switch='exists_to_in=off'; 2304SELECT * FROM t1 2305WHERE EXISTS ( SELECT * FROM t2, t3 2306WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2307f1 23086 2309EXPLAIN EXTENDED 2310SELECT * FROM t1 2311WHERE EXISTS ( SELECT * FROM t2, t3 2312WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2313id select_type table type possible_keys key key_len ref rows filtered Extra 23141 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 23152 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 23162 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) 23172 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index 23183 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 2319Warnings: 2320Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 2321Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(/* select#2 */ select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3` limit 1)) 2322# this checks the result set above 2323set optimizer_switch= 'materialization=off,semijoin=off'; 2324SELECT * FROM t1 2325WHERE EXISTS ( SELECT * FROM t2, t3 2326WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2327f1 23286 2329set optimizer_switch= @local_optimizer_switch; 2330DROP TABLE t1,t2,t3; 2331# 2332# MDEV-9686: IN subquery used in WHERE of a subquery from select list 2333# 2334CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); 2335INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); 2336CREATE TABLE t2 (f2 INT); 2337INSERT INTO t2 VALUES (1),(2),(3),(4),(5); 2338# t1.pk is always IN ( SELECT f2 FROM t2 ), 2339# so the IN condition should be true for every row, 2340# and thus COUNT(*) should always return 5 2341SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2342WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2343pk f1 sq 23441 4 5 23452 3 5 23463 3 5 23474 6 5 23485 3 5 2349EXPLAIN EXTENDED 2350SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2351WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2352id select_type table type possible_keys key key_len ref rows filtered Extra 23531 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 23542 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 23552 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) 23563 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 2357Warnings: 2358Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 2359Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((/* select#2 */ select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` 2360# this checks the result set above 2361set @local_optimizer_switch= @@optimizer_switch; 2362set optimizer_switch= 'materialization=off,semijoin=off'; 2363SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2364WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2365pk f1 sq 23661 4 5 23672 3 5 23683 3 5 23694 6 5 23705 3 5 2371set optimizer_switch= @local_optimizer_switch; 2372DROP TABLE t1,t2; 2373# 2374# mdev-12838: scan of materialized of semi-join subquery in join 2375# 2376set @local_optimizer_switch=@@optimizer_switch; 2377CREATE TABLE t1 ( 2378dispatch_group varchar(32), 2379assignment_group varchar(32), 2380sys_id char(32), 2381PRIMARY KEY (sys_id), 2382KEY idx1 (dispatch_group), 2383KEY idx2 (assignment_group) 2384) ENGINE=MyISAM; 2385CREATE TABLE t2 ( 2386ugroup varchar(32), 2387user varchar(32), 2388sys_id char(32), 2389PRIMARY KEY (sys_id), 2390KEY idx3 (ugroup), 2391KEY idx4 (user) 2392) ENGINE=MyISAM; 2393CREATE TABLE t3 ( 2394type mediumtext, 2395sys_id char(32), 2396PRIMARY KEY (sys_id) 2397) ENGINE=MyISAM; 2398set optimizer_switch='materialization=off'; 2399explain SELECT t1.assignment_group 2400FROM t1, t3 2401WHERE t1.assignment_group = t3.sys_id AND 2402t1.dispatch_group IN 2403(SELECT t2.ugroup 2404FROM t2, t3 t3_i 2405WHERE t2.ugroup = t3_i.sys_id AND 2406t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2407t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2408id select_type table type possible_keys key key_len ref rows Extra 24091 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary 24101 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 24111 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary 24121 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index 2413SELECT t1.assignment_group 2414FROM t1, t3 2415WHERE t1.assignment_group = t3.sys_id AND 2416t1.dispatch_group IN 2417(SELECT t2.ugroup 2418FROM t2, t3 t3_i 2419WHERE t2.ugroup = t3_i.sys_id AND 2420t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2421t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2422assignment_group 2423df50316637232000158bbfc8bcbe5d23 2424e08fad2637232000158bbfc8bcbe5d39 2425ec70316637232000158bbfc8bcbe5d60 24267b10fd2637232000158bbfc8bcbe5d30 2427ebb4620037332000158bbfc8bcbe5d89 2428set optimizer_switch='materialization=on'; 2429explain SELECT t1.assignment_group 2430FROM t1, t3 2431WHERE t1.assignment_group = t3.sys_id AND 2432t1.dispatch_group IN 2433(SELECT t2.ugroup 2434FROM t2, t3 t3_i 2435WHERE t2.ugroup = t3_i.sys_id AND 2436t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2437t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2438id select_type table type possible_keys key key_len ref rows Extra 24391 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 24401 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where 24411 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index 24422 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where 24432 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 2444SELECT t1.assignment_group 2445FROM t1, t3 2446WHERE t1.assignment_group = t3.sys_id AND 2447t1.dispatch_group IN 2448(SELECT t2.ugroup 2449FROM t2, t3 t3_i 2450WHERE t2.ugroup = t3_i.sys_id AND 2451t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2452t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2453assignment_group 2454df50316637232000158bbfc8bcbe5d23 2455e08fad2637232000158bbfc8bcbe5d39 2456ec70316637232000158bbfc8bcbe5d60 24577b10fd2637232000158bbfc8bcbe5d30 2458ebb4620037332000158bbfc8bcbe5d89 2459DROP TABLE t1,t2,t3; 2460set optimizer_switch=@local_optimizer_switch; 2461# 2462# MDEV-16751: Server crashes in st_join_table::cleanup or 2463# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 2464# 2465set @save_join_cache_level= @@join_cache_level; 2466set join_cache_level=4; 2467CREATE TABLE t1 ( id int NOT NULL); 2468INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); 2469CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; 2470INSERT INTO t2 VALUES (11,11),(12,12),(13,13); 2471explain 2472SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 2473id select_type table type possible_keys key key_len ref rows Extra 24741 PRIMARY t1 ALL NULL NULL NULL NULL 9 24751 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 24762 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2477SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 24781 24791 24801 24811 2482set @@join_cache_level= @save_join_cache_level; 2483alter table t1 add key(id); 2484explain 2485SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 2486id select_type table type possible_keys key key_len ref rows Extra 24871 PRIMARY t1 index id id 4 NULL 9 Using index 24881 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 24892 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2490SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 24911 24921 24931 24941 2495drop table t1,t2; 2496# 2497# MDEV-15454: Nested SELECT IN returns wrong results 2498# 2499CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); 2500CREATE TABLE t2 ( a int, b int ); 2501INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); 2502CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); 2503INSERT INTO t3 (c, b) VALUES (27, 96); 2504CREATE PROCEDURE prepare_data() 2505BEGIN 2506DECLARE i INT DEFAULT 1; 2507WHILE i < 1000 DO 2508INSERT INTO t1 (a) VALUES (i); 2509INSERT INTO t2 (a,b) VALUES (i,56); 2510INSERT INTO t3 (c,b) VALUES (i,i); 2511SET i = i + 1; 2512END WHILE; 2513END$$ 2514CALL prepare_data(); 2515SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); 2516a 25177878 25183465 25191403 25204189 25218732 25225 2523set @local_optimizer_switch= @@optimizer_switch; 2524SET optimizer_switch='materialization=off'; 2525SELECT t1.a FROM t1 2526WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; 2527a 25285 2529SET optimizer_switch='materialization=on'; 2530SELECT t1.a FROM t1 2531WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; 2532a 25335 2534drop procedure prepare_data; 2535set @@optimizer_switch= @local_optimizer_switch; 2536drop table t1,t2,t3; 2537CREATE TABLE t1 ( id int NOT NULL, key(id)); 2538INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); 2539CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); 2540INSERT INTO t2 VALUES (11,11),(12,12),(13,13); 2541CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; 2542explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 2543id select_type table type possible_keys key key_len ref rows Extra 25441 PRIMARY t1 index id id 4 NULL 9 Using index 25451 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 25462 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2547SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 25481 25491 25501 25511 2552drop table t1,t2; 2553drop view v1; 2554# 2555# MDEV-19580: function invocation in the left part of IN subquery 2556# 2557create table t1 (id int, a varchar(50), b int); 2558insert into t1 values 2559(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); 2560create table t2 (id int, a varchar(50), x int); 2561insert into t2 values 2562(1,'grand',1),(2,'average',1),(3,'serf',0); 2563create table t3 (d1 date, d2 date, t1_id int, t2_id int ); 2564insert into t3 values 2565('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), 2566('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); 2567create table t4 ( id int, a varchar(50) ); 2568insert into t4 values 2569(1,'songwriter'),(2,'song character'); 2570create function f1(who int, dt date) returns int 2571deterministic 2572begin 2573declare result int; 2574select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; 2575return result; 2576end$$ 2577create function f2(who int, dt date) returns int 2578begin 2579declare result int; 2580select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; 2581return result; 2582end$$ 2583# Deterministic function in left part of IN subquery: semi-join is OK 2584select * from t1 2585left join t4 on t1.b = t4.id 2586where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); 2587id a b id a 25883 paul 1 1 songwriter 25894 art 1 1 songwriter 25901 mrs 2 2 song character 2591explain extended select * from t1 2592left join t4 on t1.b = t4.id 2593where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); 2594id select_type table type possible_keys key key_len ref rows filtered Extra 25951 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 25961 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 25971 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 25982 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2599Warnings: 2600Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` 2601# Non-deterministic function in left part of IN subq: semi-join is OK 2602select * from t1 2603left join t4 on t1.b = t4.id 2604where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2605id a b id a 26063 paul 1 1 songwriter 26074 art 1 1 songwriter 26081 mrs 2 2 song character 2609explain extended select * from t1 2610left join t4 on t1.b = t4.id 2611where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2612id select_type table type possible_keys key key_len ref rows filtered Extra 26131 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 26141 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 26151 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 26162 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2617Warnings: 2618Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` 2619select t1.*, t4.*, 2620(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s 2621from t1 left join t4 on t1.b = t4.id 2622where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2623id a b id a s 26243 paul 1 1 songwriter 1 26254 art 1 1 songwriter 1 26261 mrs 2 2 song character 2 2627explain extended select t1.*, t4.*, 2628(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s 2629from t1 left join t4 on t1.b = t4.id 2630where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2631id select_type table type possible_keys key key_len ref rows filtered Extra 26321 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 26331 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 26341 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 26353 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 26362 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where 2637Warnings: 2638Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 2639Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(/* select#2 */ select max(`test`.`t4`.`id`) from `test`.`t4` where `test`.`t4`.`id` = `test`.`t1`.`b` and sleep(0) = 0) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` 2640drop function f1; 2641drop function f2; 2642drop table t1,t2,t3,t4; 2643# End of 5.5 tests 2644# 2645# MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT 2646# 2647create table t0(a int); 2648insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2649create table t1 (a int, b int, c int); 2650insert into t1 2651select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 2652from t0 A, t0 B, t0 C; 2653create table t2 (a int, b int, c int); 2654insert into t2 select A.a, A.a, A.a from t1 A; 2655insert into t2 select * from t2; 2656insert into t2 select * from t2; 2657create table t3 as select * from t2 limit 1; 2658# The testcase only makes sense if the following uses Materialization: 2659explain 2660select * from t1 where (a,b) in (select max(a),b from t2 group by b); 2661id select_type table type possible_keys key key_len ref rows Extra 26621 PRIMARY t1 ALL NULL NULL NULL NULL 1000 Using where 26631 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 26642 MATERIALIZED t2 ALL NULL NULL NULL NULL 4000 Using temporary 2665flush status; 2666replace into t3 2667select * from t1 where (a,b) in (select max(a),b from t2 group by b); 2668# Sequential reads: 2669# 1K is read from t1 2670# 4K is read from t2 2671# 1K groups is read from the tmp. table 2672# 2673# Lookups: 2674# 4K lookups in group by table 2675# 1K lookups in temp.table 2676# 2677# Writes: 2678# 2x 1K writes to temporary tables (grouping table and subquery materialization table 2679# 2680# The point is that neither counter should be in the millions (this 2681# will happen if Materialization is not used 2682show status where Variable_name like 'Handler_read%' or Variable_name like 'Handler_%write%'; 2683Variable_name Value 2684Handler_read_first 0 2685Handler_read_key 5004 2686Handler_read_last 0 2687Handler_read_next 0 2688Handler_read_prev 0 2689Handler_read_retry 0 2690Handler_read_rnd 0 2691Handler_read_rnd_deleted 0 2692Handler_read_rnd_next 6003 2693Handler_tmp_write 2000 2694Handler_write 1000 2695drop table t0,t1,t2,t3; 2696# 2697# MDEV-7971: Assertion `name != __null' failed in ACL_internal_schema_registry::lookup 2698# on 2nd execution os PS with multi-table update 2699# 2700CREATE TABLE t1 (f1 INT); 2701INSERT INTO t1 VALUES (1),(2); 2702CREATE TABLE t2 (f2 INT); 2703INSERT INTO t2 VALUES (3),(4); 2704CREATE TABLE t3 (f3 INT); 2705INSERT INTO t3 VALUES (5),(6); 2706PREPARE stmt FROM ' 2707 UPDATE t1, t2 2708 SET f1 = 5 2709 WHERE 8 IN ( SELECT MIN(f3) FROM t3 ) 2710'; 2711EXECUTE stmt; 2712EXECUTE stmt; 2713DROP TABLE t1,t2,t3; 2714# 2715# MDEV-10389: Query returns different results on a debug vs non-debug build of the same revision 2716# 2717CREATE TABLE t1 (i1 INT, i2 INT NOT NULL); 2718INSERT INTO t1 VALUES (1,4),(2,6); 2719SELECT * FROM t1 AS alias1 2720WHERE alias1.i1 IN ( 2721SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) 2722); 2723i1 i2 27241 4 27252 6 2726DROP TABLE t1; 2727