1drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; 2set join_cache_level=1; 3drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; 4set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; 5create table t1 (oref int, grp int, ie int) ; 6insert into t1 (oref, grp, ie) values 7(1, 1, 1), 8(1, 1, 1), 9(1, 2, NULL), 10(2, 1, 3), 11(3, 1, 4), 12(3, 2, NULL); 13create table t2 (oref int, a int); 14insert into t2 values 15(1, 1), 16(2, 2), 17(3, 3), 18(4, NULL), 19(2, NULL); 20select a, oref, a in (select max(ie) 21from t1 where oref=t2.oref group by grp) Z from t2; 22a oref Z 231 1 1 242 2 0 253 3 NULL 26NULL 4 0 27NULL 2 NULL 28explain extended 29select a, oref, a in (select max(ie) 30from t1 where oref=t2.oref group by grp) Z from t2; 31id select_type table type possible_keys key key_len ref rows filtered Extra 321 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 332 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 34Warnings: 35Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 36Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select max(`test`.`t1`.`ie`) from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` group by `test`.`t1`.`grp` having trigcond(<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` 37explain extended 38select a, oref from t2 39where a in (select max(ie) from t1 where oref=t2.oref group by grp); 40id select_type table type possible_keys key key_len ref rows filtered Extra 411 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where 422 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 43Warnings: 44Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 45Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select max(`test`.`t1`.`ie`) from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` group by `test`.`t1`.`grp` having <cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) 46select a, oref, a in ( 47select max(ie) from t1 where oref=t2.oref group by grp union 48select max(ie) from t1 where oref=t2.oref group by grp 49) Z from t2; 50a oref Z 511 1 1 522 2 0 533 3 NULL 54NULL 4 0 55NULL 2 NULL 56create table t3 (a int); 57insert into t3 values (NULL), (NULL); 58flush status; 59select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 60a in (select max(ie) from t1 where oref=4 group by grp) 610 620 63show status like 'Handler_read_rnd_next'; 64Variable_name Value 65Handler_read_rnd_next 11 66select ' ^ This must show 11' Z; 67Z 68 ^ This must show 11 69set @save_optimizer_switch=@@optimizer_switch; 70set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 71explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 72id select_type table type possible_keys key key_len ref rows filtered Extra 731 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 742 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 75Warnings: 76Note 1003 /* select#1 */ select <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select max(`test`.`t1`.`ie`) from `test`.`t1` where `test`.`t1`.`oref` = 4 group by `test`.`t1`.`grp` having trigcond(<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` 77set @@optimizer_switch=@save_optimizer_switch; 78drop table t1, t2, t3; 79create table t1 (a int, oref int, key(a)); 80insert into t1 values 81(1, 1), 82(1, NULL), 83(2, 3), 84(2, NULL), 85(3, NULL); 86insert into t1 values (5, 7), (8, 9), (4, 1); 87create table t2 (a int, oref int); 88insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); 89select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 90oref a Z 911 1 1 922 2 0 933 NULL NULL 944 NULL 0 95explain extended 96select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 97id select_type table type possible_keys key key_len ref rows filtered Extra 981 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 992 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where 100Warnings: 101Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 102Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t2` 103flush status; 104select oref, a from t2 where a in (select a from t1 where oref=t2.oref); 105oref a 1061 1 107show status like '%Handler_read_rnd_next'; 108Variable_name Value 109Handler_read_rnd_next 5 110delete from t2; 111insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); 112set optimizer_switch='subquery_cache=off'; 113flush status; 114select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 115oref a Z 1160 NULL 0 1170 NULL 0 1180 NULL 0 1190 NULL 0 120show status like '%Handler_read%'; 121Variable_name Value 122Handler_read_first 0 123Handler_read_key 0 124Handler_read_last 0 125Handler_read_next 0 126Handler_read_prev 0 127Handler_read_retry 0 128Handler_read_rnd 0 129Handler_read_rnd_deleted 0 130Handler_read_rnd_next 41 131select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; 132Z 133No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. 134set @@optimizer_switch=@save_optimizer_switch; 135drop table t1, t2; 136create table t1 (a int, b int, primary key (a)); 137insert into t1 values (1,1), (3,1),(100,1); 138create table t2 (a int, b int); 139insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); 140select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; 141a b Z 1421 1 1 1432 1 0 144NULL 1 NULL 145NULL 0 0 146drop table t1, t2; 147create table t1 (a int, b int, key(a)); 148insert into t1 values 149(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 150create table t2 like t1; 151insert into t2 select * from t1; 152update t2 set b=1; 153create table t3 (a int, oref int); 154insert into t3 values (1, 1), (NULL,1), (NULL,0); 155select a, oref, 156t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 157from t3; 158a oref Z 1591 1 1 160NULL 1 NULL 161NULL 0 0 162explain extended 163select a, oref, 164t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 165from t3; 166id select_type table type possible_keys key key_len ref rows filtered Extra 1671 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 1682 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key 1692 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where 170Warnings: 171Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 172Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` 173drop table t1, t2, t3; 174create table t1 (a int NOT NULL, b int NOT NULL, key(a)); 175insert into t1 values 176(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 177create table t2 like t1; 178insert into t2 select * from t1; 179update t2 set b=1; 180create table t3 (a int, oref int); 181insert into t3 values (1, 1), (NULL,1), (NULL,0); 182select a, oref, 183t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 184from t3; 185a oref Z 1861 1 1 187NULL 1 NULL 188NULL 0 0 189This must show a trig_cond: 190explain extended 191select a, oref, 192t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 193from t3; 194id select_type table type possible_keys key key_len ref rows filtered Extra 1951 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 1962 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key 1972 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where 198Warnings: 199Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 200Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3` 201drop table t1,t2,t3; 202create table t1 (oref int, grp int); 203insert into t1 (oref, grp) values 204(1, 1), 205(1, 1); 206create table t2 (oref int, a int); 207insert into t2 values 208(1, NULL), 209(2, NULL); 210select a, oref, 211a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; 212a oref Z 213NULL 1 NULL 214NULL 2 0 215This must show a trig_cond: 216explain extended 217select a, oref, 218a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; 219id select_type table type possible_keys key key_len ref rows filtered Extra 2201 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2212 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary 222Warnings: 223Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 224Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having `test`.`t1`.`grp` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0)))))) AS `Z` from `test`.`t2` 225drop table t1, t2; 226create table t1 (a int, b int, primary key (a)); 227insert into t1 values (1,1), (3,1),(100,1); 228create table t2 (a int, b int); 229insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); 230select a,b, a in (select a from t1 where t1.b = t2.b union select a from 231t1 where t1.b = t2.b) Z from t2 ; 232a b Z 2331 1 1 2342 1 0 235NULL 1 NULL 236NULL 0 0 237select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; 238a b Z 2391 1 1 2402 1 0 241NULL 1 NULL 242NULL 0 0 243drop table t1, t2; 244create table t3 (a int); 245insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 246create table t2 (a int, b int, oref int); 247insert into t2 values (NULL,1, 100), (NULL,2, 100); 248create table t1 (a int, b int, c int, key(a,b)); 249insert into t1 select 2*A, 2*A, 100 from t3; 250explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; 251id select_type table type possible_keys key key_len ref rows filtered Extra 2521 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2532 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key 254Warnings: 255Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 256Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null))))) AS `Z` from `test`.`t2` 257select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; 258a b oref Z 259NULL 1 100 0 260NULL 2 100 NULL 261create table t4 (x int); 262insert into t4 select A.a + 10*B.a from t1 A, t1 B; 263explain extended 264select a,b, oref, 265(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 266from t2; 267id select_type table type possible_keys key key_len ref rows filtered Extra 2681 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2692 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key 2702 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) 271Warnings: 272Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 273Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(/* select#2 */ select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2` 274select a,b, oref, 275(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 276from t2; 277a b oref Z 278NULL 1 100 0 279NULL 2 100 NULL 280drop table t1,t2,t3,t4; 281create table t1 (oref char(4), grp int, ie1 int, ie2 int); 282insert into t1 (oref, grp, ie1, ie2) values 283('aa', 10, 2, 1), 284('aa', 10, 1, 1), 285('aa', 20, 2, 1), 286('bb', 10, 3, 1), 287('cc', 10, 4, 2), 288('cc', 20, 3, 2), 289('ee', 10, 2, 1), 290('ee', 10, 1, 2), 291('ff', 20, 2, 2), 292('ff', 20, 1, 2); 293create table t2 (oref char(4), a int, b int); 294insert into t2 values 295('ee', NULL, 1), 296('bb', 2, 1), 297('ff', 2, 2), 298('cc', 3, NULL), 299('bb', NULL, NULL), 300('aa', 1, 1), 301('dd', 1, NULL); 302alter table t1 add index idx(ie1,ie2); 303select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ; 304oref a b Z 305cc 3 NULL NULL 306insert into t2 values ('new1', 10,10); 307insert into t1 values ('new1', 1234, 10, NULL); 308select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 309oref a b Z 310new1 10 10 NULL 311explain extended 312select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 313id select_type table type possible_keys key key_len ref rows filtered Extra 3141 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where 3152 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key 316Warnings: 317Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 318Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1` or `test`.`t1`.`ie1` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2` or `test`.`t1`.`ie2` is null)) having trigcond(`test`.`t1`.`ie1` is null) and trigcond(`test`.`t1`.`ie2` is null))))) AS `Z` from `test`.`t2` where `test`.`t2`.`a` = 10 and `test`.`t2`.`b` = 10 319drop table t1, t2; 320create table t1 (oref char(4), grp int, ie int); 321insert into t1 (oref, grp, ie) values 322('aa', 10, 2), 323('aa', 10, 1), 324('aa', 20, NULL), 325('bb', 10, 3), 326('cc', 10, 4), 327('cc', 20, NULL), 328('ee', 10, NULL), 329('ee', 10, NULL), 330('ff', 20, 2), 331('ff', 20, 1); 332create table t2 (oref char(4), a int); 333insert into t2 values 334('ee', NULL), 335('bb', 2), 336('ff', 2), 337('cc', 3), 338('aa', 1), 339('dd', NULL), 340('bb', NULL); 341select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 342oref a Z 343ee NULL NULL 344bb 2 0 345ff 2 1 346cc 3 NULL 347aa 1 1 348dd NULL 0 349bb NULL NULL 350select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 351oref a 352aa 1 353ff 2 354select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 355oref a 356bb 2 357dd NULL 358select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 359oref a Z 360ee NULL NULL 361bb 2 0 362ff 2 0 363cc 3 NULL 364aa 1 1 365dd NULL 0 366bb NULL NULL 367select oref, a from t2 where 368a in (select min(ie) from t1 where oref=t2.oref group by grp); 369oref a 370aa 1 371select oref, a from t2 where 372a not in (select min(ie) from t1 where oref=t2.oref group by grp); 373oref a 374bb 2 375ff 2 376dd NULL 377update t1 set ie=3 where oref='ff' and ie=1; 378select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by 379grp) Z from t2; 380oref a Z 381ee NULL NULL 382bb 2 0 383ff 2 1 384cc 3 NULL 385aa 1 1 386dd NULL 0 387bb NULL NULL 388select oref, a from t2 where a in (select min(ie) from t1 where 389oref=t2.oref group by grp); 390oref a 391ff 2 392aa 1 393select oref, a from t2 where a not in (select min(ie) from t1 where 394oref=t2.oref group by grp); 395oref a 396bb 2 397dd NULL 398select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by 399grp having min(ie) > 1) Z from t2; 400oref a Z 401ee NULL 0 402bb 2 0 403ff 2 1 404cc 3 0 405aa 1 0 406dd NULL 0 407bb NULL NULL 408select oref, a from t2 where a in (select min(ie) from t1 where 409oref=t2.oref group by grp having min(ie) > 1); 410oref a 411ff 2 412select oref, a from t2 where a not in (select min(ie) from t1 where 413oref=t2.oref group by grp having min(ie) > 1); 414oref a 415ee NULL 416bb 2 417cc 3 418aa 1 419dd NULL 420alter table t1 add index idx(ie); 421explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 422id select_type table type possible_keys key key_len ref rows Extra 4231 PRIMARY t2 ALL NULL NULL NULL NULL 7 4242 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key 425select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 426oref a Z 427ee NULL NULL 428bb 2 0 429ff 2 1 430cc 3 NULL 431aa 1 1 432dd NULL 0 433bb NULL NULL 434select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 435oref a 436ff 2 437aa 1 438select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 439oref a 440bb 2 441dd NULL 442alter table t1 drop index idx; 443alter table t1 add index idx(oref,ie); 444explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 445id select_type table type possible_keys key key_len ref rows Extra 4461 PRIMARY t2 ALL NULL NULL NULL NULL 7 4472 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key 448select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 449oref a Z 450ee NULL NULL 451bb 2 0 452ff 2 1 453cc 3 NULL 454aa 1 1 455dd NULL 0 456bb NULL NULL 457select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 458oref a 459ff 2 460aa 1 461select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 462oref a 463bb 2 464dd NULL 465explain 466select oref, a, 467a in (select min(ie) from t1 where oref=t2.oref 468group by grp having min(ie) > 1) Z 469from t2; 470id select_type table type possible_keys key key_len ref rows Extra 4711 PRIMARY t2 ALL NULL NULL NULL NULL 7 4722 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary 473select oref, a, 474a in (select min(ie) from t1 where oref=t2.oref 475group by grp having min(ie) > 1) Z 476from t2; 477oref a Z 478ee NULL 0 479bb 2 0 480ff 2 1 481cc 3 0 482aa 1 0 483dd NULL 0 484bb NULL NULL 485select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 486group by grp having min(ie) > 1); 487oref a 488ff 2 489select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 490group by grp having min(ie) > 1); 491oref a 492ee NULL 493bb 2 494cc 3 495aa 1 496dd NULL 497drop table t1,t2; 498create table t1 (oref char(4), grp int, ie1 int, ie2 int); 499insert into t1 (oref, grp, ie1, ie2) values 500('aa', 10, 2, 1), 501('aa', 10, 1, 1), 502('aa', 20, 2, 1), 503('bb', 10, 3, 1), 504('cc', 10, 4, 2), 505('cc', 20, 3, 2), 506('ee', 10, 2, 1), 507('ee', 10, 1, 2), 508('ff', 20, 2, 2), 509('ff', 20, 1, 2); 510create table t2 (oref char(4), a int, b int); 511insert into t2 values 512('ee', NULL, 1), 513('bb', 2, 1), 514('ff', 2, 2), 515('cc', 3, NULL), 516('bb', NULL, NULL), 517('aa', 1, 1), 518('dd', 1, NULL); 519select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 520oref a b Z 521ee NULL 1 NULL 522bb 2 1 0 523ff 2 2 1 524cc 3 NULL NULL 525bb NULL NULL NULL 526aa 1 1 1 527dd 1 NULL 0 528select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 529oref a b 530aa 1 1 531ff 2 2 532select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); 533oref a b 534bb 2 1 535dd 1 NULL 536select oref, a, b, 537(a,b) in (select min(ie1),max(ie2) from t1 538where oref=t2.oref group by grp) Z 539from t2; 540oref a b Z 541ee NULL 1 0 542bb 2 1 0 543ff 2 2 0 544cc 3 NULL NULL 545bb NULL NULL NULL 546aa 1 1 1 547dd 1 NULL 0 548select oref, a, b from t2 where 549(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); 550oref a b 551aa 1 1 552select oref, a, b from t2 where 553(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); 554oref a b 555ee NULL 1 556bb 2 1 557ff 2 2 558dd 1 NULL 559alter table t1 add index idx(ie1,ie2); 560explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 561id select_type table type possible_keys key key_len ref rows Extra 5621 PRIMARY t2 ALL NULL NULL NULL NULL 7 5632 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key 564select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 565oref a b Z 566ee NULL 1 NULL 567bb 2 1 0 568ff 2 2 1 569cc 3 NULL NULL 570bb NULL NULL NULL 571aa 1 1 1 572dd 1 NULL 0 573select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 574oref a b 575ff 2 2 576aa 1 1 577select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); 578oref a b 579bb 2 1 580dd 1 NULL 581explain extended 582select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 583id select_type table type possible_keys key key_len ref rows filtered Extra 5841 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 5852 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key 586Warnings: 587Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 588Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1` or `test`.`t1`.`ie1` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2` or `test`.`t1`.`ie2` is null)) having trigcond(`test`.`t1`.`ie1` is null) and trigcond(`test`.`t1`.`ie2` is null))))) AS `Z` from `test`.`t2` 589drop table t1,t2; 590create table t1 (oref char(4), grp int, ie int primary key); 591insert into t1 (oref, grp, ie) values 592('aa', 10, 2), 593('aa', 10, 1), 594('bb', 10, 3), 595('cc', 10, 4), 596('cc', 20, 5), 597('cc', 10, 6); 598create table t2 (oref char(4), a int); 599insert into t2 values 600('ee', NULL), 601('bb', 2), 602('cc', 5), 603('cc', 2), 604('cc', NULL), 605('aa', 1), 606('bb', NULL); 607explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 608id select_type table type possible_keys key key_len ref rows Extra 6091 PRIMARY t2 ALL NULL NULL NULL NULL 7 6102 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key 611select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 612oref a Z 613ee NULL 0 614bb 2 0 615cc 5 1 616cc 2 0 617cc NULL NULL 618aa 1 1 619bb NULL NULL 620select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 621oref a 622cc 5 623aa 1 624select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 625oref a 626ee NULL 627bb 2 628cc 2 629explain 630select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 631id select_type table type possible_keys key key_len ref rows Extra 6321 PRIMARY t2 ALL NULL NULL NULL NULL 7 6332 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary 634select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 635oref a Z 636ee NULL 0 637bb 2 0 638cc 5 1 639cc 2 0 640cc NULL NULL 641aa 1 1 642bb NULL NULL 643drop table t1,t2; 644create table t1 (a int, b int); 645insert into t1 values (0,0), (2,2), (3,3); 646create table t2 (a int, b int); 647insert into t2 values (1,1), (3,3); 648select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 649a b Z 6500 0 0 6512 2 0 6523 3 1 653insert into t2 values (NULL,4); 654select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 655a b Z 6560 0 0 6572 2 0 6583 3 1 659drop table t1,t2; 660CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); 661INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), 662(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), 663(1,9,'m'); 664CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); 665INSERT INTO t2 SELECT * FROM t1; 666SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) 667as test FROM t1 GROUP BY a; 668a MAX(b) test 6691 9 m 6702 3 h 6713 4 i 672SELECT * FROM t1 GROUP by t1.a 673HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c 674HAVING MAX(t2.b+t1.a) < 10)); 675a b c 676SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; 677a b c 6781 3 c 6792 3 h 6803 3 j 6811 4 d 6823 4 i 6831 9 m 684SELECT a, MAX(b), 685(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 686LIMIT 1) 687as cnt, 688(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 689as t_b, 690(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 691as t_b, 692(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) 693as t_b 694FROM t1 GROUP BY a; 695a MAX(b) cnt t_b t_b t_b 6961 9 1 9 m m 6972 3 1 3 h h 6983 4 1 4 i i 699SELECT a, MAX(b), 700(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 701FROM t1 GROUP BY a; 702a MAX(b) test 7031 9 m 7042 3 h 7053 4 i 706DROP TABLE t1, t2; 707set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 708CREATE TABLE t1 (a int); 709CREATE TABLE t2 (b int, PRIMARY KEY(b)); 710INSERT INTO t1 VALUES (1), (NULL), (4); 711INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6); 712EXPLAIN EXTENDED 713SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); 714id select_type table type possible_keys key key_len ref rows filtered Extra 7151 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 7161 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 7172 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 718Warnings: 719Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t1`.`a` and !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) 720SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); 721a 722SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); 723a 7241 7254 726DROP TABLE t1,t2; 727CREATE TABLE t1 (id int); 728CREATE TABLE t2 (id int PRIMARY KEY); 729CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); 730INSERT INTO t1 VALUES (2), (NULL), (3), (1); 731INSERT INTO t2 VALUES (234), (345), (457); 732INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); 733EXPLAIN 734SELECT * FROM t1 735WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 736WHERE t3.name='xxx' AND t2.id=t3.id); 737id select_type table type possible_keys key key_len ref rows Extra 7381 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 7392 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key 7402 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using where 741SELECT * FROM t1 742WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 743WHERE t3.name='xxx' AND t2.id=t3.id); 744id 7452 746NULL 7473 7481 749SELECT (t1.id IN (SELECT t2.id FROM t2,t3 750WHERE t3.name='xxx' AND t2.id=t3.id)) AS x 751FROM t1; 752x 7530 7540 7550 7560 757DROP TABLE t1,t2,t3; 758CREATE TABLE t1 (a INT NOT NULL); 759INSERT INTO t1 VALUES (1),(-1), (65),(66); 760CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); 761INSERT INTO t2 VALUES (65),(66); 762SELECT a FROM t1 WHERE a NOT IN (65,66); 763a 7641 765-1 766SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 767a 7681 769-1 770EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 771id select_type table type possible_keys key key_len ref rows Extra 7721 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 7732 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where 774DROP TABLE t1, t2; 775set @@optimizer_switch=@save_optimizer_switch; 776CREATE TABLE t1 (a INT); 777INSERT INTO t1 VALUES(1); 778CREATE TABLE t2 (placeholder CHAR(11)); 779INSERT INTO t2 VALUES("placeholder"); 780SELECT ROW(1, 2) IN (SELECT t1.a, 2) FROM t1 GROUP BY t1.a; 781ROW(1, 2) IN (SELECT t1.a, 2) 7821 783SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a; 784ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) 7851 786DROP TABLE t1, t2; 787CREATE TABLE t1 (a INT); 788INSERT INTO t1 VALUES (1),(2),(3); 789CREATE TABLE t2 SELECT * FROM t1; 790SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0); 7911 7921 7931 7941 795DROP TABLE t1, t2; 796create table t1 (a int, b decimal(13, 3)); 797insert into t1 values (1, 0.123); 798select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; 799delete from t1; 800load data infile "subselect.out.file.1" into table t1; 801select * from t1; 802a b 8031 0.123 804drop table t1; 805CREATE TABLE t1 ( 806pk INT PRIMARY KEY, 807int_key INT, 808varchar_key VARCHAR(5) UNIQUE, 809varchar_nokey VARCHAR(5) 810); 811INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p'); 812SELECT varchar_nokey 813FROM t1 814WHERE NULL NOT IN ( 815SELECT INNR.pk FROM t1 AS INNR2 816LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key ) 817WHERE INNR.varchar_key > 'n{' 818); 819varchar_nokey 820DROP TABLE t1; 821CREATE TABLE t1 (a INT); 822INSERT INTO t1 VALUES (1), (2), (11); 823# 2nd and 3rd columns should be same 824SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; 825a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22) 8261 0 0 8272 0 0 82811 0 0 829SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; 830a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12) 8311 0 0 8322 0 0 83311 1 1 834SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1; 835a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22) 8361 0 0 8372 0 0 83811 0 0 839SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1; 840a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12) 8411 0 0 8422 0 0 84311 1 1 844SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1; 845x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22) 8461 0 0 8472 0 0 84811 0 0 849# 2nd and 3rd columns should be same 850EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; 851id select_type table type possible_keys key key_len ref rows Extra 8521 PRIMARY t1 ALL NULL NULL NULL NULL 3 8533 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 8542 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 855SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; 856x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12) 8571 0 0 8582 0 0 85911 1 1 860DROP TABLE t1; 861# both columns should be same 862SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL); 863ROW(1,2) = (SELECT NULL, NULL) ROW(1,2) IN (SELECT NULL, NULL) 864NULL NULL 865SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL); 866ROW(1,2) = (SELECT 1, NULL) ROW(1,2) IN (SELECT 1, NULL) 867NULL NULL 868SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2); 869ROW(1,2) = (SELECT NULL, 2) ROW(1,2) IN (SELECT NULL, 2) 870NULL NULL 871SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); 872ROW(1,2) = (SELECT NULL, 1) ROW(1,2) IN (SELECT NULL, 1) 8730 0 874SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); 875ROW(1,2) = (SELECT 1, 1) ROW(1,2) IN (SELECT 1, 1) 8760 0 877SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); 878ROW(1,2) = (SELECT 1, 2) ROW(1,2) IN (SELECT 1, 2) 8791 1 880CREATE TABLE t1 (a INT, b INT, c INT); 881INSERT INTO t1 VALUES (1,1,1), (1,1,1); 882EXPLAIN EXTENDED 883SELECT c FROM 884( SELECT 885(SELECT COUNT(a) FROM 886(SELECT COUNT(b) FROM t1) AS x GROUP BY c 887) FROM t1 GROUP BY b 888) AS y; 889ERROR 42S22: Unknown column 'c' in 'field list' 890SHOW WARNINGS; 891Level Code Message 892Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 893Note 1981 Aggregate function 'count()' of SELECT #3 belongs to SELECT #2 894Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 895Error 1054 Unknown column 'c' in 'field list' 896DROP TABLE t1; 897End of 5.0 tests 898# 899# BUG#36896: Server crash on SELECT FROM DUAL 900# 901create table t1 (a int); 902select 1 as res from dual where (1) in (select * from t1); 903res 904drop table t1; 905create table t0 (a int); 906insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 907create table t1 ( 908a int(11) default null, 909b int(11) default null, 910key (a) 911); 912insert into t1 select A.a+10*(B.a+10*C.a),A.a+10*(B.a+10*C.a) from t0 A, t0 B, t0 C; 913create table t2 (a int(11) default null); 914insert into t2 values (0),(1); 915create table t3 (a int(11) default null); 916insert into t3 values (0),(1); 917create table t4 (a int(11) default null); 918insert into t4 values (0),(1); 919create table t5 (a int(11) default null); 920insert into t5 values (0),(1),(0),(1); 921select * from t2, t3 922where 923t2.a < 10 and 924t3.a+1 = 2 and 925t3.a in (select t1.b from t1 926where t1.a+1=t1.a+1 and 927t1.a < (select t4.a+10 928from t4, t5 limit 2)); 929ERROR 21000: Subquery returns more than 1 row 930drop table t0, t1, t2, t3, t4, t5; 931# 932# BUG#48177 - SELECTs with NOT IN subqueries containing NULL 933# values return too many records 934# 935CREATE TABLE t1 ( 936i1 int DEFAULT NULL, 937i2 int DEFAULT NULL 938) ; 939INSERT INTO t1 VALUES (1, NULL); 940INSERT INTO t1 VALUES (2, 3); 941INSERT INTO t1 VALUES (4, NULL); 942INSERT INTO t1 VALUES (4, 0); 943INSERT INTO t1 VALUES (NULL, NULL); 944CREATE TABLE t2 ( 945i1 int DEFAULT NULL, 946i2 int DEFAULT NULL 947) ; 948INSERT INTO t2 VALUES (4, NULL); 949INSERT INTO t2 VALUES (5, 0); 950 951Data in t1 952SELECT i1, i2 FROM t1; 953i1 i2 9541 NULL 9552 3 9564 NULL 9574 0 958NULL NULL 959 960Data in subquery (should be filtered out) 961SELECT i1, i2 FROM t2 ORDER BY i1; 962i1 i2 9634 NULL 9645 0 965FLUSH STATUS; 966set @save_optimizer_switch2=@@optimizer_switch; 967set optimizer_switch='subquery_cache=off'; 968 969SELECT i1, i2 970FROM t1 971WHERE (i1, i2) 972NOT IN (SELECT i1, i2 FROM t2); 973i1 i2 9741 NULL 9752 3 976 977# Check that the subquery only has to be evaluated once 978# for all-NULL values even though there are two (NULL,NULL) records 979# Baseline: 980SHOW STATUS LIKE '%Handler_read_rnd_next'; 981Variable_name Value 982Handler_read_rnd_next 18 983 984INSERT INTO t1 VALUES (NULL, NULL); 985FLUSH STATUS; 986 987SELECT i1, i2 988FROM t1 989WHERE (i1, i2) 990NOT IN (SELECT i1, i2 FROM t2); 991i1 i2 9921 NULL 9932 3 994 995# Handler_read_rnd_next should be one more than baseline 996# (read record from t1, but do not read from t2) 997SHOW STATUS LIKE '%Handler_read_rnd_next'; 998Variable_name Value 999Handler_read_rnd_next 19 1000set @@optimizer_switch=@save_optimizer_switch2; 1001DROP TABLE t1,t2; 1002End of 5.1 tests 1003CREATE TABLE t1 ( 1004a int(11) NOT NULL, 1005b int(11) NOT NULL, 1006c datetime default NULL, 1007PRIMARY KEY (a), 1008KEY idx_bc (b,c) 1009); 1010INSERT INTO t1 VALUES 1011(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'), 1012(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'), 1013(406994,67,'2006-02-27 11:26:46'), (256,67,NULL), 1014(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL), 1015(406988,67,'2006-02-23 17:07:22'), (255,67,NULL), 1016(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'), 1017(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'), 1018(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'), 1019(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'), 1020(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'), 1021(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'), 1022(223456,67,NULL),(4101,67,NULL),(1133,67,NULL), 1023(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'), 1024(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'), 1025(154503,67,'2005-10-28 11:52:38'); 1026create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc; 1027create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc; 1028create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc; 1029create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc; 1030set @@optimizer_switch=@save_optimizer_switch; 1031set @@optimizer_switch='materialization=off'; 1032update t22 set c = '2005-12-08 15:58:27' where a = 255; 1033explain select t21.* from t21,t22 where t21.a = t22.a and 1034t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; 1035id select_type table type possible_keys key key_len ref rows Extra 10361 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort 10371 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 10381 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (flat, BNL join) 10391 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join) 1040select t21.* from t21,t22 where t21.a = t22.a and 1041t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; 1042a b c 1043256 67 NULL 1044set @@optimizer_switch=@save_optimizer_switch; 1045drop table t1, t11, t12, t21, t22; 1046create table t1(a int); 1047insert into t1 values (0),(1); 1048set @@optimizer_switch='firstmatch=off,materialization=off'; 1049explain 1050select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; 1051id select_type table type possible_keys key key_len ref rows Extra 10521 PRIMARY X ALL NULL NULL NULL NULL 2 10532 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where 10542 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1055select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; 1056subq 1057NULL 10580 1059set @@optimizer_switch=@save_optimizer_switch; 1060drop table t1; 1061create table t0 (a int); 1062insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1063create table t1 as select * from t0; 1064insert into t1 select a+10 from t0; 1065set @@optimizer_switch='firstmatch=off,materialization=off'; 1066insert into t0 values(2); 1067explain select * from t1 where 2 in (select a from t0); 1068id select_type table type possible_keys key key_len ref rows Extra 10691 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary 10701 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1071select * from t1 where 2 in (select a from t0); 1072a 10730 10741 10752 10763 10774 10785 10796 10807 10818 10829 108310 108411 108512 108613 108714 108815 108916 109017 109118 109219 1093set @@optimizer_switch=@save_optimizer_switch; 1094set @@optimizer_switch='materialization=off'; 1095explain select * from t1 where 2 in (select a from t0); 1096id select_type table type possible_keys key key_len ref rows Extra 10971 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch 10981 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1099select * from t1 where 2 in (select a from t0); 1100a 11010 11021 11032 11043 11054 11065 11076 11087 11098 11109 111110 111211 111312 111413 111514 111615 111716 111817 111918 112019 1121set @@optimizer_switch=@save_optimizer_switch; 1122set @@optimizer_switch=@save_optimizer_switch; 1123set @@optimizer_switch='materialization=off'; 1124set @tmp_optimizer_switch=@@optimizer_switch; 1125set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1126explain select * from (select a from t0) X where a in (select a from t1); 1127id select_type table type possible_keys key key_len ref rows Extra 11281 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 11291 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 11302 DERIVED t0 ALL NULL NULL NULL NULL 11 1131drop table t0, t1; 1132set optimizer_switch=@tmp_optimizer_switch; 1133create table t0 (a int); 1134insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1135create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); 1136insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; 1137insert into t1 select * from t1 where kp1 < 20; 1138create table t3 (a int); 1139insert into t3 select A.a + 10*B.a from t0 A, t0 B; 1140explain select * from t3 where a in (select kp1 from t1 where kp1<20); 1141id select_type table type possible_keys key key_len ref rows Extra 11421 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where 11431 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) 1144create table t4 (pk int primary key); 1145insert into t4 select a from t3; 1146explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 1147and t4.pk=t1.c); 1148id select_type table type possible_keys key key_len ref rows Extra 11491 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where 11501 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where 11511 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) 1152drop table t1, t3, t4; 1153set @@optimizer_switch=@save_optimizer_switch; 1154create table t1 (a int) as select * from t0 where a < 5; 1155set @save_max_heap_table_size=@@max_heap_table_size; 1156set @@optimizer_switch='firstmatch=off,materialization=off'; 1157set @@max_heap_table_size= 16384; 1158explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); 1159id select_type table type possible_keys key key_len ref rows Extra 11601 PRIMARY A ALL NULL NULL NULL NULL 10 11611 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 11621 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary; Using join buffer (flat, BNL join) 11631 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 11641 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (flat, BNL join) 1165flush status; 1166select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); 1167count(*) 11685000 1169show status like 'Created_tmp_disk_tables'; 1170Variable_name Value 1171Created_tmp_disk_tables 1 1172set @save_max_heap_table_size=@@max_heap_table_size; 1173set @@optimizer_switch=@save_optimizer_switch; 1174drop table t0, t1; 1175create table t0 (a int); 1176insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1177create table t2(a int); 1178insert into t2 values (1),(2); 1179create table t3 ( a int , filler char(100), key(a)); 1180insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; 1181explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); 1182id select_type table type possible_keys key key_len ref rows Extra 11831 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 11841 PRIMARY t3 ref a a 5 test.t2.a 1 11852 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 1186select * from t3 where a in (select a from t2); 1187a filler 11881 filler 11892 filler 1190drop table t0, t2, t3; 1191set @@optimizer_switch='firstmatch=off,materialization=off'; 1192create table t1 (a date); 1193insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01'); 1194create table t2 (a int); 1195insert into t2 values (1),(2); 1196create table t3 (a char(10)); 1197insert into t3 select * from t1; 1198insert into t3 values (1),(2); 1199explain select * from t2 where a in (select a from t1); 1200id select_type table type possible_keys key key_len ref rows Extra 12011 PRIMARY t2 ALL NULL NULL NULL NULL 2 12021 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1203explain select * from t2 where a in (select a from t2); 1204id select_type table type possible_keys key key_len ref rows Extra 12051 PRIMARY t2 ALL NULL NULL NULL NULL 2 12061 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1207explain select * from t2 where a in (select a from t3); 1208id select_type table type possible_keys key key_len ref rows Extra 12091 PRIMARY t2 ALL NULL NULL NULL NULL 2 12101 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1211explain select * from t1 where a in (select a from t3); 1212id select_type table type possible_keys key key_len ref rows Extra 12131 PRIMARY t1 ALL NULL NULL NULL NULL 4 12141 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1215drop table t1, t2, t3; 1216create table t1 (a decimal); 1217insert into t1 values (1),(2); 1218explain select * from t1 where a in (select a from t1); 1219id select_type table type possible_keys key key_len ref rows Extra 12201 PRIMARY t1 ALL NULL NULL NULL NULL 2 12211 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1222drop table t1; 1223set @@optimizer_switch=@save_optimizer_switch; 1224set @@optimizer_switch=@save_optimizer_switch; 1225set @@optimizer_switch='materialization=off'; 1226create table t1 (a int); 1227insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1228create table t2 as select * from t1; 1229create table t3 (a int, b int, filler char(100), key(a)); 1230insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C; 1231explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; 1232id select_type table type possible_keys key key_len ref rows Extra 12331 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where 12341 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary; Using join buffer (flat, BNL join) 12351 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary 1236explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); 1237id select_type table type possible_keys key key_len ref rows Extra 12381 PRIMARY A ALL NULL NULL NULL NULL 10 Using where 12391 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 12402 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where 1241explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); 1242id select_type table type possible_keys key key_len ref rows Extra 12431 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 12442 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12452 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1246explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); 1247id select_type table type possible_keys key key_len ref rows Extra 12481 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 12492 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12502 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1251explain select straight_join * from t2 X, t2 Y 1252where X.a in (select straight_join A.a from t1 A, t1 B); 1253id select_type table type possible_keys key key_len ref rows Extra 12541 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 12551 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 12562 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12572 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1258create table t0 (a int, b int); 1259insert into t0 values(1,1); 1260explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); 1261id select_type table type possible_keys key key_len ref rows Extra 12621 PRIMARY t0 system NULL NULL NULL NULL 1 12631 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary 12641 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary 1265create table t4 as select a as x, a as y from t1; 1266explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); 1267id select_type table type possible_keys key key_len ref rows Extra 12681 PRIMARY t0 system NULL NULL NULL NULL 1 12691 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary 12701 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary 1271drop table t0,t1,t2,t3,t4; 1272create table t0 (a int); 1273insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1274create table t1 (a int, b int, filler char(100), key(a,b)); 1275insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; 1276create table t2 as select * from t1; 1277explain select * from t2 where a in (select b from t1 where a=3); 1278id select_type table type possible_keys key key_len ref rows Extra 12791 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where 12801 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) 1281explain select * from t2 where (b,a) in (select a,b from t1 where a=3); 1282id select_type table type possible_keys key key_len ref rows Extra 12831 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where 12841 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) 1285drop table t1,t2; 1286set @@optimizer_switch=@save_optimizer_switch; 1287create table t1 (a int, b int); 1288insert into t1 select a,a from t0; 1289create table t2 (a int, b int); 1290insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; 1291set @@optimizer_switch='firstmatch=off,materialization=off'; 1292explain select * from t1 where (a,b) in (select a,b from t2); 1293id select_type table type possible_keys key key_len ref rows Extra 12941 PRIMARY t1 ALL NULL NULL NULL NULL 10 12951 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1296set @save_optimizer_search_depth=@@optimizer_search_depth; 1297set @@optimizer_search_depth=63; 1298Warnings: 1299Warning 1292 Truncated incorrect optimizer_search_depth value: '63' 1300explain select * from t1 where (a,b) in (select a,b from t2); 1301id select_type table type possible_keys key key_len ref rows Extra 13021 PRIMARY t1 ALL NULL NULL NULL NULL 10 13031 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1304set @@optimizer_search_depth=@save_optimizer_search_depth; 1305set @@optimizer_switch=@save_optimizer_switch; 1306drop table t0, t1, t2; 1307set @@optimizer_switch='materialization=off'; 1308create table t0 (a decimal(4,2)); 1309insert into t0 values (10.24), (22.11); 1310create table t1 as select * from t0; 1311insert into t1 select * from t0; 1312explain select * from t0 where a in (select a from t1); 1313id select_type table type possible_keys key key_len ref rows Extra 13141 PRIMARY t0 ALL NULL NULL NULL NULL 2 13151 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) 1316select * from t0 where a in (select a from t1); 1317a 131810.24 131922.11 1320drop table t0, t1; 1321create table t0(a date); 1322insert into t0 values ('2008-01-01'),('2008-02-02'); 1323create table t1 as select * from t0; 1324insert into t1 select * from t0; 1325explain select * from t0 where a in (select a from t1); 1326id select_type table type possible_keys key key_len ref rows Extra 13271 PRIMARY t0 ALL NULL NULL NULL NULL 2 13281 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) 1329select * from t0 where a in (select a from t1); 1330a 13312008-01-01 13322008-02-02 1333drop table t0, t1; 1334create table t0(a int); 1335insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1336create table t1 as select a as a, a as b, a as c from t0 where a < 3; 1337create table t2 as select a as a, a as b from t0 where a < 3; 1338insert into t2 select * from t2; 1339explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); 1340id select_type table type possible_keys key key_len ref rows Extra 13411 PRIMARY t1 ALL NULL NULL NULL NULL 3 13421 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join) 13431 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 13441 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) 1345drop table t0,t1,t2; 1346set @@optimizer_switch=@save_optimizer_switch; 1347 1348BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 1349 1350CREATE TABLE t1 ( 1351`pk` int(11) NOT NULL AUTO_INCREMENT, 1352`int_key` int(11) DEFAULT NULL, 1353PRIMARY KEY (`pk`), 1354KEY `int_key` (`int_key`) 1355) ENGINE=MyISAM; 1356INSERT INTO t1 VALUES (1,9),(2,3),(3,8),(4,6),(5,9),(6,5),(7,5),(8,9),(9,1),(10,10); 1357SELECT `pk` FROM t1 AS OUTR WHERE `int_key` = ALL ( 1358SELECT `int_key` FROM t1 AS INNR WHERE INNR . `pk` >= 9 1359); 1360pk 1361DROP TABLE t1; 1362 1363BUG#40118 Crash when running Batched Key Access and requiring one match for each key 1364 1365create table t0(a int); 1366insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1367create table t1 (a int, key(a)); 1368insert into t1 select * from t0; 1369alter table t1 add b int not null, add filler char(200); 1370insert into t1 select * from t1; 1371insert into t1 select * from t1; 1372set @save_join_cache_level=@@join_cache_level; 1373set join_cache_level=6; 1374select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); 1375a 13760 13771 13782 13793 13804 13815 13826 13837 13848 13859 1386set join_cache_level=@save_join_cache_level; 1387drop table t0, t1; 1388# 1389# BUG#32665 Query with dependent subquery is too slow 1390# 1391create table t1 ( 1392idIndividual int primary key 1393); 1394insert into t1 values (1),(2); 1395create table t2 ( 1396idContact int primary key, 1397contactType int, 1398idObj int 1399); 1400insert into t2 values (1,1,1),(2,2,2),(3,3,3); 1401create table t3 ( 1402idAddress int primary key, 1403idContact int, 1404postalStripped varchar(100) 1405); 1406insert into t3 values (1,1, 'foo'), (2,2,'bar'); 1407The following must be converted to a semi-join: 1408set @save_optimizer_switch=@@optimizer_switch; 1409set @@optimizer_switch='materialization=off'; 1410explain extended SELECT a.idIndividual FROM t1 a 1411WHERE a.idIndividual IN 1412( SELECT c.idObj FROM t3 cona 1413INNER JOIN t2 c ON c.idContact=cona.idContact 1414WHERE cona.postalStripped='T2H3B2' 1415 ); 1416id select_type table type possible_keys key key_len ref rows filtered Extra 14171 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary 14181 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using where 14191 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary 1420Warnings: 1421Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact` 1422set @@optimizer_switch=@save_optimizer_switch; 1423drop table t1,t2,t3; 1424# 1425# BUG#47367 Crash in Name_resolution_context::process_error 1426# 1427SET SESSION optimizer_switch = 'semijoin=off'; 1428CREATE TABLE t1 (f1 INTEGER); 1429CREATE TABLE t2 LIKE t1; 1430CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 1431CALL p1; 1432f1 1433ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 1434CALL p1; 1435f1 1436DROP PROCEDURE p1; 1437# Restore the original column list of table t2: 1438ALTER TABLE t2 CHANGE COLUMN my_column f1 INT; 1439SET SESSION optimizer_switch = 'semijoin=on'; 1440# Recreate procedure so that we eliminate any caching effects 1441CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 1442CALL p1; 1443f1 1444ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 1445CALL p1; 1446ERROR 42S22: Unknown column 'f1' in 'where clause' 1447DROP PROCEDURE p1; 1448DROP TABLE t1, t2; 1449# 1450# fix of lp:824425 (prohibiting subqueries in row in 1451# left part of IN/ALL/ANY) 1452# 1453CREATE TABLE t1 ( a int) ; 1454INSERT INTO t1 VALUES (20),(30); 1455CREATE TABLE t2 (a int) ; 1456INSERT INTO t2 VALUES (3),(9); 1457CREATE TABLE t3 ( a int, b int) ; 1458INSERT INTO t3 VALUES (20,5),(30,6); 1459set @optimizer_switch_save=@@optimizer_switch; 1460SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; 1461SELECT * FROM t1 1462WHERE ( 1463( SELECT a FROM t2 WHERE a = 9 ) 1464) NOT IN ( 1465SELECT b 1466FROM t3 1467); 1468a 146920 147030 1471explain extended 1472SELECT * FROM t1 1473WHERE ( 1474( SELECT a FROM t2 WHERE a = 9 ) 1475) NOT IN ( 1476SELECT b 1477FROM t3 1478); 1479id select_type table type possible_keys key key_len ref rows filtered Extra 14801 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 14813 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 14822 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 1483Warnings: 1484Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 1485SELECT * FROM t1 1486WHERE ( 1487( SELECT a FROM t2 WHERE a = 9 ), 1488( SELECT a FROM t2 WHERE a = 3 ) 1489) NOT IN ( 1490SELECT b , a 1491FROM t3 1492); 1493ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' 1494set optimizer_switch=@optimizer_switch_save; 1495drop table t1,t2,t3; 1496End of 5.3 tests 1497# 1498# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery 1499# in the select list 1500# 1501 1502CREATE TABLE t1 ( 1503i int(11) DEFAULT NULL, 1504v varchar(1) DEFAULT NULL 1505); 1506 1507INSERT INTO t1 VALUES (8,'v'); 1508INSERT INTO t1 VALUES (9,'r'); 1509INSERT INTO t1 VALUES (NULL,'y'); 1510 1511CREATE TABLE t2 ( 1512i int(11) DEFAULT NULL, 1513v varchar(1) DEFAULT NULL, 1514KEY i_key (i) 1515); 1516 1517INSERT INTO t2 VALUES (NULL,'r'); 1518INSERT INTO t2 VALUES (0,'c'); 1519INSERT INTO t2 VALUES (0,'o'); 1520INSERT INTO t2 VALUES (2,'v'); 1521INSERT INTO t2 VALUES (7,'c'); 1522 1523SELECT i, v, (SELECT COUNT(DISTINCT i) 1524FROM t1 1525WHERE v = t2.v) as subsel 1526FROM t2; 1527i v subsel 1528NULL r 1 15290 c 0 15300 o 0 15312 v 1 15327 c 0 1533 1534EXPLAIN EXTENDED 1535SELECT i, v, (SELECT COUNT(DISTINCT i) 1536FROM t1 1537WHERE v = t2.v) as subsel 1538FROM t2; 1539id select_type table type possible_keys key key_len ref rows filtered Extra 15401 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 15412 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1542Warnings: 1543Note 1276 Field or reference 'test.t2.v' of SELECT #2 was resolved in SELECT #1 1544Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`v` AS `v`,<expr_cache><`test`.`t2`.`v`>((/* select#2 */ select count(distinct `test`.`t1`.`i`) from `test`.`t1` where `test`.`t1`.`v` = `test`.`t2`.`v`)) AS `subsel` from `test`.`t2` 1545DROP TABLE t1,t2; 1546End of 5.6 tests 1547# end of 10.2 test 1548