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 t1i range _it1_idx _it1_idx # NULL 3 100.00 Using where; 1111 PRIMARY <subquery2> eq_ref distinct_key distinct_key # func 1 100.00 1122 MATERIALIZED t2i range it2i1,it2i3 it2i1 # NULL 5 100.00 Using where; 113Warnings: 114Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0' 115select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 116a1 a2 1171 - 01 2 - 01 1181 - 02 2 - 02 119explain extended 120select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); 121id select_type table type possible_keys key key_len ref rows filtered Extra 1221 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # 1231 PRIMARY <subquery2> eq_ref distinct_key # 8 # 1 100.00 # 1242 MATERIALIZED t2i range it2i1,it2i3 # 9 # 5 100.00 # 125Warnings: 126Note 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` 127select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); 128a1 a2 1291 - 01 2 - 01 1301 - 02 2 - 02 131explain extended 132select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 133id select_type table type possible_keys key key_len ref rows filtered Extra 1341 PRIMARY t1i range _it1_idx _it1_idx # NULL 3 100.00 Using where; 1351 PRIMARY <subquery2> eq_ref distinct_key distinct_key # func,func 1 100.00 1362 MATERIALIZED t2i range it2i1,it2i2,it2i3 it2i3 # NULL 5 100.00 Using where; 137Warnings: 138Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0' 139select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 140a1 a2 1411 - 01 2 - 01 1421 - 02 2 - 02 143explain extended 144select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); 145id select_type table type possible_keys key key_len ref rows filtered Extra 1461 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1471 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 1482 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # 149Warnings: 150Note 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` 151select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); 152a1 a2 1531 - 01 2 - 01 1541 - 02 2 - 02 155explain extended 156select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 157id select_type table type possible_keys key key_len ref rows filtered Extra 1581 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1591 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 1602 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # 161Warnings: 162Note 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` 163select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 164a1 a2 1651 - 01 2 - 01 1661 - 02 2 - 02 167create table t2i_c like t2i; 168insert into t2i_c select * from t2i; 169insert into t2i_c select * from t2i; 170insert into t2i_c select * from t2i; 171insert into t2i_c select * from t2i; 172analyze table t2i_c; 173Table Op Msg_type Msg_text 174test.t2i_c analyze status Engine-independent statistics collected 175test.t2i_c analyze status OK 176show create table t2i_c; 177Table Create Table 178t2i_c CREATE TABLE `t2i_c` ( 179 `b1` char(8) DEFAULT NULL, 180 `b2` char(8) DEFAULT NULL, 181 KEY `it2i1` (`b1`), 182 KEY `it2i2` (`b2`), 183 KEY `it2i3` (`b1`,`b2`) 184) ENGINE=MyISAM DEFAULT CHARSET=latin1 185explain extended 186select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); 187id select_type table type possible_keys key key_len ref rows filtered Extra 1881 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1891 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 1902 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 100.00 Using index for group-by 191Warnings: 192Note 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` 193select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); 194a1 a2 1951 - 01 2 - 01 1961 - 02 2 - 02 197prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; 198execute st1; 199id select_type table type possible_keys key key_len ref rows Extra 2001 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2011 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2022 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by 203execute st1; 204id select_type table type possible_keys key key_len ref rows Extra 2051 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2061 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2072 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by 208prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; 209execute st2; 210a1 a2 2111 - 01 2 - 01 2121 - 02 2 - 02 213execute st2; 214a1 a2 2151 - 01 2 - 01 2161 - 02 2 - 02 217drop table t2i_c; 218explain extended 219select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 220id select_type table type possible_keys key key_len ref rows filtered Extra 2211 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2221 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2232 MATERIALIZED t2i range it2i1,it2i3 it2i3 9 NULL 5 100.00 Using where; Using index 224Warnings: 225Note 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` 226select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 227a1 a2 2281 - 01 2 - 01 2291 - 02 2 - 02 230select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); 231ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 232set @local_optimizer_switch=@@optimizer_switch; 233set @@optimizer_switch=@optimizer_switch_local_default; 234set @@optimizer_switch='semijoin=off'; 235prepare st1 from 236"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 237set @@optimizer_switch=@optimizer_switch_local_default; 238set @@optimizer_switch='materialization=off,in_to_exists=on'; 239execute st1; 240a1 a2 2411 - 01 2 - 01 2421 - 02 2 - 02 243set @@optimizer_switch=@optimizer_switch_local_default; 244set @@optimizer_switch='semijoin=off'; 245execute st1; 246a1 a2 2471 - 01 2 - 01 2481 - 02 2 - 02 249set @@optimizer_switch=@optimizer_switch_local_default; 250set @@optimizer_switch='materialization=off,in_to_exists=on'; 251prepare st1 from 252"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 253set @@optimizer_switch=@optimizer_switch_local_default; 254set @@optimizer_switch='semijoin=off'; 255execute st1; 256a1 a2 2571 - 01 2 - 01 2581 - 02 2 - 02 259set @@optimizer_switch=@optimizer_switch_local_default; 260set @@optimizer_switch='materialization=off,in_to_exists=on'; 261execute st1; 262a1 a2 2631 - 01 2 - 01 2641 - 02 2 - 02 265set @@optimizer_switch=@local_optimizer_switch; 266explain extended 267select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 268id select_type table type possible_keys key key_len ref rows filtered Extra 2691 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 2701 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 2712 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 272Warnings: 273Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where 1 274select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 275a1 a2 2761 - 01 2 - 01 2771 - 02 2 - 02 278explain extended 279select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 280id select_type table type possible_keys key key_len ref rows filtered Extra 2811 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 2821 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 test.t2i.b1,test.t2i.b2 1 100.00 Using index 283Warnings: 284Note 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` 285select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 286a1 a2 2871 - 01 2 - 01 2881 - 02 2 - 02 289/****************************************************************************** 290* Views, UNIONs, several levels of nesting. 291******************************************************************************/ 292# materialize the result of subquery over temp-table view 293create algorithm=merge view v1 as 294select b1, c2 from t2, t3 where b2 > c2; 295create algorithm=merge view v2 as 296select b1, c2 from t2, t3 group by b2, c2; 297Warnings: 298Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 299create algorithm=temptable view v1m as 300select b1, c2 from t2, t3 where b2 > c2; 301create algorithm=temptable view v2m as 302select b1, c2 from t2, t3 group by b2, c2; 303select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null); 304b1 c2 3051 - 02 2 - 01 3061 - 02 2 - 01 3071 - 03 2 - 01 3081 - 03 2 - 02 309select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null); 310b1 c2 3111 - 02 2 - 01 3121 - 02 2 - 01 3131 - 03 2 - 01 3141 - 03 2 - 02 315select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null); 316b1 c2 3171 - 02 2 - 01 3181 - 02 2 - 01 3191 - 03 2 - 01 3201 - 03 2 - 02 321select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null); 322b1 c2 3231 - 02 2 - 01 3241 - 02 2 - 01 3251 - 03 2 - 01 3261 - 03 2 - 02 327drop view v1, v2, v1m, v2m; 328explain extended 329select * from t1 330where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 331(a1, a2) in (select c1, c2 from t3 332where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 333id select_type table type possible_keys key key_len ref rows filtered Extra 3341 PRIMARY t1 ALL NULL NULL NULL NULL 3 98.44 3351 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3361 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3373 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 98.44 Using where 3383 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 3392 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 98.44 Using where 340Warnings: 341Note 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' 342select * from t1 343where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 344(a1, a2) in (select c1, c2 from t3 345where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 346a1 a2 3471 - 01 2 - 01 3481 - 02 2 - 02 349explain extended 350select * from t1i 351where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 352(a1, a2) in (select c1, c2 from t3i 353where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 354id select_type table type possible_keys key key_len ref rows filtered Extra 3551 PRIMARY t1i range it1i1,it1i2,it1i3 # # # 3 100.00 # 3561 PRIMARY <subquery3> eq_ref distinct_key # # # 1 100.00 # 3571 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 3583 MATERIALIZED t3i range it3i1,it3i2,it3i3 # # # 4 100.00 # 3593 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 3602 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # 361Warnings: 362Note 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`.`t2i`.`b1` = `test`.`t3i`.`c1` and `test`.`t2i`.`b2` = `test`.`t3i`.`c2` and `test`.`t2i`.`b1` > '0' and `test`.`t3i`.`c2` > '0' 363select * from t1i 364where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 365(a1, a2) in (select c1, c2 from t3i 366where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 367a1 a2 3681 - 01 2 - 01 3691 - 02 2 - 02 370explain extended 371select * from t1 372where (a1, a2) in (select b1, b2 from t2 373where b2 in (select c2 from t3 where c2 LIKE '%02') or 374b2 in (select c2 from t3 where c2 LIKE '%03')) and 375(a1, a2) in (select c1, c2 from t3 376where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 377id select_type table type possible_keys key key_len ref rows filtered Extra 3781 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 3791 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3801 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3815 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 3825 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 3832 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 99.22 Using where 3844 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3853 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 386Warnings: 387Note 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' 388select * from t1 389where (a1, a2) in (select b1, b2 from t2 390where b2 in (select c2 from t3 where c2 LIKE '%02') or 391b2 in (select c2 from t3 where c2 LIKE '%03')) and 392(a1, a2) in (select c1, c2 from t3 393where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 394a1 a2 3951 - 02 2 - 02 396explain extended 397select * from t1 398where (a1, a2) in (select b1, b2 from t2 399where b2 in (select c2 from t3 t3a where c1 = a1) or 400b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 401(a1, a2) in (select c1, c2 from t3 t3c 402where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 403id select_type table type possible_keys key key_len ref rows filtered Extra 4041 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 4051 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4061 PRIMARY t2 ALL NULL NULL NULL NULL 5 99.22 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4075 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 99.22 Using where 4085 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 1 100.00 Using index 4094 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 4103 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 411Warnings: 412Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 413Note 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' 414select * from t1 415where (a1, a2) in (select b1, b2 from t2 416where b2 in (select c2 from t3 t3a where c1 = a1) or 417b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 418(a1, a2) in (select c1, c2 from t3 t3c 419where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 420a1 a2 4211 - 01 2 - 01 4221 - 02 2 - 02 423explain extended 424(select * from t1 425where (a1, a2) in (select b1, b2 from t2 426where b2 in (select c2 from t3 where c2 LIKE '%02') or 427b2 in (select c2 from t3 where c2 LIKE '%03') 428group by b1, b2) and 429(a1, a2) in (select c1, c2 from t3 430where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 431UNION 432(select * from t1i 433where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 434(a1, a2) in (select c1, c2 from t3i 435where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 436id select_type table type possible_keys key key_len ref rows filtered Extra 4371 PRIMARY t1 ALL NULL # # # 3 99.22 # 4381 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # 4391 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 4405 MATERIALIZED t3 ALL NULL # # # 4 99.22 # 4415 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 4422 MATERIALIZED t2 ALL NULL # # # 5 99.22 # 4434 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 4443 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 4457 UNION t1i range it1i1,it1i2,it1i3 # # # 3 100.00 # 4467 UNION <subquery9> eq_ref distinct_key # # # 1 100.00 # 4477 UNION <subquery8> eq_ref distinct_key # # # 1 100.00 # 4489 MATERIALIZED t3i range it3i1,it3i2,it3i3 # # # 4 100.00 # 4499 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 4508 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # 451NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # 452Warnings: 453Note 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`.`t2i`.`b1` = `test`.`t3i`.`c1` and `test`.`t2i`.`b2` = `test`.`t3i`.`c2` and `test`.`t2i`.`b1` > '0' and `test`.`t3i`.`c2` > '0') 454(select * from t1 455where (a1, a2) in (select b1, b2 from t2 456where b2 in (select c2 from t3 where c2 LIKE '%02') or 457b2 in (select c2 from t3 where c2 LIKE '%03') 458group by b1, b2) and 459(a1, a2) in (select c1, c2 from t3 460where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 461UNION 462(select * from t1i 463where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 464(a1, a2) in (select c1, c2 from t3i 465where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 466a1 a2 4671 - 02 2 - 02 4681 - 01 2 - 01 469explain extended 470select * from t1 471where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 472(a1, a2) in (select c1, c2 from t3 473where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 474id select_type table type possible_keys key key_len ref rows filtered Extra 4751 PRIMARY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 4761 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4774 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4784 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 4792 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 4803 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 481NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 482Warnings: 483Note 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' 484select * from t1 485where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 486(a1, a2) in (select c1, c2 from t3 487where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 488a1 a2 4891 - 01 2 - 01 4901 - 02 2 - 02 491explain extended 492select * from t1, t3 493where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 494(c1, c2) in (select c1, c2 from t3 495where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 496a1 = c1; 497id select_type table type possible_keys key key_len ref rows filtered Extra 4981 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4991 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) 5001 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 5014 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 5024 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 5032 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 5043 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 505NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 506Warnings: 507Note 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' 508select * from t1, t3 509where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 510(c1, c2) in (select c1, c2 from t3 511where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 512a1 = c1; 513a1 a2 c1 c2 5141 - 01 2 - 01 1 - 01 2 - 01 5151 - 02 2 - 02 1 - 02 2 - 02 516/****************************************************************************** 517* Negative tests, where materialization should not be applied. 518******************************************************************************/ 519# UNION in a subquery 520explain extended 521select * from t3 522where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 523id select_type table type possible_keys key key_len ref rows filtered Extra 5241 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 5252 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 5263 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where 527NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 528Warnings: 529Note 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`))) 530select * from t3 531where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 532c1 c2 5331 - 01 2 - 01 5341 - 02 2 - 02 5351 - 03 2 - 03 536explain extended 537select * from t1 538where (a1, a2) in (select b1, b2 from t2 539where b2 in (select c2 from t3 t3a where c1 = a1) or 540b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 541(a1, a2) in (select c1, c2 from t3 t3c 542where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 543id select_type table type possible_keys key key_len ref rows filtered Extra 5441 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5451 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 1 100.00 Using index; Start temporary 5461 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) 5471 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 5484 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 5493 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 550Warnings: 551Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 552Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 553Note 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`))))) 554explain extended 555select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 556id select_type table type possible_keys key key_len ref rows filtered Extra 5571 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5582 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 559Warnings: 560Note 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))) 561select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 562a1 a2 5631 - 01 2 - 01 564explain extended 565select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 566id select_type table type possible_keys key key_len ref rows filtered Extra 5671 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5682 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 569Warnings: 570Note 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))) 571select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 572a1 a2 5731 - 01 2 - 01 574/****************************************************************************** 575* Subqueries in other uncovered clauses. 576******************************************************************************/ 577/* SELECT clause */ 578select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; 579((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL 5800 5810 5820 583/* GROUP BY clause */ 584create table columns (col int key); 585insert into columns values (1), (2); 586explain extended 587select * from t1 group by (select col from columns limit 1); 588id select_type table type possible_keys key key_len ref rows filtered Extra 5891 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 5902 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 591Warnings: 592Note 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) 593select * from t1 group by (select col from columns limit 1); 594a1 a2 5951 - 00 2 - 00 596explain extended 597select * from t1 group by (a1 in (select col from columns)); 598id select_type table type possible_keys key key_len ref rows filtered Extra 5991 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort 6002 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key 601Warnings: 602Note 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`))))) 603select * from t1 group by (a1 in (select col from columns)); 604a1 a2 6051 - 00 2 - 00 606Warnings: 607Warning 1292 Truncated incorrect DOUBLE value: '1 - 00' 608Warning 1292 Truncated incorrect DOUBLE value: '1 - 01' 609Warning 1292 Truncated incorrect DOUBLE value: '1 - 02' 610/* ORDER BY clause */ 611explain extended 612select * from t1 order by (select col from columns limit 1); 613id select_type table type possible_keys key key_len ref rows filtered Extra 6141 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 6152 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 616Warnings: 617Note 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) 618select * from t1 order by (select col from columns limit 1); 619a1 a2 6201 - 00 2 - 00 6211 - 01 2 - 01 6221 - 02 2 - 02 623/****************************************************************************** 624* Column types/sizes that affect materialization. 625******************************************************************************/ 626/* 627Test that BLOBs are not materialized (except when arguments of some functions). 628*/ 629# force materialization to be always considered 630set @prefix_len = 6; 631set @blob_len = 16; 632set @suffix_len = @blob_len - @prefix_len; 633create table t1_16 (a1 blob(16), a2 blob(16)); 634create table t2_16 (b1 blob(16), b2 blob(16)); 635create table t3_16 (c1 blob(16), c2 blob(16)); 636insert into t1_16 values 637(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 638insert into t1_16 values 639(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 640insert into t1_16 values 641(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 642insert into t2_16 values 643(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 644insert into t2_16 values 645(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 646insert into t2_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 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 650insert into t3_16 values 651(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 652insert into t3_16 values 653(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 654insert into t3_16 values 655(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 656explain extended select left(a1,7), left(a2,7) 657from t1_16 658where a1 in (select b1 from t2_16 where b1 > '0'); 659id select_type table type possible_keys key key_len ref rows filtered Extra 6601 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 6611 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 662Warnings: 663Note 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' 664select left(a1,7), left(a2,7) 665from t1_16 666where a1 in (select b1 from t2_16 where b1 > '0'); 667left(a1,7) left(a2,7) 6681 - 01x 2 - 01x 6691 - 02x 2 - 02x 670explain extended select left(a1,7), left(a2,7) 671from t1_16 672where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); 673id select_type table type possible_keys key key_len ref rows filtered Extra 6741 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 6751 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 676Warnings: 677Note 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' 678select left(a1,7), left(a2,7) 679from t1_16 680where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); 681left(a1,7) left(a2,7) 6821 - 01x 2 - 01x 6831 - 02x 2 - 02x 684explain extended select left(a1,7), left(a2,7) 685from t1_16 686where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); 687id select_type table type possible_keys key key_len ref rows filtered Extra 6881 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 6891 PRIMARY <subquery2> eq_ref distinct_key distinct_key 19 func 1 100.00 Using where 6902 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where 691Warnings: 692Note 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) 693select left(a1,7), left(a2,7) 694from t1_16 695where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); 696left(a1,7) left(a2,7) 6971 - 01x 2 - 01x 6981 - 02x 2 - 02x 699explain extended select left(a1,7), left(a2,7) 700from t1_16 701where a1 in (select group_concat(b1) from t2_16 group by b2); 702id select_type table type possible_keys key key_len ref rows filtered Extra 7031 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 7042 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort 705Warnings: 706Note 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 ','))))) 707select left(a1,7), left(a2,7) 708from t1_16 709where a1 in (select group_concat(b1) from t2_16 group by b2); 710left(a1,7) left(a2,7) 7111 - 01x 2 - 01x 7121 - 02x 2 - 02x 713set @@group_concat_max_len = 256; 714explain extended select left(a1,7), left(a2,7) 715from t1_16 716where a1 in (select group_concat(b1) from t2_16 group by b2); 717id select_type table type possible_keys key key_len ref rows filtered Extra 7181 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 7191 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_16.a1 1 100.00 Using where 7202 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort 721Warnings: 722Note 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)` 723select left(a1,7), left(a2,7) 724from t1_16 725where a1 in (select group_concat(b1) from t2_16 group by b2); 726left(a1,7) left(a2,7) 7271 - 01x 2 - 01x 7281 - 02x 2 - 02x 729explain extended 730select * from t1 731where concat(a1,'x') IN 732(select left(a1,8) from t1_16 733where (a1, a2) IN 734(select t2_16.b1, t2_16.b2 from t2_16, t2 735where t2.b2 = substring(t2_16.b2,1,6) and 736t2.b1 IN (select c1 from t3 where c2 > '0'))); 737id select_type table type possible_keys key key_len ref rows filtered Extra 7381 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 7391 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; Using join buffer (flat, BNL join) 7401 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 7411 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) 7421 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join) 743Warnings: 744Note 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) 745drop table t1_16, t2_16, t3_16; 746set @blob_len = 512; 747set @suffix_len = @blob_len - @prefix_len; 748create table t1_512 (a1 blob(512), a2 blob(512)); 749create table t2_512 (b1 blob(512), b2 blob(512)); 750create table t3_512 (c1 blob(512), c2 blob(512)); 751insert into t1_512 values 752(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 753insert into t1_512 values 754(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 755insert into t1_512 values 756(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 757insert into t2_512 values 758(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 759insert into t2_512 values 760(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 761insert into t2_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 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 765insert into t3_512 values 766(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 767insert into t3_512 values 768(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 769insert into t3_512 values 770(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 771explain extended select left(a1,7), left(a2,7) 772from t1_512 773where a1 in (select b1 from t2_512 where b1 > '0'); 774id select_type table type possible_keys key key_len ref rows filtered Extra 7751 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 7761 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 777Warnings: 778Note 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' 779select left(a1,7), left(a2,7) 780from t1_512 781where a1 in (select b1 from t2_512 where b1 > '0'); 782left(a1,7) left(a2,7) 7831 - 01x 2 - 01x 7841 - 02x 2 - 02x 785explain extended select left(a1,7), left(a2,7) 786from t1_512 787where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); 788id select_type table type possible_keys key key_len ref rows filtered Extra 7891 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 7901 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 791Warnings: 792Note 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' 793select left(a1,7), left(a2,7) 794from t1_512 795where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); 796left(a1,7) left(a2,7) 7971 - 01x 2 - 01x 7981 - 02x 2 - 02x 799explain extended select left(a1,7), left(a2,7) 800from t1_512 801where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); 802id select_type table type possible_keys key key_len ref rows filtered Extra 8031 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 8041 PRIMARY <subquery2> eq_ref distinct_key distinct_key 516 func 1 100.00 Using where 8052 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where 806Warnings: 807Note 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) 808select left(a1,7), left(a2,7) 809from t1_512 810where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); 811left(a1,7) left(a2,7) 8121 - 01x 2 - 01x 8131 - 02x 2 - 02x 814explain extended select left(a1,7), left(a2,7) 815from t1_512 816where a1 in (select group_concat(b1) from t2_512 group by b2); 817id select_type table type possible_keys key key_len ref rows filtered Extra 8181 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 8191 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 8202 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort 821Warnings: 822Note 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)` 823select left(a1,7), left(a2,7) 824from t1_512 825where a1 in (select group_concat(b1) from t2_512 group by b2); 826left(a1,7) left(a2,7) 827Warnings: 828Warning 1260 Row 1 was cut by GROUP_CONCAT() 829Warning 1260 Row 2 was cut by GROUP_CONCAT() 830Warning 1260 Row 3 was cut by GROUP_CONCAT() 831set @@group_concat_max_len = 256; 832explain extended select left(a1,7), left(a2,7) 833from t1_512 834where a1 in (select group_concat(b1) from t2_512 group by b2); 835id select_type table type possible_keys key key_len ref rows filtered Extra 8361 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 8371 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 8382 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort 839Warnings: 840Note 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)` 841select left(a1,7), left(a2,7) 842from t1_512 843where a1 in (select group_concat(b1) from t2_512 group by b2); 844left(a1,7) left(a2,7) 845Warnings: 846Warning 1260 Row 1 was cut by GROUP_CONCAT() 847Warning 1260 Row 2 was cut by GROUP_CONCAT() 848Warning 1260 Row 3 was cut by GROUP_CONCAT() 849drop table t1_512, t2_512, t3_512; 850set @blob_len = 1024; 851set @suffix_len = @blob_len - @prefix_len; 852create table t1_1024 (a1 blob(1024), a2 blob(1024)); 853create table t2_1024 (b1 blob(1024), b2 blob(1024)); 854create table t3_1024 (c1 blob(1024), c2 blob(1024)); 855insert into t1_1024 values 856(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 857insert into t1_1024 values 858(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 859insert into t1_1024 values 860(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 861insert into t2_1024 values 862(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 863insert into t2_1024 values 864(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 865insert into t2_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 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 869insert into t3_1024 values 870(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 871insert into t3_1024 values 872(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 873insert into t3_1024 values 874(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 875explain extended select left(a1,7), left(a2,7) 876from t1_1024 877where a1 in (select b1 from t2_1024 where b1 > '0'); 878id select_type table type possible_keys key key_len ref rows filtered Extra 8791 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 8801 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 881Warnings: 882Note 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' 883select left(a1,7), left(a2,7) 884from t1_1024 885where a1 in (select b1 from t2_1024 where b1 > '0'); 886left(a1,7) left(a2,7) 8871 - 01x 2 - 01x 8881 - 02x 2 - 02x 889explain extended select left(a1,7), left(a2,7) 890from t1_1024 891where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); 892id select_type table type possible_keys key key_len ref rows filtered Extra 8931 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 8941 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 895Warnings: 896Note 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' 897select left(a1,7), left(a2,7) 898from t1_1024 899where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); 900left(a1,7) left(a2,7) 9011 - 01x 2 - 01x 9021 - 02x 2 - 02x 903explain extended select left(a1,7), left(a2,7) 904from t1_1024 905where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); 906id select_type table type possible_keys key key_len ref rows filtered Extra 9071 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 9081 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 909Warnings: 910Note 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) 911select left(a1,7), left(a2,7) 912from t1_1024 913where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); 914left(a1,7) left(a2,7) 9151 - 01x 2 - 01x 9161 - 02x 2 - 02x 917explain extended select left(a1,7), left(a2,7) 918from t1_1024 919where a1 in (select group_concat(b1) from t2_1024 group by b2); 920id select_type table type possible_keys key key_len ref rows filtered Extra 9211 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 9221 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 9232 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort 924Warnings: 925Note 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)` 926select left(a1,7), left(a2,7) 927from t1_1024 928where a1 in (select group_concat(b1) from t2_1024 group by b2); 929left(a1,7) left(a2,7) 930Warnings: 931Warning 1260 Row 1 was cut by GROUP_CONCAT() 932Warning 1260 Row 2 was cut by GROUP_CONCAT() 933Warning 1260 Row 3 was cut by GROUP_CONCAT() 934set @@group_concat_max_len = 256; 935explain extended select left(a1,7), left(a2,7) 936from t1_1024 937where a1 in (select group_concat(b1) from t2_1024 group by b2); 938id select_type table type possible_keys key key_len ref rows filtered Extra 9391 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 9401 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 9412 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort 942Warnings: 943Note 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)` 944select left(a1,7), left(a2,7) 945from t1_1024 946where a1 in (select group_concat(b1) from t2_1024 group by b2); 947left(a1,7) left(a2,7) 948Warnings: 949Warning 1260 Row 1 was cut by GROUP_CONCAT() 950Warning 1260 Row 2 was cut by GROUP_CONCAT() 951Warning 1260 Row 3 was cut by GROUP_CONCAT() 952drop table t1_1024, t2_1024, t3_1024; 953set @blob_len = 1025; 954set @suffix_len = @blob_len - @prefix_len; 955create table t1_1025 (a1 blob(1025), a2 blob(1025)); 956create table t2_1025 (b1 blob(1025), b2 blob(1025)); 957create table t3_1025 (c1 blob(1025), c2 blob(1025)); 958insert into t1_1025 values 959(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 960insert into t1_1025 values 961(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 962insert into t1_1025 values 963(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 964insert into t2_1025 values 965(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 966insert into t2_1025 values 967(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 968insert into t2_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 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 972insert into t3_1025 values 973(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 974insert into t3_1025 values 975(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 976insert into t3_1025 values 977(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); 978explain extended select left(a1,7), left(a2,7) 979from t1_1025 980where a1 in (select b1 from t2_1025 where b1 > '0'); 981id select_type table type possible_keys key key_len ref rows filtered Extra 9821 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 9831 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 984Warnings: 985Note 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' 986select left(a1,7), left(a2,7) 987from t1_1025 988where a1 in (select b1 from t2_1025 where b1 > '0'); 989left(a1,7) left(a2,7) 9901 - 01x 2 - 01x 9911 - 02x 2 - 02x 992explain extended select left(a1,7), left(a2,7) 993from t1_1025 994where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); 995id select_type table type possible_keys key key_len ref rows filtered Extra 9961 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 9971 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 998Warnings: 999Note 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' 1000select left(a1,7), left(a2,7) 1001from t1_1025 1002where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); 1003left(a1,7) left(a2,7) 10041 - 01x 2 - 01x 10051 - 02x 2 - 02x 1006explain extended select left(a1,7), left(a2,7) 1007from t1_1025 1008where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); 1009id select_type table type possible_keys key key_len ref rows filtered Extra 10101 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 10111 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1012Warnings: 1013Note 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) 1014select left(a1,7), left(a2,7) 1015from t1_1025 1016where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); 1017left(a1,7) left(a2,7) 10181 - 01x 2 - 01x 10191 - 02x 2 - 02x 1020explain extended select left(a1,7), left(a2,7) 1021from t1_1025 1022where a1 in (select group_concat(b1) from t2_1025 group by b2); 1023id select_type table type possible_keys key key_len ref rows filtered Extra 10241 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 10251 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 10262 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort 1027Warnings: 1028Note 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)` 1029select left(a1,7), left(a2,7) 1030from t1_1025 1031where a1 in (select group_concat(b1) from t2_1025 group by b2); 1032left(a1,7) left(a2,7) 1033Warnings: 1034Warning 1260 Row 1 was cut by GROUP_CONCAT() 1035Warning 1260 Row 2 was cut by GROUP_CONCAT() 1036Warning 1260 Row 3 was cut by GROUP_CONCAT() 1037set @@group_concat_max_len = 256; 1038explain extended select left(a1,7), left(a2,7) 1039from t1_1025 1040where a1 in (select group_concat(b1) from t2_1025 group by b2); 1041id select_type table type possible_keys key key_len ref rows filtered Extra 10421 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 10431 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 10442 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort 1045Warnings: 1046Note 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)` 1047select left(a1,7), left(a2,7) 1048from t1_1025 1049where a1 in (select group_concat(b1) from t2_1025 group by b2); 1050left(a1,7) left(a2,7) 1051Warnings: 1052Warning 1260 Row 1 was cut by GROUP_CONCAT() 1053Warning 1260 Row 2 was cut by GROUP_CONCAT() 1054Warning 1260 Row 3 was cut by GROUP_CONCAT() 1055drop table t1_1025, t2_1025, t3_1025; 1056create table t1bit (a1 bit(3), a2 bit(3)); 1057create table t2bit (b1 bit(3), b2 bit(3)); 1058insert into t1bit values (b'000', b'100'); 1059insert into t1bit values (b'001', b'101'); 1060insert into t1bit values (b'010', b'110'); 1061insert into t2bit values (b'001', b'101'); 1062insert into t2bit values (b'010', b'110'); 1063insert into t2bit values (b'110', b'111'); 1064explain extended select bin(a1), bin(a2) 1065from t1bit 1066where (a1, a2) in (select b1, b2 from t2bit); 1067id select_type table type possible_keys key key_len ref rows filtered Extra 10681 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 10691 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 10702 MATERIALIZED t2bit ALL NULL NULL NULL NULL 3 100.00 1071Warnings: 1072Note 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 1073select bin(a1), bin(a2) 1074from t1bit 1075where (a1, a2) in (select b1, b2 from t2bit); 1076bin(a1) bin(a2) 10771 101 107810 110 1079drop table t1bit, t2bit; 1080create table t1bb (a1 bit(3), a2 blob(3)); 1081create table t2bb (b1 bit(3), b2 blob(3)); 1082insert into t1bb values (b'000', '100'); 1083insert into t1bb values (b'001', '101'); 1084insert into t1bb values (b'010', '110'); 1085insert into t2bb values (b'001', '101'); 1086insert into t2bb values (b'010', '110'); 1087insert into t2bb values (b'110', '111'); 1088explain extended select bin(a1), a2 1089from t1bb 1090where (a1, a2) in (select b1, b2 from t2bb); 1091id select_type table type possible_keys key key_len ref rows filtered Extra 10921 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00 10931 PRIMARY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1094Warnings: 1095Note 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` 1096select bin(a1), a2 1097from t1bb 1098where (a1, a2) in (select b1, b2 from t2bb); 1099bin(a1) a2 11001 101 110110 110 1102drop table t1bb, t2bb; 1103drop table t1, t2, t3, t1i, t2i, t3i, columns; 1104/****************************************************************************** 1105* Test the cache of the left operand of IN. 1106******************************************************************************/ 1107# Test that default values of Cached_item are not used for comparison 1108create table t1 (s1 int); 1109create table t2 (s2 int); 1110insert into t1 values (5),(1),(0); 1111insert into t2 values (0), (1); 1112select s2 from t2 where s2 in (select s1 from t1); 1113s2 11140 11151 1116drop table t1, t2; 1117create table t1 (a int not null, b int not null); 1118create table t2 (c int not null, d int not null); 1119create table t3 (e int not null); 1120insert into t1 values (1,10); 1121insert into t1 values (1,20); 1122insert into t1 values (2,10); 1123insert into t1 values (2,20); 1124insert into t1 values (2,30); 1125insert into t1 values (3,20); 1126insert into t1 values (4,40); 1127insert into t2 values (2,10); 1128insert into t2 values (2,20); 1129insert into t2 values (2,40); 1130insert into t2 values (3,20); 1131insert into t2 values (4,10); 1132insert into t2 values (5,10); 1133insert into t3 values (10); 1134insert into t3 values (10); 1135insert into t3 values (20); 1136insert into t3 values (30); 1137explain extended 1138select a from t1 where a in (select c from t2 where d >= 20); 1139id select_type table type possible_keys key key_len ref rows filtered Extra 11401 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 11411 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11422 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 1143Warnings: 1144Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1145select a from t1 where a in (select c from t2 where d >= 20); 1146a 11472 11482 11492 11503 1151create index it1a on t1(a); 1152explain extended 1153select a from t1 where a in (select c from t2 where d >= 20); 1154id select_type table type possible_keys key key_len ref rows filtered Extra 11551 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index 11561 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11572 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 1158Warnings: 1159Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1160select a from t1 where a in (select c from t2 where d >= 20); 1161a 11622 11632 11642 11653 1166insert into t2 values (1,10); 1167explain extended 1168select a from t1 where a in (select c from t2 where d >= 20); 1169id select_type table type possible_keys key key_len ref rows filtered Extra 11701 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index 11711 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 11722 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1173Warnings: 1174Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 1175select a from t1 where a in (select c from t2 where d >= 20); 1176a 11772 11782 11792 11803 1181explain extended 1182select a from t1 group by a having a in (select c from t2 where d >= 20); 1183id select_type table type possible_keys key key_len ref rows filtered Extra 11841 PRIMARY t1 range NULL it1a 4 NULL 8 100.00 Using index for group-by 11852 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1186Warnings: 1187Note 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`)))) 1188select a from t1 group by a having a in (select c from t2 where d >= 20); 1189a 11902 11913 1192create index iab on t1(a, b); 1193explain extended 1194select a from t1 group by a having a in (select c from t2 where d >= 20); 1195id select_type table type possible_keys key key_len ref rows filtered Extra 11961 PRIMARY t1 range NULL it1a 4 NULL 8 100.00 Using index for group-by 11972 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 1198Warnings: 1199Note 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`)))) 1200select a from t1 group by a having a in (select c from t2 where d >= 20); 1201a 12022 12033 1204explain extended 1205select a from t1 group by a 1206having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 1207id select_type table type possible_keys key key_len ref rows filtered Extra 12081 PRIMARY t1 range NULL iab 4 NULL 8 100.00 Using index for group-by 12092 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where 12103 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 1211Warnings: 1212Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 1213Note 1981 Aggregate function 'max()' of SELECT #3 belongs to SELECT #1 1214Note 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`))) 1215select a from t1 group by a 1216having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 1217a 12182 12193 1220explain extended 1221select a from t1 1222where a in (select c from t2 where d >= some(select e from t3 where b=e)); 1223id select_type table type possible_keys key key_len ref rows filtered Extra 12241 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary 12251 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary 12263 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 1227Warnings: 1228Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 1229Note 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`)))) 1230select a from t1 1231where a in (select c from t2 where d >= some(select e from t3 where b=e)); 1232a 12332 12342 12352 12363 12371 1238drop table t1, t2, t3; 1239create table t2 (a int, b int, key(a), key(b)); 1240insert into t2 values (3,3),(3,3),(3,3); 1241select 1 from t2 where 1242t2.a > 1 1243or 1244t2.a = 3 and not t2.a not in (select t2.b from t2); 12451 12461 12471 12481 1249drop table t2; 1250create table t1 (a1 int key); 1251create table t2 (b1 int); 1252insert into t1 values (5); 1253explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1254id select_type table type possible_keys key key_len ref rows Extra 12551 PRIMARY t1 system NULL NULL NULL NULL 1 12561 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 12572 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1258select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1259min(a1) 1260NULL 1261set @local_optimizer_switch=@@optimizer_switch; 1262set @@optimizer_switch=@optimizer_switch_local_default; 1263set @@optimizer_switch='materialization=off,in_to_exists=on'; 1264explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1265id select_type table type possible_keys key key_len ref rows Extra 12661 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12672 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1268select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 1269min(a1) 1270NULL 1271set @@optimizer_switch=@optimizer_switch_local_default; 1272set @@optimizer_switch='semijoin=off'; 1273explain select min(a1) from t1 where 7 in (select b1 from t2); 1274id select_type table type possible_keys key key_len ref rows Extra 12751 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12762 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1277select min(a1) from t1 where 7 in (select b1 from t2); 1278min(a1) 1279NULL 1280set @@optimizer_switch=@optimizer_switch_local_default; 1281set @@optimizer_switch='materialization=off,in_to_exists=on'; 1282# with MariaDB and MWL#90, this particular case is solved: 1283explain select min(a1) from t1 where 7 in (select b1 from t2); 1284id select_type table type possible_keys key key_len ref rows Extra 12851 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1286select min(a1) from t1 where 7 in (select b1 from t2); 1287min(a1) 1288NULL 1289# but when we go around MWL#90 code, the problem still shows up: 1290explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; 1291id select_type table type possible_keys key key_len ref rows Extra 12921 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 12932 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1294select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; 1295min(a1) 1296NULL 1297set @@optimizer_switch= @local_optimizer_switch; 1298drop table t1,t2; 1299create table t1 (a char(2), b varchar(10)); 1300insert into t1 values ('a', 'aaa'); 1301insert into t1 values ('aa', 'aaaa'); 1302explain select a,b from t1 where b in (select a from t1); 1303id select_type table type possible_keys key key_len ref rows Extra 13041 PRIMARY t1 ALL NULL NULL NULL NULL 2 13051 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 Using where 13062 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 1307select a,b from t1 where b in (select a from t1); 1308a b 1309prepare st1 from "select a,b from t1 where b in (select a from t1)"; 1310execute st1; 1311a b 1312execute st1; 1313a b 1314drop table t1; 1315# 1316# BUG#49630: Segfault in select_describe() with double 1317# nested subquery and materialization 1318# 1319CREATE TABLE t1 (t1i int); 1320CREATE TABLE t2 (t2i int); 1321CREATE TABLE t3 (t3i int); 1322CREATE TABLE t4 (t4i int); 1323INSERT INTO t1 VALUES (1); 1324INSERT INTO t2 VALUES (1),(2); 1325INSERT INTO t3 VALUES (1),(2); 1326INSERT INTO t4 VALUES (1),(2); 1327 1328EXPLAIN 1329SELECT t1i 1330FROM t1 JOIN t4 ON t1i=t4i 1331WHERE (t1i) IN ( 1332SELECT t2i 1333FROM t2 1334WHERE (t2i) IN ( 1335SELECT max(t3i) 1336FROM t3 1337GROUP BY t3i 1338) 1339); 1340id select_type table type possible_keys key key_len ref rows Extra 13411 PRIMARY t1 system NULL NULL NULL NULL 1 13421 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 const 1 13431 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 13441 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 13453 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using temporary 1346DROP TABLE t1,t2,t3,t4; 1347CREATE TABLE t1 ( 1348pk INTEGER AUTO_INCREMENT, 1349col_int_nokey INTEGER, 1350col_int_key INTEGER, 1351col_varchar_key VARCHAR(1), 1352PRIMARY KEY (pk), 1353KEY (col_int_key), 1354KEY (col_varchar_key, col_int_key) 1355) 1356; 1357INSERT INTO t1 ( 1358col_int_key, col_int_nokey, col_varchar_key 1359) 1360VALUES 1361(2, NULL, 'w'), 1362(9, 7, 'm'), 1363(3, 9, 'm'), 1364(9, 7, 'k'), 1365(NULL, 4, 'r'), 1366(9, 2, 't'), 1367(3, 6, 'j'), 1368(8, 8, 'u'), 1369(8, NULL, 'h'), 1370(53, 5, 'o'), 1371(0, NULL, NULL), 1372(5, 6, 'k'), 1373(166, 188, 'e'), 1374(3, 2, 'n'), 1375(0, 1, 't'), 1376(1, 1, 'c'), 1377(9, 0, 'm'), 1378(5, 9, 'y'), 1379(6, NULL, 'f'), 1380(2, 4, 'd') 1381; 1382SELECT table2.col_varchar_key AS field1, 1383table2.col_int_nokey AS field2 1384FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2 1385ON (table2.col_varchar_key = table1.col_varchar_key ) ) 1386WHERE table1.pk = 6 1387HAVING ( field2 ) IN 1388( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2 1389FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2 1390ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) ) 1391ORDER BY field2 1392; 1393field1 field2 1394t 1 1395t 2 1396drop table t1; 1397# 1398# BUG#53103: MTR test ps crashes in optimize_cond() 1399# when running with --debug 1400# 1401CREATE TABLE t1(track varchar(15)); 1402INSERT INTO t1 VALUES ('CAD'), ('CAD'); 1403PREPARE STMT FROM 1404"SELECT 1 FROM t1 1405 WHERE 1406 track IN (SELECT track FROM t1 1407 GROUP BY track 1408 HAVING track>='CAD')"; 1409EXECUTE STMT ; 14101 14111 14121 1413EXECUTE STMT ; 14141 14151 14161 1417DEALLOCATE PREPARE STMT; 1418DROP TABLE t1; 1419# End of BUG#53103 1420# 1421# BUG#54511 - Assertion failed: cache != 0L in file 1422# sql_select.cc::sub_select_cache on HAVING 1423# 1424CREATE TABLE t1 (i int(11)); 1425CREATE TABLE t2 (c char(1)); 1426CREATE TABLE t3 (c char(1)); 1427INSERT INTO t1 VALUES (1), (2); 1428INSERT INTO t2 VALUES ('a'), ('b'); 1429INSERT INTO t3 VALUES ('x'), ('y'); 1430SELECT COUNT( i ),i 1431FROM t1 1432HAVING ('c') 1433IN (SELECT t2.c FROM (t2 JOIN t3)); 1434COUNT( i ) i 1435DROP TABLE t1,t2,t3; 1436# End BUG#54511 1437# 1438# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 1439# on subquery in FROM 1440# 1441CREATE TABLE t1 (a INTEGER); 1442CREATE TABLE t2 (b INTEGER); 1443INSERT INTO t2 VALUES (1); 1444set @tmp_optimizer_switch=@@optimizer_switch; 1445set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1446explain SELECT a FROM ( 1447SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1448) table1; 1449id select_type table type possible_keys key key_len ref rows Extra 14501 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 14512 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 14523 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1453SELECT a FROM ( 1454SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1455) table1; 1456a 1457set optimizer_switch=@tmp_optimizer_switch; 1458DROP TABLE t1, t2; 1459# End BUG#56367 1460# 1461# Bug#59833 - materialization=on/off leads to different result set 1462# when using IN 1463# 1464CREATE TABLE t1 ( 1465pk int NOT NULL, 1466f1 int DEFAULT NULL, 1467PRIMARY KEY (pk) 1468) ENGINE=MyISAM; 1469CREATE TABLE t2 ( 1470pk int NOT NULL, 1471f1 int DEFAULT NULL, 1472PRIMARY KEY (pk) 1473) ENGINE=MyISAM; 1474INSERT INTO t1 VALUES (10,0); 1475INSERT INTO t2 VALUES (10,0),(11,0); 1476explain SELECT * FROM t1 JOIN t2 USING (f1) 1477WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1478id select_type table type possible_keys key key_len ref rows Extra 14791 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1480SELECT * FROM t1 JOIN t2 USING (f1) 1481WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1482f1 pk pk 1483DROP TABLE t1, t2; 1484# End Bug#59833 1485# 1486# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT 1487# 1488CREATE TABLE t1 ( 1489col_varchar_key varchar(1) DEFAULT NULL, 1490col_varchar_nokey varchar(1) DEFAULT NULL, 1491KEY col_varchar_key (col_varchar_key)) 1492; 1493INSERT INTO t1 VALUES 1494('v','v'),('r','r'); 1495CREATE TABLE t2 ( 1496col_varchar_key varchar(1) DEFAULT NULL, 1497col_varchar_nokey varchar(1) DEFAULT NULL, 1498KEY col_varchar_key(col_varchar_key)) 1499; 1500INSERT INTO t2 VALUES 1501('r','r'),('c','c'); 1502CREATE VIEW v3 AS SELECT * FROM t2; 1503SELECT DISTINCT alias2.col_varchar_key 1504FROM t1 AS alias1 JOIN v3 AS alias2 1505ON alias2.col_varchar_key = alias1.col_varchar_key 1506HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2) 1507; 1508col_varchar_key 1509r 1510DROP TABLE t1, t2; 1511DROP VIEW v3; 1512# End Bug#11852644 1513 1514# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW 1515# INSTEAD OF NULL WHEN MATERIALIZATION ON 1516 1517CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY; 1518CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY; 1519INSERT INTO t2 VALUES (8),(7); 1520CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY; 1521INSERT INTO t3 VALUES (7); 1522SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3 1523FROM t3 1524LEFT JOIN t1 1525ON t1.col_int_nokey 1526WHERE (194, 200) IN ( 1527SELECT SQ4_alias1.col_int_nokey, 1528SQ4_alias2.col_int_nokey 1529FROM t2 AS SQ4_alias1 1530JOIN 1531t2 AS SQ4_alias2 1532ON SQ4_alias2.col_int_nokey = 5 1533) 1534GROUP BY field3 ; 1535MIN(t3.col_int_nokey) field3 1536DROP TABLE t1; 1537DROP TABLE t2; 1538DROP TABLE t3; 1539CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM; 1540INSERT INTO t1 (f1, f2) VALUES (1, 1.789); 1541INSERT INTO t1 (f1, f2) VALUES (13, 1.454); 1542INSERT INTO t1 (f1, f2) VALUES (10, 1.668); 1543CREATE TABLE t2 LIKE t1; 1544INSERT INTO t2 VALUES (1, 1.789); 1545INSERT INTO t2 VALUES (13, 1.454); 1546set @local_optimizer_switch=@@optimizer_switch; 1547set @@optimizer_switch=@optimizer_switch_local_default; 1548SET @@optimizer_switch='semijoin=on,materialization=on'; 1549EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); 1550id select_type table type possible_keys key key_len ref rows Extra 15511 PRIMARY t1 ALL NULL NULL NULL NULL 3 15521 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 15532 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 1554SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); 1555COUNT(*) 15562 1557set @@optimizer_switch= @local_optimizer_switch; 1558DROP TABLE t1, t2; 1559CREATE TABLE t1 ( 1560pk int, 1561a varchar(1), 1562b varchar(4), 1563c varchar(4), 1564d varchar(4), 1565PRIMARY KEY (pk) 1566); 1567INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); 1568CREATE TABLE t2 LIKE t1; 1569INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); 1570set @local_optimizer_switch=@@optimizer_switch; 1571set @@optimizer_switch=@optimizer_switch_local_default; 1572SET @@optimizer_switch='semijoin=on,materialization=on'; 1573EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); 1574id select_type table type possible_keys key key_len ref rows Extra 15751 PRIMARY t1 ALL NULL NULL NULL NULL 2 15761 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 15772 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan 1578SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); 1579pk 15802 1581SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); 1582pk 15832 1584DROP TABLE t1, t2; 1585set optimizer_switch=@local_optimizer_switch; 1586# 1587# BUG#50019: Wrong result for IN-subquery with materialization 1588# 1589create table t1(i int); 1590insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1591create table t2(i int); 1592insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1593create table t3(i int); 1594insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1595select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); 1596i 15971 15982 15993 16004 1601set @local_optimizer_switch=@@optimizer_switch; 1602set session optimizer_switch='materialization=off,in_to_exists=on'; 1603select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); 1604i 16054 16063 16072 16081 1609set session optimizer_switch=@local_optimizer_switch; 1610drop table t1, t2, t3; 1611create table t0 (a int); 1612insert into t0 values (0),(1),(2); 1613create table t1 (a int); 1614insert into t1 values (0),(1),(2); 1615explain select a, a in (select a from t1) from t0; 1616id select_type table type possible_keys key key_len ref rows Extra 16171 PRIMARY t0 ALL NULL NULL NULL NULL 3 16182 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 1619select a, a in (select a from t1) from t0; 1620a a in (select a from t1) 16210 1 16221 1 16232 1 1624prepare s from 'select a, a in (select a from t1) from t0'; 1625execute s; 1626a a in (select a from t1) 16270 1 16281 1 16292 1 1630update t1 set a=123; 1631execute s; 1632a a in (select a from t1) 16330 0 16341 0 16352 0 1636drop table t0, t1; 1637set optimizer_switch='firstmatch=on'; 1638# 1639# MWL#90, review feedback: check what happens when the subquery 1640# looks like candidate for MWL#90 checking at the first glance 1641# but then subselect_hash_sj_engine::init_permanent() discovers 1642# that it's not possible to perform duplicate removal for the 1643# selected datatypes, and so materialization isn't applicable after 1644# all. 1645# 1646set @blob_len = 1024; 1647set @suffix_len = @blob_len - @prefix_len; 1648create table t1_1024 (a1 blob(1024), a2 blob(1024)); 1649create table t2_1024 (b1 blob(1024), b2 blob(1024)); 1650insert into t1_1024 values 1651(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); 1652insert into t1_1024 values 1653(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 1654insert into t1_1024 values 1655(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 1656insert into t2_1024 values 1657(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); 1658insert into t2_1024 values 1659(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); 1660insert into t2_1024 values 1661(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); 1662explain 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'); 1663id select_type table type possible_keys key key_len ref rows Extra 16641 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 Using where 16652 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 Using where 1666select 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'); 1667left(a1,7) left(a2,7) 16681 - 01x 2 - 01x 1669drop table t1_1024, t2_1024; 1670# 1671# BUG##836491: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization 1672# 1673CREATE TABLE t1 (c int, d varchar(1), KEY(d)) ; 1674INSERT INTO t1 VALUES (2,'x'),(2,'x'),(2,'j'),(2,'c'); 1675CREATE TABLE t2 (a int, d varchar(1)) ; 1676INSERT INTO t2 VALUES (1,'x'); 1677CREATE TABLE t3 (d varchar(1)) ; 1678INSERT INTO t3 VALUES ('x'),('x'),('j'),('c'); 1679SELECT t2.a, t1.c 1680FROM t1, t2 1681WHERE t2.d IN ( SELECT d FROM t3 ) 1682AND t1.d = t2.d 1683GROUP BY 1 , 2; 1684a c 16851 2 1686drop table t1,t2,t3; 1687# 1688# BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization 1689# 1690CREATE TABLE t1 (a varchar(1)); 1691INSERT INTO t1 VALUES ('a'),('a'); 1692CREATE TABLE t2 (a varchar(1)); 1693CREATE TABLE t3 (a int); 1694INSERT INTO t3 VALUES (1),(2); 1695CREATE TABLE t4 (a varchar(1)); 1696INSERT INTO t4 VALUES ('a'),('a'); 1697SELECT t1.a 1698FROM t1 1699WHERE t1.a IN ( 1700SELECT t2.a 1701FROM t2, t3 1702) 1703HAVING a IN ( 1704SELECT a 1705FROM t4 1706); 1707a 1708DROP TABLE t1, t2, t3, t4; 1709# 1710# BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization 1711# 1712CREATE TABLE t1 (a int) ; 1713INSERT IGNORE INTO t1 VALUES (1),(1); 1714CREATE TABLE t2 (a int); 1715INSERT INTO t2 VALUES (1); 1716CREATE TABLE t3 (a int); 1717CREATE TABLE t4 (a int); 1718INSERT INTO t4 VALUES (2),(2); 1719CREATE TABLE t5 (a int); 1720INSERT INTO t5 VALUES (1); 1721SELECT * FROM t1 1722WHERE (a) IN ( 1723SELECT t5.a 1724FROM ( 1725t2 1726LEFT JOIN ( t3 , t4 ) 1727ON 1 = 1 1728) 1729JOIN t5 1730); 1731a 17321 17331 1734DROP TABLE t1,t2,t3,t4,t5; 1735# 1736# BUG#836532: Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization 1737# 1738CREATE TABLE t2 (a int); 1739INSERT IGNORE INTO t2 VALUES ('a'),('a'); 1740Warnings: 1741Warning 1366 Incorrect integer value: 'a' for column `test`.`t2`.`a` at row 1 1742Warning 1366 Incorrect integer value: 'a' for column `test`.`t2`.`a` at row 2 1743CREATE TABLE t4 (a varchar(1)); 1744INSERT INTO t4 VALUES ('m'),('o'); 1745CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ; 1746INSERT INTO t3 VALUES ('b','b'); 1747CREATE TABLE t5 (a varchar(1), KEY (a)) ; 1748INSERT INTO t5 VALUES ('d'),('e'); 1749SELECT * 1750FROM t2 1751WHERE t2.a = ALL ( 1752SELECT t4.a 1753FROM t4 1754WHERE t4.a IN ( 1755SELECT t3.a 1756FROM t3 , t5 1757WHERE ( t5.a = t3.b ) 1758) 1759); 1760a 17610 17620 1763DROP TABLE t2,t3,t4,t5; 1764# 1765# BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization 1766# 1767set @tmp_860300=@@optimizer_switch; 1768set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; 1769CREATE TABLE t1 (f2 int); 1770INSERT INTO t1 VALUES (9),(6); 1771CREATE TABLE t3 (f4 int); 1772CREATE TABLE t4 (f6 varchar(1)); 1773SELECT * 1774FROM t3 1775WHERE 'h' IN (SELECT f6 1776FROM t4 1777WHERE 5 IN (SELECT f2 FROM t1) 1778GROUP BY t4.f6); 1779f4 1780DROP TABLE t1,t3,t4; 1781set optimizer_switch=@tmp_860300; 1782# 1783# BUG#860535: Assertion `keypart_map' failed in mi_rkey with semijoin 1784# 1785set @tmp_860535=@@optimizer_switch; 1786set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; 1787CREATE TABLE t1 (f3 int) ; 1788INSERT INTO t1 VALUES (1),(7); 1789CREATE TABLE t2 (f3 int , f5 varchar(1), KEY (f3)) ; 1790INSERT INTO t2 VALUES (7,'b'); 1791CREATE TABLE t3 (f3 int , f4 varchar(1) , KEY(f3), KEY (f4,f3)) ; 1792INSERT INTO t3 VALUES (1,'t'),(7,'g'); 1793CREATE TABLE t4 1794SELECT f3 1795FROM t1 WHERE ( f3 ) NOT IN ( 1796SELECT f3 1797FROM t2 1798WHERE f5 IN ( 1799SELECT f4 1800FROM t3 1801WHERE t3.f3 < 3 1802) 1803); 1804SELECT * FROM t4; 1805f3 18061 18077 1808DROP TABLE t1, t2, t3, t4; 1809set optimizer_switch=@tmp_860535; 1810# 1811# BUG#860553: Crash in create_ref_for_key with semijoin + materialization 1812# 1813CREATE TABLE t1 (f1 int) ; 1814CREATE TABLE t2 (f5 varchar(52) NOT NULL) ; 1815CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)); 1816CREATE TABLE t4 (f3 int, KEY (f3)); 1817INSERT INTO t4 VALUES (17),(20); 1818CREATE TABLE t5 (f2 int); 1819INSERT INTO t5 VALUES (0),(0); 1820SELECT * 1821FROM t1 1822JOIN t2 1823ON ( t2.f5 ) IN ( 1824SELECT t3.f4 1825FROM t3 1826WHERE ( 1 ) IN ( 1827SELECT t4.f3 1828FROM t4 , t5 1829) 1830); 1831f1 f5 1832DROP TABLE t1, t2, t3, t4, t5; 1833# 1834# BUG#868908: Crash in check_simple_equality() with semijoin + materialization + prepared statement 1835# 1836CREATE TABLE t1 ( a int ); 1837CREATE TABLE t3 ( b int, c int) ; 1838CREATE TABLE t2 ( a int ) ; 1839CREATE TABLE t4 ( a int , c int) ; 1840PREPARE st1 FROM " 1841SELECT STRAIGHT_JOIN * 1842FROM t1 1843WHERE ( 3 ) IN ( 1844 SELECT t3.b 1845 FROM t3 1846 LEFT JOIN ( 1847 t2 STRAIGHT_JOIN t4 ON ( t4.c = t2.a ) 1848 ) ON ( t4.a = t3.c ) 1849); 1850"; 1851EXECUTE st1; 1852a 1853EXECUTE st1; 1854a 1855DROP TABLE t1,t2,t3,t4; 1856# 1857# BUG#901032: Wrong result for MIN/MAX on an indexed column with materialization and semijoin 1858# 1859CREATE TABLE t1 ( a INT, KEY(a) ); 1860INSERT INTO t1 VALUES (1); 1861CREATE TABLE t2 ( b INT ); 1862INSERT INTO t2 VALUES (2); 1863CREATE TABLE t3 ( c INT ); 1864INSERT INTO t3 VALUES (2); 1865SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c); 1866MIN(a) 18671 1868DROP TABLE t1,t2,t3; 1869# 1870# 1871# BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread 1872# 1873CREATE TABLE t1 ( a INT ); 1874INSERT INTO t1 VALUES (1), (2); 1875CREATE TABLE t2 ( b INT ); 1876INSERT INTO t2 VALUES (3), (4); 1877CREATE TABLE t3 ( c INT ); 1878INSERT INTO t3 VALUES (5), (6); 1879SELECT * FROM t1 WHERE EXISTS ( 1880SELECT DISTINCT b FROM t2 1881WHERE b <= a 1882AND b IN ( SELECT c FROM t3 GROUP BY c ) 1883); 1884a 1885DROP TABLE t1,t2,t3; 1886# 1887# BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED 1888# 1889CREATE TABLE t1 ( a INT, KEY(a) ); 1890INSERT INTO t1 VALUES (8); 1891EXPLAIN EXTENDED 1892SELECT * FROM t1 1893WHERE a IN ( SELECT MIN(a) FROM t1 ); 1894id select_type table type possible_keys key key_len ref rows filtered Extra 18951 PRIMARY t1 system a NULL NULL NULL 1 100.00 18961 PRIMARY <subquery2> system NULL NULL NULL NULL 1 100.00 18972 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 1898Warnings: 1899Note 1003 /* select#1 */ select 8 AS `a` from dual where 1 1900DROP TABLE t1; 1901# 1902# BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON 1903# 1904CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; 1905INSERT INTO t1 VALUES (4); 1906CREATE TABLE t2 ( b INT NOT NULL, c INT ); 1907INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1); 1908SELECT * FROM t1 LEFT JOIN t2 ON ( a = b ) 1909WHERE a IN ( SELECT c FROM t2 ); 1910a b c 19114 4 2 19124 4 2 19134 4 4 1914DROP TABLE t1,t2; 1915# 1916# BUG#922254: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) 1917# 1918CREATE TABLE t1 ( a VARCHAR(3) ); 1919CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) ); 1920INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron'); 1921EXPLAIN 1922SELECT * FROM 1923( SELECT * FROM t1 ) AS alias1, 1924t2 AS alias2 1925WHERE b = a AND a IN ( 1926SELECT alias3.c 1927FROM t2 AS alias3, t2 AS alias4 1928WHERE alias4.c = alias3.b 1929); 1930id select_type table type possible_keys key key_len ref rows Extra 19311 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1932DROP TABLE t1,t2; 1933# 1934# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result 1935# 1936create table t1 (a int, b int); 1937insert into t1 values (7,5), (3,3), (5,4), (9,3); 1938create table t2 (a int, b int, index i_a(a)); 1939insert into t2 values 1940(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); 1941explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); 1942id select_type table type possible_keys key key_len ref rows Extra 19431 PRIMARY t1 ALL NULL NULL NULL NULL 4 19441 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 19452 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where 1946select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); 1947a b 19487 5 19493 3 1950drop table t1,t2; 1951# 1952# BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE 1953# 1954CREATE TABLE t1 (a INT); 1955INSERT INTO t1 VALUES (0),(8); 1956SELECT STRAIGHT_JOIN MIN(a) FROM t1 1957WHERE a IN ( 1958SELECT a FROM t1 1959WHERE 'condition'='impossible' 1960 ); 1961MIN(a) 1962NULL 1963DROP TABLE t1; 1964# 1965# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT 1966# 1967CREATE TABLE t1(a int); 1968INSERT INTO t1 values(1),(2); 1969CREATE TABLE t2(a int); 1970INSERT INTO t2 values(1),(2); 1971# Should use Materialization: 1972EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); 1973id select_type table type possible_keys key key_len ref rows Extra 19741 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 19751 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 19762 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary 1977flush status; 1978CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); 1979SHOW STATUS LIKE 'Created_tmp_tables'; 1980Variable_name Value 1981Created_tmp_tables 2 1982DROP TABLE t1,t2,t3; 1983# 1984# BUG#939009: Crash with aggregate function in IN subquery 1985# 1986SET @local_optimizer_switch=@@optimizer_switch; 1987SET optimizer_switch='materialization=on,semijoin=on'; 1988CREATE TABLE t1 (a int, b int); 1989INSERT INTO t1 VALUES (7,1), (4,2), (7,7); 1990CREATE TABLE t2 ( c INT ); 1991INSERT INTO t2 VALUES (4), (7), (6); 1992EXPLAIN EXTENDED 1993SELECT * FROM t1 1994WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); 1995id select_type table type possible_keys key key_len ref rows filtered Extra 19961 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 19971 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 19982 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 1999Warnings: 2000Note 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) 2001SELECT * FROM t1 2002WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); 2003a b 20047 7 2005EXPLAIN 2006SELECT * FROM t1 2007WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); 2008id select_type table type possible_keys key key_len ref rows Extra 20091 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 20101 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 20112 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2012SELECT * FROM t1 2013WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); 2014a b 2015SET optimizer_switch=@local_optimizer_switch; 2016DROP TABLE t1,t2; 2017# 2018# BUG#946055: Crash with semijoin IN subquery when hash join is used 2019# 2020CREATE TABLE t1 (a int); 2021INSERT INTO t1 VALUES (7); 2022CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); 2023INSERT INTO t2 VALUES 2024(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), 2025(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); 2026SET @local_optimizer_switch=@@optimizer_switch; 2027SET join_cache_level=2; 2028EXPLAIN 2029SELECT a, c FROM t1, t2 2030WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2031WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2032id select_type table type possible_keys key key_len ref rows Extra 20331 PRIMARY t1 system NULL NULL NULL NULL 1 20341 PRIMARY t2 index c c 5 NULL 8 Using index 20351 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 20362 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 20372 MATERIALIZED s1 ALL c NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 20383 SUBQUERY t2 ALL NULL NULL NULL NULL 8 2039SELECT a, c FROM t1, t2 2040WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2041WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2042a c 20437 1 20447 1 20457 1 2046SET optimizer_switch='join_cache_hashed=on'; 2047SET join_cache_level=4; 2048EXPLAIN 2049SELECT a, c FROM t1, t2 2050WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2051WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2052id select_type table type possible_keys key key_len ref rows Extra 20531 PRIMARY t1 system NULL NULL NULL NULL 1 20541 PRIMARY t2 index c c 5 NULL 8 Using index 20551 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 20562 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 20572 MATERIALIZED s1 hash_ALL c #hash#$hj 5 const 8 Using where; Using join buffer (flat, BNLH join) 20583 SUBQUERY t2 ALL NULL NULL NULL NULL 8 2059SELECT a, c FROM t1, t2 2060WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 2061WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); 2062a c 20637 1 20647 1 20657 1 2066SET optimizer_switch=@local_optimizer_switch; 2067SET join_cache_level=@save_join_cache_level; 2068DROP TABLE t1,t2; 2069# 2070# BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization 2071# 2072CREATE TABLE t1 ( a VARCHAR(1) ); 2073INSERT INTO t1 VALUES ('y'),('z'); 2074CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) ); 2075INSERT INTO t2 VALUES ('v','v'),('v','v'); 2076CREATE VIEW v2 AS SELECT * FROM t2; 2077PREPARE ps FROM ' 2078SELECT a FROM t1, v2 2079WHERE ( c, b ) IN ( SELECT b, b FROM t2 ) 2080GROUP BY a '; 2081EXECUTE ps; 2082a 2083y 2084z 2085EXECUTE ps; 2086a 2087y 2088z 2089DROP VIEW v2; 2090DROP TABLE t1, t2; 2091# 2092# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 2093# 2094CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; 2095INSERT INTO t1 VALUES ('b','b'),('e','e'); 2096CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; 2097INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); 2098SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); 2099a1 a2 b1 b2 2100b b v v 2101b b s s 2102b b y y 2103DROP TABLE t1,t2; 2104# 2105# MDEV-4465: Reproducible crash (mysqld got signal 11) in multi_delete::initialize_tables with semijoin+materialization 2106# 2107CREATE TABLE t1 ( 2108id int(11) NOT NULL 2109); 2110CREATE TABLE t2 ( 2111id int(11) NOT NULL, 2112a_id int(11) DEFAULT NULL 2113); 2114insert into t1 values (1), (2), (3); 2115insert into t2 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 3), (7, 3); 2116delete t2 from t2 where a_id in (select * from (select t1.id from t1 limit 2) as x); 2117drop table t1,t2; 2118# This must be at the end: 2119set optimizer_switch=@save_optimizer_switch; 2120set join_cache_level=@save_join_cache_level; 2121# 2122# MDEV-4908: Assertion `((Item_cond *) cond)->functype() == 2123# ((Item_cond *) new_item)->functype()' fails on a query with 2124# IN and equal conditions, AND/OR, materialization+semijoin 2125# 2126SET @local_optimizer_switch=@@optimizer_switch; 2127SET optimizer_switch = 'materialization=on,semijoin=on'; 2128CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; 2129INSERT INTO t1 VALUES (1,3,5),(2,4,6); 2130SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); 2131pk a b 2132drop table t1; 2133SET optimizer_switch=@local_optimizer_switch; 2134# 2135# MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries 2136# 2137CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; 2138INSERT INTO t1 VALUES (1,3,5),(2,4,6); 2139SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); 2140pk a b 2141DROP TABLE t1; 2142# 2143# MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 2144# execution of PS with IN subqueries, materialization+semijoin 2145# 2146CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2147INSERT INTO t1 VALUES (1),(3); 2148CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2149CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; 2150INSERT INTO t2 VALUES (8),(9); 2151PREPARE stmt FROM " 2152SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) 2153"; 2154EXECUTE stmt; 2155a 2156EXECUTE stmt; 2157a 2158DROP TABLE t1, t2; 2159DROP VIEW v2; 2160# 2161# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON 2162# 2163set tmp_memory_table_size=0; 2164CREATE TABLE t1 (a INT); 2165INSERT INTO t1 VALUES (1),(2); 2166CREATE TABLE t2 (b INT); 2167INSERT INTO t2 VALUES (3),(4); 2168SELECT * FROM t1 AS t1_1, t1 AS t1_2 2169WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); 2170a a 2171DROP TABLE t1,t2; 2172set tmp_memory_table_size=default; 2173# End of 5.3 tests 2174# 2175# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries 2176# 2177set @tmp_mdev5056=@@join_cache_level; 2178SET join_cache_level = 2; 2179CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM; 2180INSERT INTO t1 VALUES 2181('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'), 2182('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'), 2183('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'), 2184('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM'); 2185CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM; 2186INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN'); 2187SELECT * FROM t1 AS alias1, t1 AS alias2 2188WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 ); 2189c1 c2 c1 c2 2190CA ML CA ML 2191CA ML RO ML 2192DROP TABLE t1,t2; 2193set join_cache_level=@tmp_mdev5056; 2194# 2195# MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 2196# execution of PS with IN subqueries, materialization+semijoin 2197# 2198CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2199INSERT INTO t1 VALUES (1),(3); 2200CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2201CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; 2202INSERT INTO t2 VALUES (8),(9); 2203PREPARE stmt FROM " 2204SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) 2205"; 2206EXECUTE stmt; 2207a 2208EXECUTE stmt; 2209a 2210DROP TABLE t1, t2; 2211DROP VIEW v2; 2212# 2213# MDEV-6289 : Unexpected results when querying information_schema 2214# 2215CREATE TABLE t1 ( 2216id int(11) unsigned NOT NULL AUTO_INCREMENT, 2217db varchar(254) NOT NULL DEFAULT '', 2218PRIMARY KEY (id), 2219UNIQUE KEY db (db) 2220) DEFAULT CHARSET=utf8; 2221INSERT INTO t1 (db) VALUES ('mysqltest1'),('mysqltest2'),('mysqltest3'),('mysqltest4'); 2222drop database if exists mysqltest1; 2223drop database if exists mysqltest2; 2224drop database if exists mysqltest3; 2225drop database if exists mysqltest4; 2226create database mysqltest1; 2227create database mysqltest2; 2228create database mysqltest3; 2229create database mysqltest4; 2230SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; 2231db 2232mysqltest4 2233mysqltest3 2234mysqltest2 2235mysqltest1 2236EXPLAIN EXTENDED 2237SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; 2238id select_type table type possible_keys key key_len ref rows filtered Extra 22391 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort 22401 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index 22412 MATERIALIZED schemata ALL NULL NULL NULL NULL NULL NULL 2242Warnings: 2243Note 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 2244drop table t1; 2245drop database mysqltest1; 2246drop database mysqltest2; 2247drop database mysqltest3; 2248drop database mysqltest4; 2249# 2250# MDEV-7810 Wrong result on execution of a query as a PS 2251# (both 1st and further executions) 2252CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; 2253INSERT INTO t1 VALUES (0),(8); 2254SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); 2255a 22560 2257PREPARE stmt FROM " 2258SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) 2259"; 2260execute stmt; 2261a 22620 2263execute stmt; 2264a 22650 2266drop table t1; 2267# 2268# MDEV-12429: IN subquery used in WHERE of EXISTS subquery 2269# 2270CREATE TABLE t1 ( 2271pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; 2272INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); 2273SELECT sq1.f2 FROM t1 AS sq1 2274WHERE EXISTS ( SELECT * FROM t1 AS sq2 2275WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2276f2 2277foo 2278set @local_optimizer_switch= @@optimizer_switch; 2279set optimizer_switch='exists_to_in=off'; 2280EXPLAIN 2281SELECT sq1.f2 FROM t1 AS sq1 2282WHERE EXISTS ( SELECT * FROM t1 AS sq2 2283WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2284id select_type table type possible_keys key key_len ref rows Extra 22851 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where 22862 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 22872 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 22883 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2289# this checks the result set above 2290set optimizer_switch= 'materialization=off,semijoin=off'; 2291SELECT sq1.f2 FROM t1 AS sq1 2292WHERE EXISTS ( SELECT * FROM t1 AS sq2 2293WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 2294f2 2295foo 2296set optimizer_switch= @local_optimizer_switch; 2297DROP TABLE t1; 2298# 2299# MDEV-12145: IN subquery used in WHERE of EXISTS subquery 2300# 2301CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; 2302INSERT INTO t1 VALUES (4),(6); 2303CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; 2304INSERT INTO t2 VALUES (8),(7),(1); 2305CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; 2306INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); 2307set @local_optimizer_switch= @@optimizer_switch; 2308set optimizer_switch='exists_to_in=off'; 2309SELECT * FROM t1 2310WHERE EXISTS ( SELECT * FROM t2, t3 2311WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2312f1 23136 2314EXPLAIN EXTENDED 2315SELECT * FROM t1 2316WHERE EXISTS ( SELECT * FROM t2, t3 2317WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2318id select_type table type possible_keys key key_len ref rows filtered Extra 23191 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 23202 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 23212 DEPENDENT SUBQUERY t2 range i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) 23222 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index 23233 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 2324Warnings: 2325Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 2326Note 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)) 2327# this checks the result set above 2328set optimizer_switch= 'materialization=off,semijoin=off'; 2329SELECT * FROM t1 2330WHERE EXISTS ( SELECT * FROM t2, t3 2331WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); 2332f1 23336 2334set optimizer_switch= @local_optimizer_switch; 2335DROP TABLE t1,t2,t3; 2336# 2337# MDEV-9686: IN subquery used in WHERE of a subquery from select list 2338# 2339CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); 2340INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); 2341CREATE TABLE t2 (f2 INT); 2342INSERT INTO t2 VALUES (1),(2),(3),(4),(5); 2343# t1.pk is always IN ( SELECT f2 FROM t2 ), 2344# so the IN condition should be true for every row, 2345# and thus COUNT(*) should always return 5 2346SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2347WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2348pk f1 sq 23491 4 5 23502 3 5 23513 3 5 23524 6 5 23535 3 5 2354EXPLAIN EXTENDED 2355SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2356WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2357id select_type table type possible_keys key key_len ref rows filtered Extra 23581 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 23592 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 23602 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) 23613 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 2362Warnings: 2363Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 2364Note 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` 2365# this checks the result set above 2366set @local_optimizer_switch= @@optimizer_switch; 2367set optimizer_switch= 'materialization=off,semijoin=off'; 2368SELECT pk, f1, ( SELECT COUNT(*) FROM t2 2369WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; 2370pk f1 sq 23711 4 5 23722 3 5 23733 3 5 23744 6 5 23755 3 5 2376set optimizer_switch= @local_optimizer_switch; 2377DROP TABLE t1,t2; 2378# 2379# mdev-12838: scan of materialized of semi-join subquery in join 2380# 2381set @local_optimizer_switch=@@optimizer_switch; 2382CREATE TABLE t1 ( 2383dispatch_group varchar(32), 2384assignment_group varchar(32), 2385sys_id char(32), 2386PRIMARY KEY (sys_id), 2387KEY idx1 (dispatch_group), 2388KEY idx2 (assignment_group) 2389) ENGINE=MyISAM; 2390CREATE TABLE t2 ( 2391ugroup varchar(32), 2392user varchar(32), 2393sys_id char(32), 2394PRIMARY KEY (sys_id), 2395KEY idx3 (ugroup), 2396KEY idx4 (user) 2397) ENGINE=MyISAM; 2398CREATE TABLE t3 ( 2399type mediumtext, 2400sys_id char(32), 2401PRIMARY KEY (sys_id) 2402) ENGINE=MyISAM; 2403set optimizer_switch='materialization=off'; 2404explain SELECT t1.assignment_group 2405FROM t1, t3 2406WHERE t1.assignment_group = t3.sys_id AND 2407t1.dispatch_group IN 2408(SELECT t2.ugroup 2409FROM t2, t3 t3_i 2410WHERE t2.ugroup = t3_i.sys_id AND 2411t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2412t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2413id select_type table type possible_keys key key_len ref rows Extra 24141 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary 24151 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 24161 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary 24171 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index 2418SELECT t1.assignment_group 2419FROM t1, t3 2420WHERE t1.assignment_group = t3.sys_id AND 2421t1.dispatch_group IN 2422(SELECT t2.ugroup 2423FROM t2, t3 t3_i 2424WHERE t2.ugroup = t3_i.sys_id AND 2425t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2426t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2427assignment_group 2428df50316637232000158bbfc8bcbe5d23 2429e08fad2637232000158bbfc8bcbe5d39 2430ec70316637232000158bbfc8bcbe5d60 24317b10fd2637232000158bbfc8bcbe5d30 2432ebb4620037332000158bbfc8bcbe5d89 2433set optimizer_switch='materialization=on'; 2434explain SELECT t1.assignment_group 2435FROM t1, t3 2436WHERE t1.assignment_group = t3.sys_id AND 2437t1.dispatch_group IN 2438(SELECT t2.ugroup 2439FROM t2, t3 t3_i 2440WHERE t2.ugroup = t3_i.sys_id AND 2441t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2442t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2443id select_type table type possible_keys key key_len ref rows Extra 24441 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 24451 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where 24461 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index 24472 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where 24482 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 2449SELECT t1.assignment_group 2450FROM t1, t3 2451WHERE t1.assignment_group = t3.sys_id AND 2452t1.dispatch_group IN 2453(SELECT t2.ugroup 2454FROM t2, t3 t3_i 2455WHERE t2.ugroup = t3_i.sys_id AND 2456t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND 2457t2.user = '86826bf03710200044e0bfc8bcbe5d79'); 2458assignment_group 2459df50316637232000158bbfc8bcbe5d23 2460e08fad2637232000158bbfc8bcbe5d39 2461ec70316637232000158bbfc8bcbe5d60 24627b10fd2637232000158bbfc8bcbe5d30 2463ebb4620037332000158bbfc8bcbe5d89 2464DROP TABLE t1,t2,t3; 2465set optimizer_switch=@local_optimizer_switch; 2466# 2467# MDEV-16751: Server crashes in st_join_table::cleanup or 2468# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 2469# 2470set @save_join_cache_level= @@join_cache_level; 2471set join_cache_level=4; 2472CREATE TABLE t1 ( id int NOT NULL); 2473INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); 2474CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; 2475INSERT INTO t2 VALUES (11,11),(12,12),(13,13); 2476explain 2477SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 2478id select_type table type possible_keys key key_len ref rows Extra 24791 PRIMARY t1 ALL NULL NULL NULL NULL 9 24801 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 24812 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2482SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 24831 24841 24851 24861 2487set @@join_cache_level= @save_join_cache_level; 2488alter table t1 add key(id); 2489explain 2490SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 2491id select_type table type possible_keys key key_len ref rows Extra 24921 PRIMARY t1 index id id 4 NULL 9 Using index 24931 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 24942 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2495SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 24961 24971 24981 24991 2500drop table t1,t2; 2501# 2502# MDEV-15454: Nested SELECT IN returns wrong results 2503# 2504CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); 2505CREATE TABLE t2 ( a int, b int ); 2506INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); 2507CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); 2508INSERT INTO t3 (c, b) VALUES (27, 96); 2509CREATE PROCEDURE prepare_data() 2510BEGIN 2511DECLARE i INT DEFAULT 1; 2512WHILE i < 1000 DO 2513INSERT INTO t1 (a) VALUES (i); 2514INSERT INTO t2 (a,b) VALUES (i,56); 2515INSERT INTO t3 (c,b) VALUES (i,i); 2516SET i = i + 1; 2517END WHILE; 2518END$$ 2519CALL prepare_data(); 2520SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); 2521a 25227878 25233465 25241403 25254189 25268732 25275 2528set @local_optimizer_switch= @@optimizer_switch; 2529SET optimizer_switch='materialization=off'; 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 2534SET optimizer_switch='materialization=on'; 2535SELECT t1.a FROM t1 2536WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; 2537a 25385 2539drop procedure prepare_data; 2540set @@optimizer_switch= @local_optimizer_switch; 2541drop table t1,t2,t3; 2542CREATE TABLE t1 ( id int NOT NULL, key(id)); 2543INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); 2544CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); 2545INSERT INTO t2 VALUES (11,11),(12,12),(13,13); 2546CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; 2547explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 2548id select_type table type possible_keys key key_len ref rows Extra 25491 PRIMARY t1 index id id 4 NULL 9 Using index 25501 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 25512 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where 2552SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 25531 25541 25551 25561 2557drop table t1,t2; 2558drop view v1; 2559# 2560# MDEV-19580: function invocation in the left part of IN subquery 2561# 2562create table t1 (id int, a varchar(50), b int); 2563insert into t1 values 2564(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); 2565create table t2 (id int, a varchar(50), x int); 2566insert into t2 values 2567(1,'grand',1),(2,'average',1),(3,'serf',0); 2568create table t3 (d1 date, d2 date, t1_id int, t2_id int ); 2569insert into t3 values 2570('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), 2571('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); 2572create table t4 ( id int, a varchar(50) ); 2573insert into t4 values 2574(1,'songwriter'),(2,'song character'); 2575create function f1(who int, dt date) returns int 2576deterministic 2577begin 2578declare result int; 2579select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; 2580return result; 2581end$$ 2582create function f2(who int, dt date) returns int 2583begin 2584declare result int; 2585select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; 2586return result; 2587end$$ 2588# Deterministic function in left part of IN subquery: semi-join is OK 2589select * from t1 2590left join t4 on t1.b = t4.id 2591where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); 2592id a b id a 25933 paul 1 1 songwriter 25944 art 1 1 songwriter 25951 mrs 2 2 song character 2596explain extended select * from t1 2597left join t4 on t1.b = t4.id 2598where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); 2599id select_type table type possible_keys key key_len ref rows filtered Extra 26001 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 26011 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 26021 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 26032 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2604Warnings: 2605Note 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` 2606# Non-deterministic function in left part of IN subq: semi-join is OK 2607select * from t1 2608left join t4 on t1.b = t4.id 2609where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2610id a b id a 26113 paul 1 1 songwriter 26124 art 1 1 songwriter 26131 mrs 2 2 song character 2614explain extended select * from t1 2615left join t4 on t1.b = t4.id 2616where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2617id select_type table type possible_keys key key_len ref rows filtered Extra 26181 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 26191 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 26201 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 26212 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2622Warnings: 2623Note 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` 2624select t1.*, t4.*, 2625(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s 2626from t1 left join t4 on t1.b = t4.id 2627where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2628id a b id a s 26293 paul 1 1 songwriter 1 26304 art 1 1 songwriter 1 26311 mrs 2 2 song character 2 2632explain extended select t1.*, t4.*, 2633(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s 2634from t1 left join t4 on t1.b = t4.id 2635where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); 2636id select_type table type possible_keys key key_len ref rows filtered Extra 26371 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 26381 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where 26391 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 26403 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where 26412 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where 2642Warnings: 2643Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 2644Note 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` 2645drop function f1; 2646drop function f2; 2647drop table t1,t2,t3,t4; 2648# End of 5.5 tests 2649# 2650# MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT 2651# 2652create table t0(a int); 2653insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2654create table t1 (a int, b int, c int); 2655insert into t1 2656select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 2657from t0 A, t0 B, t0 C; 2658create table t2 (a int, b int, c int); 2659insert into t2 select A.a, A.a, A.a from t1 A; 2660insert into t2 select * from t2; 2661insert into t2 select * from t2; 2662create table t3 as select * from t2 limit 1; 2663# The testcase only makes sense if the following uses Materialization: 2664explain 2665select * from t1 where (a,b) in (select max(a),b from t2 group by b); 2666id select_type table type possible_keys key key_len ref rows Extra 26671 PRIMARY t1 ALL NULL NULL NULL NULL 1000 Using where 26681 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 26692 MATERIALIZED t2 ALL NULL NULL NULL NULL 4000 Using temporary 2670flush status; 2671replace into t3 2672select * from t1 where (a,b) in (select max(a),b from t2 group by b); 2673# Sequential reads: 2674# 1K is read from t1 2675# 4K is read from t2 2676# 1K groups is read from the tmp. table 2677# 2678# Lookups: 2679# 4K lookups in group by table 2680# 1K lookups in temp.table 2681# 2682# Writes: 2683# 2x 1K writes to temporary tables (grouping table and subquery materialization table 2684# 2685# The point is that neither counter should be in the millions (this 2686# will happen if Materialization is not used 2687show status where Variable_name like 'Handler_read%' or Variable_name like 'Handler_%write%'; 2688Variable_name Value 2689Handler_read_first 0 2690Handler_read_key 5004 2691Handler_read_last 0 2692Handler_read_next 0 2693Handler_read_prev 0 2694Handler_read_retry 0 2695Handler_read_rnd 0 2696Handler_read_rnd_deleted 0 2697Handler_read_rnd_next 6003 2698Handler_tmp_write 2000 2699Handler_write 1000 2700drop table t0,t1,t2,t3; 2701# 2702# MDEV-7971: Assertion `name != __null' failed in ACL_internal_schema_registry::lookup 2703# on 2nd execution os PS with multi-table update 2704# 2705CREATE TABLE t1 (f1 INT); 2706INSERT INTO t1 VALUES (1),(2); 2707CREATE TABLE t2 (f2 INT); 2708INSERT INTO t2 VALUES (3),(4); 2709CREATE TABLE t3 (f3 INT); 2710INSERT INTO t3 VALUES (5),(6); 2711PREPARE stmt FROM ' 2712 UPDATE t1, t2 2713 SET f1 = 5 2714 WHERE 8 IN ( SELECT MIN(f3) FROM t3 ) 2715'; 2716EXECUTE stmt; 2717EXECUTE stmt; 2718DROP TABLE t1,t2,t3; 2719# 2720# MDEV-10389: Query returns different results on a debug vs non-debug build of the same revision 2721# 2722CREATE TABLE t1 (i1 INT, i2 INT NOT NULL); 2723INSERT INTO t1 VALUES (1,4),(2,6); 2724SELECT * FROM t1 AS alias1 2725WHERE alias1.i1 IN ( 2726SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) 2727); 2728i1 i2 27291 4 27302 6 2731DROP TABLE t1; 2732