1set @old_opt_switch=@@optimizer_switch; 2set optimizer_switch='subquery_materialization_cost_based=off'; 3SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 4Warnings: 5Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 6drop table if exists t1, t2, t3, t1i, t2i, t3i; 7drop view if exists v1, v2, v1m, v2m; 8create table t1 (a1 char(8), a2 char(8)); 9create table t2 (b1 char(8), b2 char(8)); 10create table t3 (c1 char(8), c2 char(8)); 11insert into t1 values ('1 - 00', '2 - 00'); 12insert into t1 values ('1 - 01', '2 - 01'); 13insert into t1 values ('1 - 02', '2 - 02'); 14insert into t2 values ('1 - 01', '2 - 01'); 15insert into t2 values ('1 - 01', '2 - 01'); 16insert into t2 values ('1 - 02', '2 - 02'); 17insert into t2 values ('1 - 02', '2 - 02'); 18insert into t2 values ('1 - 03', '2 - 03'); 19insert into t3 values ('1 - 01', '2 - 01'); 20insert into t3 values ('1 - 02', '2 - 02'); 21insert into t3 values ('1 - 03', '2 - 03'); 22insert into t3 values ('1 - 04', '2 - 04'); 23create table t1i (a1 char(8), a2 char(8)); 24create table t2i (b1 char(8), b2 char(8)); 25create table t3i (c1 char(8), c2 char(8)); 26create index it1i1 on t1i (a1); 27create index it1i2 on t1i (a2); 28create index it1i3 on t1i (a1, a2); 29create index it2i1 on t2i (b1); 30create index it2i2 on t2i (b2); 31create index it2i3 on t2i (b1, b2); 32create index it3i1 on t3i (c1); 33create index it3i2 on t3i (c2); 34create index it3i3 on t3i (c1, c2); 35insert into t1i select * from t1; 36insert into t2i select * from t2; 37insert into t3i select * from t3; 38/****************************************************************************** 39* Simple tests. 40******************************************************************************/ 41# non-indexed nullable fields 42explain extended 43select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 44id select_type table partitions type possible_keys key key_len ref rows filtered Extra 451 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 462 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 47Warnings: 48Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 49Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`)))) 50select * from t1 where a1 in (select b1 from t2 where b1 > '0'); 51a1 a2 521 - 01 2 - 01 531 - 02 2 - 02 54explain extended 55select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 56id select_type table partitions type possible_keys key key_len ref rows filtered Extra 571 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 582 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 59Warnings: 60Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 61Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`)))) 62select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); 63a1 a2 641 - 01 2 - 01 651 - 02 2 - 02 66explain extended 67select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 68id select_type table partitions type possible_keys key key_len ref rows filtered Extra 691 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 702 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 71Warnings: 72Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 73Note 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,1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) 74select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); 75a1 a2 761 - 01 2 - 01 771 - 02 2 - 02 78explain extended 79select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 80id select_type table partitions type possible_keys key key_len ref rows filtered Extra 811 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 822 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using temporary; Using filesort 83Warnings: 84Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 85Note 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,1 from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) or isnull(`test`.`t2`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = min(`test`.`t2`.`b2`)) or isnull(min(`test`.`t2`.`b2`))) and <is_not_null_test>(`test`.`t2`.`b1`) and <is_not_null_test>(min(`test`.`t2`.`b2`))))) 86select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); 87a1 a2 881 - 01 2 - 01 891 - 02 2 - 02 90explain extended 91select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 92id select_type table partitions type possible_keys key key_len ref rows filtered Extra 931 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 942 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i3 it2i1 9 func 2 100.00 Using where; Using index 95Warnings: 96Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 97Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i1 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`))))) 98select * from t1i where a1 in (select b1 from t2i where b1 > '0'); 99a1 a2 1001 - 01 2 - 01 1011 - 02 2 - 02 102explain extended 103select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); 104id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1051 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 1062 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i3 it2i1 9 func 2 100.00 Using where; Using index 107Warnings: 108Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 109Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i1 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`))))) 110select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); 111a1 a2 1121 - 01 2 - 01 1131 - 02 2 - 02 114explain extended 115select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 116id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1171 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 1182 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 119Warnings: 120Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 121Note 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`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) 122select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); 123a1 a2 1241 - 01 2 - 01 1251 - 02 2 - 02 126explain extended 127select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); 128id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1291 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 1302 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 131Warnings: 132Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 133Note 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`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) 134select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); 135a1 a2 1361 - 01 2 - 01 1371 - 02 2 - 02 138explain extended 139select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 140id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1411 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 1422 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by 143Warnings: 144Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 145Note 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`),<exists>(/* select#2 */ select 1,1 from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1i`.`a2`) = min(`test`.`t2i`.`b2`)) or isnull(min(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(min(`test`.`t2i`.`b2`))))) 146select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 147a1 a2 1481 - 01 2 - 01 1491 - 02 2 - 02 150explain extended 151select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); 152id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1531 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1542 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 9 NULL 3 100.00 Using index for group-by 155Warnings: 156Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 157Note 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,1 from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or isnull(max(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`))))) 158select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); 159a1 a2 1601 - 01 2 - 01 1611 - 02 2 - 02 162prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; 163execute st1; 164id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1651 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1662 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 9 NULL 3 100.00 Using index for group-by 167Warnings: 168Note 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 `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or isnull(max(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`))))) 169execute st1; 170id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1711 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1722 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 9 NULL 3 100.00 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`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or isnull(max(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`))))) 175prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; 176execute st2; 177a1 a2 1781 - 01 2 - 01 1791 - 02 2 - 02 180execute st2; 181a1 a2 1821 - 01 2 - 01 1831 - 02 2 - 02 184explain extended 185select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 186id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1871 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1882 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by 189Warnings: 190Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 191Note 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,1 from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = min(`test`.`t2i`.`b2`)) or isnull(min(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(min(`test`.`t2i`.`b2`))))) 192select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); 193a1 a2 1941 - 01 2 - 01 1951 - 02 2 - 02 196select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); 197ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 198explain extended 199select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 200id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2011 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2022 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 203Warnings: 204Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 205Note 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,1 from `test`.`t2` where ((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) 206select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); 207a1 a2 2081 - 01 2 - 01 2091 - 02 2 - 02 210explain extended 211select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 212id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2131 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2142 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 215Warnings: 216Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 217Note 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`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) 218select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); 219a1 a2 2201 - 01 2 - 01 2211 - 02 2 - 02 222/****************************************************************************** 223* Views, UNIONs, several levels of nesting. 224******************************************************************************/ 225# materialize the result of subquery over temp-table view 226create algorithm=merge view v1 as 227select b1, c2 from t2, t3 where b2 > c2; 228create algorithm=merge view v2 as 229select b1, c2 from t2, t3 group by b2, c2; 230Warnings: 231Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 232create algorithm=temptable view v1m as 233select b1, c2 from t2, t3 where b2 > c2; 234create algorithm=temptable view v2m as 235select b1, c2 from t2, t3 group by b2, c2; 236select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null); 237b1 c2 2381 - 02 2 - 01 2391 - 02 2 - 01 2401 - 03 2 - 01 2411 - 03 2 - 02 242select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null); 243b1 c2 2441 - 02 2 - 01 2451 - 02 2 - 01 2461 - 03 2 - 01 2471 - 03 2 - 02 248select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null); 249b1 c2 2501 - 02 2 - 01 2511 - 02 2 - 01 2521 - 03 2 - 01 2531 - 03 2 - 02 254select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null); 255b1 c2 2561 - 02 2 - 01 2571 - 02 2 - 01 2581 - 03 2 - 01 2591 - 03 2 - 02 260drop view v1, v2, v1m, v2m; 261explain extended 262select * from t1 263where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 264(a1, a2) in (select c1, c2 from t3 265where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 266id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2671 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2683 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 2694 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 2702 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 271Warnings: 272Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 273Note 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,1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#3 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`))))) 274select * from t1 275where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and 276(a1, a2) in (select c1, c2 from t3 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 t1i 283where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 284(a1, a2) in (select c1, c2 from t3i 285where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 286id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2871 PRIMARY t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2883 DEPENDENT SUBQUERY t3i NULL index_subquery it3i1,it3i2,it3i3 it3i1 9 func 1 25.00 Using where 2894 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 2902 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 291Warnings: 292Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 293Note 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`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i1 where (<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3i`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3i`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1i`.`a1`) = `test`.`t3i`.`c1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t3i`.`c2`)))))) 294select * from t1i 295where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 296(a1, a2) in (select c1, c2 from t3i 297where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 298a1 a2 2991 - 01 2 - 01 3001 - 02 2 - 02 301explain extended 302select * from t1 303where (a1, a2) in (select b1, b2 from t2 304where b2 in (select c2 from t3 where c2 LIKE '%02') or 305b2 in (select c2 from t3 where c2 LIKE '%03')) and 306(a1, a2) in (select c1, c2 from t3 307where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 308id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3091 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3105 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3116 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 3122 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 3134 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3143 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 315Warnings: 316Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 317Note 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,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`))))) 318select * from t1 319where (a1, a2) in (select b1, b2 from t2 320where b2 in (select c2 from t3 where c2 LIKE '%02') or 321b2 in (select c2 from t3 where c2 LIKE '%03')) and 322(a1, a2) in (select c1, c2 from t3 323where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 324a1 a2 3251 - 02 2 - 02 326explain extended 327select * from t1 328where (a1, a2) in (select b1, b2 from t2 329where b2 in (select c2 from t3 t3a where c1 = a1) or 330b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 331(a1, a2) in (select c1, c2 from t3 t3c 332where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 333id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3341 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3355 DEPENDENT SUBQUERY t3c NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3366 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 3372 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 3384 DEPENDENT SUBQUERY t3b NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3393 DEPENDENT SUBQUERY t3a NULL ALL NULL NULL NULL NULL 4 25.00 Using where 340Warnings: 341Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 342Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 343Note 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,1 from `test`.`t2` where ((<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`,<exists>(/* select#4 */ select 1 from `test`.`t3` `t3b` where ((`test`.`t3b`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3b`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` `t3c` where (<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) 344select * from t1 345where (a1, a2) in (select b1, b2 from t2 346where b2 in (select c2 from t3 t3a where c1 = a1) or 347b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 348(a1, a2) in (select c1, c2 from t3 t3c 349where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 350a1 a2 3511 - 01 2 - 01 3521 - 02 2 - 02 353explain extended 354(select * from t1 355where (a1, a2) in (select b1, b2 from t2 356where b2 in (select c2 from t3 where c2 LIKE '%02') or 357b2 in (select c2 from t3 where c2 LIKE '%03') 358group by b1, b2) and 359(a1, a2) in (select c1, c2 from t3 360where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 361UNION 362(select * from t1i 363where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 364(a1, a2) in (select c1, c2 from t3i 365where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 366id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3671 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3685 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3696 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 3702 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 3714 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3723 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 3737 UNION t1i NULL index NULL it1i3 18 NULL 3 100.00 Using where; Using index 3749 DEPENDENT SUBQUERY t3i NULL index_subquery it3i1,it3i2,it3i3 it3i1 9 func 1 25.00 Using where 37510 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 3768 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 377NULL UNION RESULT <union1,7> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 378Warnings: 379Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 380Note 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,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i1 where (<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3i`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3i`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1i`.`a1`) = `test`.`t3i`.`c1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t3i`.`c2`))))))) 381(select * from t1 382where (a1, a2) in (select b1, b2 from t2 383where b2 in (select c2 from t3 where c2 LIKE '%02') or 384b2 in (select c2 from t3 where c2 LIKE '%03') 385group by b1, b2) and 386(a1, a2) in (select c1, c2 from t3 387where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) 388UNION 389(select * from t1i 390where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and 391(a1, a2) in (select c1, c2 from t3i 392where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); 393a1 a2 3941 - 02 2 - 02 3951 - 01 2 - 01 396explain extended 397select * from t1 398where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 399(a1, a2) in (select c1, c2 from t3 400where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 401id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4021 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 4034 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 4045 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 4052 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 4063 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 407NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 408Warnings: 409Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 410Note 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,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 <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#4 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`))))) 411select * from t1 412where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 413(a1, a2) in (select c1, c2 from t3 414where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 415a1 a2 4161 - 01 2 - 01 4171 - 02 2 - 02 418explain extended 419select * from t1, t3 420where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 421(c1, c2) in (select c1, c2 from t3 422where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 423a1 = c1; 424id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4251 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 4261 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 4274 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 4285 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 4292 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 4303 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 431NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 432Warnings: 433Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 434Note 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` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) 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 <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(/* select#4 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t3`.`c1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t3`.`c2`))))) 435select * from t1, t3 436where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and 437(c1, c2) in (select c1, c2 from t3 438where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and 439a1 = c1; 440a1 a2 c1 c2 4411 - 01 2 - 01 1 - 01 2 - 01 4421 - 02 2 - 02 1 - 02 2 - 02 443/****************************************************************************** 444* Negative tests, where materialization should not be applied. 445******************************************************************************/ 446# UNION in a subquery 447explain extended 448select * from t3 449where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 450id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4511 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 4522 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 4533 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 454NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 455Warnings: 456Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 457Note 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`)))) 458select * from t3 459where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); 460c1 c2 4611 - 01 2 - 01 4621 - 02 2 - 02 4631 - 03 2 - 03 464explain extended 465select * from t1 466where (a1, a2) in (select b1, b2 from t2 467where b2 in (select c2 from t3 t3a where c1 = a1) or 468b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and 469(a1, a2) in (select c1, c2 from t3 t3c 470where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 471id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4721 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 4735 DEPENDENT SUBQUERY t3c NULL ALL NULL NULL NULL NULL 4 25.00 Using where 4746 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using where; Using index 4752 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where 4764 DEPENDENT SUBQUERY t3b NULL ALL NULL NULL NULL NULL 4 25.00 Using where 4773 DEPENDENT SUBQUERY t3a NULL ALL NULL NULL NULL NULL 4 25.00 Using where 478Warnings: 479Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 480Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 481Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 482Note 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,1 from `test`.`t2` where ((<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`,<exists>(/* select#4 */ select 1 from `test`.`t3` `t3b` where ((`test`.`t3b`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3b`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` `t3c` where (<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) 483explain extended 484select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 485id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4861 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 4872 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 488Warnings: 489Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 490Note 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')))) 491select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); 492a1 a2 4931 - 01 2 - 01 494explain extended 495select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 496id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4971 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 4982 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 499Warnings: 500Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 501Note 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')))) 502select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); 503a1 a2 5041 - 01 2 - 01 505/****************************************************************************** 506* Subqueries in other uncovered clauses. 507******************************************************************************/ 508/* SELECT clause */ 509select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; 510((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL 5110 5120 5130 514/* GROUP BY clause */ 515create table columns (col int key); 516insert into columns values (1), (2); 517explain extended 518select * from t1 group by (select col from columns limit 1); 519id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5201 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 5212 SUBQUERY columns NULL index NULL PRIMARY 4 NULL 2 100.00 Using index 522Warnings: 523Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 524Note 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) 525select * from t1 group by (select col from columns limit 1); 526a1 a2 5271 - 00 2 - 00 528explain extended 529select * from t1 group by (a1 in (select col from columns)); 530id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5311 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort 5322 DEPENDENT SUBQUERY columns NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index; Full scan on NULL key 533Warnings: 534Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 535Note 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)))) 536select * from t1 group by (a1 in (select col from columns)); 537a1 a2 5381 - 00 2 - 00 539/* ORDER BY clause */ 540explain extended 541select * from t1 order by (select col from columns limit 1); 542id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5431 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 5442 SUBQUERY columns NULL index NULL PRIMARY 4 NULL 2 100.00 Using index 545Warnings: 546Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 547Note 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) 548select * from t1 order by (select col from columns limit 1); 549a1 a2 5501 - 00 2 - 00 5511 - 01 2 - 01 5521 - 02 2 - 02 553/****************************************************************************** 554* Column types/sizes that affect materialization. 555******************************************************************************/ 556# test for BIT fields 557create table t1bit (a1 bit(3), a2 bit(3)); 558create table t2bit (b1 bit(3), b2 bit(3)); 559insert into t1bit values (b'000', b'100'); 560insert into t1bit values (b'001', b'101'); 561insert into t1bit values (b'010', b'110'); 562insert into t2bit values (b'001', b'101'); 563insert into t2bit values (b'010', b'110'); 564insert into t2bit values (b'110', b'111'); 565explain extended select bin(a1), bin(a2) 566from t1bit 567where (a1, a2) in (select b1, b2 from t2bit); 568id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5691 PRIMARY t1bit NULL ALL NULL NULL NULL NULL 3 100.00 Using where 5702 DEPENDENT SUBQUERY t2bit NULL ALL NULL NULL NULL NULL 3 33.33 Using where 571Warnings: 572Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 573Note 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` where <in_optimizer>((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2bit` where ((<cache>(`test`.`t1bit`.`a1`) = `test`.`t2bit`.`b1`) and (<cache>(`test`.`t1bit`.`a2`) = `test`.`t2bit`.`b2`)))) 574select bin(a1), bin(a2) 575from t1bit 576where (a1, a2) in (select b1, b2 from t2bit); 577bin(a1) bin(a2) 5781 101 57910 110 580drop table t1bit, t2bit; 581create table t1bb (a1 bit(3), a2 blob(3)); 582create table t2bb (b1 bit(3), b2 blob(3)); 583insert into t1bb values (b'000', '100'); 584insert into t1bb values (b'001', '101'); 585insert into t1bb values (b'010', '110'); 586insert into t2bb values (b'001', '101'); 587insert into t2bb values (b'010', '110'); 588insert into t2bb values (b'110', '111'); 589explain extended select bin(a1), a2 590from t1bb 591where (a1, a2) in (select b1, b2 from t2bb); 592id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5931 PRIMARY t1bb NULL ALL NULL NULL NULL NULL 3 100.00 Using where 5942 DEPENDENT SUBQUERY t2bb NULL ALL NULL NULL NULL NULL 3 33.33 Using where 595Warnings: 596Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 597Note 1003 /* select#1 */ select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` where <in_optimizer>((`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2bb` where ((<cache>(`test`.`t1bb`.`a1`) = `test`.`t2bb`.`b1`) and (<cache>(`test`.`t1bb`.`a2`) = `test`.`t2bb`.`b2`)))) 598select bin(a1), a2 599from t1bb 600where (a1, a2) in (select b1, b2 from t2bb); 601bin(a1) a2 6021 101 60310 110 604drop table t1bb, t2bb; 605drop table t1, t2, t3, t1i, t2i, t3i, columns; 606/****************************************************************************** 607* Test the cache of the left operand of IN. 608******************************************************************************/ 609# Test that default values of Cached_item are not used for comparison 610create table t1 (s1 int); 611create table t2 (s2 int); 612insert into t1 values (5),(1),(0); 613insert into t2 values (0), (1); 614select s2 from t2 where s2 in (select s1 from t1); 615s2 6160 6171 618drop table t1, t2; 619create table t1 (a int not null, b int not null); 620create table t2 (c int not null, d int not null); 621create table t3 (e int not null); 622insert into t1 values (1,10); 623insert into t1 values (1,20); 624insert into t1 values (2,10); 625insert into t1 values (2,20); 626insert into t1 values (2,30); 627insert into t1 values (3,20); 628insert into t1 values (4,40); 629insert into t2 values (2,10); 630insert into t2 values (2,20); 631insert into t2 values (2,40); 632insert into t2 values (3,20); 633insert into t2 values (4,10); 634insert into t2 values (5,10); 635insert into t3 values (10); 636insert into t3 values (10); 637insert into t3 values (20); 638insert into t3 values (30); 639explain extended 640select a from t1 where a in (select c from t2 where d >= 20); 641id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6421 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 6432 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 6 16.67 Using where 644Warnings: 645Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 646Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 647select a from t1 where a in (select c from t2 where d >= 20); 648a 6492 6502 6512 6523 653create index it1a on t1(a); 654explain extended 655select a from t1 where a in (select c from t2 where d >= 20); 656id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6571 PRIMARY t1 NULL index NULL it1a 4 NULL 7 100.00 Using where; Using index 6582 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 6 16.67 Using where 659Warnings: 660Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 661Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 662select a from t1 where a in (select c from t2 where d >= 20); 663a 6642 6652 6662 6673 668insert into t2 values (1,10); 669explain extended 670select a from t1 where a in (select c from t2 where d >= 20); 671id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6721 PRIMARY t1 NULL index NULL it1a 4 NULL 7 100.00 Using where; Using index 6732 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 14.29 Using where 674Warnings: 675Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 676Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 677select a from t1 where a in (select c from t2 where d >= 20); 678a 6792 6802 6812 6823 683explain extended 684select a from t1 group by a having a in (select c from t2 where d >= 20); 685id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6861 PRIMARY t1 NULL index it1a it1a 4 NULL 7 100.00 Using index 6872 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 14.29 Using where 688Warnings: 689Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 690Note 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 ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 691select a from t1 group by a having a in (select c from t2 where d >= 20); 692a 6932 6943 695create index iab on t1(a, b); 696explain extended 697select a from t1 group by a having a in (select c from t2 where d >= 20); 698id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6991 PRIMARY t1 NULL index it1a,iab it1a 4 NULL 7 100.00 Using index 7002 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 14.29 Using where 701Warnings: 702Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 703Note 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 ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 704select a from t1 group by a having a in (select c from t2 where d >= 20); 705a 7062 7073 708explain extended 709select a from t1 group by a 710having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 711id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7121 PRIMARY t1 NULL index it1a,iab iab 8 NULL 7 100.00 Using index 7132 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 14.29 Using where 7143 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 715Warnings: 716Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 717Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 718Note 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`)))) 719select a from t1 group by a 720having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); 721a 7222 7233 724explain extended 725select a from t1 726where a in (select c from t2 where d >= some(select e from t3 where b=e)); 727id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7281 PRIMARY t1 NULL index NULL iab 8 NULL 7 100.00 Using where; Using index 7292 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 14.29 Using where 7303 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 731Warnings: 732Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 733Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 734Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <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 ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 735select a from t1 736where a in (select c from t2 where d >= some(select e from t3 where b=e)); 737a 7381 7392 7402 7412 7423 743drop table t1, t2, t3; 744create table t2 (a int, b int, key(a), key(b)); 745insert into t2 values (3,3),(3,3),(3,3); 746select 1 from t2 where 747t2.a > 1 748or 749t2.a = 3 and not t2.a not in (select t2.b from t2); 7501 7511 7521 7531 754drop table t2; 755create table t1 (a1 int key); 756create table t2 (b1 int); 757insert into t1 values (5); 758explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); 759id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7601 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 7612 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 762Warnings: 763Note 1003 /* select#1 */ select min(`test`.`t1`.`a1`) AS `min(a1)` from `test`.`t1` where 0 764select min(a1) from t1 where 7 in (select b1 from t2 group by b1); 765min(a1) 766NULL 767explain select min(a1) from t1 where 7 in (select b1 from t2); 768id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7691 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 7702 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 771Warnings: 772Note 1003 /* select#1 */ select min(`test`.`t1`.`a1`) AS `min(a1)` from `test`.`t1` where 0 773select min(a1) from t1 where 7 in (select b1 from t2); 774min(a1) 775NULL 776drop table t1,t2; 777create table t1 (a char(2), b varchar(10)); 778insert into t1 values ('a', 'aaa'); 779insert into t1 values ('aa', 'aaaa'); 780explain select a,b from t1 where b in (select a from t1); 781id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7821 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 7832 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 784Warnings: 785Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`b`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`b`) = `test`.`t1`.`a`))) 786select a,b from t1 where b in (select a from t1); 787a b 788prepare st1 from "select a,b from t1 where b in (select a from t1)"; 789execute st1; 790a b 791execute st1; 792a b 793drop table t1; 794CREATE TABLE t1 (a varchar(5), b varchar(10)); 795INSERT INTO t1 VALUES 796('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2), 797('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8); 798SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 799a b 800BBB 4 801CCC 7 802AAA 8 803EXPLAIN 804SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 805id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8061 PRIMARY t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where 8072 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using temporary; Using filesort 808Warnings: 809Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` group by `test`.`t1`.`a` having (((<cache>(`test`.`t1`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) and ((<cache>(`test`.`t1`.`b`) = max(`test`.`t1`.`b`)) or isnull(max(`test`.`t1`.`b`))) and <is_not_null_test>(`test`.`t1`.`a`) and <is_not_null_test>(max(`test`.`t1`.`b`))))) 810ALTER TABLE t1 ADD INDEX(a); 811FLUSH STATUS; 812SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 813a b 814BBB 4 815CCC 7 816AAA 8 817SHOW SESSION STATUS LIKE 'Sort_scan%'; 818Variable_name Value 819Sort_scan 9 820EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 821id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8221 PRIMARY t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where 8232 DEPENDENT SUBQUERY t1 NULL ALL a NULL NULL NULL 9 100.00 Using temporary; Using filesort 824Warnings: 825Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` group by `test`.`t1`.`a` having (((<cache>(`test`.`t1`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) and ((<cache>(`test`.`t1`.`b`) = max(`test`.`t1`.`b`)) or isnull(max(`test`.`t1`.`b`))) and <is_not_null_test>(`test`.`t1`.`a`) and <is_not_null_test>(max(`test`.`t1`.`b`))))) 826DROP TABLE t1; 827CREATE TABLE t1 (a INT); 828INSERT INTO t1 VALUES (1),(2); 829EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); 830id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8311 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 8322 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 833Warnings: 834Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 835Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 1 836EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); 837id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8381 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 8392 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 840Warnings: 841Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 842Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 0 843DROP TABLE t1; 844# 845# BUG#49630: Segfault in select_describe() with double 846# nested subquery and materialization 847# 848CREATE TABLE t1 (t1i int); 849CREATE TABLE t2 (t2i int); 850CREATE TABLE t3 (t3i int); 851CREATE TABLE t4 (t4i int); 852INSERT INTO t1 VALUES (1); 853INSERT INTO t2 VALUES (1),(2); 854INSERT INTO t3 VALUES (1),(2); 855INSERT INTO t4 VALUES (1),(2); 856 857EXPLAIN 858SELECT t1i 859FROM t1 JOIN t4 ON t1i=t4i 860WHERE (t1i) IN ( 861SELECT t2i 862FROM t2 863WHERE (t2i) IN ( 864SELECT t3i 865FROM t3 866GROUP BY t3i 867) 868); 869id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8701 PRIMARY t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 8711 PRIMARY t4 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 8722 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 8733 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 874Warnings: 875Note 1003 /* select#1 */ select '1' AS `t1i` from `test`.`t4` where ((`test`.`t4`.`t4i` = '1') and <in_optimizer>('1',<exists>(/* select#2 */ select 1 from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`t2i`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (<cache>(`test`.`t2`.`t2i`) = `test`.`t3`.`t3i`))) and (<cache>('1') = `test`.`t2`.`t2i`))))) 876DROP TABLE t1,t2,t3,t4; 877# 878# BUG#46680 - Assertion failed in file item_subselect.cc, 879# line 305 crashing on HAVING subquery 880# 881# Create tables 882# 883CREATE TABLE t1 ( 884pk INT, 885v VARCHAR(1) DEFAULT NULL, 886PRIMARY KEY(pk) 887); 888CREATE TABLE t2 LIKE t1; 889CREATE TABLE t3 LIKE t1; 890CREATE TABLE empty1 (a int); 891INSERT INTO t1 VALUES (1,'c'),(2,NULL); 892INSERT INTO t2 VALUES (3,'m'),(4,NULL); 893INSERT INTO t3 VALUES (1,'n'); 894 895# 896# 1) Test that subquery materialization is setup for query with 897# premature optimize() exit due to "Impossible WHERE" 898# 899SELECT MIN(t2.pk) 900FROM t2 JOIN t1 ON t1.pk=t2.pk 901WHERE 'j' 902HAVING ('m') IN ( 903SELECT v 904FROM t2); 905MIN(t2.pk) 906NULL 907Warnings: 908Warning 1292 Truncated incorrect INTEGER value: 'j' 909 910EXPLAIN 911SELECT MIN(t2.pk) 912FROM t2 JOIN t1 ON t1.pk=t2.pk 913WHERE 'j' 914HAVING ('m') IN ( 915SELECT v 916FROM t2); 917id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9181 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 9192 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 920Warnings: 921Warning 1292 Truncated incorrect INTEGER value: 'j' 922Note 1003 /* select#1 */ select min(`test`.`t2`.`pk`) AS `MIN(t2.pk)` from `test`.`t2` join `test`.`t1` where 0 having <in_optimizer>('m',<exists>(/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>('m')))) 923 924# 925# 2) Test that subquery materialization is setup for query with 926# premature optimize() exit due to "No matching min/max row" 927# 928SELECT MIN(t2.pk) 929FROM t2 930WHERE t2.pk>10 931HAVING ('m') IN ( 932SELECT v 933FROM t2); 934MIN(t2.pk) 935NULL 936 937EXPLAIN 938SELECT MIN(t2.pk) 939FROM t2 940WHERE t2.pk>10 941HAVING ('m') IN ( 942SELECT v 943FROM t2); 944id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9451 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No matching min/max row 9462 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 947Warnings: 948Note 1003 /* select#1 */ select min(`test`.`t2`.`pk`) AS `MIN(t2.pk)` from `test`.`t2` where (`test`.`t2`.`pk` > 10) having <in_optimizer>('m',<exists>(/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>('m')))) 949 950# 951# 3) Test that subquery materialization is setup for query with 952# premature optimize() exit due to "Select tables optimized away" 953# 954SELECT MIN(pk) 955FROM t1 956WHERE pk=NULL 957HAVING ('m') IN ( 958SELECT v 959FROM t2); 960MIN(pk) 961NULL 962 963EXPLAIN 964SELECT MIN(pk) 965FROM t1 966WHERE pk=NULL 967HAVING ('m') IN ( 968SELECT v 969FROM t2); 970id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9711 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 9722 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 973Warnings: 974Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(pk)` from `test`.`t1` where (`test`.`t1`.`pk` = NULL) having <in_optimizer>('m',<exists>(/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>('m')))) 975 976# 977# 4) Test that subquery materialization is setup for query with 978# premature optimize() exit due to "No matching row in const table" 979# 980 981SELECT MIN(a) 982FROM (SELECT a FROM empty1) tt 983HAVING ('m') IN ( 984SELECT v 985FROM t2); 986MIN(a) 987NULL 988 989EXPLAIN 990SELECT MIN(a) 991FROM (SELECT a FROM empty1) tt 992HAVING ('m') IN ( 993SELECT v 994FROM t2); 995id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9961 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 9973 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 998Warnings: 999Note 1003 /* select#1 */ select min(NULL) AS `MIN(a)` from `test`.`empty1` having <in_optimizer>('m',<exists>(/* select#3 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>('m')))) 1000 1001# 1002# 5) Test that subquery materialization is setup for query with 1003# premature optimize() exit due to "Impossible WHERE noticed 1004# after reading const tables" 1005# 1006SELECT min(t1.pk) 1007FROM t1 1008WHERE t1.pk IN (SELECT 1 from t3 where pk>10) 1009HAVING ('m') IN ( 1010SELECT v 1011FROM t2); 1012min(t1.pk) 1013NULL 1014 1015EXPLAIN 1016SELECT min(t1.pk) 1017FROM t1 1018WHERE t1.pk IN (SELECT 1 from t3 where pk>10) 1019HAVING ('m') IN ( 1020SELECT v 1021FROM t2); 1022id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10231 PRIMARY t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 10243 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 10252 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1026Warnings: 1027Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `min(t1.pk)` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`pk`,<exists>(/* select#2 */ select 1 from dual where (('1' > 10) and (<cache>(`test`.`t1`.`pk`) = 1)))) having <in_optimizer>('m',<exists>(/* select#3 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>('m')))) 1028# 1029# Cleanup for BUG#46680 1030# 1031DROP TABLE IF EXISTS t1,t2,t3,empty1; 1032# 1033# BUG#52344 - Subquery materialization: 1034# Assertion if subquery in on-clause of outer join 1035# 1036CREATE TABLE t1 (i INTEGER); 1037INSERT INTO t1 VALUES (10); 1038CREATE TABLE t2 (j INTEGER); 1039INSERT INTO t2 VALUES (5); 1040CREATE TABLE t3 (k INTEGER); 1041EXPLAIN 1042SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); 1043id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10441 PRIMARY t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 10451 PRIMARY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 10462 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1047Warnings: 1048Note 1003 /* select#1 */ select '10' AS `i`,NULL AS `j` from `test`.`t2` where 1 1049SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); 1050i j 105110 NULL 1052EXPLAIN 1053SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); 1054id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10551 PRIMARY t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 10561 PRIMARY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 10572 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1058Warnings: 1059Note 1003 /* select#1 */ select '10' AS `i`,NULL AS `j` from `test`.`t2` where 1 1060SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); 1061i j 106210 NULL 1063DROP TABLE t1, t2, t3; 1064# End BUG#52344 1065CREATE TABLE t1 ( 1066pk INTEGER AUTO_INCREMENT, 1067col_int_nokey INTEGER, 1068col_int_key INTEGER, 1069col_varchar_key VARCHAR(1), 1070PRIMARY KEY (pk), 1071KEY (col_int_key), 1072KEY (col_varchar_key, col_int_key) 1073) 1074; 1075INSERT INTO t1 ( 1076col_int_key, col_int_nokey, col_varchar_key 1077) 1078VALUES 1079(2, NULL, 'w'), 1080(9, 7, 'm'), 1081(3, 9, 'm'), 1082(9, 7, 'k'), 1083(NULL, 4, 'r'), 1084(9, 2, 't'), 1085(3, 6, 'j'), 1086(8, 8, 'u'), 1087(8, NULL, 'h'), 1088(53, 5, 'o'), 1089(0, NULL, NULL), 1090(5, 6, 'k'), 1091(166, 188, 'e'), 1092(3, 2, 'n'), 1093(0, 1, 't'), 1094(1, 1, 'c'), 1095(9, 0, 'm'), 1096(5, 9, 'y'), 1097(6, NULL, 'f'), 1098(2, 4, 'd') 1099; 1100SELECT table2.col_varchar_key AS field1, 1101table2.col_int_nokey AS field2 1102FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2 1103ON (table2.col_varchar_key = table1.col_varchar_key ) ) 1104WHERE table1.pk = 6 1105HAVING ( field2 ) IN 1106( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2 1107FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2 1108ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) ) 1109ORDER BY field2 1110; 1111field1 field2 1112t 1 1113t 2 1114drop table t1; 1115# 1116# BUG#53103: MTR test ps crashes in optimize_cond() 1117# when running with --debug 1118# 1119CREATE TABLE t1(track varchar(15)); 1120INSERT INTO t1 VALUES ('CAD'), ('CAD'); 1121PREPARE STMT FROM 1122"SELECT 1 FROM t1 1123 WHERE 1124 track IN (SELECT track FROM t1 1125 GROUP BY track 1126 HAVING track>='CAD')"; 1127EXECUTE STMT ; 11281 11291 11301 1131EXECUTE STMT ; 11321 11331 11341 1135DEALLOCATE PREPARE STMT; 1136DROP TABLE t1; 1137# End of BUG#53103 1138# 1139# BUG#54511 - Assertion failed: cache != 0L in file 1140# sql_select.cc::sub_select_cache on HAVING 1141# 1142CREATE TABLE t1 (i int(11)); 1143CREATE TABLE t2 (c char(1)); 1144CREATE TABLE t3 (c char(1)); 1145INSERT INTO t1 VALUES (1), (2); 1146INSERT INTO t2 VALUES ('a'), ('b'); 1147INSERT INTO t3 VALUES ('x'), ('y'); 1148SELECT COUNT( i ),i 1149FROM t1 1150HAVING ('c') 1151IN (SELECT t2.c FROM (t2 JOIN t3)); 1152COUNT( i ) i 1153DROP TABLE t1,t2,t3; 1154# End BUG#54511 1155# 1156# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 1157# on subquery in FROM 1158# 1159CREATE TABLE t1 (a INTEGER); 1160CREATE TABLE t2 (b INTEGER); 1161INSERT INTO t2 VALUES (1); 1162explain SELECT a FROM ( 1163SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1164) table1; 1165id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11661 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 11673 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1168Warnings: 1169Note 1003 /* select#1 */ select NULL AS `a` from `test`.`t1` left join `test`.`t2` on(((NULL > 3) or <in_optimizer>(NULL,<exists>(/* select#3 */ select 1 from `test`.`t1` where (<cache>(NULL) = NULL))))) 1170SELECT a FROM ( 1171SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) 1172) table1; 1173a 1174DROP TABLE t1, t2; 1175# End BUG#56367 1176# 1177# Bug#59833 - materialization=on/off leads to different result set 1178# when using IN 1179# 1180CREATE TABLE t1 ( 1181pk int NOT NULL, 1182f1 int DEFAULT NULL, 1183PRIMARY KEY (pk) 1184) ENGINE=MyISAM; 1185CREATE TABLE t2 ( 1186pk int NOT NULL, 1187f1 int DEFAULT NULL, 1188PRIMARY KEY (pk) 1189) ENGINE=MyISAM; 1190INSERT INTO t1 VALUES (10,0); 1191INSERT INTO t2 VALUES (10,0),(11,0); 1192explain SELECT * FROM t1 JOIN t2 USING (f1) 1193WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1194id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11951 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 11962 DEPENDENT SUBQUERY t1 NULL system PRIMARY NULL NULL NULL 1 100.00 NULL 1197Warnings: 1198Note 1003 /* select#1 */ select '0' AS `f1`,'10' AS `pk`,`test`.`t2`.`pk` AS `pk` from `test`.`t2` where ((`test`.`t2`.`f1` = '0') and <in_optimizer>('0',<exists>(/* select#2 */ select 1 from dual where (<cache>('0') = '10')))) 1199SELECT * FROM t1 JOIN t2 USING (f1) 1200WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); 1201f1 pk pk 1202DROP TABLE t1, t2; 1203# End Bug#59833 1204# 1205# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT 1206# 1207CREATE TABLE t1 ( 1208col_varchar_key varchar(1) DEFAULT NULL, 1209col_varchar_nokey varchar(1) DEFAULT NULL, 1210KEY col_varchar_key (col_varchar_key)) 1211; 1212INSERT INTO t1 VALUES 1213('v','v'),('r','r'); 1214CREATE TABLE t2 ( 1215col_varchar_key varchar(1) DEFAULT NULL, 1216col_varchar_nokey varchar(1) DEFAULT NULL, 1217KEY col_varchar_key(col_varchar_key)) 1218; 1219INSERT INTO t2 VALUES 1220('r','r'),('c','c'); 1221CREATE VIEW v3 AS SELECT * FROM t2; 1222SELECT DISTINCT alias2.col_varchar_key 1223FROM t1 AS alias1 JOIN v3 AS alias2 1224ON alias2.col_varchar_key = alias1.col_varchar_key 1225HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2) 1226; 1227col_varchar_key 1228r 1229DROP TABLE t1, t2; 1230DROP VIEW v3; 1231# End Bug#11852644 1232 1233# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW 1234# INSTEAD OF NULL WHEN MATERIALIZATION ON 1235 1236CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY; 1237CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY; 1238INSERT INTO t2 VALUES (8),(7); 1239CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY; 1240INSERT INTO t3 VALUES (7); 1241SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3 1242FROM t3 1243LEFT JOIN t1 1244ON t1.col_int_nokey 1245WHERE (194, 200) IN ( 1246SELECT SQ4_alias1.col_int_nokey, 1247SQ4_alias2.col_int_nokey 1248FROM t2 AS SQ4_alias1 1249JOIN 1250t2 AS SQ4_alias2 1251ON SQ4_alias2.col_int_nokey = 5 1252) 1253GROUP BY field3 ; 1254MIN(t3.col_int_nokey) field3 1255DROP TABLE t1; 1256DROP TABLE t2; 1257DROP TABLE t3; 1258# 1259# Bug#13419028 - SUBQUERY MATERIALIZATION NOT USED IN CREATE 1260# SELECT 1261# 1262CREATE TABLE t1(a int); 1263INSERT INTO t1 values(1),(2); 1264CREATE TABLE t2(a int); 1265INSERT INTO t2 values(1),(2); 1266EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1267id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12681 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 12692 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1270Warnings: 1271Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))) 1272FLUSH STATUS; 1273SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1274a 12751 12762 1277CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2); 1278SELECT * FROM t3; 1279a 12801 12812 1282SHOW STATUS LIKE "CREATED_TMP_TABLES"; 1283Variable_name Value 1284Created_tmp_tables 0 1285DROP TABLE t1,t2,t3; 1286# 1287# Bug#13552968: Extra row with materialization on join + subquery in 1288# 1289CREATE TABLE t1 ( 1290col_varchar_nokey varchar(1) NOT NULL 1291) ENGINE=MyISAM; 1292INSERT INTO t1 VALUES ('b'); 1293CREATE TABLE t2 ( 1294col_varchar_nokey varchar(1) NOT NULL 1295) ENGINE=MyISAM; 1296INSERT INTO t2 VALUES ('k'); 1297CREATE TABLE t3 ( 1298col_varchar_nokey varchar(1) NOT NULL 1299) ENGINE=MyISAM; 1300explain SELECT STRAIGHT_JOIN * 1301FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey 1302FROM t3); 1303id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13041 PRIMARY t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 13051 PRIMARY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 13062 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1307Warnings: 1308Note 1003 /* select#1 */ select straight_join 'b' AS `col_varchar_nokey`,NULL AS `col_varchar_nokey` from `test`.`t2` where 1 1309SELECT STRAIGHT_JOIN * 1310FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey 1311FROM t3); 1312col_varchar_nokey col_varchar_nokey 1313b NULL 1314DROP TABLE t1, t2, t3; 1315# End of test for bug#13552968 1316# 1317# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) 1318# in join_read_const_table() 1319# 1320CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; 1321INSERT INTO t1 VALUES(1); 1322CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; 1323SELECT * 1324FROM t1 LEFT JOIN t2 1325ON t2.v IN(SELECT v FROM t1); 1326v v 13271 NULL 1328DROP TABLE t1, t2; 1329# End of test for bug#13591383. 1330# 1331# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive()) 1332# in join_read_const_table() 1333# 1334CREATE TABLE t1 ( 1335pk int NOT NULL, 1336col_int_nokey int DEFAULT NULL, 1337col_int_key int DEFAULT NULL, 1338PRIMARY KEY (pk), 1339KEY col_int_key (col_int_key) 1340) ENGINE=MyISAM; 1341INSERT INTO t1 VALUES (1,2,4), (2,150,62); 1342CREATE TABLE t2 ( 1343pk int NOT NULL, 1344col_int_key int DEFAULT NULL, 1345PRIMARY KEY (pk) 1346) ENGINE=MyISAM; 1347INSERT INTO t2 VALUES (1,7); 1348explain SELECT table1.pk, table2.pk 1349FROM t2 AS table1 LEFT JOIN t2 AS table2 1350ON table2.pk = table1.pk AND 1351table2.col_int_key IN 1352(SELECT col_int_key 1353FROM t1 AS innr 1354WHERE innr.col_int_nokey > innr.col_int_nokey 1355GROUP BY col_int_key 1356HAVING COUNT(*) > 0 1357); 1358id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13591 PRIMARY table1 NULL system NULL NULL NULL NULL 1 100.00 NULL 13601 PRIMARY table2 NULL system PRIMARY NULL NULL NULL 1 100.00 NULL 13612 DEPENDENT SUBQUERY innr NULL ALL col_int_key NULL NULL NULL 2 50.00 Using where; Using temporary; Using filesort 1362Warnings: 1363Note 1003 /* select#1 */ select '1' AS `pk`,NULL AS `pk` from `test`.`t2` `table2` where 1 1364FLUSH STATUS; 1365SELECT table1.pk, table2.pk 1366FROM t2 AS table1 LEFT JOIN t2 AS table2 1367ON table2.pk = table1.pk AND 1368table2.col_int_key IN 1369(SELECT col_int_key 1370FROM t1 AS innr 1371WHERE innr.col_int_nokey > innr.col_int_nokey 1372GROUP BY col_int_key 1373HAVING COUNT(*) > 0 1374); 1375pk pk 13761 NULL 1377SHOW SESSION STATUS LIKE 'Sort_scan%'; 1378Variable_name Value 1379Sort_scan 1 1380DROP TABLE t1, t2; 1381# End of test for bug#13607423. 1382 1383Test of WL#6094 "Allow subquery materialization in NOT IN if all 1384columns are not nullable" 1385 1386create table t1(a int not null); 1387create table t2(a int not null); 1388insert into t1 values(1),(2); 1389insert into t2 values(1),(2); 1390Test in SELECT list 1391 1392cols not nullable => subq materialization 1393explain extended select a, (a,a) in (select a,a from t2) from t1; 1394id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13951 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 13962 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1397Warnings: 1398Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1399Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)) having (<is_not_null_test>(`test`.`t2`.`a`) and <is_not_null_test>(`test`.`t2`.`a`)))) AS `(a,a) in (select a,a from t2)` from `test`.`t1` 1400select a, (a,a) in (select a,a from t2) from t1; 1401a (a,a) in (select a,a from t2) 14021 1 14032 1 1404 1405cols not nullable => subq materialization 1406explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1407from t1 join t2 on t1.a+t2.a=1000; 1408id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14091 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 14101 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14112 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1412Warnings: 1413Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1414Note 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`) and (<cache>(`test`.`t1`.`a`) = `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) 1415select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1416from t1 join t2 on t1.a+t2.a=1000; 1417a a (t1.a,t1.a) in (select a,a from t2 as t3) 1418 1419t2.a is not nullable, but in the query it may appear as NULL 1420as it's in an outer join. So, no materialization. 1421explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1422from t1 left join t2 on t1.a+t2.a=1000; 1423id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14241 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 14251 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14262 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1427Warnings: 1428Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1429Note 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 1430select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1431from t1 left join t2 on t1.a+t2.a=1000; 1432a a (t2.a,t2.a) in (select a,a from t2 as t3) 14331 NULL NULL 14342 NULL NULL 1435 1436alter table t2 modify a int; 1437two nullable inner cols => no subq materialization 1438explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1439from t1 join t2 on t1.a+t2.a=1000; 1440id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14411 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 14421 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14432 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 56.25 Using where 1444Warnings: 1445Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1446Note 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) 1447select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3) 1448from t1 join t2 on t1.a+t2.a=1000; 1449a a (t1.a,t1.a) in (select a,a from t2 as t3) 1450alter table t2 modify a int not null; 1451 1452Test in WHERE 1453 1454top-level => subq materialization. With one exception: if 1455semijoin is enabled in @@optimizer_switch, semijoin is chosen, 1456then rejected (due to outer join), and in that case, the 1457fallback is IN->EXISTS, subq-materialization is not tried... 1458explain extended select t1.a, t2.a 1459from t1 join t2 on t1.a+t2.a=3 1460where (t2.a,t2.a) in (select a,a from t2 as t3); 1461id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14621 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 14631 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14642 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1465Warnings: 1466Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1467Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`)))) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3)) 1468select t1.a, t2.a 1469from t1 join t2 on t1.a+t2.a=3 1470where (t2.a,t2.a) in (select a,a from t2 as t3); 1471a a 14722 1 14731 2 1474 1475cols not nullable => subq materialization 1476explain extended select t1.a, t2.a 1477from t1 join t2 on t1.a+t2.a=3 1478where (t2.a,t2.a) not in (select a,a from t2 as t3); 1479id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14801 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 14811 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 14822 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1483Warnings: 1484Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1485Note 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`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`)) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`)))))) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3)) 1486select t1.a, t2.a 1487from t1 join t2 on t1.a+t2.a=3 1488where (t2.a,t2.a) not in (select a,a from t2 as t3); 1489a a 1490drop table t1,t2; 1491 1492Test of WL6095 "Allow subquery materialization in NOT IN if 1493single-column subquery" 1494 1495create table t1(a int null); 1496create table t2(a int null); 1497insert into t1 values(1),(2); 1498insert into t2 values(1),(2); 1499 1500one col => subq materialization 1501explain extended select a, a in (select a from t2) from t1; 1502id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15031 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 15042 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1505Warnings: 1506Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1507Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) AS `a in (select a from t2)` from `test`.`t1` 1508select a, a in (select a from t2) from t1; 1509a a in (select a from t2) 15101 1 15112 1 1512 1513t2.a is not nullable, but in the query it may appear as NULL 1514as it's in an outer join. But there is only one inner column so 1515materialization is possible 1516explain extended select t1.a, t2.a, t2.a in (select * from t2 as t3) 1517from t1 left join t2 on t1.a+t2.a=1000; 1518id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15191 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 15201 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 15212 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1522Warnings: 1523Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1524Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 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) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true))) 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 1525select t1.a, t2.a, t2.a in (select * from t2 as t3) 1526from t1 left join t2 on t1.a+t2.a=1000; 1527a a t2.a in (select * from t2 as t3) 15281 NULL NULL 15292 NULL NULL 1530 1531_two_ outer columns, nullable => no materialization 1532explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1533from t1 left join t2 on t1.a+t2.a=1000; 1534id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15351 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 15361 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 15372 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1538Warnings: 1539Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1540Note 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 1541select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3) 1542from t1 left join t2 on t1.a+t2.a=1000; 1543a a (t2.a,t2.a) in (select a,a from t2 as t3) 15441 NULL NULL 15452 NULL NULL 1546drop table t1,t2; 1547 1548Test in HAVING 1549create table t1(a int, b int); 1550create table t2(a int); 1551insert into t1 values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3); 1552insert into t2 values(10),(20); 1553no NULLs. 1554explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1555having (z in (select * from t2)) is null; 1556id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15571 PRIMARY t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 15582 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1559Warnings: 1560Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1561Note 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`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true)))) 1562select t1.a as z, sum(t1.b) from t1 group by t1.a 1563having (z in (select * from t2)) is null; 1564z sum(t1.b) 1565one outer NULL 1566insert into t1 values(null,null); 1567explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1568having (z in (select * from t2)) is null; 1569id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15701 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 15712 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1572Warnings: 1573Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1574Note 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`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true)))) 1575select t1.a as z, sum(t1.b) from t1 group by t1.a 1576having (z in (select * from t2)) is null; 1577z sum(t1.b) 1578NULL NULL 1579one outer NULL and one inner NULL 1580insert into t2 values(null); 1581explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1582having (z in (select * from t2)) is null; 1583id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15841 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 15852 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1586Warnings: 1587Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1588Note 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`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true)))) 1589select t1.a as z, sum(t1.b) from t1 group by t1.a 1590having (z in (select * from t2)) is null; 1591z sum(t1.b) 1592NULL NULL 15931 6 15942 6 1595one inner NULL 1596delete from t1 where a is null; 1597explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a 1598having (z in (select * from t2)) is null; 1599id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16001 PRIMARY t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 16012 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1602Warnings: 1603Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1604Note 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`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true)))) 1605select t1.a as z, sum(t1.b) from t1 group by t1.a 1606having (z in (select * from t2)) is null; 1607z sum(t1.b) 16081 6 16092 6 1610drop table t1,t2; 1611 1612Verify that an inner NULL is looked up only once (result is 1613cached). 1614create table t1(a int); 1615create table t2(a int); 1616insert into t1 values(1),(2),(3),(4),(5),(6); 1617insert into t1 select * from t1; 1618insert into t2 values(10),(20),(NULL); 1619explain extended select a, (a in (select * from t2)) from t1; 1620id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16211 PRIMARY t1 NULL ALL NULL NULL NULL NULL 12 100.00 NULL 16222 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1623Warnings: 1624Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1625Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) AS `(a in (select * from t2))` from `test`.`t1` 1626flush status; 1627select a, (a in (select * from t2)) from t1; 1628a (a in (select * from t2)) 16291 NULL 16302 NULL 16313 NULL 16324 NULL 16335 NULL 16346 NULL 16351 NULL 16362 NULL 16373 NULL 16384 NULL 16395 NULL 16406 NULL 1641There will be one look-up in the temporary table for each row 1642of t1 (12), plus one additional look-up to check whether table 1643contains a NULL value. 1644show status like "handler_read_key"; 1645Variable_name Value 1646Handler_read_key 0 1647drop table t1,t2; 1648# 1649# Bug#13495157 - SUBQUERY MATERIALIZATION NOT USED FOR CERTAIN 1650# STATEMENTS 1651# 1652CREATE TABLE t1(a INT); 1653INSERT INTO t1 VALUES(1),(2),(3); 1654CREATE TABLE t2(a INT); 1655INSERT INTO t2 VALUES(1),(2),(4); 1656# subquery materialization used for SELECT: 1657EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1658id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16591 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 16602 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1661Warnings: 1662Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` <> 2) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)))) 1663SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1664a 16651 1666# Also used for INSERT SELECT: 1667CREATE TABLE t3 SELECT * FROM t1; 1668EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1669id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16701 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL 16711 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 16722 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1673INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1674SELECT * FROM t3; 1675a 16761 16771 16782 16793 1680EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1681id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16821 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL 16831 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary 16842 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1685INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1686SELECT * FROM t2; 1687a 16881 16891 16902 16914 1692EXPLAIN INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1693id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16941 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL 16951 PRIMARY t2 NULL ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary 16962 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 1697INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2); 1698SELECT * FROM t2; 1699a 17001 17011 17021 17031 17042 17054 17064 1707# Not used for single-table UPDATE, DELETE: 1708EXPLAIN SELECT * FROM t2 WHERE a IN (SELECT * FROM t1); 1709id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17101 PRIMARY t2 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 17112 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1712Warnings: 1713Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))) 1714EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1); 1715id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17161 UPDATE t2 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 17172 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1718UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1); 1719SELECT * FROM t2; 1720a 17210 17220 17230 17240 17251 17264 17274 1728EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t1); 1729id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17301 DELETE t2 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 17312 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1732DELETE FROM t2 WHERE a IN (SELECT * FROM t1); 1733SELECT * FROM t2; 1734a 17350 17360 17370 17380 17394 17404 1741EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t2); 1742ERROR HY000: You can't specify target table 't2' for update in FROM clause 1743EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t2); 1744ERROR HY000: You can't specify target table 't2' for update in FROM clause 1745UPDATE t2 SET a=3 WHERE a=0; 1746# Used for multi-table UPDATE, DELETE: 1747EXPLAIN SELECT * FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1748id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17491 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL 17501 PRIMARY t2 NULL ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (Block Nested Loop) 17512 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1752Warnings: 1753Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t2` join `test`.`t3` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` <> 2) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) 1754EXPLAIN UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1755id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17561 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL 17571 UPDATE t2 NULL ALL NULL NULL NULL NULL 6 100.00 Using where 17582 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1759UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1760SELECT * FROM t2; 1761a 17621 17631 17641 17651 17664 17674 1768EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1769id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17701 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL 17711 DELETE t2 NULL ALL NULL NULL NULL NULL 6 100.00 Using where 17722 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 1773DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2); 1774SELECT * FROM t2; 1775a 17764 17774 1778EXPLAIN UPDATE t2,t3 SET t2.a=10 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2); 1779ERROR HY000: You can't specify target table 't2' for update in FROM clause 1780EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2); 1781ERROR HY000: You can't specify target table 't2' for update in FROM clause 1782DROP TABLE t1,t2,t3; 1783# 1784# Test that subquery materialization only does one lookup: does 1785# not try to read the next row if the first row failed the 1786# subquery's WHERE. We use a case where index lookup is not 1787# enough to satisfy IN(), because index has length two when the 1788# outer value has length three, and thus the post-filtering 1789# WHERE added by subselect_hash_sj_engine::setup() makes the 1790# decision. 1791# 1792create table t1 (a varchar(3)); 1793create table t2 (a varchar(2)); 1794insert into t1 values('aaa'), ('aaa'); 1795insert into t2 values('aa'), ('aa'); 1796explain select * from t1 where a in (select a from t2); 1797id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17981 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 17992 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 1800Warnings: 1801Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))) 1802flush status; 1803select * from t1 where a in (select a from t2); 1804a 1805show status like "handler_read%"; 1806Variable_name Value 1807Handler_read_first 0 1808Handler_read_key 0 1809Handler_read_last 0 1810Handler_read_next 0 1811Handler_read_prev 0 1812Handler_read_rnd 0 1813Handler_read_rnd_next 9 1814drop table t1,t2; 1815# 1816# Bug#13655791 DIFFERENT RESULT WITH WL6094 ON QUERY WITH XOR 1817# IN WHERE CLAUSE + MYISAM 1818# 1819CREATE TABLE t1 ( 1820pk int NOT NULL, 1821col_varchar_nokey varchar(1) DEFAULT NULL, 1822PRIMARY KEY (pk) 1823); 1824INSERT INTO t1 VALUES (10,'x'); 1825CREATE TABLE t2 ( 1826pk int NOT NULL, 1827col_varchar_nokey varchar(1) DEFAULT NULL, 1828PRIMARY KEY (pk) 1829); 1830INSERT INTO t2 VALUES (1,'v'), (5,'x'), (11,'z'), (12,'c'), (15,'y'); 1831CREATE TABLE t3 ( 1832pk int NOT NULL, 1833col_int_key int DEFAULT NULL, 1834PRIMARY KEY (pk), 1835KEY col_int_key (col_int_key) 1836); 1837INSERT INTO t3 VALUES (10,8); 1838CREATE TABLE t4 ( 1839pk int NOT NULL, 1840col_varchar_nokey varchar(1) DEFAULT NULL, 1841PRIMARY KEY (pk) 1842); 1843INSERT INTO t4 VALUES (1,'x'); 1844EXPLAIN SELECT outr.pk, outr.col_varchar_nokey, outr2.col_varchar_nokey 1845FROM t2 AS outr2 1846JOIN t4 AS outr 1847ON (outr2.col_varchar_nokey > outr.col_varchar_nokey) 1848WHERE 1849outr.col_varchar_nokey IN ( 1850SELECT innr.col_varchar_nokey 1851FROM t3 AS innr2 1852LEFT JOIN t1 AS innr 1853ON (innr2.col_int_key >= innr.pk) 1854) 1855XOR outr.pk < 6 1856; 1857id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18581 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 18592 DEPENDENT SUBQUERY innr2 NULL system NULL NULL NULL NULL 1 100.00 NULL 18602 DEPENDENT SUBQUERY innr NULL system PRIMARY NULL NULL NULL 1 100.00 NULL 1861Warnings: 1862Note 1003 /* select#1 */ select '1' AS `pk`,'x' AS `col_varchar_nokey`,`test`.`outr2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2` `outr2` where ((<in_optimizer>('x',<exists>(/* select#2 */ select 1 from `test`.`t1` `innr` where <if>(outer_field_is_not_null, ((<cache>('x') = NULL) or isnull(NULL)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(NULL), true))) xor ('1' < 6)) and (`test`.`outr2`.`col_varchar_nokey` > 'x')) 1863FLUSH STATUS; 1864SELECT outr.pk, outr.col_varchar_nokey, outr2.col_varchar_nokey 1865FROM t2 AS outr2 1866JOIN t4 AS outr 1867ON (outr2.col_varchar_nokey > outr.col_varchar_nokey) 1868WHERE 1869outr.col_varchar_nokey IN ( 1870SELECT innr.col_varchar_nokey 1871FROM t3 AS innr2 1872LEFT JOIN t1 AS innr 1873ON (innr2.col_int_key >= innr.pk) 1874) 1875XOR outr.pk < 6 1876; 1877pk col_varchar_nokey col_varchar_nokey 1878SHOW STATUS LIKE "HANDLER_READ%"; 1879Variable_name Value 1880Handler_read_first 3 1881Handler_read_key 0 1882Handler_read_last 0 1883Handler_read_next 0 1884Handler_read_prev 0 1885Handler_read_rnd 0 1886Handler_read_rnd_next 3 1887DROP TABLE t1,t2,t3,t4; 1888# 1889# Bug#13727407: Assert !item->const_item() || !item->not_null_tables() 1890# 1891CREATE TABLE t1 ( 1892col_int_key INT, 1893KEY col_int_key (col_int_key) 1894); 1895INSERT INTO t1 VALUES (1); 1896CREATE TABLE t2 ( 1897col_int_key INT, 1898col_time_key TIME, 1899col_datetime_nokey DATETIME, 1900KEY col_int_key (col_int_key), 1901KEY col_time_key (col_time_key) 1902); 1903INSERT INTO t2 VALUES 1904(7,'14:03:03','2001-11-28 00:50:27'), (1,'01:46:09','2007-10-09 19:53:04'); 1905EXPLAIN SELECT col_datetime_nokey AS x 1906FROM t2 AS outr 1907WHERE col_int_key IN ( 1908SELECT STRAIGHT_JOIN col_int_key 1909FROM t1 1910) AND outr.col_int_key = 0 1911HAVING x = '2000-09-09' 1912ORDER BY col_time_key; 1913id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19141 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 19152 DEPENDENT SUBQUERY t1 NULL system col_int_key NULL NULL NULL 1 100.00 NULL 1916Warnings: 1917Note 1003 /* select#1 */ select `test`.`outr`.`col_datetime_nokey` AS `x` from `test`.`t2` `outr` where ((`test`.`outr`.`col_int_key` = 0) and <in_optimizer>(0,<exists>(/* select#2 */ select straight_join 1 from dual where (<cache>(0) = '1')))) having (`x` = '2000-09-09') order by `test`.`outr`.`col_time_key` 1918SELECT col_datetime_nokey AS x 1919FROM t2 AS outr 1920WHERE col_int_key IN ( 1921SELECT STRAIGHT_JOIN col_int_key 1922FROM t1 1923) AND outr.col_int_key = 0 1924HAVING x = '2000-09-09' 1925ORDER BY col_time_key; 1926x 1927DROP TABLE t1, t2; 1928# 1929# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN 1930# SUBSELECT_HASH_SJ_ENGINE::EXEC 1931# 1932CREATE TABLE t1 1933(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5)) 1934ENGINE=InnoDB; 1935INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,''); 1936Warnings: 1937Warning 1366 Incorrect integer value: '' for column 'c5' at row 1 1938CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB; 1939INSERT INTO t2 VALUES(0,'',''); 1940Warnings: 1941Warning 1366 Incorrect integer value: '' for column 'c4' at row 1 1942Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1 1943CREATE TABLE t3 1944(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk)) 1945ENGINE=InnoDB; 1946INSERT INTO t3 VALUES(0,8,'',0,''); 1947Warnings: 1948Warning 1366 Incorrect integer value: '' for column 'c1' at row 1 1949Warning 1366 Incorrect integer value: '' for column 'cy' at row 1 1950EXPLAIN SELECT o.c2 AS x FROM t1 AS o 1951WHERE o.c1 IN 1952(SELECT innr.c4 AS y 1953FROM t2 AS innr2 JOIN t3 AS innr 1954ON (innr2.c4k=innr.c4) 1955WHERE innr.c1=6 OR NOT innr.c1=innr.pk 1956ORDER BY innr.c4) 1957AND o.c4=7 XOR o.pk=3 ORDER BY o.pk; 1958id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19591 PRIMARY o NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using filesort 19602 DEPENDENT SUBQUERY innr2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where 19612 DEPENDENT SUBQUERY innr NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop) 1962Warnings: 1963Note 1003 /* select#1 */ select `test`.`o`.`c2` AS `x` from `test`.`t1` `o` where ((<in_optimizer>(`test`.`o`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` `innr2` join `test`.`t3` `innr` where ((`test`.`innr`.`c4` = `test`.`innr2`.`c4k`) and ((`test`.`innr`.`c1` = 6) or (`test`.`innr`.`c1` <> `test`.`innr`.`pk`)) and <if>(outer_field_is_not_null, ((<cache>(`test`.`o`.`c1`) = `test`.`innr2`.`c4k`) or isnull(`test`.`innr2`.`c4k`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`innr`.`c4`), true))) and (`test`.`o`.`c4` = 7)) xor (`test`.`o`.`pk` = 3)) order by `test`.`o`.`pk` 1964SELECT o.c2 AS x FROM t1 AS o 1965WHERE o.c1 IN 1966(SELECT innr.c4 AS y 1967FROM t2 AS innr2 JOIN t3 AS innr 1968ON (innr2.c4k=innr.c4) 1969WHERE innr.c1=6 OR NOT innr.c1=innr.pk 1970ORDER BY innr.c4) 1971AND o.c4=7 XOR o.pk=3 ORDER BY o.pk; 1972x 1973DROP TABLE t1,t2,t3; 1974# End of 5.6 tests 1975# 1976# Bug #18770217 DIFFERENT RESULT WITH SP ON 2ND EXECUTION OF QUERY WITH STRAIGHT_JOIN IN NOT IN 1977# 1978# 1979# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT 1980# 1981CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB; 1982SET @str= repeat("a",450); 1983SET @num=1000; 1984INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str)); 1985INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1986INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1987INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1988INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1989INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1; 1990SELECT COUNT(*) FROM t1; 1991COUNT(*) 199232 1993ANALYZE TABLE t1; 1994Table Op Msg_type Msg_text 1995test.t1 analyze status OK 1996set @save_heap_size= @@max_heap_table_size; 1997set @@max_heap_table_size= 16384; 1998set @saved_engine=@@internal_tmp_disk_storage_engine; 1999set global internal_tmp_disk_storage_engine = 'myisam'; 2000EXPLAIN SELECT COUNT(*) 2001FROM t1 2002WHERE t1.a NOT IN ( 2003SELECT t2.a FROM t1 as t2 2004); 2005id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20061 PRIMARY t1 NULL ALL NULL NULL NULL NULL 32 100.00 Using where 20072 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 32 100.00 Using where 2008Warnings: 2009Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` `t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))))) 2010SELECT COUNT(*) 2011FROM t1 2012WHERE t1.a NOT IN ( 2013SELECT t2.a FROM t1 as t2 2014); 2015COUNT(*) 20160 2017set global internal_tmp_disk_storage_engine = 'innodb'; 2018EXPLAIN SELECT COUNT(*) 2019FROM t1 2020WHERE t1.a NOT IN ( 2021SELECT t2.a FROM t1 as t2 2022); 2023id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20241 PRIMARY t1 NULL ALL NULL NULL NULL NULL 32 100.00 Using where 20252 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 32 100.00 Using where 2026Warnings: 2027Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` `t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))))) 2028SELECT COUNT(*) 2029FROM t1 2030WHERE t1.a NOT IN ( 2031SELECT t2.a FROM t1 as t2 2032); 2033COUNT(*) 20340 2035DROP TABLE t1; 2036set global internal_tmp_disk_storage_engine = @saved_engine; 2037set @@max_heap_table_size= @save_heap_size; 2038# 2039# Bug#19805761 ASSERTION FAILURE IN SUBSELECT_HASH_SJ_ENGINE::EXEC WITH SUBQUERY IN LEFT ARGUME 2040# 2041CREATE TABLE t1(a INT) ENGINE=INNODB; 2042INSERT INTO t1 VALUES(1); 2043CREATE TABLE t2 LIKE t1; 2044ANALYZE TABLE t1,t2; 2045Table Op Msg_type Msg_text 2046test.t1 analyze status OK 2047test.t2 analyze status OK 2048EXPLAIN SELECT (SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1) FROM t1; 2049id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20501 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 20513 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where 20522 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 2053Warnings: 2054Note 1003 /* select#1 */ select (not(<in_optimizer>((/* select#2 */ select 1,2 from `test`.`t2`),<exists>(/* select#3 */ select 1,1 from `test`.`t1` where (<if>(outer_field_is_not_null, ((<cache>(<cache>(1)) = 1) or <cache>(isnull(1))), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(2)) = 2) or <cache>(isnull(2))), true)) having (<if>(outer_field_is_not_null, <cache>(<is_not_null_test>(1)), true) and <if>(outer_field_is_not_null, <cache>(<is_not_null_test>(2)), true)))))) AS `(SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1)` from `test`.`t1` 2055SELECT (SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1) FROM t1; 2056(SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1) 2057NULL 2058DROP TABLE t1,t2; 2059# 2060# Bug#20729351 ASSERTION FAILED: ITEM_IN->LEFT_EXPR->ELEMENT_INDEX(0)->MAYBE_NULL 2061# 2062CREATE TABLE t1 (a LONGBLOB) ENGINE=INNODB; 2063INSERT INTO t1 VALUES ('a'), ('a'), ('a'); 2064CREATE TABLE t2 (a INT) ENGINE=INNODB; 2065INSERT INTO t2 VALUES(1), (1), (1), (1); 2066ANALYZE TABLE t1, t2; 2067Table Op Msg_type Msg_text 2068test.t1 analyze status OK 2069test.t2 analyze status OK 2070SELECT 2071(SELECT NULL IN (NULL) FROM t1 WHERE a) = 2072ANY(SELECT 1 FROM t2) 2073FROM t1; 2074(SELECT NULL IN (NULL) FROM t1 WHERE a) = 2075ANY(SELECT 1 FROM t2) 2076NULL 2077NULL 2078NULL 2079DROP TABLE t1, t2; 2080# 2081# Bug#22089623 ASSERTION IN ITEM_FUNC_TRIG_COND::VAL_INT() 2082# WITH SUBQUERY ON LEFT SIDE OF IN 2083# 2084EXPLAIN SELECT (SELECT NULL, NULL) IN (SELECT 1, 2); 2085id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20861 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 20873 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 20882 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2089Warnings: 2090Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL) IN (SELECT 1, 2)` 2091SELECT (SELECT NULL, NULL) IN (SELECT 1, 2); 2092(SELECT NULL, NULL) IN (SELECT 1, 2) 2093NULL 2094EXPLAIN SELECT (SELECT 1, 1) IN (SELECT 1, 2); 2095id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20961 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 20973 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 20982 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2099Warnings: 2100Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1),<exists>(/* select#3 */ select 1,2 having 0)) AS `(SELECT 1, 1) IN (SELECT 1, 2)` 2101SELECT (SELECT 1, 1) IN (SELECT 1, 2); 2102(SELECT 1, 1) IN (SELECT 1, 2) 21030 2104EXPLAIN SELECT (SELECT 1, 2) IN (SELECT 1, 2); 2105id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21061 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21073 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21082 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2109Warnings: 2110Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2),<exists>(/* select#3 */ select 1,2 having 1)) AS `(SELECT 1, 2) IN (SELECT 1, 2)` 2111SELECT (SELECT 1, 2) IN (SELECT 1, 2); 2112(SELECT 1, 2) IN (SELECT 1, 2) 21131 2114EXPLAIN SELECT (SELECT NULL, 2) IN (SELECT 1, 2); 2115id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21161 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21173 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21182 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2119Warnings: 2120Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,2),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true)))) AS `(SELECT NULL, 2) IN (SELECT 1, 2)` 2121SELECT (SELECT NULL, 2) IN (SELECT 1, 2); 2122(SELECT NULL, 2) IN (SELECT 1, 2) 2123NULL 2124EXPLAIN SELECT (SELECT 1, NULL) IN (SELECT 1, 2); 2125id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21261 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21273 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21282 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2129Warnings: 2130Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,NULL),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, NULL) IN (SELECT 1, 2)` 2131SELECT (SELECT 1, NULL) IN (SELECT 1, 2); 2132(SELECT 1, NULL) IN (SELECT 1, 2) 2133NULL 2134EXPLAIN SELECT (SELECT NULL, 1) IN (SELECT 1, 2); 2135id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21361 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21373 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 21382 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2139Warnings: 2140Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,1),<exists>(/* select#3 */ select 1,2 having 0)) AS `(SELECT NULL, 1) IN (SELECT 1, 2)` 2141SELECT (SELECT NULL, 1) IN (SELECT 1, 2); 2142(SELECT NULL, 1) IN (SELECT 1, 2) 21430 2144EXPLAIN SELECT (SELECT 2, NULL) IN (SELECT 1, 2); 2145id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21461 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21473 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 21482 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2149Warnings: 2150Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 2,NULL),<exists>(/* select#3 */ select 1,2 having 0)) AS `(SELECT 2, NULL) IN (SELECT 1, 2)` 2151SELECT (SELECT 2, NULL) IN (SELECT 1, 2); 2152(SELECT 2, NULL) IN (SELECT 1, 2) 21530 2154EXPLAIN SELECT (SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2); 2155id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21561 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21574 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21582 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21593 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2160NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 2161Warnings: 2162Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 union /* select#3 */ select 1,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`2`)) = 2)))) AS `(SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2)` 2163SELECT (SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2); 2164(SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2) 21651 2166EXPLAIN SELECT (SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2); 2167id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21681 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21694 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21702 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21713 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2172NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 2173Warnings: 2174Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union /* select#3 */ select 1,1),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`1`)) = 2)))) AS `(SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2)` 2175SELECT (SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2); 2176(SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2) 21770 2178EXPLAIN SELECT (SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2); 2179id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21801 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21814 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21822 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21833 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2184NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 2185Warnings: 2186Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL union /* select#3 */ select NULL,NULL),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`NULL`)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`NULL`)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2)` 2187SELECT (SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2); 2188(SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2) 2189NULL 2190EXPLAIN SELECT (SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2); 2191id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21921 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21934 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21942 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21953 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2196NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 2197Warnings: 2198Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union /* select#3 */ select 2,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`1`)) = 2)))) AS `(SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2)` 2199SELECT (SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2); 2200ERROR 21000: Subquery returns more than 1 row 2201EXPLAIN SELECT (SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2); 2202id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22031 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22044 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22052 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 22063 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2207NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 2208Warnings: 2209Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 from DUAL where 0 union /* select#3 */ select 1,2 from DUAL where 0),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`1`)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`2`)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)` 2210SELECT (SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2); 2211(SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2) 2212NULL 2213EXPLAIN SELECT (SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2); 2214id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22151 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22164 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22172 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22183 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2219Warnings: 2220Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 union all /* select#3 */ select 1,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`2`)) = 2)))) AS `(SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2)` 2221SELECT (SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2); 2222ERROR 21000: Subquery returns more than 1 row 2223EXPLAIN SELECT (SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2); 2224id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22251 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22264 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22272 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22283 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2229Warnings: 2230Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union all /* select#3 */ select 1,1),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`1`)) = 2)))) AS `(SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2)` 2231SELECT (SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2); 2232ERROR 21000: Subquery returns more than 1 row 2233EXPLAIN SELECT (SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2); 2234id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22351 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22364 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22372 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22383 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2239Warnings: 2240Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL union all /* select#3 */ select NULL,NULL),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`NULL`)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`NULL`)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2)` 2241SELECT (SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2); 2242ERROR 21000: Subquery returns more than 1 row 2243EXPLAIN SELECT (SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2); 2244id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22451 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22464 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22472 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22483 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2249Warnings: 2250Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union all /* select#3 */ select 2,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`1`)) = 1) and (<cache>(<cache>(`1`)) = 2)))) AS `(SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2)` 2251SELECT (SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2); 2252ERROR 21000: Subquery returns more than 1 row 2253EXPLAIN SELECT (SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2); 2254id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22551 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22564 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22572 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 22583 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2259Warnings: 2260Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 from DUAL where 0 union all /* select#3 */ select 1,2 from DUAL where 0),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`1`)) = 1) or isnull(1)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`2`)) = 2) or isnull(2)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)` 2261SELECT (SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2); 2262(SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2) 2263NULL 2264# 2265# Bug#20536077: WRONG RESULT FOR SELECT NULL,NULL IN (SUBQUERY) 2266# 2267# The following queries correctly returned NULL. 2268CREATE TABLE s (s INT) ENGINE=InnoDB; 2269INSERT INTO s VALUES(1); 2270SELECT NULL IN (SELECT 1); 2271NULL IN (SELECT 1) 2272NULL 2273SELECT NULL = (SELECT 1); 2274NULL = (SELECT 1) 2275NULL 2276SELECT NULL = (SELECT 1 FROM s); 2277NULL = (SELECT 1 FROM s) 2278NULL 2279SELECT (NULL, NULL) IN (SELECT 1, 2); 2280(NULL, NULL) IN (SELECT 1, 2) 2281NULL 2282# The following queries returned 0 instead of NULL. 2283SELECT NULL IN (SELECT 1 FROM s); 2284NULL IN (SELECT 1 FROM s) 2285NULL 2286SELECT (SELECT NULL, NULL FROM DUAL) IN (SELECT 1, 2 FROM s) FROM s; 2287(SELECT NULL, NULL FROM DUAL) IN (SELECT 1, 2 FROM s) 2288NULL 2289SELECT (SELECT NULL,NULL) IN (SELECT 1, 2); 2290(SELECT NULL,NULL) IN (SELECT 1, 2) 2291NULL 2292CREATE TABLE u(a INT, b INT) ENGINE=MyISAM; 2293INSERT INTO u VALUES (NULL, NULL); 2294SELECT (SELECT * FROM u) IN (SELECT 1, 2 FROM s) FROM s; 2295(SELECT * FROM u) IN (SELECT 1, 2 FROM s) 2296NULL 2297SELECT (SELECT * FROM u) IN (SELECT 1, 2); 2298(SELECT * FROM u) IN (SELECT 1, 2) 2299NULL 2300DROP TABLE s, u; 2301set @@optimizer_switch=@old_opt_switch; 2302SET sql_mode = default; 2303set optimizer_switch=default; 2304