1drop table if exists t1,t2,t3,t4; 2drop view if exists v1,v2,v3; 3set @subselect_extra_tmp=@@optimizer_switch; 4set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test, 5"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); 6# From explain.test: 7# 8# Bug#37870: Usage of uninitialized value caused failed assertion. 9# 10create table t1 (dt datetime not null, t time not null); 11create table t2 (dt datetime not null); 12insert into t1 values ('2001-01-01 1:1:1', '1:1:1'), 13('2001-01-01 1:1:1', '1:1:1'); 14insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); 15flush tables; 16EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); 17id select_type table type possible_keys key key_len ref rows Extra 181 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where 191 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR); Using join buffer (flat, BNL join) 20flush tables; 21SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); 22dt 23flush tables; 24EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); 25id select_type table type possible_keys key key_len ref rows Extra 261 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where 271 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR); Using join buffer (flat, BNL join) 28flush tables; 29SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); 30dt 312001-01-01 01:01:01 322001-01-01 01:01:01 33drop tables t1, t2; 34# From type_datetime.test: 35# 36# Bug #32694: NOT NULL table field in a subquery produces invalid results 37# 38create table t1 (id int(10) not null, cur_date datetime not null); 39create table t2 (id int(10) not null, cur_date date not null); 40insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); 41insert into t2 (id, cur_date) values (1, '2007-04-25'); 42explain extended 43select * from t1 44where id in (select id from t1 as x1 where (t1.cur_date is null)); 45id select_type table type possible_keys key key_len ref rows filtered Extra 461 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 47Warnings: 48Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 49Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where 0 50select * from t1 51where id in (select id from t1 as x1 where (t1.cur_date is null)); 52id cur_date 53explain extended 54select * from t2 55where id in (select id from t2 as x1 where (t2.cur_date is null)); 56id select_type table type possible_keys key key_len ref rows filtered Extra 571 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 58Warnings: 59Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 60Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where 0 61select * from t2 62where id in (select id from t2 as x1 where (t2.cur_date is null)); 63id cur_date 64insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22'); 65insert into t2 (id, cur_date) values (2, '2007-04-26'); 66explain extended 67select * from t1 68where id in (select id from t1 as x1 where (t1.cur_date is null)); 69id select_type table type possible_keys key key_len ref rows filtered Extra 701 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 711 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 72Warnings: 73Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 74Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where `test`.`x1`.`id` = `test`.`t1`.`id` and `test`.`t1`.`cur_date` = 0 75select * from t1 76where id in (select id from t1 as x1 where (t1.cur_date is null)); 77id cur_date 78explain extended 79select * from t2 80where id in (select id from t2 as x1 where (t2.cur_date is null)); 81id select_type table type possible_keys key key_len ref rows filtered Extra 821 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 831 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) 84Warnings: 85Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 86Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where `test`.`x1`.`id` = `test`.`t2`.`id` and `test`.`t2`.`cur_date` = 0 87select * from t2 88where id in (select id from t2 as x1 where (t2.cur_date is null)); 89id cur_date 90drop table t1,t2; 91# 92# From group_min_max.test 93# 94create table t1 ( 95a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 96); 97insert into t1 (a1, a2, b, c, d) values 98('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 99('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 100('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 101('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 102('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 103('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 104('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 105('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 106('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 107('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 108('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 109('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 110('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 111('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 112('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 113('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 114('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 115('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 116('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 117('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 118('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 119('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 120('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 121('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 122('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 123('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 124('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 125('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 126('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 127('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 128('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 129('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 130create index idx_t1_0 on t1 (a1); 131create index idx_t1_1 on t1 (a1,a2,b,c); 132create index idx_t1_2 on t1 (a1,a2,b); 133analyze table t1; 134Table Op Msg_type Msg_text 135test.t1 analyze status Engine-independent statistics collected 136test.t1 analyze status Table is already up to date 137create table t2 ( 138a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' 139); 140insert into t2 select * from t1; 141insert into t2 (a1, a2, b, c, d) values 142('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 143('a','a','a',NULL,'xyz'), 144('a','a','b',NULL,'xyz'), 145('a','b','a',NULL,'xyz'), 146('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 147('d','b','b',NULL,'xyz'), 148('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 149('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), 150('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 151('a','a','a',NULL,'xyz'), 152('a','a','b',NULL,'xyz'), 153('a','b','a',NULL,'xyz'), 154('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 155('d','b','b',NULL,'xyz'), 156('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 157('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); 158create index idx_t2_0 on t2 (a1); 159create index idx_t2_1 on t2 (a1,a2,b,c); 160create index idx_t2_2 on t2 (a1,a2,b); 161analyze table t2; 162Table Op Msg_type Msg_text 163test.t2 analyze status Engine-independent statistics collected 164test.t2 analyze status Table is already up to date 165create table t3 ( 166a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' 167); 168insert into t3 (a1, a2, b, c, d) values 169('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 170('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 171('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 172('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 173('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 174('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 175('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 176('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 177('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 178('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 179('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 180('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 181insert into t3 (a1, a2, b, c, d) values 182('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 183('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 184('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 185('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 186('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 187('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 188('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 189('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 190('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 191('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 192('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 193('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 194insert into t3 (a1, a2, b, c, d) values 195('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 196('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 197('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 198('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 199('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 200('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 201('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 202('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 203('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 204('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 205('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 206('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 207insert into t3 (a1, a2, b, c, d) values 208('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 209('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 210('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 211('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 212('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 213('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 214('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 215('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 216('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 217('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 218('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 219('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 220create index idx_t3_0 on t3 (a1); 221create index idx_t3_1 on t3 (a1,a2,b,c); 222create index idx_t3_2 on t3 (a1,a2,b); 223analyze table t3; 224Table Op Msg_type Msg_text 225test.t3 analyze status Engine-independent statistics collected 226test.t3 analyze status Table is already up to date 227explain select a1,a2,b,c,min(c), max(c) from t1 228where exists ( select * from t2 229where t2.c in (select c from t3 where t3.c > t1.b) and 230t2.c > 'b1' ) 231group by a1,a2,b; 232id select_type table type possible_keys key key_len ref rows Extra 2331 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2342 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2352 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) 236select a1,a2,b,c,min(c), max(c) from t1 237where exists ( select * from t2 238where t2.c in (select c from t3 where t3.c > t1.b) and 239t2.c > 'b1' ) 240group by a1,a2,b; 241a1 a2 b c min(c) max(c) 242a a a a111 a111 d111 243a a b e112 e112 h112 244a b a i121 i121 l121 245a b b m122 m122 p122 246b a a a211 a211 d211 247b a b e212 e212 h212 248b b a i221 i221 l221 249b b b m222 m222 p222 250c a a a311 a311 d311 251c a b e312 e312 h312 252c b a i321 i321 l321 253c b b m322 m322 p322 254d a a a411 a411 d411 255d a b e412 e412 h412 256d b a i421 i421 l421 257d b b m422 m422 p422 258explain select a1,a2,b,c,min(c), max(c) from t1 259where exists ( select * from t2 260where t2.c in (select c from t3 where t3.c > t1.c) and 261t2.c > 'b1' ) 262group by a1,a2,b; 263id select_type table type possible_keys key key_len ref rows Extra 2641 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2652 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2662 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) 267select a1,a2,b,c,min(c), max(c) from t1 268where exists ( select * from t2 269where t2.c in (select c from t3 where t3.c > t1.c) and 270t2.c > 'b1' ) 271group by a1,a2,b; 272a1 a2 b c min(c) max(c) 273a a a a111 a111 d111 274a a b e112 e112 h112 275a b a i121 i121 l121 276a b b m122 m122 p122 277b a a a211 a211 d211 278b a b e212 e212 h212 279b b a i221 i221 l221 280b b b m222 m222 p222 281c a a a311 a311 d311 282c a b e312 e312 h312 283c b a i321 i321 l321 284c b b m322 m322 o322 285d a a a411 a411 d411 286d a b e412 e412 h412 287d b a i421 i421 l421 288d b b m422 m422 o422 289drop table t1, t2, t3; 290# 291# From group_by.test 292# 293# Bug #21174: Index degrades sort performance and 294# optimizer does not honor IGNORE INDEX. 295# a.k.a WL3527. 296# 297CREATE TABLE t1 (a INT, b INT, 298PRIMARY KEY (a), 299KEY i2(a,b)); 300INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); 301INSERT INTO t1 SELECT a + 8,b FROM t1; 302INSERT INTO t1 SELECT a + 16,b FROM t1; 303INSERT INTO t1 SELECT a + 32,b FROM t1; 304INSERT INTO t1 SELECT a + 64,b FROM t1; 305INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16; 306ANALYZE TABLE t1; 307Table Op Msg_type Msg_text 308test.t1 analyze status Engine-independent statistics collected 309test.t1 analyze status OK 310EXPLAIN SELECT 1 FROM t1 WHERE a IN 311(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 312id select_type table type possible_keys key key_len ref rows Extra 3131 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index 3141 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 3152 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 316CREATE TABLE t2 (a INT, b INT, KEY(a)); 317INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); 318EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 319id select_type table type possible_keys key key_len ref rows Extra 3201 SIMPLE t2 index NULL a 5 NULL 2 321EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; 322id select_type table type possible_keys key key_len ref rows Extra 3231 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 324EXPLAIN SELECT 1 FROM t2 WHERE a IN 325(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 326id select_type table type possible_keys key key_len ref rows Extra 3271 PRIMARY t2 index a a 5 NULL 4 Using index 3281 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 3292 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 330DROP TABLE t1, t2; 331# 332# From derived_view.test 333# 334set @tmp_subselect_extra_derived=@@optimizer_switch; 335set optimizer_switch='derived_merge=on,derived_with_keys=on'; 336# 337# LP bug #806504: right join over a view/derived table 338# 339CREATE TABLE t1 (a int, b int) ; 340INSERT INTO t1 VALUES (0,0); 341CREATE TABLE t2 (a int) ; 342INSERT INTO t2 VALUES (0), (0); 343CREATE VIEW v1 AS SELECT * FROM t1; 344SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 345WHERE t.a IN (SELECT b FROM t1); 346a a b 347NULL 0 0 348EXPLAIN EXTENDED 349SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 350WHERE t.a IN (SELECT b FROM t1); 351id select_type table type possible_keys key key_len ref rows filtered Extra 3521 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 3531 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 3541 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 355Warnings: 356Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` semi join (dual) where 1 357SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 358WHERE t.a IN (SELECT b FROM t1); 359a a b 360NULL 0 0 361EXPLAIN EXTENDED 362SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 363WHERE t.a IN (SELECT b FROM t1); 364id select_type table type possible_keys key key_len ref rows filtered Extra 3651 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 3661 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 3671 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 368Warnings: 369Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` semi join (dual) where 1 370DROP VIEW v1; 371DROP TABLE t1,t2; 372# 373# LP bug #793448: materialized view accessed by two-component key 374# 375CREATE TABLE t1 (a int, b int); 376INSERT INTO t1 VALUES (9,3), (2,5); 377CREATE TABLE t2 (a int, b int); 378INSERT INTO t2 VALUES (9,3), (3,7), (9,1), (2,5), (2,4), (3,8); 379CREATE TABLE t3 (a int, b int); 380INSERT INTO t3 VALUES (10,3), (9,7), (9,1), (2,4); 381CREATE VIEW v1(a,b) AS SELECT a, MAX(b) FROM t2 GROUP BY a; 382CREATE VIEW v2(a,b) AS SELECT a,b FROM t2 UNION SELECT a,b FROM t3; 383SELECT * FROM v1; 384a b 3852 5 3863 8 3879 3 388SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); 389a 3909 3912 392EXPLAIN 393SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); 394id select_type table type possible_keys key key_len ref rows Extra 3951 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 3961 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) 3973 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 398SELECT * FROM v2; 399a b 4009 3 4013 7 4029 1 4032 5 4042 4 4053 8 40610 3 4079 7 408SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); 409a 4109 4112 412EXPLAIN 413SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); 414id select_type table type possible_keys key key_len ref rows Extra 4151 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 4161 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 4173 DERIVED t2 ALL NULL NULL NULL NULL 6 4184 UNION t3 ALL NULL NULL NULL NULL 4 419NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL 420DROP VIEW v1,v2; 421DROP TABLE t1,t2,t3; 422# 423# LP bug #874006: materialized view used in IN subquery 424# 425CREATE TABLE t3 (a int NOT NULL, b varchar(1), c varchar(1)); 426INSERT INTO t3 VALUES (19,NULL,NULL), (20,'r','r'); 427CREATE TABLE t1 (a int, b varchar(1) , c varchar(1)); 428INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y'); 429CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1)); 430INSERT INTO t2 VALUES (4,3,'r'); 431CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 432SET SESSION optimizer_switch='derived_with_keys=off'; 433EXPLAIN 434SELECT * FROM t3 435WHERE t3.b IN (SELECT v1.b FROM v1, t2 436WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); 437id select_type table type possible_keys key key_len ref rows Extra 4381 PRIMARY t2 system NULL NULL NULL NULL 1 4391 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 4401 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer (flat, BNL join) 4413 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where 442SELECT * FROM t3 443WHERE t3.b IN (SELECT v1.b FROM v1, t2 444WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); 445a b c 44620 r r 447SET SESSION optimizer_switch='derived_with_keys=on'; 448EXPLAIN 449SELECT * FROM t3 450WHERE t3.b IN (SELECT v1.b FROM v1, t2 451WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); 452id select_type table type possible_keys key key_len ref rows Extra 4531 PRIMARY t2 system NULL NULL NULL NULL 1 4541 PRIMARY <derived3> ref key1 key1 8 const,const 0 Start temporary 4551 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) 4563 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where 457SELECT * FROM t3 458WHERE t3.b IN (SELECT v1.b FROM v1, t2 459WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); 460a b c 46120 r r 462DROP VIEW v1; 463DROP TABLE t1,t2,t3; 464# 465# LP bug #873263: materialized view used in correlated IN subquery 466# 467CREATE TABLE t1 (a int, b int) ; 468INSERT INTO t1 VALUES (5,4), (9,8); 469CREATE TABLE t2 (a int, b int) ; 470INSERT INTO t2 VALUES (4,5), (5,1); 471CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; 472SET SESSION optimizer_switch='derived_with_keys=on'; 473EXPLAIN 474SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); 475id select_type table type possible_keys key key_len ref rows Extra 4761 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 4771 PRIMARY <derived3> ref key0 key0 10 test.t1.b,test.t1.a 2 FirstMatch(t1) 4783 DERIVED t2 ALL NULL NULL NULL NULL 2 479SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); 480a b 4815 4 482DROP VIEW v2; 483DROP TABLE t1,t2; 484set optimizer_switch= @tmp_subselect_extra_derived; 485