1set @@optimizer_switch='optimize_join_buffer_size=on'; 2set @@optimizer_switch='semijoin_with_cache=on'; 3set @@optimizer_switch='outer_join_with_cache=on'; 4set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 5set join_cache_level=6; 6drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; 7set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; 8create table t1 (oref int, grp int, ie int) ; 9insert into t1 (oref, grp, ie) values 10(1, 1, 1), 11(1, 1, 1), 12(1, 2, NULL), 13(2, 1, 3), 14(3, 1, 4), 15(3, 2, NULL); 16create table t2 (oref int, a int); 17insert into t2 values 18(1, 1), 19(2, 2), 20(3, 3), 21(4, NULL), 22(2, NULL); 23select a, oref, a in (select max(ie) 24from t1 where oref=t2.oref group by grp) Z from t2; 25a oref Z 261 1 1 272 2 0 283 3 NULL 29NULL 4 0 30NULL 2 NULL 31explain extended 32select a, oref, a in (select max(ie) 33from t1 where oref=t2.oref group by grp) Z from t2; 34id select_type table type possible_keys key key_len ref rows filtered Extra 351 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 362 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 37Warnings: 38Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 39Note 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` 40explain extended 41select a, oref from t2 42where a in (select max(ie) from t1 where oref=t2.oref group by grp); 43id select_type table type possible_keys key key_len ref rows filtered Extra 441 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where 452 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 46Warnings: 47Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 48Note 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`))))) 49select a, oref, a in ( 50select max(ie) from t1 where oref=t2.oref group by grp union 51select max(ie) from t1 where oref=t2.oref group by grp 52) Z from t2; 53a oref Z 541 1 1 552 2 0 563 3 NULL 57NULL 4 0 58NULL 2 NULL 59create table t3 (a int); 60insert into t3 values (NULL), (NULL); 61flush status; 62select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 63a in (select max(ie) from t1 where oref=4 group by grp) 640 650 66show status like 'Handler_read_rnd_next'; 67Variable_name Value 68Handler_read_rnd_next 11 69select ' ^ This must show 11' Z; 70Z 71 ^ This must show 11 72set @save_optimizer_switch=@@optimizer_switch; 73set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 74explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 75id select_type table type possible_keys key key_len ref rows filtered Extra 761 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 772 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary 78Warnings: 79Note 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` 80set @@optimizer_switch=@save_optimizer_switch; 81drop table t1, t2, t3; 82create table t1 (a int, oref int, key(a)); 83insert into t1 values 84(1, 1), 85(1, NULL), 86(2, 3), 87(2, NULL), 88(3, NULL); 89insert into t1 values (5, 7), (8, 9), (4, 1); 90create table t2 (a int, oref int); 91insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); 92select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 93oref a Z 941 1 1 952 2 0 963 NULL NULL 974 NULL 0 98explain extended 99select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 100id select_type table type possible_keys key key_len ref rows filtered Extra 1011 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 1022 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where 103Warnings: 104Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 105Note 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` 106flush status; 107select oref, a from t2 where a in (select a from t1 where oref=t2.oref); 108oref a 1091 1 110show status like '%Handler_read_rnd_next'; 111Variable_name Value 112Handler_read_rnd_next 5 113delete from t2; 114insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); 115set optimizer_switch='subquery_cache=off'; 116flush status; 117select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; 118oref a Z 1190 NULL 0 1200 NULL 0 1210 NULL 0 1220 NULL 0 123show status like '%Handler_read%'; 124Variable_name Value 125Handler_read_first 0 126Handler_read_key 0 127Handler_read_last 0 128Handler_read_next 0 129Handler_read_prev 0 130Handler_read_retry 0 131Handler_read_rnd 0 132Handler_read_rnd_deleted 0 133Handler_read_rnd_next 41 134select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; 135Z 136No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. 137set @@optimizer_switch=@save_optimizer_switch; 138drop table t1, t2; 139create table t1 (a int, b int, primary key (a)); 140insert into t1 values (1,1), (3,1),(100,1); 141create table t2 (a int, b int); 142insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); 143select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; 144a b Z 1451 1 1 1462 1 0 147NULL 1 NULL 148NULL 0 0 149drop table t1, t2; 150create table t1 (a int, b int, key(a)); 151insert into t1 values 152(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 153create table t2 like t1; 154insert into t2 select * from t1; 155update t2 set b=1; 156create table t3 (a int, oref int); 157insert into t3 values (1, 1), (NULL,1), (NULL,0); 158select a, oref, 159t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 160from t3; 161a oref Z 1621 1 1 163NULL 1 NULL 164NULL 0 0 165explain extended 166select a, oref, 167t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 168from t3; 169id select_type table type possible_keys key key_len ref rows filtered Extra 1701 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 1712 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key 1722 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 173Warnings: 174Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 175Note 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` 176drop table t1, t2, t3; 177create table t1 (a int NOT NULL, b int NOT NULL, key(a)); 178insert into t1 values 179(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 180create table t2 like t1; 181insert into t2 select * from t1; 182update t2 set b=1; 183create table t3 (a int, oref int); 184insert into t3 values (1, 1), (NULL,1), (NULL,0); 185select a, oref, 186t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 187from t3; 188a oref Z 1891 1 1 190NULL 1 NULL 191NULL 0 0 192This must show a trig_cond: 193explain extended 194select a, oref, 195t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 196from t3; 197id select_type table type possible_keys key key_len ref rows filtered Extra 1981 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 1992 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key 2002 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 201Warnings: 202Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 203Note 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` 204drop table t1,t2,t3; 205create table t1 (oref int, grp int); 206insert into t1 (oref, grp) values 207(1, 1), 208(1, 1); 209create table t2 (oref int, a int); 210insert into t2 values 211(1, NULL), 212(2, NULL); 213select a, oref, 214a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; 215a oref Z 216NULL 1 NULL 217NULL 2 0 218This must show a trig_cond: 219explain extended 220select a, oref, 221a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; 222id select_type table type possible_keys key key_len ref rows filtered Extra 2231 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2242 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary 225Warnings: 226Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 227Note 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` 228drop table t1, t2; 229create table t1 (a int, b int, primary key (a)); 230insert into t1 values (1,1), (3,1),(100,1); 231create table t2 (a int, b int); 232insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); 233select a,b, a in (select a from t1 where t1.b = t2.b union select a from 234t1 where t1.b = t2.b) Z from t2 ; 235a b Z 2361 1 1 2372 1 0 238NULL 1 NULL 239NULL 0 0 240select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; 241a b Z 2421 1 1 2432 1 0 244NULL 1 NULL 245NULL 0 0 246drop table t1, t2; 247create table t3 (a int); 248insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 249create table t2 (a int, b int, oref int); 250insert into t2 values (NULL,1, 100), (NULL,2, 100); 251create table t1 (a int, b int, c int, key(a,b)); 252insert into t1 select 2*A, 2*A, 100 from t3; 253explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; 254id select_type table type possible_keys key key_len ref rows filtered Extra 2551 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2562 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key 257Warnings: 258Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 259Note 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` 260select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; 261a b oref Z 262NULL 1 100 0 263NULL 2 100 NULL 264create table t4 (x int); 265insert into t4 select A.a + 10*B.a from t1 A, t1 B; 266explain extended 267select a,b, oref, 268(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 269from t2; 270id select_type table type possible_keys key key_len ref rows filtered Extra 2711 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2722 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key 2732 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) 274Warnings: 275Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 276Note 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` 277select a,b, oref, 278(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 279from t2; 280a b oref Z 281NULL 1 100 0 282NULL 2 100 NULL 283drop table t1,t2,t3,t4; 284create table t1 (oref char(4), grp int, ie1 int, ie2 int); 285insert into t1 (oref, grp, ie1, ie2) values 286('aa', 10, 2, 1), 287('aa', 10, 1, 1), 288('aa', 20, 2, 1), 289('bb', 10, 3, 1), 290('cc', 10, 4, 2), 291('cc', 20, 3, 2), 292('ee', 10, 2, 1), 293('ee', 10, 1, 2), 294('ff', 20, 2, 2), 295('ff', 20, 1, 2); 296create table t2 (oref char(4), a int, b int); 297insert into t2 values 298('ee', NULL, 1), 299('bb', 2, 1), 300('ff', 2, 2), 301('cc', 3, NULL), 302('bb', NULL, NULL), 303('aa', 1, 1), 304('dd', 1, NULL); 305alter table t1 add index idx(ie1,ie2); 306select 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 ; 307oref a b Z 308cc 3 NULL NULL 309insert into t2 values ('new1', 10,10); 310insert into t1 values ('new1', 1234, 10, NULL); 311select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 312oref a b Z 313new1 10 10 NULL 314explain extended 315select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 316id select_type table type possible_keys key key_len ref rows filtered Extra 3171 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where 3182 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key 319Warnings: 320Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 321Note 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 322drop table t1, t2; 323create table t1 (oref char(4), grp int, ie int); 324insert into t1 (oref, grp, ie) values 325('aa', 10, 2), 326('aa', 10, 1), 327('aa', 20, NULL), 328('bb', 10, 3), 329('cc', 10, 4), 330('cc', 20, NULL), 331('ee', 10, NULL), 332('ee', 10, NULL), 333('ff', 20, 2), 334('ff', 20, 1); 335create table t2 (oref char(4), a int); 336insert into t2 values 337('ee', NULL), 338('bb', 2), 339('ff', 2), 340('cc', 3), 341('aa', 1), 342('dd', NULL), 343('bb', NULL); 344select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 345oref a Z 346ee NULL NULL 347bb 2 0 348ff 2 1 349cc 3 NULL 350aa 1 1 351dd NULL 0 352bb NULL NULL 353select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 354oref a 355aa 1 356ff 2 357select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 358oref a 359bb 2 360dd NULL 361select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 362oref a Z 363ee NULL NULL 364bb 2 0 365ff 2 0 366cc 3 NULL 367aa 1 1 368dd NULL 0 369bb NULL NULL 370select oref, a from t2 where 371a in (select min(ie) from t1 where oref=t2.oref group by grp); 372oref a 373aa 1 374select oref, a from t2 where 375a not in (select min(ie) from t1 where oref=t2.oref group by grp); 376oref a 377bb 2 378ff 2 379dd NULL 380update t1 set ie=3 where oref='ff' and ie=1; 381select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by 382grp) Z from t2; 383oref a Z 384ee NULL NULL 385bb 2 0 386ff 2 1 387cc 3 NULL 388aa 1 1 389dd NULL 0 390bb NULL NULL 391select oref, a from t2 where a in (select min(ie) from t1 where 392oref=t2.oref group by grp); 393oref a 394ff 2 395aa 1 396select oref, a from t2 where a not in (select min(ie) from t1 where 397oref=t2.oref group by grp); 398oref a 399bb 2 400dd NULL 401select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by 402grp having min(ie) > 1) Z from t2; 403oref a Z 404ee NULL 0 405bb 2 0 406ff 2 1 407cc 3 0 408aa 1 0 409dd NULL 0 410bb NULL NULL 411select oref, a from t2 where a in (select min(ie) from t1 where 412oref=t2.oref group by grp having min(ie) > 1); 413oref a 414ff 2 415select oref, a from t2 where a not in (select min(ie) from t1 where 416oref=t2.oref group by grp having min(ie) > 1); 417oref a 418ee NULL 419bb 2 420cc 3 421aa 1 422dd NULL 423alter table t1 add index idx(ie); 424explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 425id select_type table type possible_keys key key_len ref rows Extra 4261 PRIMARY t2 ALL NULL NULL NULL NULL 7 4272 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key 428select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 429oref a Z 430ee NULL NULL 431bb 2 0 432ff 2 1 433cc 3 NULL 434aa 1 1 435dd NULL 0 436bb NULL NULL 437select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 438oref a 439aa 1 440ff 2 441select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 442oref a 443bb 2 444dd NULL 445alter table t1 drop index idx; 446alter table t1 add index idx(oref,ie); 447explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 448id select_type table type possible_keys key key_len ref rows Extra 4491 PRIMARY t2 ALL NULL NULL NULL NULL 7 4502 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key 451select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 452oref a Z 453ee NULL NULL 454bb 2 0 455ff 2 1 456cc 3 NULL 457aa 1 1 458dd NULL 0 459bb NULL NULL 460select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 461oref a 462ff 2 463aa 1 464select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 465oref a 466bb 2 467dd NULL 468explain 469select oref, a, 470a in (select min(ie) from t1 where oref=t2.oref 471group by grp having min(ie) > 1) Z 472from t2; 473id select_type table type possible_keys key key_len ref rows Extra 4741 PRIMARY t2 ALL NULL NULL NULL NULL 7 4752 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary 476select oref, a, 477a in (select min(ie) from t1 where oref=t2.oref 478group by grp having min(ie) > 1) Z 479from t2; 480oref a Z 481ee NULL 0 482bb 2 0 483ff 2 1 484cc 3 0 485aa 1 0 486dd NULL 0 487bb NULL NULL 488select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 489group by grp having min(ie) > 1); 490oref a 491ff 2 492select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 493group by grp having min(ie) > 1); 494oref a 495ee NULL 496bb 2 497cc 3 498aa 1 499dd NULL 500drop table t1,t2; 501create table t1 (oref char(4), grp int, ie1 int, ie2 int); 502insert into t1 (oref, grp, ie1, ie2) values 503('aa', 10, 2, 1), 504('aa', 10, 1, 1), 505('aa', 20, 2, 1), 506('bb', 10, 3, 1), 507('cc', 10, 4, 2), 508('cc', 20, 3, 2), 509('ee', 10, 2, 1), 510('ee', 10, 1, 2), 511('ff', 20, 2, 2), 512('ff', 20, 1, 2); 513create table t2 (oref char(4), a int, b int); 514insert into t2 values 515('ee', NULL, 1), 516('bb', 2, 1), 517('ff', 2, 2), 518('cc', 3, NULL), 519('bb', NULL, NULL), 520('aa', 1, 1), 521('dd', 1, NULL); 522select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 523oref a b Z 524ee NULL 1 NULL 525bb 2 1 0 526ff 2 2 1 527cc 3 NULL NULL 528bb NULL NULL NULL 529aa 1 1 1 530dd 1 NULL 0 531select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 532oref a b 533aa 1 1 534ff 2 2 535select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); 536oref a b 537bb 2 1 538dd 1 NULL 539select oref, a, b, 540(a,b) in (select min(ie1),max(ie2) from t1 541where oref=t2.oref group by grp) Z 542from t2; 543oref a b Z 544ee NULL 1 0 545bb 2 1 0 546ff 2 2 0 547cc 3 NULL NULL 548bb NULL NULL NULL 549aa 1 1 1 550dd 1 NULL 0 551select oref, a, b from t2 where 552(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); 553oref a b 554aa 1 1 555select oref, a, b from t2 where 556(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); 557oref a b 558ee NULL 1 559bb 2 1 560ff 2 2 561dd 1 NULL 562alter table t1 add index idx(ie1,ie2); 563explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 564id select_type table type possible_keys key key_len ref rows Extra 5651 PRIMARY t2 ALL NULL NULL NULL NULL 7 5662 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key 567select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 568oref a b Z 569ee NULL 1 NULL 570bb 2 1 0 571ff 2 2 1 572cc 3 NULL NULL 573bb NULL NULL NULL 574aa 1 1 1 575dd 1 NULL 0 576select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 577oref a b 578aa 1 1 579ff 2 2 580select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); 581oref a b 582bb 2 1 583dd 1 NULL 584explain extended 585select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; 586id select_type table type possible_keys key key_len ref rows filtered Extra 5871 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 5882 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key 589Warnings: 590Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 591Note 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` 592drop table t1,t2; 593create table t1 (oref char(4), grp int, ie int primary key); 594insert into t1 (oref, grp, ie) values 595('aa', 10, 2), 596('aa', 10, 1), 597('bb', 10, 3), 598('cc', 10, 4), 599('cc', 20, 5), 600('cc', 10, 6); 601create table t2 (oref char(4), a int); 602insert into t2 values 603('ee', NULL), 604('bb', 2), 605('cc', 5), 606('cc', 2), 607('cc', NULL), 608('aa', 1), 609('bb', NULL); 610explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 611id select_type table type possible_keys key key_len ref rows Extra 6121 PRIMARY t2 ALL NULL NULL NULL NULL 7 6132 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key 614select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; 615oref a Z 616ee NULL 0 617bb 2 0 618cc 5 1 619cc 2 0 620cc NULL NULL 621aa 1 1 622bb NULL NULL 623select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 624oref a 625aa 1 626cc 5 627select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); 628oref a 629ee NULL 630bb 2 631cc 2 632explain 633select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 634id select_type table type possible_keys key key_len ref rows Extra 6351 PRIMARY t2 ALL NULL NULL NULL NULL 7 6362 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary 637select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; 638oref a Z 639ee NULL 0 640bb 2 0 641cc 5 1 642cc 2 0 643cc NULL NULL 644aa 1 1 645bb NULL NULL 646drop table t1,t2; 647create table t1 (a int, b int); 648insert into t1 values (0,0), (2,2), (3,3); 649create table t2 (a int, b int); 650insert into t2 values (1,1), (3,3); 651select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 652a b Z 6530 0 0 6542 2 0 6553 3 1 656insert into t2 values (NULL,4); 657select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 658a b Z 6590 0 0 6602 2 0 6613 3 1 662drop table t1,t2; 663CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); 664INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), 665(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), 666(1,9,'m'); 667CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); 668INSERT INTO t2 SELECT * FROM t1; 669SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) 670as test FROM t1 GROUP BY a; 671a MAX(b) test 6721 9 m 6732 3 h 6743 4 i 675SELECT * FROM t1 GROUP by t1.a 676HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c 677HAVING MAX(t2.b+t1.a) < 10)); 678a b c 679SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; 680a b c 6811 3 c 6822 3 h 6833 3 j 6841 4 d 6853 4 i 6861 9 m 687SELECT a, MAX(b), 688(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 689LIMIT 1) 690as cnt, 691(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 692as t_b, 693(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 694as t_b, 695(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) 696as t_b 697FROM t1 GROUP BY a; 698a MAX(b) cnt t_b t_b t_b 6991 9 1 9 m m 7002 3 1 3 h h 7013 4 1 4 i i 702SELECT a, MAX(b), 703(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 704FROM t1 GROUP BY a; 705a MAX(b) test 7061 9 m 7072 3 h 7083 4 i 709DROP TABLE t1, t2; 710set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 711CREATE TABLE t1 (a int); 712CREATE TABLE t2 (b int, PRIMARY KEY(b)); 713INSERT INTO t1 VALUES (1), (NULL), (4); 714INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6); 715EXPLAIN EXTENDED 716SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); 717id select_type table type possible_keys key key_len ref rows filtered Extra 7181 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 7191 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 7202 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 721Warnings: 722Note 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)))) 723SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); 724a 725SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); 726a 7271 7284 729DROP TABLE t1,t2; 730CREATE TABLE t1 (id int); 731CREATE TABLE t2 (id int PRIMARY KEY); 732CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); 733INSERT INTO t1 VALUES (2), (NULL), (3), (1); 734INSERT INTO t2 VALUES (234), (345), (457); 735INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); 736EXPLAIN 737SELECT * FROM t1 738WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 739WHERE t3.name='xxx' AND t2.id=t3.id); 740id select_type table type possible_keys key key_len ref rows Extra 7411 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 7422 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key 7432 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 744SELECT * FROM t1 745WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 746WHERE t3.name='xxx' AND t2.id=t3.id); 747id 7482 749NULL 7503 7511 752SELECT (t1.id IN (SELECT t2.id FROM t2,t3 753WHERE t3.name='xxx' AND t2.id=t3.id)) AS x 754FROM t1; 755x 7560 7570 7580 7590 760DROP TABLE t1,t2,t3; 761CREATE TABLE t1 (a INT NOT NULL); 762INSERT INTO t1 VALUES (1),(-1), (65),(66); 763CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); 764INSERT INTO t2 VALUES (65),(66); 765SELECT a FROM t1 WHERE a NOT IN (65,66); 766a 7671 768-1 769SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 770a 7711 772-1 773EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 774id select_type table type possible_keys key key_len ref rows Extra 7751 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 7762 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where 777DROP TABLE t1, t2; 778set @@optimizer_switch=@save_optimizer_switch; 779CREATE TABLE t1 (a INT); 780INSERT INTO t1 VALUES(1); 781CREATE TABLE t2 (placeholder CHAR(11)); 782INSERT INTO t2 VALUES("placeholder"); 783SELECT ROW(1, 2) IN (SELECT t1.a, 2) FROM t1 GROUP BY t1.a; 784ROW(1, 2) IN (SELECT t1.a, 2) 7851 786SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a; 787ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) 7881 789DROP TABLE t1, t2; 790CREATE TABLE t1 (a INT); 791INSERT INTO t1 VALUES (1),(2),(3); 792CREATE TABLE t2 SELECT * FROM t1; 793SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0); 7941 7951 7961 7971 798DROP TABLE t1, t2; 799create table t1 (a int, b decimal(13, 3)); 800insert into t1 values (1, 0.123); 801select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; 802delete from t1; 803load data infile "subselect.out.file.1" into table t1; 804select * from t1; 805a b 8061 0.123 807drop table t1; 808CREATE TABLE t1 ( 809pk INT PRIMARY KEY, 810int_key INT, 811varchar_key VARCHAR(5) UNIQUE, 812varchar_nokey VARCHAR(5) 813); 814INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p'); 815SELECT varchar_nokey 816FROM t1 817WHERE NULL NOT IN ( 818SELECT INNR.pk FROM t1 AS INNR2 819LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key ) 820WHERE INNR.varchar_key > 'n{' 821); 822varchar_nokey 823DROP TABLE t1; 824CREATE TABLE t1 (a INT); 825INSERT INTO t1 VALUES (1), (2), (11); 826# 2nd and 3rd columns should be same 827SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; 828a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22) 8291 0 0 8302 0 0 83111 0 0 832SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; 833a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12) 8341 0 0 8352 0 0 83611 1 1 837SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1; 838a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22) 8391 0 0 8402 0 0 84111 0 0 842SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1; 843a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12) 8441 0 0 8452 0 0 84611 1 1 847SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1; 848x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22) 8491 0 0 8502 0 0 85111 0 0 852# 2nd and 3rd columns should be same 853EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; 854id select_type table type possible_keys key key_len ref rows Extra 8551 PRIMARY t1 ALL NULL NULL NULL NULL 3 8563 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 8572 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 858SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; 859x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12) 8601 0 0 8612 0 0 86211 1 1 863DROP TABLE t1; 864# both columns should be same 865SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL); 866ROW(1,2) = (SELECT NULL, NULL) ROW(1,2) IN (SELECT NULL, NULL) 867NULL NULL 868SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL); 869ROW(1,2) = (SELECT 1, NULL) ROW(1,2) IN (SELECT 1, NULL) 870NULL NULL 871SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2); 872ROW(1,2) = (SELECT NULL, 2) ROW(1,2) IN (SELECT NULL, 2) 873NULL NULL 874SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); 875ROW(1,2) = (SELECT NULL, 1) ROW(1,2) IN (SELECT NULL, 1) 8760 0 877SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); 878ROW(1,2) = (SELECT 1, 1) ROW(1,2) IN (SELECT 1, 1) 8790 0 880SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); 881ROW(1,2) = (SELECT 1, 2) ROW(1,2) IN (SELECT 1, 2) 8821 1 883CREATE TABLE t1 (a INT, b INT, c INT); 884INSERT INTO t1 VALUES (1,1,1), (1,1,1); 885EXPLAIN EXTENDED 886SELECT c FROM 887( SELECT 888(SELECT COUNT(a) FROM 889(SELECT COUNT(b) FROM t1) AS x GROUP BY c 890) FROM t1 GROUP BY b 891) AS y; 892ERROR 42S22: Unknown column 'c' in 'field list' 893SHOW WARNINGS; 894Level Code Message 895Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 896Note 1981 Aggregate function 'count()' of SELECT #3 belongs to SELECT #2 897Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 898Error 1054 Unknown column 'c' in 'field list' 899DROP TABLE t1; 900End of 5.0 tests 901# 902# BUG#36896: Server crash on SELECT FROM DUAL 903# 904create table t1 (a int); 905select 1 as res from dual where (1) in (select * from t1); 906res 907drop table t1; 908create table t0 (a int); 909insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 910create table t1 ( 911a int(11) default null, 912b int(11) default null, 913key (a) 914); 915insert 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; 916create table t2 (a int(11) default null); 917insert into t2 values (0),(1); 918create table t3 (a int(11) default null); 919insert into t3 values (0),(1); 920create table t4 (a int(11) default null); 921insert into t4 values (0),(1); 922create table t5 (a int(11) default null); 923insert into t5 values (0),(1),(0),(1); 924select * from t2, t3 925where 926t2.a < 10 and 927t3.a+1 = 2 and 928t3.a in (select t1.b from t1 929where t1.a+1=t1.a+1 and 930t1.a < (select t4.a+10 931from t4, t5 limit 2)); 932ERROR 21000: Subquery returns more than 1 row 933drop table t0, t1, t2, t3, t4, t5; 934# 935# BUG#48177 - SELECTs with NOT IN subqueries containing NULL 936# values return too many records 937# 938CREATE TABLE t1 ( 939i1 int DEFAULT NULL, 940i2 int DEFAULT NULL 941) ; 942INSERT INTO t1 VALUES (1, NULL); 943INSERT INTO t1 VALUES (2, 3); 944INSERT INTO t1 VALUES (4, NULL); 945INSERT INTO t1 VALUES (4, 0); 946INSERT INTO t1 VALUES (NULL, NULL); 947CREATE TABLE t2 ( 948i1 int DEFAULT NULL, 949i2 int DEFAULT NULL 950) ; 951INSERT INTO t2 VALUES (4, NULL); 952INSERT INTO t2 VALUES (5, 0); 953 954Data in t1 955SELECT i1, i2 FROM t1; 956i1 i2 9571 NULL 9582 3 9594 NULL 9604 0 961NULL NULL 962 963Data in subquery (should be filtered out) 964SELECT i1, i2 FROM t2 ORDER BY i1; 965i1 i2 9664 NULL 9675 0 968FLUSH STATUS; 969set @save_optimizer_switch2=@@optimizer_switch; 970set optimizer_switch='subquery_cache=off'; 971 972SELECT i1, i2 973FROM t1 974WHERE (i1, i2) 975NOT IN (SELECT i1, i2 FROM t2); 976i1 i2 9771 NULL 9782 3 979 980# Check that the subquery only has to be evaluated once 981# for all-NULL values even though there are two (NULL,NULL) records 982# Baseline: 983SHOW STATUS LIKE '%Handler_read_rnd_next'; 984Variable_name Value 985Handler_read_rnd_next 18 986 987INSERT INTO t1 VALUES (NULL, NULL); 988FLUSH STATUS; 989 990SELECT i1, i2 991FROM t1 992WHERE (i1, i2) 993NOT IN (SELECT i1, i2 FROM t2); 994i1 i2 9951 NULL 9962 3 997 998# Handler_read_rnd_next should be one more than baseline 999# (read record from t1, but do not read from t2) 1000SHOW STATUS LIKE '%Handler_read_rnd_next'; 1001Variable_name Value 1002Handler_read_rnd_next 19 1003set @@optimizer_switch=@save_optimizer_switch2; 1004DROP TABLE t1,t2; 1005End of 5.1 tests 1006CREATE TABLE t1 ( 1007a int(11) NOT NULL, 1008b int(11) NOT NULL, 1009c datetime default NULL, 1010PRIMARY KEY (a), 1011KEY idx_bc (b,c) 1012); 1013INSERT INTO t1 VALUES 1014(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'), 1015(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'), 1016(406994,67,'2006-02-27 11:26:46'), (256,67,NULL), 1017(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL), 1018(406988,67,'2006-02-23 17:07:22'), (255,67,NULL), 1019(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'), 1020(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'), 1021(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'), 1022(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'), 1023(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'), 1024(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'), 1025(223456,67,NULL),(4101,67,NULL),(1133,67,NULL), 1026(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'), 1027(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'), 1028(154503,67,'2005-10-28 11:52:38'); 1029create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc; 1030create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc; 1031create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc; 1032create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc; 1033set @@optimizer_switch=@save_optimizer_switch; 1034set @@optimizer_switch='materialization=off'; 1035update t22 set c = '2005-12-08 15:58:27' where a = 255; 1036explain select t21.* from t21,t22 where t21.a = t22.a and 1037t22.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; 1038id select_type table type possible_keys key key_len ref rows Extra 10391 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort 10401 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) 10411 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join) 10421 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) 1043select t21.* from t21,t22 where t21.a = t22.a and 1044t22.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; 1045a b c 1046256 67 NULL 1047set @@optimizer_switch=@save_optimizer_switch; 1048drop table t1, t11, t12, t21, t22; 1049create table t1(a int); 1050insert into t1 values (0),(1); 1051set @@optimizer_switch='firstmatch=off,materialization=off'; 1052explain 1053select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; 1054id select_type table type possible_keys key key_len ref rows Extra 10551 PRIMARY X ALL NULL NULL NULL NULL 2 10562 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where 10572 DEPENDENT SUBQUERY Z hash_ALL NULL #hash#$hj 5 test.Y.a 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1058select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; 1059subq 1060NULL 10610 1062set @@optimizer_switch=@save_optimizer_switch; 1063drop table t1; 1064create table t0 (a int); 1065insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1066create table t1 as select * from t0; 1067insert into t1 select a+10 from t0; 1068set @@optimizer_switch='firstmatch=off,materialization=off'; 1069insert into t0 values(2); 1070explain select * from t1 where 2 in (select a from t0); 1071id select_type table type possible_keys key key_len ref rows Extra 10721 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary 10731 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1074select * from t1 where 2 in (select a from t0); 1075a 10760 10771 10782 10793 10804 10815 10826 10837 10848 10859 108610 108711 108812 108913 109014 109115 109216 109317 109418 109519 1096set @@optimizer_switch=@save_optimizer_switch; 1097set @@optimizer_switch='materialization=off'; 1098explain select * from t1 where 2 in (select a from t0); 1099id select_type table type possible_keys key key_len ref rows Extra 11001 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch 11011 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1102select * from t1 where 2 in (select a from t0); 1103a 11040 11051 11062 11073 11084 11095 11106 11117 11128 11139 111410 111511 111612 111713 111814 111915 112016 112117 112218 112319 1124set @@optimizer_switch=@save_optimizer_switch; 1125set @@optimizer_switch=@save_optimizer_switch; 1126set @@optimizer_switch='materialization=off'; 1127set @tmp_optimizer_switch=@@optimizer_switch; 1128set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1129explain select * from (select a from t0) X where a in (select a from t1); 1130id select_type table type possible_keys key key_len ref rows Extra 11311 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 11321 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 11332 DERIVED t0 ALL NULL NULL NULL NULL 11 1134drop table t0, t1; 1135set optimizer_switch=@tmp_optimizer_switch; 1136create table t0 (a int); 1137insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1138create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); 1139insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; 1140insert into t1 select * from t1 where kp1 < 20; 1141create table t3 (a int); 1142insert into t3 select A.a + 10*B.a from t0 A, t0 B; 1143explain select * from t3 where a in (select kp1 from t1 where kp1<20); 1144id select_type table type possible_keys key key_len ref rows Extra 11451 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where 11461 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) 1147create table t4 (pk int primary key); 1148insert into t4 select a from t3; 1149explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 1150and t4.pk=t1.c); 1151id select_type table type possible_keys key key_len ref rows Extra 11521 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where 11531 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where 11541 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) 1155drop table t1, t3, t4; 1156set @@optimizer_switch=@save_optimizer_switch; 1157create table t1 (a int) as select * from t0 where a < 5; 1158set @save_max_heap_table_size=@@max_heap_table_size; 1159set @@optimizer_switch='firstmatch=off,materialization=off'; 1160set @@max_heap_table_size= 16384; 1161explain 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); 1162id select_type table type possible_keys key key_len ref rows Extra 11631 PRIMARY A ALL NULL NULL NULL NULL 10 11641 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 11651 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary; Using join buffer (incremental, BNL join) 11661 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; Using join buffer (incremental, BNLH join) 11671 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (incremental, BNL join) 1168flush status; 1169select 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); 1170count(*) 11715000 1172show status like 'Created_tmp_disk_tables'; 1173Variable_name Value 1174Created_tmp_disk_tables 1 1175set @save_max_heap_table_size=@@max_heap_table_size; 1176set @@optimizer_switch=@save_optimizer_switch; 1177drop table t0, t1; 1178create table t0 (a int); 1179insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1180create table t2(a int); 1181insert into t2 values (1),(2); 1182create table t3 ( a int , filler char(100), key(a)); 1183insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; 1184explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); 1185id select_type table type possible_keys key key_len ref rows Extra 11861 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 11871 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 11882 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 1189select * from t3 where a in (select a from t2); 1190a filler 11911 filler 11922 filler 1193drop table t0, t2, t3; 1194set @@optimizer_switch='firstmatch=off,materialization=off'; 1195create table t1 (a date); 1196insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01'); 1197create table t2 (a int); 1198insert into t2 values (1),(2); 1199create table t3 (a char(10)); 1200insert into t3 select * from t1; 1201insert into t3 values (1),(2); 1202explain select * from t2 where a in (select a from t1); 1203id select_type table type possible_keys key key_len ref rows Extra 12041 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 12051 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.a 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1206explain select * from t2 where a in (select a from t2); 1207id select_type table type possible_keys key key_len ref rows Extra 12081 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 12091 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t2.a 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1210explain select * from t2 where a in (select a from t3); 1211id select_type table type possible_keys key key_len ref rows Extra 12121 PRIMARY t2 ALL NULL NULL NULL NULL 2 12131 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1214explain select * from t1 where a in (select a from t3); 1215id select_type table type possible_keys key key_len ref rows Extra 12161 PRIMARY t1 ALL NULL NULL NULL NULL 4 12171 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1218drop table t1, t2, t3; 1219create table t1 (a decimal); 1220insert into t1 values (1),(2); 1221explain select * from t1 where a in (select a from t1); 1222id select_type table type possible_keys key key_len ref rows Extra 12231 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 12241 PRIMARY t1 hash_ALL NULL #hash#$hj 6 test.t1.a 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1225drop table t1; 1226set @@optimizer_switch=@save_optimizer_switch; 1227set @@optimizer_switch=@save_optimizer_switch; 1228set @@optimizer_switch='materialization=off'; 1229create table t1 (a int); 1230insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1231create table t2 as select * from t1; 1232create table t3 (a int, b int, filler char(100), key(a)); 1233insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C; 1234explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; 1235id select_type table type possible_keys key key_len ref rows Extra 12361 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where 12371 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary; Using join buffer (flat, BNL join) 12381 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1239explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); 1240id select_type table type possible_keys key key_len ref rows Extra 12411 PRIMARY A ALL NULL NULL NULL NULL 10 Using where 12421 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 12432 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where 1244explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); 1245id select_type table type possible_keys key key_len ref rows Extra 12461 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 12472 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12482 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1249explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); 1250id select_type table type possible_keys key key_len ref rows Extra 12511 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 12522 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12532 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1254explain select straight_join * from t2 X, t2 Y 1255where X.a in (select straight_join A.a from t1 A, t1 B); 1256id select_type table type possible_keys key key_len ref rows Extra 12571 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 12581 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 12592 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 12602 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1261create table t0 (a int, b int); 1262insert into t0 values(1,1); 1263explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); 1264id select_type table type possible_keys key key_len ref rows Extra 12651 PRIMARY t0 system NULL NULL NULL NULL 1 12661 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary 12671 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1268create table t4 as select a as x, a as y from t1; 1269explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); 1270id select_type table type possible_keys key key_len ref rows Extra 12711 PRIMARY t0 system NULL NULL NULL NULL 1 12721 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary 12731 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1274drop table t0,t1,t2,t3,t4; 1275create table t0 (a int); 1276insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1277create table t1 (a int, b int, filler char(100), key(a,b)); 1278insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; 1279create table t2 as select * from t1; 1280explain select * from t2 where a in (select b from t1 where a=3); 1281id select_type table type possible_keys key key_len ref rows Extra 12821 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where 12831 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) 1284explain select * from t2 where (b,a) in (select a,b from t1 where a=3); 1285id select_type table type possible_keys key key_len ref rows Extra 12861 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where 12871 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) 1288drop table t1,t2; 1289set @@optimizer_switch=@save_optimizer_switch; 1290create table t1 (a int, b int); 1291insert into t1 select a,a from t0; 1292create table t2 (a int, b int); 1293insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; 1294set @@optimizer_switch='firstmatch=off,materialization=off'; 1295explain select * from t1 where (a,b) in (select a,b from t2); 1296id select_type table type possible_keys key key_len ref rows Extra 12971 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where 12981 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1299set @save_optimizer_search_depth=@@optimizer_search_depth; 1300set @@optimizer_search_depth=63; 1301Warnings: 1302Warning 1292 Truncated incorrect optimizer_search_depth value: '63' 1303explain select * from t1 where (a,b) in (select a,b from t2); 1304id select_type table type possible_keys key key_len ref rows Extra 13051 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where 13061 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) 1307set @@optimizer_search_depth=@save_optimizer_search_depth; 1308set @@optimizer_switch=@save_optimizer_switch; 1309drop table t0, t1, t2; 1310set @@optimizer_switch='materialization=off'; 1311create table t0 (a decimal(4,2)); 1312insert into t0 values (10.24), (22.11); 1313create table t1 as select * from t0; 1314insert into t1 select * from t0; 1315explain select * from t0 where a in (select a from t1); 1316id select_type table type possible_keys key key_len ref rows Extra 13171 PRIMARY t0 ALL NULL NULL NULL NULL 2 13181 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) 1319select * from t0 where a in (select a from t1); 1320a 132110.24 132222.11 1323drop table t0, t1; 1324create table t0(a date); 1325insert into t0 values ('2008-01-01'),('2008-02-02'); 1326create table t1 as select * from t0; 1327insert into t1 select * from t0; 1328explain select * from t0 where a in (select a from t1); 1329id select_type table type possible_keys key key_len ref rows Extra 13301 PRIMARY t0 ALL NULL NULL NULL NULL 2 13311 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) 1332select * from t0 where a in (select a from t1); 1333a 13342008-01-01 13352008-02-02 1336drop table t0, t1; 1337create table t0(a int); 1338insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1339create table t1 as select a as a, a as b, a as c from t0 where a < 3; 1340create table t2 as select a as a, a as b from t0 where a < 3; 1341insert into t2 select * from t2; 1342explain 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); 1343id select_type table type possible_keys key key_len ref rows Extra 13441 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 13451 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Start temporary; Using join buffer (flat, BNLH join) 13461 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) 13471 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) 1348drop table t0,t1,t2; 1349set @@optimizer_switch=@save_optimizer_switch; 1350 1351BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 1352 1353CREATE TABLE t1 ( 1354`pk` int(11) NOT NULL AUTO_INCREMENT, 1355`int_key` int(11) DEFAULT NULL, 1356PRIMARY KEY (`pk`), 1357KEY `int_key` (`int_key`) 1358) ENGINE=MyISAM; 1359INSERT INTO t1 VALUES (1,9),(2,3),(3,8),(4,6),(5,9),(6,5),(7,5),(8,9),(9,1),(10,10); 1360SELECT `pk` FROM t1 AS OUTR WHERE `int_key` = ALL ( 1361SELECT `int_key` FROM t1 AS INNR WHERE INNR . `pk` >= 9 1362); 1363pk 1364DROP TABLE t1; 1365 1366BUG#40118 Crash when running Batched Key Access and requiring one match for each key 1367 1368create table t0(a int); 1369insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1370create table t1 (a int, key(a)); 1371insert into t1 select * from t0; 1372alter table t1 add b int not null, add filler char(200); 1373insert into t1 select * from t1; 1374insert into t1 select * from t1; 1375set @save_join_cache_level=@@join_cache_level; 1376set join_cache_level=6; 1377select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); 1378a 13790 13801 13812 13823 13834 13845 13856 13867 13878 13889 1389set join_cache_level=@save_join_cache_level; 1390drop table t0, t1; 1391# 1392# BUG#32665 Query with dependent subquery is too slow 1393# 1394create table t1 ( 1395idIndividual int primary key 1396); 1397insert into t1 values (1),(2); 1398create table t2 ( 1399idContact int primary key, 1400contactType int, 1401idObj int 1402); 1403insert into t2 values (1,1,1),(2,2,2),(3,3,3); 1404create table t3 ( 1405idAddress int primary key, 1406idContact int, 1407postalStripped varchar(100) 1408); 1409insert into t3 values (1,1, 'foo'), (2,2,'bar'); 1410The following must be converted to a semi-join: 1411set @save_optimizer_switch=@@optimizer_switch; 1412set @@optimizer_switch='materialization=off'; 1413explain extended SELECT a.idIndividual FROM t1 a 1414WHERE a.idIndividual IN 1415( SELECT c.idObj FROM t3 cona 1416INNER JOIN t2 c ON c.idContact=cona.idContact 1417WHERE cona.postalStripped='T2H3B2' 1418 ); 1419id select_type table type possible_keys key key_len ref rows filtered Extra 14201 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary 14211 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 14221 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary 1423Warnings: 1424Note 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` 1425set @@optimizer_switch=@save_optimizer_switch; 1426drop table t1,t2,t3; 1427# 1428# BUG#47367 Crash in Name_resolution_context::process_error 1429# 1430SET SESSION optimizer_switch = 'semijoin=off'; 1431CREATE TABLE t1 (f1 INTEGER); 1432CREATE TABLE t2 LIKE t1; 1433CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 1434CALL p1; 1435f1 1436ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 1437CALL p1; 1438f1 1439DROP PROCEDURE p1; 1440# Restore the original column list of table t2: 1441ALTER TABLE t2 CHANGE COLUMN my_column f1 INT; 1442SET SESSION optimizer_switch = 'semijoin=on'; 1443# Recreate procedure so that we eliminate any caching effects 1444CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 1445CALL p1; 1446f1 1447ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 1448CALL p1; 1449ERROR 42S22: Unknown column 'f1' in 'where clause' 1450DROP PROCEDURE p1; 1451DROP TABLE t1, t2; 1452# 1453# fix of lp:824425 (prohibiting subqueries in row in 1454# left part of IN/ALL/ANY) 1455# 1456CREATE TABLE t1 ( a int) ; 1457INSERT INTO t1 VALUES (20),(30); 1458CREATE TABLE t2 (a int) ; 1459INSERT INTO t2 VALUES (3),(9); 1460CREATE TABLE t3 ( a int, b int) ; 1461INSERT INTO t3 VALUES (20,5),(30,6); 1462set @optimizer_switch_save=@@optimizer_switch; 1463SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; 1464SELECT * FROM t1 1465WHERE ( 1466( SELECT a FROM t2 WHERE a = 9 ) 1467) NOT IN ( 1468SELECT b 1469FROM t3 1470); 1471a 147220 147330 1474explain extended 1475SELECT * FROM t1 1476WHERE ( 1477( SELECT a FROM t2 WHERE a = 9 ) 1478) NOT IN ( 1479SELECT b 1480FROM t3 1481); 1482id select_type table type possible_keys key key_len ref rows filtered Extra 14831 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 14843 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 14852 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 1486Warnings: 1487Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 1488SELECT * FROM t1 1489WHERE ( 1490( SELECT a FROM t2 WHERE a = 9 ), 1491( SELECT a FROM t2 WHERE a = 3 ) 1492) NOT IN ( 1493SELECT b , a 1494FROM t3 1495); 1496ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' 1497set optimizer_switch=@optimizer_switch_save; 1498drop table t1,t2,t3; 1499End of 5.3 tests 1500# 1501# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery 1502# in the select list 1503# 1504 1505CREATE TABLE t1 ( 1506i int(11) DEFAULT NULL, 1507v varchar(1) DEFAULT NULL 1508); 1509 1510INSERT INTO t1 VALUES (8,'v'); 1511INSERT INTO t1 VALUES (9,'r'); 1512INSERT INTO t1 VALUES (NULL,'y'); 1513 1514CREATE TABLE t2 ( 1515i int(11) DEFAULT NULL, 1516v varchar(1) DEFAULT NULL, 1517KEY i_key (i) 1518); 1519 1520INSERT INTO t2 VALUES (NULL,'r'); 1521INSERT INTO t2 VALUES (0,'c'); 1522INSERT INTO t2 VALUES (0,'o'); 1523INSERT INTO t2 VALUES (2,'v'); 1524INSERT INTO t2 VALUES (7,'c'); 1525 1526SELECT i, v, (SELECT COUNT(DISTINCT i) 1527FROM t1 1528WHERE v = t2.v) as subsel 1529FROM t2; 1530i v subsel 1531NULL r 1 15320 c 0 15330 o 0 15342 v 1 15357 c 0 1536 1537EXPLAIN EXTENDED 1538SELECT i, v, (SELECT COUNT(DISTINCT i) 1539FROM t1 1540WHERE v = t2.v) as subsel 1541FROM t2; 1542id select_type table type possible_keys key key_len ref rows filtered Extra 15431 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 15442 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1545Warnings: 1546Note 1276 Field or reference 'test.t2.v' of SELECT #2 was resolved in SELECT #1 1547Note 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` 1548DROP TABLE t1,t2; 1549End of 5.6 tests 1550# end of 10.2 test 1551