1set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on'; 2set @old_opt_switch=@@optimizer_switch; 3set optimizer_switch='subquery_materialization_cost_based=off'; 4drop table if exists t1, t2, t3, t1i, t2i, t3i; 5drop view if exists v1, v2, v1m, v2m; 6create table t1 (a1 char(8), a2 char(8)); 7create table t2 (b1 char(8), b2 char(8)); 8create table t3 (c1 char(8), c2 char(8)); 9insert into t1 values ('1 - 00', '2 - 00'); 10insert into t1 values ('1 - 01', '2 - 01'); 11insert into t1 values ('1 - 02', '2 - 02'); 12insert into t2 values ('1 - 01', '2 - 01'); 13insert into t2 values ('1 - 01', '2 - 01'); 14insert into t2 values ('1 - 02', '2 - 02'); 15insert into t2 values ('1 - 02', '2 - 02'); 16insert into t2 values ('1 - 03', '2 - 03'); 17insert into t3 values ('1 - 01', '2 - 01'); 18insert into t3 values ('1 - 02', '2 - 02'); 19insert into t3 values ('1 - 03', '2 - 03'); 20insert into t3 values ('1 - 04', '2 - 04'); 21create table t1i (a1 char(8), a2 char(8)); 22create table t2i (b1 char(8), b2 char(8)); 23create table t3i (c1 char(8), c2 char(8)); 24create index it1i1 on t1i (a1); 25create index it1i2 on t1i (a2); 26create index it1i3 on t1i (a1, a2); 27create index it2i1 on t2i (b1); 28create index it2i2 on t2i (b2); 29create index it2i3 on t2i (b1, b2); 30create index it3i1 on t3i (c1); 31create index it3i2 on t3i (c2); 32create index it3i3 on t3i (c1, c2); 33insert into t1i select * from t1; 34insert into t2i select * from t2; 35insert into t3i select * from t3; 36/****************************************************************************** 37* Simple tests. 38******************************************************************************/ 39# non-indexed nullable fields 40explain extended 41select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 42id select_type table type possible_keys key key_len ref rows filtered Extra 431 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 441 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 45Warnings: 46Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0')) 47select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 48a1 a2 491 - 01 2 - 01 501 - 02 2 - 02 51explain extended 52select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 53id select_type table type possible_keys key key_len ref rows filtered Extra 541 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 551 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 56Warnings: 57Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0')) 58select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 59a1 a2 601 - 01 2 - 01 611 - 02 2 - 02 62explain extended 63select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 64id select_type table type possible_keys key key_len ref rows filtered Extra 651 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 661 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 67Warnings: 68Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0')) 69select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 70a1 a2 711 - 01 2 - 01 721 - 02 2 - 02 73explain extended 74select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 75id select_type table type possible_keys key key_len ref rows filtered Extra 761 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 772 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort 78Warnings: 79Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`min(b2)`))))) 80select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 81a1 a2 821 - 01 2 - 01 831 - 02 2 - 02 84explain extended 85select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 86id select_type table type possible_keys key key_len ref rows filtered Extra 871 SIMPLE t2i index it2i1,it2i3 it2i1 9 NULL 5 50.00 Using where; Using index; LooseScan 881 SIMPLE t1i ref it1i1,it1i3 it1i1 9 test.t2i.b1 1 100.00 NULL 89Warnings: 90Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) 91select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 92a1 a2 931 - 01 2 - 01 941 - 02 2 - 02 95explain extended 96select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); 97id select_type table type possible_keys key key_len ref rows filtered Extra 981 SIMPLE t2i index it2i1,it2i3 it2i1 9 NULL 5 50.00 Using where; Using index; LooseScan 991 SIMPLE t1i ref it1i1,it1i3 it1i1 9 test.t2i.b1 1 100.00 NULL 100Warnings: 101Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) 102select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); 103a1 a2 1041 - 01 2 - 01 1051 - 02 2 - 02 106explain extended 107select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 108id select_type table type possible_keys key key_len ref rows filtered Extra 1091 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 1101 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where 111Warnings: 112Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) 113select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 114a1 a2 1151 - 01 2 - 01 1161 - 02 2 - 02 117explain extended 118select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); 119id select_type table type possible_keys key key_len ref rows filtered Extra 1201 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 1211 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where 122Warnings: 123Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) 124select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); 125a1 a2 1261 - 01 2 - 01 1271 - 02 2 - 02 128explain extended 129select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 130id select_type table type possible_keys key key_len ref rows filtered Extra 1311 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 1322 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by 133Warnings: 134Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1i`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1i`.`a2` = `materialized-subquery`.`min(b2)`))))) 135select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 136a1 a2 1371 - 01 2 - 01 1381 - 02 2 - 02 139explain extended 140select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); 141id select_type table type possible_keys key key_len ref rows filtered Extra 1421 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1432 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 100.00 Using index for group-by 144Warnings: 145Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`max(b2)`))))) 146select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); 147a1 a2 1481 - 01 2 - 01 1491 - 02 2 - 02 150prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; 151execute st1; 152id select_type table type possible_keys key key_len ref rows Extra 1531 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1542 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 Using index for group-by 155execute st1; 156id select_type table type possible_keys key key_len ref rows Extra 1571 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1582 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 Using index for group-by 159prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; 160execute st2; 161a1 a2 1621 - 01 2 - 01 1631 - 02 2 - 02 164execute st2; 165a1 a2 1661 - 01 2 - 01 1671 - 02 2 - 02 168explain extended 169select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 170id select_type table type possible_keys key key_len ref rows filtered Extra 1711 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1722 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by 173Warnings: 174Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`min(b2)`))))) 175select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 176a1 a2 1771 - 01 2 - 01 1781 - 02 2 - 02 179select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); 180ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 181explain extended 182select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 183id select_type table type possible_keys key key_len ref rows filtered Extra 1841 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 NULL 1851 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 186Warnings: 187Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`)) 188select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 189a1 a2 1901 - 01 2 - 01 1911 - 02 2 - 02 192explain extended 193select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 194id select_type table type possible_keys key key_len ref rows filtered Extra 1951 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 1961 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where 197Warnings: 198Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`)) 199select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 200a1 a2 2011 - 01 2 - 01 2021 - 02 2 - 02 203/****************************************************************************** 204* Views, UNIONs, several levels of nesting. 205******************************************************************************/ 206# materialize the result of subquery over temp-table view 207create algorithm=merge view v1 as 208select b1, c2 from t2, t3 where b2 > c2; 209create algorithm=merge view v2 as 210select b1, c2 from t2, t3 group by b2, c2; 211Warnings: 212Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 213create algorithm=temptable view v1m as 214select b1, c2 from t2, t3 where b2 > c2; 215create algorithm=temptable view v2m as 216select b1, c2 from t2, t3 group by b2, c2; 217select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null); 218b1 c2 2191 - 02 2 - 01 2201 - 02 2 - 01 2211 - 03 2 - 01 2221 - 03 2 - 02 223select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null); 224b1 c2 2251 - 02 2 - 01 2261 - 02 2 - 01 2271 - 03 2 - 01 2281 - 03 2 - 02 229select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null); 230b1 c2 2311 - 02 2 - 01 2321 - 02 2 - 01 2331 - 03 2 - 01 2341 - 03 2 - 02 235select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null); 236b1 c2 2371 - 02 2 - 01 2381 - 02 2 - 01 2391 - 03 2 - 01 2401 - 03 2 - 02 241drop view v1, v2, v1m, v2m; 242explain extended 243select * from t1 244where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 245(a1, a2) in (select c1, c2 from t3 246where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 247id select_type table type possible_keys key key_len ref rows filtered Extra 2481 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2491 SIMPLE <subquery3> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL 2501 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery3>); Using join buffer (Block Nested Loop) 2513 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 2523 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 253Warnings: 254Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery3>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (`test`.`t1`.`a1` > '0')) 255select * from t1 256where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 257(a1, a2) in (select c1, c2 from t3 258where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 259a1 a2 2601 - 01 2 - 01 2611 - 02 2 - 02 262explain extended 263select * from t1i 264where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 265(a1, a2) in (select c1, c2 from t3i 266where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 267id select_type table type possible_keys key key_len ref rows filtered Extra 2681 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 2691 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where 2701 SIMPLE t3i ref it3i1,it3i2,it3i3 it3i1 9 test.t2i.b1 1 100.00 Using where 2711 SIMPLE t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t2i.b1,test.t2i.b2 2 100.00 Using index; FirstMatch(t1i) 272Warnings: 273Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i` join `test`.`t3i`) semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b2` > '0') and (`test`.`t2i`.`b1` > '0')) 274select * from t1i 275where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 276(a1, a2) in (select c1, c2 from t3i 277where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 278a1 a2 2791 - 01 2 - 01 2801 - 02 2 - 02 281explain extended 282select * from t1 283where (a1, a2) in (select b1, b2 from t2 284where b2 in (select c2 from t3 where c2 LIKE '%02') or 285b2 in (select c2 from t3 where c2 LIKE '%03')) and 286(a1, a2) in (select c1, c2 from t3 287where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 288id select_type table type possible_keys key key_len ref rows filtered Extra 2891 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2901 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL 2911 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop) 2925 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 2935 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 2944 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 2953 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 296Warnings: 297Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))))) 298select * from t1 299where (a1, a2) in (select b1, b2 from t2 300where b2 in (select c2 from t3 where c2 LIKE '%02') or 301b2 in (select c2 from t3 where c2 LIKE '%03')) and 302(a1, a2) in (select c1, c2 from t3 303where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 304a1 a2 3051 - 02 2 - 02 306explain extended 307select * from t1 308where (a1, a2) in (select b1, b2 from t2 309where b2 in (select c2 from t3 t3a where c1 = a1) or 310b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 311(a1, a2) in (select c1, c2 from t3 t3c 312where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 313id select_type table type possible_keys key key_len ref rows filtered Extra 3141 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3151 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL 3161 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop) 3175 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where 3185 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 3194 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3203 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 321Warnings: 322Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 323Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))) and (`test`.`t3c`.`c2` > '0')) 324select * from t1 325where (a1, a2) in (select b1, b2 from t2 326where b2 in (select c2 from t3 t3a where c1 = a1) or 327b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 328(a1, a2) in (select c1, c2 from t3 t3c 329where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 330a1 a2 3311 - 01 2 - 01 3321 - 02 2 - 02 333explain extended 334(select * from t1 335where (a1, a2) in (select b1, b2 from t2 336where b2 in (select c2 from t3 where c2 LIKE '%02') or 337b2 in (select c2 from t3 where c2 LIKE '%03') 338group by b1, b2) and 339(a1, a2) in (select c1, c2 from t3 340where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 341UNION 342(select * from t1i 343where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 344(a1, a2) in (select c1, c2 from t3i 345where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 346id select_type table type possible_keys key key_len ref rows filtered Extra 3471 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3481 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL 3491 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop) 3505 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3515 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 3524 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3533 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3547 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan 3557 UNION t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where 3567 UNION t3i ref it3i1,it3i2,it3i3 it3i1 9 test.t2i.b1 1 100.00 Using where 3577 UNION t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t2i.b1,test.t2i.b2 2 100.00 Using index; FirstMatch(t1i) 358NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL Using temporary 359Warnings: 360Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i` join `test`.`t3i`) semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b2` > '0') and (`test`.`t2i`.`b1` > '0'))) 361(select * from t1 362where (a1, a2) in (select b1, b2 from t2 363where b2 in (select c2 from t3 where c2 LIKE '%02') or 364b2 in (select c2 from t3 where c2 LIKE '%03') 365group by b1, b2) and 366(a1, a2) in (select c1, c2 from t3 367where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 368UNION 369(select * from t1i 370where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 371(a1, a2) in (select c1, c2 from t3i 372where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 373a1 a2 3741 - 02 2 - 02 3751 - 01 2 - 01 376explain extended 377select * from t1 378where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 379(a1, a2) in (select c1, c2 from t3 380where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 381id select_type table type possible_keys key key_len ref rows filtered Extra 3821 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3831 PRIMARY <subquery4> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL 3844 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3854 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 3862 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3873 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where 388NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary 389Warnings: 390Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`<subquery4>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery4>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0')) 391select * from t1 392where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 393(a1, a2) in (select c1, c2 from t3 394where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 395a1 a2 3961 - 01 2 - 01 3971 - 02 2 - 02 398explain extended 399select * from t1, t3 400where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 401(c1, c2) in (select c1, c2 from t3 402where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 403a1 = c1; 404id select_type table type possible_keys key key_len ref rows filtered Extra 4051 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4061 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (Block Nested Loop) 4071 PRIMARY <subquery4> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t3.c2 1 100.00 NULL 4084 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 4094 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop) 4102 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4113 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where 412NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary 413Warnings: 414Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`<subquery4>`.`c2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`<subquery4>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0')) 415select * from t1, t3 416where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 417(c1, c2) in (select c1, c2 from t3 418where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 419a1 = c1; 420a1 a2 c1 c2 4211 - 01 2 - 01 1 - 01 2 - 01 4221 - 02 2 - 02 1 - 02 2 - 02 423/****************************************************************************** 424* Negative tests, where materialization should not be applied. 425******************************************************************************/ 426# UNION in a subquery 427explain extended 428select * from t3 429where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 430id select_type table type possible_keys key key_len ref rows filtered Extra 4311 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 4322 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4333 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where 434NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary 435Warnings: 436Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union /* select#3 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`)))) 437select * from t3 438where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 439c1 c2 4401 - 01 2 - 01 4411 - 02 2 - 02 4421 - 03 2 - 03 443explain extended 444select * from t1 445where (a1, a2) in (select b1, b2 from t2 446where b2 in (select c2 from t3 t3a where c1 = a1) or 447b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 448(a1, a2) in (select c1, c2 from t3 t3c 449where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 450id select_type table type possible_keys key key_len ref rows filtered Extra 4511 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary 4521 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index 4531 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (Block Nested Loop) 4541 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t3c); Using join buffer (Block Nested Loop) 4554 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where 4563 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where 457Warnings: 458Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 459Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 460Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3` `t3c`) semi join (`test`.`t2`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))))) 461explain extended 462select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 463id select_type table type possible_keys key key_len ref rows filtered Extra 4641 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4652 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 466Warnings: 467Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01')))) 468select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 469a1 a2 4701 - 01 2 - 01 471explain extended 472select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 473id select_type table type possible_keys key key_len ref rows filtered Extra 4741 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4752 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 476Warnings: 477Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01')))) 478select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 479a1 a2 4801 - 01 2 - 01 481/****************************************************************************** 482* Subqueries in other uncovered clauses. 483******************************************************************************/ 484/* SELECT clause */ 485select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; 486((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL 4870 4880 4890 490/* GROUP BY clause */ 491create table columns (col int key); 492insert into columns values (1), (2); 493explain extended 494select * from t1 group by (select col from columns limit 1); 495id select_type table type possible_keys key key_len ref rows filtered Extra 4961 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 NULL 4972 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 498Warnings: 499Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1) 500select * from t1 group by (select col from columns limit 1); 501a1 a2 5021 - 00 2 - 00 503explain extended 504select * from t1 group by (a1 in (select col from columns)); 505id select_type table type possible_keys key key_len ref rows filtered Extra 5061 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort 5072 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key 508Warnings: 509Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where <if>(outer_field_is_not_null, (<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`), true)))) 510select * from t1 group by (a1 in (select col from columns)); 511a1 a2 5121 - 00 2 - 00 513/* ORDER BY clause */ 514explain extended 515select * from t1 order by (select col from columns limit 1); 516id select_type table type possible_keys key key_len ref rows filtered Extra 5171 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 NULL 5182 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index 519Warnings: 520Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1) 521select * from t1 order by (select col from columns limit 1); 522a1 a2 5231 - 00 2 - 00 5241 - 01 2 - 01 5251 - 02 2 - 02 526/****************************************************************************** 527* Column types/sizes that affect materialization. 528******************************************************************************/ 529# test for BIT fields 530create table t1bit (a1 bit(3), a2 bit(3)); 531create table t2bit (b1 bit(3), b2 bit(3)); 532insert into t1bit values (b'000', b'100'); 533insert into t1bit values (b'001', b'101'); 534insert into t1bit values (b'010', b'110'); 535insert into t2bit values (b'001', b'101'); 536insert into t2bit values (b'010', b'110'); 537insert into t2bit values (b'110', b'111'); 538explain extended select bin(a1), bin(a2) 539from t1bit 540where (a1, a2) in (select b1, b2 from t2bit); 541id select_type table type possible_keys key key_len ref rows filtered Extra 5421 SIMPLE t1bit ALL NULL NULL NULL NULL 3 100.00 NULL 5431 SIMPLE t2bit ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1bit); Using join buffer (Block Nested Loop) 544Warnings: 545Note 1003 /* select#1 */ select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where ((`test`.`t2bit`.`b2` = `test`.`t1bit`.`a2`) and (`test`.`t2bit`.`b1` = `test`.`t1bit`.`a1`)) 546select bin(a1), bin(a2) 547from t1bit 548where (a1, a2) in (select b1, b2 from t2bit); 549bin(a1) bin(a2) 5501 101 55110 110 552drop table t1bit, t2bit; 553create table t1bb (a1 bit(3), a2 blob(3)); 554create table t2bb (b1 bit(3), b2 blob(3)); 555insert into t1bb values (b'000', '100'); 556insert into t1bb values (b'001', '101'); 557insert into t1bb values (b'010', '110'); 558insert into t2bb values (b'001', '101'); 559insert into t2bb values (b'010', '110'); 560insert into t2bb values (b'110', '111'); 561explain extended select bin(a1), a2 562from t1bb 563where (a1, a2) in (select b1, b2 from t2bb); 564id select_type table type possible_keys key key_len ref rows filtered Extra 5651 SIMPLE t1bb ALL NULL NULL NULL NULL 3 100.00 NULL 5661 SIMPLE t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1bb); Using join buffer (Block Nested Loop) 567Warnings: 568Note 1003 /* select#1 */ select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where ((`test`.`t2bb`.`b2` = `test`.`t1bb`.`a2`) and (`test`.`t2bb`.`b1` = `test`.`t1bb`.`a1`)) 569select bin(a1), a2 570from t1bb 571where (a1, a2) in (select b1, b2 from t2bb); 572bin(a1) a2 5731 101 57410 110 575drop table t1bb, t2bb; 576drop table t1, t2, t3, t1i, t2i, t3i, columns; 577/****************************************************************************** 578* Test the cache of the left operand of IN. 579******************************************************************************/ 580# Test that default values of Cached_item are not used for comparison 581create table t1 (s1 int); 582create table t2 (s2 int); 583insert into t1 values (5),(1),(0); 584insert into t2 values (0), (1); 585select s2 from t2 where s2 in (select s1 from t1); 586s2 5870 5881 589drop table t1, t2; 590create table t1 (a int not null, b int not null); 591create table t2 (c int not null, d int not null); 592create table t3 (e int not null); 593insert into t1 values (1,10); 594insert into t1 values (1,20); 595insert into t1 values (2,10); 596insert into t1 values (2,20); 597insert into t1 values (2,30); 598insert into t1 values (3,20); 599insert into t1 values (4,40); 600insert into t2 values (2,10); 601insert into t2 values (2,20); 602insert into t2 values (2,40); 603insert into t2 values (3,20); 604insert into t2 values (4,10); 605insert into t2 values (5,10); 606insert into t3 values (10); 607insert into t3 values (10); 608insert into t3 values (20); 609insert into t3 values (30); 610explain extended 611select a from t1 where a in (select c from t2 where d >= 20); 612id select_type table type possible_keys key key_len ref rows filtered Extra 6131 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL 6141 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (Block Nested Loop) 6152 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 616Warnings: 617Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20)) 618select a from t1 where a in (select c from t2 where d >= 20); 619a 6202 6212 6222 6233 624create index it1a on t1(a); 625explain extended 626select a from t1 where a in (select c from t2 where d >= 20); 627id select_type table type possible_keys key key_len ref rows filtered Extra 6281 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL 6291 SIMPLE t1 ref it1a it1a 4 <subquery2>.c 2 100.00 Using index 6302 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 631Warnings: 632Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20)) 633select a from t1 where a in (select c from t2 where d >= 20); 634a 6352 6362 6372 6383 639insert into t2 values (1,10); 640explain extended 641select a from t1 where a in (select c from t2 where d >= 20); 642id select_type table type possible_keys key key_len ref rows filtered Extra 6431 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL 6441 SIMPLE t1 ref it1a it1a 4 <subquery2>.c 2 100.00 Using index 6452 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where 646Warnings: 647Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20)) 648select a from t1 where a in (select c from t2 where d >= 20); 649a 6502 6512 6522 6533 654explain extended 655select a from t1 group by a having a in (select c from t2 where d >= 20); 656id select_type table type possible_keys key key_len ref rows filtered Extra 6571 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index 6582 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where 659Warnings: 660Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`c`))))) 661select a from t1 group by a having a in (select c from t2 where d >= 20); 662a 6632 6643 665create index iab on t1(a, b); 666explain extended 667select a from t1 group by a having a in (select c from t2 where d >= 20); 668id select_type table type possible_keys key key_len ref rows filtered Extra 6691 PRIMARY t1 index it1a,iab it1a 4 NULL 7 100.00 Using index 6702 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where 671Warnings: 672Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`c`))))) 673select a from t1 group by a having a in (select c from t2 where d >= 20); 674a 6752 6763 677explain extended 678select a from t1 group by a 679having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 680id select_type table type possible_keys key key_len ref rows filtered Extra 6811 PRIMARY t1 index it1a,iab iab 8 NULL 7 100.00 Using index 6822 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where 6833 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 684Warnings: 685Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 686Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 687select a from t1 group by a 688having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 689a 6902 6913 692explain extended 693select a from t1 694where a in (select c from t2 where d >= some(select e from t3 where b=e)); 695id select_type table type possible_keys key key_len ref rows filtered Extra 6961 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary 6971 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary 6983 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 699Warnings: 700Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 701Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))) 702select a from t1 703where a in (select c from t2 where d >= some(select e from t3 where b=e)); 704a 7051 7062 7072 7082 7093 710drop table t1, t2, t3; 711create table t2 (a int, b int, key(a), key(b)); 712insert into t2 values (3,3),(3,3),(3,3); 713select 1 from t2 where 714t2.a > 1 715or 716t2.a = 3 and not t2.a not in (select t2.b from t2); 7171 7181 7191 7201 721drop table t2; 722create table t1 (a1 int key); 723create table t2 (b1 int); 724insert into t1 values (5); 725explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); 726id select_type table type possible_keys key key_len ref rows Extra 7271 SIMPLE t1 system NULL NULL NULL NULL 1 NULL 7281 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; FirstMatch(t1) 729select min(a1) from t1 where 7 in (select b1 from t2 group by b1); 730min(a1) 731NULL 732explain select min(a1) from t1 where 7 in (select b1 from t2); 733id select_type table type possible_keys key key_len ref rows Extra 7341 SIMPLE t1 system NULL NULL NULL NULL 1 NULL 7351 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; FirstMatch(t1) 736select min(a1) from t1 where 7 in (select b1 from t2); 737min(a1) 738NULL 739drop table t1,t2; 740create table t1 (a char(2), b varchar(10)); 741insert into t1 values ('a', 'aaa'); 742insert into t1 values ('aa', 'aaaa'); 743explain select a,b from t1 where b in (select a from t1); 744id select_type table type possible_keys key key_len ref rows Extra 7451 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 7461 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 747select a,b from t1 where b in (select a from t1); 748a b 749prepare st1 from "select a,b from t1 where b in (select a from t1)"; 750execute st1; 751a b 752execute st1; 753a b 754drop table t1; 755CREATE TABLE t1 (a varchar(5), b varchar(10)); 756INSERT INTO t1 VALUES 757('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2), 758('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8); 759SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 760a b 761BBB 4 762CCC 7 763AAA 8 764EXPLAIN 765SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 766id select_type table type possible_keys key key_len ref rows Extra 7671 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where 7682 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 769ALTER TABLE t1 ADD INDEX(a); 770FLUSH STATUS; 771SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 772a b 773BBB 4 774CCC 7 775AAA 8 776SHOW SESSION STATUS LIKE 'Sort_scan%'; 777Variable_name Value 778Sort_scan 1 779EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 780id select_type table type possible_keys key key_len ref rows Extra 7811 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where 7822 SUBQUERY t1 ALL a NULL NULL NULL 9 Using temporary; Using filesort 783DROP TABLE t1; 784CREATE TABLE t1 (a INT); 785INSERT INTO t1 VALUES (1),(2); 786EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); 787id select_type table type possible_keys key key_len ref rows filtered Extra 7881 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 FirstMatch 7891 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 790Warnings: 791Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` semi join (`test`.`t1`) where 1 792EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); 793id select_type table type possible_keys key key_len ref rows filtered Extra 7941 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch 7951 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 796Warnings: 797Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` semi join (`test`.`t1`) where (`test`.`t1`.`a` > 3) 798DROP TABLE t1; 799# 800# BUG#49630: Segfault in select_describe() with double 801# nested subquery and materialization 802# 803CREATE TABLE t1 (t1i int); 804CREATE TABLE t2 (t2i int); 805CREATE TABLE t3 (t3i int); 806CREATE TABLE t4 (t4i int); 807INSERT INTO t1 VALUES (1); 808INSERT INTO t2 VALUES (1),(2); 809INSERT INTO t3 VALUES (1),(2); 810INSERT INTO t4 VALUES (1),(2); 811 812EXPLAIN 813SELECT t1i 814FROM t1 JOIN t4 ON t1i=t4i 815WHERE (t1i) IN ( 816SELECT t2i 817FROM t2 818WHERE (t2i) IN ( 819SELECT t3i 820FROM t3 821GROUP BY t3i 822) 823); 824id select_type table type possible_keys key key_len ref rows Extra 8251 SIMPLE t1 system NULL NULL NULL NULL 1 NULL 8261 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where 8271 SIMPLE <subquery2> const <auto_key> <auto_key> 5 const 1 NULL 8282 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 8292 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 830DROP TABLE t1,t2,t3,t4; 831# 832# BUG#46680 - Assertion failed in file item_subselect.cc, 833# line 305 crashing on HAVING subquery 834# 835# Create tables 836# 837CREATE TABLE t1 ( 838pk INT, 839v VARCHAR(1) DEFAULT NULL, 840PRIMARY KEY(pk) 841); 842CREATE TABLE t2 LIKE t1; 843CREATE TABLE t3 LIKE t1; 844CREATE TABLE empty1 (a int); 845INSERT INTO t1 VALUES (1,'c'),(2,NULL); 846INSERT INTO t2 VALUES (3,'m'),(4,NULL); 847INSERT INTO t3 VALUES (1,'n'); 848 849# 850# 1) Test that subquery materialization is setup for query with 851# premature optimize() exit due to "Impossible WHERE" 852# 853SELECT MIN(t2.pk) 854FROM t2 JOIN t1 ON t1.pk=t2.pk 855WHERE 'j' 856HAVING ('m') IN ( 857SELECT v 858FROM t2); 859MIN(t2.pk) 860NULL 861Warnings: 862Warning 1292 Truncated incorrect INTEGER value: 'j' 863 864EXPLAIN 865SELECT MIN(t2.pk) 866FROM t2 JOIN t1 ON t1.pk=t2.pk 867WHERE 'j' 868HAVING ('m') IN ( 869SELECT v 870FROM t2); 871id select_type table type possible_keys key key_len ref rows Extra 8721 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 8732 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 874Warnings: 875Warning 1292 Truncated incorrect INTEGER value: 'j' 876 877# 878# 2) Test that subquery materialization is setup for query with 879# premature optimize() exit due to "No matching min/max row" 880# 881SELECT MIN(t2.pk) 882FROM t2 883WHERE t2.pk>10 884HAVING ('m') IN ( 885SELECT v 886FROM t2); 887MIN(t2.pk) 888NULL 889 890EXPLAIN 891SELECT MIN(t2.pk) 892FROM t2 893WHERE t2.pk>10 894HAVING ('m') IN ( 895SELECT v 896FROM t2); 897id select_type table type possible_keys key key_len ref rows Extra 8981 PRIMARY NULL NULL NULL NULL NULL NULL NULL No matching min/max row 8992 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 900 901# 902# 3) Test that subquery materialization is setup for query with 903# premature optimize() exit due to "Select tables optimized away" 904# 905SELECT MIN(pk) 906FROM t1 907WHERE pk=NULL 908HAVING ('m') IN ( 909SELECT v 910FROM t2); 911MIN(pk) 912NULL 913 914EXPLAIN 915SELECT MIN(pk) 916FROM t1 917WHERE pk=NULL 918HAVING ('m') IN ( 919SELECT v 920FROM t2); 921id select_type table type possible_keys key key_len ref rows Extra 9221 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 9232 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 924 925# 926# 4) Test that subquery materialization is setup for query with 927# premature optimize() exit due to "No matching row in const table" 928# 929 930SELECT MIN(a) 931FROM (SELECT a FROM empty1) tt 932HAVING ('m') IN ( 933SELECT v 934FROM t2); 935MIN(a) 936NULL 937 938EXPLAIN 939SELECT MIN(a) 940FROM (SELECT a FROM empty1) tt 941HAVING ('m') IN ( 942SELECT v 943FROM t2); 944id select_type table type possible_keys key key_len ref rows Extra 9451 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 9463 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 9472 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 948 949# 950# 5) Test that subquery materialization is setup for query with 951# premature optimize() exit due to "Impossible WHERE noticed 952# after reading const tables" 953# 954SELECT min(t1.pk) 955FROM t1 956WHERE t1.pk IN (SELECT 1 from t3 where pk>10) 957HAVING ('m') IN ( 958SELECT v 959FROM t2); 960min(t1.pk) 961NULL 962 963EXPLAIN 964SELECT min(t1.pk) 965FROM t1 966WHERE t1.pk IN (SELECT 1 from t3 where pk>10) 967HAVING ('m') IN ( 968SELECT v 969FROM t2); 970id select_type table type possible_keys key key_len ref rows Extra 9711 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index 9721 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index; FirstMatch(t1) 9733 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 974# 975# Cleanup for BUG#46680 976# 977DROP TABLE IF EXISTS t1,t2,t3,empty1; 978# 979# BUG#52344 - Subquery materialization: 980# Assertion if subquery in on-clause of outer join 981# 982CREATE TABLE t1 (i INTEGER); 983INSERT INTO t1 VALUES (10); 984CREATE TABLE t2 (j INTEGER); 985INSERT INTO t2 VALUES (5); 986CREATE TABLE t3 (k INTEGER); 987EXPLAIN 988SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); 989id select_type table type possible_keys key key_len ref rows Extra 9901 SIMPLE t1 system NULL NULL NULL NULL 1 NULL 9911 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Start temporary 9921 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; End temporary 993SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); 994i j 99510 NULL 996EXPLAIN 997SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); 998id select_type table type possible_keys key key_len ref rows Extra 9991 PRIMARY t1 system NULL NULL NULL NULL 1 NULL 10001 PRIMARY t2 system NULL NULL NULL NULL 1 NULL 10012 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1002SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); 1003i j 100410 NULL 1005DROP TABLE t1, t2, t3; 1006# End BUG#52344 1007CREATE TABLE t1 ( 1008pk INTEGER AUTO_INCREMENT, 1009col_int_nokey INTEGER, 1010col_int_key INTEGER, 1011col_varchar_key VARCHAR(1), 1012PRIMARY KEY (pk), 1013KEY (col_int_key), 1014KEY (col_varchar_key, col_int_key) 1015) 1016; 1017INSERT INTO t1 ( 1018col_int_key, col_int_nokey, col_varchar_key 1019) 1020VALUES 1021(2, NULL, 'w'), 1022(9, 7, 'm'), 1023(3, 9, 'm'), 1024(9, 7, 'k'), 1025(NULL, 4, 'r'), 1026(9, 2, 't'), 1027(3, 6, 'j'), 1028(8, 8, 'u'), 1029(8, NULL, 'h'), 1030(53, 5, 'o'), 1031(0, NULL, NULL), 1032(5, 6, 'k'), 1033(166, 188, 'e'), 1034(3, 2, 'n'), 1035(0, 1, 't'), 1036(1, 1, 'c'), 1037(9, 0, 'm'), 1038(5, 9, 'y'), 1039(6, NULL, 'f'), 1040(2, 4, 'd') 1041; 1042SELECT table2.col_varchar_key AS field1, 1043table2.col_int_nokey AS field2 1044FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2 1045ON (table2.col_varchar_key = table1.col_varchar_key ) ) 1046WHERE table1.pk = 6 1047HAVING ( field2 ) IN 1048( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2 1049FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2 1050ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) ) 1051ORDER BY field2 1052; 1053field1 field2 1054t 1 1055t 2 1056drop table t1; 1057# 1058# BUG#53103: MTR test ps crashes in optimize_cond() 1059# when running with --debug 1060# 1061CREATE TABLE t1(track varchar(15)); 1062INSERT INTO t1 VALUES ('CAD'), ('CAD'); 1063PREPARE STMT FROM 1064"SELECT 1 FROM t1 1065 WHERE 1066 track IN (SELECT track FROM t1 1067 GROUP BY track 1068 HAVING track>='CAD')"; 1069EXECUTE STMT ; 10701 10711 10721 1073EXECUTE STMT ; 10741 10751 10761 1077DEALLOCATE PREPARE STMT; 1078DROP TABLE t1; 1079# End of BUG#53103 1080# 1081# BUG#54511 - Assertion failed: cache != 0L in file 1082# sql_select.cc::sub_select_cache on HAVING 1083# 1084CREATE TABLE t1 (i int(11)); 1085CREATE TABLE t2 (c char(1)); 1086CREATE TABLE t3 (c char(1)); 1087INSERT INTO t1 VALUES (1), (2); 1088INSERT INTO t2 VALUES ('a'), ('b'); 1089INSERT INTO t3 VALUES ('x'), ('y'); 1090SELECT COUNT( i ),i 1091FROM t1 1092HAVING ('c') 1093IN (SELECT t2.c FROM (t2 JOIN t3)); 1094COUNT( i ) i 1095DROP TABLE t1,t2,t3; 1096# End BUG#54511 1097# 1098# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 1099# on subquery in FROM 1100# 1101CREATE TABLE t1 (a INTEGER); 1102CREATE TABLE t2 (b INTEGER); 1103INSERT INTO t2 VALUES (1); 1104explain SELECT a FROM ( 1105SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1106) table1; 1107id select_type table type possible_keys key key_len ref rows Extra 11081 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 11092 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 11103 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1111SELECT a FROM ( 1112SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1113) table1; 1114a 1115DROP TABLE t1, t2; 1116# End BUG#56367 1117# 1118# Bug#59833 - materialization=on/off leads to different result set 1119# when using IN 1120# 1121CREATE TABLE t1 ( 1122pk int NOT NULL, 1123f1 int DEFAULT NULL, 1124PRIMARY KEY (pk) 1125) ENGINE=MyISAM; 1126CREATE TABLE t2 ( 1127pk int NOT NULL, 1128f1 int DEFAULT NULL, 1129PRIMARY KEY (pk) 1130) ENGINE=MyISAM; 1131INSERT INTO t1 VALUES (10,0); 1132INSERT INTO t2 VALUES (10,0),(11,0); 1133explain SELECT * FROM t1 JOIN t2 USING (f1) 1134WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1135id select_type table type possible_keys key key_len ref rows Extra 11361 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1137SELECT * FROM t1 JOIN t2 USING (f1) 1138WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1139f1 pk pk 1140DROP TABLE t1, t2; 1141# End Bug#59833 1142# 1143# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT 1144# 1145CREATE TABLE t1 ( 1146col_varchar_key varchar(1) DEFAULT NULL, 1147col_varchar_nokey varchar(1) DEFAULT NULL, 1148KEY col_varchar_key (col_varchar_key)) 1149; 1150INSERT INTO t1 VALUES 1151('v','v'),('r','r'); 1152CREATE TABLE t2 ( 1153col_varchar_key varchar(1) DEFAULT NULL, 1154col_varchar_nokey varchar(1) DEFAULT NULL, 1155KEY col_varchar_key(col_varchar_key)) 1156; 1157INSERT INTO t2 VALUES 1158('r','r'),('c','c'); 1159CREATE VIEW v3 AS SELECT * FROM t2; 1160SELECT DISTINCT alias2.col_varchar_key 1161FROM t1 AS alias1 JOIN v3 AS alias2 1162ON alias2.col_varchar_key = alias1.col_varchar_key 1163HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2) 1164; 1165col_varchar_key 1166r 1167DROP TABLE t1, t2; 1168DROP VIEW v3; 1169# End Bug#11852644 1170 1171# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW 1172# INSTEAD OF NULL WHEN MATERIALIZATION ON 1173 1174CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY; 1175CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY; 1176INSERT INTO t2 VALUES (8),(7); 1177CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY; 1178INSERT INTO t3 VALUES (7); 1179SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3 1180FROM t3 1181LEFT JOIN t1 1182ON t1.col_int_nokey 1183WHERE (194, 200) IN ( 1184SELECT SQ4_alias1.col_int_nokey, 1185SQ4_alias2.col_int_nokey 1186FROM t2 AS SQ4_alias1 1187JOIN 1188t2 AS SQ4_alias2 1189ON SQ4_alias2.col_int_nokey = 5 1190) 1191GROUP BY field3 ; 1192MIN(t3.col_int_nokey) field3 1193DROP TABLE t1; 1194DROP TABLE t2; 1195DROP TABLE t3; 1196# 1197# Bug#13419028 - SUBQUERY MATERIALIZATION NOT USED IN CREATE 1198# SELECT 1199# 1200CREATE TABLE t1(a int); 1201INSERT INTO t1 values(1),(2); 1202CREATE TABLE t2(a int); 1203INSERT INTO t2 values(1),(2); 1204EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1205id select_type table type possible_keys key key_len ref rows Extra 12061 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 12071 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 1208FLUSH STATUS; 1209SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1210a 12111 12122 1213CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1214SELECT * FROM t3; 1215a 12161 12172 1218SHOW STATUS LIKE "CREATED_TMP_TABLES"; 1219Variable_name Value 1220Created_tmp_tables 0 1221DROP TABLE t1,t2,t3; 1222# 1223# Bug#13552968: Extra row with materialization on join + subquery in 1224# 1225CREATE TABLE t1 ( 1226col_varchar_nokey varchar(1) NOT NULL 1227) ENGINE=MyISAM; 1228INSERT INTO t1 VALUES ('b'); 1229CREATE TABLE t2 ( 1230col_varchar_nokey varchar(1) NOT NULL 1231) ENGINE=MyISAM; 1232INSERT INTO t2 VALUES ('k'); 1233CREATE TABLE t3 ( 1234col_varchar_nokey varchar(1) NOT NULL 1235) ENGINE=MyISAM; 1236explain SELECT STRAIGHT_JOIN * 1237FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey 1238FROM t3); 1239id select_type table type possible_keys key key_len ref rows Extra 12401 PRIMARY t1 system NULL NULL NULL NULL 1 NULL 12411 PRIMARY t2 system NULL NULL NULL NULL 1 NULL 12422 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1243SELECT STRAIGHT_JOIN * 1244FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey 1245FROM t3); 1246col_varchar_nokey col_varchar_nokey 1247b NULL 1248DROP TABLE t1, t2, t3; 1249# End of test for bug#13552968 1250# 1251# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) 1252# in join_read_const_table() 1253# 1254CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; 1255INSERT INTO t1 VALUES(1); 1256CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; 1257SELECT * 1258FROM t1 LEFT JOIN t2 1259ON t2.v IN(SELECT v FROM t1); 1260v v 12611 NULL 1262DROP TABLE t1, t2; 1263# End of test for bug#13591383. 1264# 1265# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive()) 1266# in join_read_const_table() 1267# 1268CREATE TABLE t1 ( 1269pk int NOT NULL, 1270col_int_nokey int DEFAULT NULL, 1271col_int_key int DEFAULT NULL, 1272PRIMARY KEY (pk), 1273KEY col_int_key (col_int_key) 1274) ENGINE=MyISAM; 1275INSERT INTO t1 VALUES (1,2,4), (2,150,62); 1276CREATE TABLE t2 ( 1277pk int NOT NULL, 1278col_int_key int DEFAULT NULL, 1279PRIMARY KEY (pk) 1280) ENGINE=MyISAM; 1281INSERT INTO t2 VALUES (1,7); 1282explain SELECT table1.pk, table2.pk 1283FROM t2 AS table1 LEFT JOIN t2 AS table2 1284ON table2.pk = table1.pk AND 1285table2.col_int_key IN 1286(SELECT col_int_key 1287FROM t1 AS innr 1288WHERE innr.col_int_nokey > innr.col_int_nokey 1289GROUP BY col_int_key 1290HAVING COUNT(*) > 0 1291); 1292id select_type table type possible_keys key key_len ref rows Extra 12931 PRIMARY table1 system NULL NULL NULL NULL 1 NULL 12941 PRIMARY table2 system PRIMARY NULL NULL NULL 1 NULL 12952 DEPENDENT SUBQUERY innr ALL col_int_key NULL NULL NULL 2 Using where; Using temporary; Using filesort 1296FLUSH STATUS; 1297SELECT table1.pk, table2.pk 1298FROM t2 AS table1 LEFT JOIN t2 AS table2 1299ON table2.pk = table1.pk AND 1300table2.col_int_key IN 1301(SELECT col_int_key 1302FROM t1 AS innr 1303WHERE innr.col_int_nokey > innr.col_int_nokey 1304GROUP BY col_int_key 1305HAVING COUNT(*) > 0 1306); 1307pk pk 13081 NULL 1309SHOW SESSION STATUS LIKE 'Sort_scan%'; 1310Variable_name Value 1311Sort_scan 1 1312DROP TABLE t1, t2; 1313# End of test for bug#13607423. 1314 1315Test of WL#6094 "Allow subquery materialization in NOT IN if all 1316columns are not nullable" 1317 1318create table t1(a int not null); 1319create table t2(a int not null); 1320insert into t1 values(1),(2); 1321insert into t2 values(1),(2); 1322Test in SELECT list 1323 1324cols not nullable => subq materialization 1325explain extended select a, (a,a) in (select a,a from t2) from t1; 1326id select_type table type possible_keys key key_len ref rows filtered Extra 13271 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 13282 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL 1329Warnings: 1330Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),(`test`.`t1`.`a`,`test`.`t1`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`a`,`test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`) and (`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(a,a) in (select a,a from t2)` from `test`.`t1` 1331select a, (a,a) in (select a,a from t2) from t1; 1332a (a,a) in (select a,a from t2) 13331 1 13342 1 1335 1336cols not nullable => subq materialization 1337explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1338from t1 join t2 on t1.a+t2.a=1000; 1339id select_type table type possible_keys key key_len ref rows filtered Extra 13401 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 13411 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 13422 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL 1343Warnings: 1344Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),(`test`.`t1`.`a`,`test`.`t1`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t3`.`a`,`test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`) and (`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000) 1345select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1346from t1 join t2 on t1.a+t2.a=1000; 1347a a (t1.a,t1.a) in (select a,a from t2 as t3) 1348 1349t2.a is not nullable, but in the query it may appear as NULL 1350as it's in an outer join. So, no materialization. 1351explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1352from t1 left join t2 on t1.a+t2.a=1000; 1353id select_type table type possible_keys key key_len ref rows filtered Extra 13541 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 13551 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 13562 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1357Warnings: 1358Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>(isnull(`test`.`t3`.`a`))), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>(isnull(`test`.`t3`.`a`))), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1 1359select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1360from t1 left join t2 on t1.a+t2.a=1000; 1361a a (t2.a,t2.a) in (select a,a from t2 as t3) 13621 NULL NULL 13632 NULL NULL 1364 1365alter table t2 modify a int; 1366two nullable inner cols => no subq materialization 1367explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1368from t1 join t2 on t1.a+t2.a=1000; 1369id select_type table type possible_keys key key_len ref rows filtered Extra 13701 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 13711 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 13722 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1373Warnings: 1374Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)) and ((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`))) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`)))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000) 1375select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1376from t1 join t2 on t1.a+t2.a=1000; 1377a a (t1.a,t1.a) in (select a,a from t2 as t3) 1378alter table t2 modify a int not null; 1379 1380Test in WHERE 1381 1382top-level => subq materialization. With one exception: if 1383semijoin is enabled in @@optimizer_switch, semijoin is chosen, 1384then rejected (due to outer join), and in that case, the 1385fallback is IN->EXISTS, subq-materialization is not tried... 1386explain extended select t1.a, t2.a 1387from t1 join t2 on t1.a+t2.a=3 1388where (t2.a,t2.a) in (select a,a from t2 as t3); 1389id select_type table type possible_keys key key_len ref rows filtered Extra 13901 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 NULL 13911 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 13921 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (Block Nested Loop) 1393Warnings: 1394Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2` `t3`) join `test`.`t2` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3)) 1395select t1.a, t2.a 1396from t1 join t2 on t1.a+t2.a=3 1397where (t2.a,t2.a) in (select a,a from t2 as t3); 1398a a 13992 1 14001 2 1401 1402cols not nullable => subq materialization 1403explain extended select t1.a, t2.a 1404from t1 join t2 on t1.a+t2.a=3 1405where (t2.a,t2.a) not in (select a,a from t2 as t3); 1406id select_type table type possible_keys key key_len ref rows filtered Extra 14071 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 14081 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14092 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL 1410Warnings: 1411Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((not(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),(`test`.`t2`.`a`,`test`.`t2`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t3`.`a`,`test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`) and (`test`.`t2`.`a` = `materialized-subquery`.`a`))))))) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3)) 1412select t1.a, t2.a 1413from t1 join t2 on t1.a+t2.a=3 1414where (t2.a,t2.a) not in (select a,a from t2 as t3); 1415a a 1416drop table t1,t2; 1417 1418Test of WL6095 "Allow subquery materialization in NOT IN if 1419single-column subquery" 1420 1421create table t1(a int null); 1422create table t2(a int null); 1423insert into t1 values(1),(2); 1424insert into t2 values(1),(2); 1425 1426one col => subq materialization 1427explain extended select a, a in (select a from t2) from t1; 1428id select_type table type possible_keys key key_len ref rows filtered Extra 14291 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 14302 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL 1431Warnings: 1432Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `a in (select a from t2)` from `test`.`t1` 1433select a, a in (select a from t2) from t1; 1434a a in (select a from t2) 14351 1 14362 1 1437 1438t2.a is not nullable, but in the query it may appear as NULL 1439as it's in an outer join. But there is only one inner column so 1440materialization is possible 1441explain extended select t1.a, t2.a, t2.a in (select * from t2 as t3) 1442from t1 left join t2 on t1.a+t2.a=1000; 1443id select_type table type possible_keys key key_len ref rows filtered Extra 14441 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 14451 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14462 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL 1447Warnings: 1448Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) AS `t2.a in (select * from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1 1449select t1.a, t2.a, t2.a in (select * from t2 as t3) 1450from t1 left join t2 on t1.a+t2.a=1000; 1451a a t2.a in (select * from t2 as t3) 14521 NULL NULL 14532 NULL NULL 1454 1455_two_ outer columns, nullable => no materialization 1456explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1457from t1 left join t2 on t1.a+t2.a=1000; 1458id select_type table type possible_keys key key_len ref rows filtered Extra 14591 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL 14601 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14612 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1462Warnings: 1463Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1 1464select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1465from t1 left join t2 on t1.a+t2.a=1000; 1466a a (t2.a,t2.a) in (select a,a from t2 as t3) 14671 NULL NULL 14682 NULL NULL 1469drop table t1,t2; 1470 1471Test in HAVING 1472create table t1(a int, b int); 1473create table t2(a int); 1474insert into t1 values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3); 1475insert into t2 values(10),(20); 1476no NULLs. 1477explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1478having (z in (select * from t2)) is null; 1479id select_type table type possible_keys key key_len ref rows filtered Extra 14801 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 14812 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL 1482Warnings: 1483Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`)))))) 1484select t1.a as z, sum(t1.b) from t1 group by t1.a 1485having (z in (select * from t2)) is null; 1486z sum(t1.b) 1487one outer NULL 1488insert into t1 values(null,null); 1489explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1490having (z in (select * from t2)) is null; 1491id select_type table type possible_keys key key_len ref rows filtered Extra 14921 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 14932 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL 1494Warnings: 1495Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`)))))) 1496select t1.a as z, sum(t1.b) from t1 group by t1.a 1497having (z in (select * from t2)) is null; 1498z sum(t1.b) 1499NULL NULL 1500one outer NULL and one inner NULL 1501insert into t2 values(null); 1502explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1503having (z in (select * from t2)) is null; 1504id select_type table type possible_keys key key_len ref rows filtered Extra 15051 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 15062 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL 1507Warnings: 1508Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`)))))) 1509select t1.a as z, sum(t1.b) from t1 group by t1.a 1510having (z in (select * from t2)) is null; 1511z sum(t1.b) 1512NULL NULL 15131 6 15142 6 1515one inner NULL 1516delete from t1 where a is null; 1517explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1518having (z in (select * from t2)) is null; 1519id select_type table type possible_keys key key_len ref rows filtered Extra 15201 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 15212 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL 1522Warnings: 1523Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`)))))) 1524select t1.a as z, sum(t1.b) from t1 group by t1.a 1525having (z in (select * from t2)) is null; 1526z sum(t1.b) 15271 6 15282 6 1529drop table t1,t2; 1530 1531Verify that an inner NULL is looked up only once (result is 1532cached). 1533create table t1(a int); 1534create table t2(a int); 1535insert into t1 values(1),(2),(3),(4),(5),(6); 1536insert into t1 select * from t1; 1537insert into t2 values(10),(20),(NULL); 1538explain extended select a, (a in (select * from t2)) from t1; 1539id select_type table type possible_keys key key_len ref rows filtered Extra 15401 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 NULL 15412 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL 1542Warnings: 1543Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(a in (select * from t2))` from `test`.`t1` 1544flush status; 1545select a, (a in (select * from t2)) from t1; 1546a (a in (select * from t2)) 15471 NULL 15482 NULL 15493 NULL 15504 NULL 15515 NULL 15526 NULL 15531 NULL 15542 NULL 15553 NULL 15564 NULL 15575 NULL 15586 NULL 1559There will be one look-up in the temporary table for each row 1560of t1 (12), plus one additional look-up to check whether table 1561contains a NULL value. 1562show status like "handler_read_key"; 1563Variable_name Value 1564Handler_read_key 13 1565drop table t1,t2; 1566# 1567# Bug#13495157 - SUBQUERY MATERIALIZATION NOT USED FOR CERTAIN 1568# STATEMENTS 1569# 1570CREATE TABLE t1(a INT); 1571INSERT INTO t1 VALUES(1),(2),(3); 1572CREATE TABLE t2(a INT); 1573INSERT INTO t2 VALUES(1),(2),(4); 1574# subquery materialization used for SELECT: 1575EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1576id select_type table type possible_keys key key_len ref rows Extra 15771 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 15781 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 1579SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1580a 15811 1582# Also used for INSERT SELECT: 1583CREATE TABLE t3 SELECT * FROM t1; 1584EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1585id select_type table type possible_keys key key_len ref rows Extra 15861 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 15871 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 1588INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1589SELECT * FROM t3; 1590a 15911 15921 15932 15943 1595EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1596id select_type table type possible_keys key key_len ref rows Extra 15971 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using temporary 15981 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 1599INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1600SELECT * FROM t2; 1601a 16021 16031 16042 16054 1606EXPLAIN INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1607id select_type table type possible_keys key key_len ref rows Extra 16081 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary 16091 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t2); Using join buffer (Block Nested Loop) 1610INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1611SELECT * FROM t2; 1612a 16131 16141 16151 16161 16172 16184 16194 1620# Not used for single-table UPDATE, DELETE: 1621EXPLAIN SELECT * FROM t2 WHERE a IN (SELECT * FROM t1); 1622id select_type table type possible_keys key key_len ref rows Extra 16231 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL 16241 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 16252 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 NULL 1626EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1); 1627id select_type table type possible_keys key key_len ref rows Extra 16281 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where 16292 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 1630UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1); 1631SELECT * FROM t2; 1632a 16330 16340 16350 16360 16371 16384 16394 1640EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t1); 1641id select_type table type possible_keys key key_len ref rows Extra 16421 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where 16432 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 1644DELETE FROM t2 WHERE a IN (SELECT * FROM t1); 1645SELECT * FROM t2; 1646a 16470 16480 16490 16500 16514 16524 1653EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t2); 1654ERROR HY000: You can't specify target table 't2' for update in FROM clause 1655EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t2); 1656ERROR HY000: You can't specify target table 't2' for update in FROM clause 1657UPDATE t2 SET a=3 WHERE a=0; 1658# Used for multi-table UPDATE, DELETE: 1659EXPLAIN SELECT * FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1660id select_type table type possible_keys key key_len ref rows Extra 16611 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL 16621 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop) 16631 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 16642 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where 1665EXPLAIN UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1666id select_type table type possible_keys key key_len ref rows Extra 16671 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL 16681 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop) 16691 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 16702 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where 1671UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1672SELECT * FROM t2; 1673a 16741 16751 16761 16771 16784 16794 1680EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1681id select_type table type possible_keys key key_len ref rows Extra 16821 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL 16831 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop) 16841 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 16852 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where 1686DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1687SELECT * FROM t2; 1688a 16894 16904 1691EXPLAIN UPDATE t2,t3 SET t2.a=10 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2); 1692ERROR HY000: You can't specify target table 't2' for update in FROM clause 1693EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2); 1694ERROR HY000: You can't specify target table 't2' for update in FROM clause 1695DROP TABLE t1,t2,t3; 1696# 1697# Test that subquery materialization only does one lookup: does 1698# not try to read the next row if the first row failed the 1699# subquery's WHERE. We use a case where index lookup is not 1700# enough to satisfy IN(), because index has length two when the 1701# outer value has length three, and thus the post-filtering 1702# WHERE added by subselect_hash_sj_engine::setup() makes the 1703# decision. 1704# 1705create table t1 (a varchar(3)); 1706create table t2 (a varchar(2)); 1707insert into t1 values('aaa'), ('aaa'); 1708insert into t2 values('aa'), ('aa'); 1709explain select * from t1 where a in (select a from t2); 1710id select_type table type possible_keys key key_len ref rows Extra 17111 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 17121 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 1713flush status; 1714select * from t1 where a in (select a from t2); 1715a 1716show status like "handler_read%"; 1717Variable_name Value 1718Handler_read_first 0 1719Handler_read_key 0 1720Handler_read_last 0 1721Handler_read_next 0 1722Handler_read_prev 0 1723Handler_read_rnd 0 1724Handler_read_rnd_next 6 1725drop table t1,t2; 1726# 1727# Bug#13655791 DIFFERENT RESULT WITH WL6094 ON QUERY WITH XOR 1728# IN WHERE CLAUSE + MYISAM 1729# 1730CREATE TABLE t1 ( 1731pk int NOT NULL, 1732col_varchar_nokey varchar(1) DEFAULT NULL, 1733PRIMARY KEY (pk) 1734); 1735INSERT INTO t1 VALUES (10,'x'); 1736CREATE TABLE t2 ( 1737pk int NOT NULL, 1738col_varchar_nokey varchar(1) DEFAULT NULL, 1739PRIMARY KEY (pk) 1740); 1741INSERT INTO t2 VALUES (1,'v'), (5,'x'), (11,'z'), (12,'c'), (15,'y'); 1742CREATE TABLE t3 ( 1743pk int NOT NULL, 1744col_int_key int DEFAULT NULL, 1745PRIMARY KEY (pk), 1746KEY col_int_key (col_int_key) 1747); 1748INSERT INTO t3 VALUES (10,8); 1749CREATE TABLE t4 ( 1750pk int NOT NULL, 1751col_varchar_nokey varchar(1) DEFAULT NULL, 1752PRIMARY KEY (pk) 1753); 1754INSERT INTO t4 VALUES (1,'x'); 1755EXPLAIN SELECT OUTR.pk, OUTR.col_varchar_nokey, OUTR2.col_varchar_nokey 1756FROM t2 AS OUTR2 1757JOIN t4 AS OUTR 1758ON (OUTR2.col_varchar_nokey > OUTR.col_varchar_nokey) 1759WHERE 1760OUTR.col_varchar_nokey IN ( 1761SELECT INNR.col_varchar_nokey 1762FROM t3 AS INNR2 1763LEFT JOIN t1 AS INNR 1764ON (INNR2.col_int_key >= INNR.pk) 1765) 1766XOR OUTR.pk < 6 1767; 1768id select_type table type possible_keys key key_len ref rows Extra 17691 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 17702 DEPENDENT SUBQUERY INNR2 system NULL NULL NULL NULL 1 NULL 17712 DEPENDENT SUBQUERY INNR system PRIMARY NULL NULL NULL 1 NULL 1772FLUSH STATUS; 1773SELECT OUTR.pk, OUTR.col_varchar_nokey, OUTR2.col_varchar_nokey 1774FROM t2 AS OUTR2 1775JOIN t4 AS OUTR 1776ON (OUTR2.col_varchar_nokey > OUTR.col_varchar_nokey) 1777WHERE 1778OUTR.col_varchar_nokey IN ( 1779SELECT INNR.col_varchar_nokey 1780FROM t3 AS INNR2 1781LEFT JOIN t1 AS INNR 1782ON (INNR2.col_int_key >= INNR.pk) 1783) 1784XOR OUTR.pk < 6 1785; 1786pk col_varchar_nokey col_varchar_nokey 1787SHOW STATUS LIKE "HANDLER_READ%"; 1788Variable_name Value 1789Handler_read_first 3 1790Handler_read_key 0 1791Handler_read_last 0 1792Handler_read_next 0 1793Handler_read_prev 0 1794Handler_read_rnd 0 1795Handler_read_rnd_next 3 1796DROP TABLE t1,t2,t3,t4; 1797# 1798# Bug#13727407: Assert !item->const_item() || !item->not_null_tables() 1799# 1800CREATE TABLE t1 ( 1801col_int_key INT, 1802KEY col_int_key (col_int_key) 1803); 1804INSERT INTO t1 VALUES (1); 1805CREATE TABLE t2 ( 1806col_int_key INT, 1807col_time_key TIME, 1808col_datetime_nokey DATETIME, 1809KEY col_int_key (col_int_key), 1810KEY col_time_key (col_time_key) 1811); 1812INSERT INTO t2 VALUES 1813(7,'14:03:03','2001-11-28 00:50:27'), (1,'01:46:09','2007-10-09 19:53:04'); 1814EXPLAIN SELECT col_datetime_nokey AS x 1815FROM t2 AS outr 1816WHERE col_int_key IN ( 1817SELECT STRAIGHT_JOIN col_int_key 1818FROM t1 1819) AND outr.col_int_key = 0 1820HAVING x = '2000-09-09' 1821ORDER BY col_time_key; 1822id select_type table type possible_keys key key_len ref rows Extra 18231 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 18242 DEPENDENT SUBQUERY t1 system col_int_key NULL NULL NULL 1 NULL 1825SELECT col_datetime_nokey AS x 1826FROM t2 AS outr 1827WHERE col_int_key IN ( 1828SELECT STRAIGHT_JOIN col_int_key 1829FROM t1 1830) AND outr.col_int_key = 0 1831HAVING x = '2000-09-09' 1832ORDER BY col_time_key; 1833x 1834DROP TABLE t1, t2; 1835# 1836# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN 1837# SUBSELECT_HASH_SJ_ENGINE::EXEC 1838# 1839CREATE TABLE t1 1840(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5)) 1841ENGINE=InnoDB; 1842INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,''); 1843Warnings: 1844Warning 1366 Incorrect integer value: '' for column 'c5' at row 1 1845CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB; 1846INSERT INTO t2 VALUES(0,'',''); 1847Warnings: 1848Warning 1366 Incorrect integer value: '' for column 'c4' at row 1 1849Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1 1850CREATE TABLE t3 1851(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk)) 1852ENGINE=InnoDB; 1853INSERT INTO t3 VALUES(0,8,'',0,''); 1854Warnings: 1855Warning 1366 Incorrect integer value: '' for column 'c1' at row 1 1856Warning 1366 Incorrect integer value: '' for column 'cy' at row 1 1857EXPLAIN SELECT o.c2 AS x FROM t1 AS o 1858WHERE o.c1 IN 1859(SELECT innr.c4 AS y 1860FROM t2 AS innr2 JOIN t3 AS innr 1861ON (innr2.c4k=innr.c4) 1862WHERE innr.c1=6 OR NOT innr.c1=innr.pk 1863ORDER BY innr.c4) 1864AND o.c4=7 XOR o.pk=3 ORDER BY o.pk; 1865id select_type table type possible_keys key key_len ref rows Extra 18661 PRIMARY o ALL NULL NULL NULL NULL 1 Using where; Using filesort 18672 SUBQUERY innr2 ALL NULL NULL NULL NULL 1 NULL 18682 SUBQUERY innr ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 1869SELECT o.c2 AS x FROM t1 AS o 1870WHERE o.c1 IN 1871(SELECT innr.c4 AS y 1872FROM t2 AS innr2 JOIN t3 AS innr 1873ON (innr2.c4k=innr.c4) 1874WHERE innr.c1=6 OR NOT innr.c1=innr.pk 1875ORDER BY innr.c4) 1876AND o.c4=7 XOR o.pk=3 ORDER BY o.pk; 1877x 1878DROP TABLE t1,t2,t3; 1879# 1880# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT 1881# 1882CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB; 1883SET @str= repeat("a",450); 1884SET @num=1000; 1885INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str)); 1886INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1887INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1888INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1889INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1890INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1891SELECT COUNT(*) FROM t1; 1892COUNT(*) 189332 1894ANALYZE TABLE t1; 1895Table Op Msg_type Msg_text 1896test.t1 analyze status OK 1897set @save_heap_size= @@max_heap_table_size; 1898set @@max_heap_table_size= 16384; 1899EXPLAIN SELECT COUNT(*) 1900FROM t1 1901WHERE t1.a NOT IN ( 1902SELECT t2.a FROM t1 as t2 1903); 1904id select_type table type possible_keys key key_len ref rows Extra 19051 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 19062 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 32 Using where 1907SELECT COUNT(*) 1908FROM t1 1909WHERE t1.a NOT IN ( 1910SELECT t2.a FROM t1 as t2 1911); 1912COUNT(*) 19130 1914ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8; 1915ANALYZE TABLE t1; 1916Table Op Msg_type Msg_text 1917test.t1 analyze status OK 1918EXPLAIN SELECT COUNT(*) 1919FROM t1 1920WHERE t1.a NOT IN ( 1921SELECT t2.a FROM t1 as t2 1922); 1923id select_type table type possible_keys key key_len ref rows Extra 19241 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 19252 SUBQUERY t2 ALL NULL NULL NULL NULL 32 NULL 1926SELECT COUNT(*) 1927FROM t1 1928WHERE t1.a NOT IN ( 1929SELECT t2.a FROM t1 as t2 1930); 1931COUNT(*) 19320 1933DROP TABLE t1; 1934set @@max_heap_table_size= @save_heap_size; 1935# End of 5.6 tests 1936set @@optimizer_switch=@old_opt_switch; 1937set optimizer_switch=default; 1938