1drop table if exists t1,t2,t3; 2set optimizer_switch='exists_to_in=on'; 3# 4# LP BUG#884644 exists2in broke name resolution 5# 6CREATE TABLE t1 (f1 integer); 7SELECT * FROM t1 WHERE EXISTS (SELECT NO_SUCH_TABLE.NO_SUCH_FIELD FROM t1); 8ERROR 42S22: Unknown column 'NO_SUCH_TABLE.NO_SUCH_FIELD' in 'field list' 9drop table t1; 10# 11# LP BUG#884657 Wrong result with exists2in , correlated subquery 12# 13CREATE TABLE t1 ( a varchar(1)) ; 14INSERT INTO t1 VALUES ('c'),('b'); 15CREATE TABLE t2 ( b varchar(1)) ; 16INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r'); 17CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; 18INSERT INTO t3 VALUES (29,'c'); 19SELECT * 20FROM t1, t2 21WHERE EXISTS ( 22SELECT a 23FROM t3 24WHERE t3.b = t1.a 25AND t3.b <> t2.b 26); 27a b 28c v 29c v 30c x 31c i 32c e 33c p 34c s 35c j 36c z 37c a 38c q 39c y 40c r 41c v 42c r 43INSERT INTO t3 VALUES (2,'c'); 44alter table t1 add index aa (a); 45alter table t3 add index bb (b); 46-- EXIST to IN then semijoin (has priority over IN to EXISTS) 47set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; 48SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 49a 50c 51explain extended 52SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 53id select_type table type possible_keys key key_len ref rows filtered Extra 541 PRIMARY t1 index aa aa 4 NULL 2 100.00 Using index 551 PRIMARY t3 ALL bb NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 56Warnings: 57Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 58Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` 59-- EXIST to IN then IN to EXISTS 60set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; 61SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 62a 63c 64explain extended 65SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 66id select_type table type possible_keys key key_len ref rows filtered Extra 671 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 682 DEPENDENT SUBQUERY t3 ALL bb NULL NULL NULL 2 100.00 Using where 69Warnings: 70Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 71Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t3`.`b` from `test`.`t3` where <cache>(`test`.`t1`.`a`) = `test`.`t3`.`b`)) 72-- EXIST2IN then MATERIALIZATION 73set optimizer_switch='exists_to_in=on,in_to_exists=off,semijoin=off,materialization=on,subquery_cache=off'; 74SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 75a 76c 77explain extended 78SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 79id select_type table type possible_keys key key_len ref rows filtered Extra 801 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 812 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 82Warnings: 83Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 84Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t3`.`b` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`))) 85-- NO EXIST2IN 86set optimizer_switch='exists_to_in=off,subquery_cache=off'; 87SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 88a 89c 90explain extended 91SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); 92id select_type table type possible_keys key key_len ref rows filtered Extra 931 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 942 DEPENDENT SUBQUERY t3 ALL bb NULL NULL NULL 2 100.00 Using where 95Warnings: 96Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 97Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` where `test`.`t3`.`b` = `test`.`t1`.`a` limit 1) 98set optimizer_switch=default; 99set optimizer_switch='exists_to_in=on'; 100drop table t1,t2,t3; 101# 102# From group_min_max.test 103# 104create table t1 ( 105a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 106); 107insert into t1 (a1, a2, b, c, d) values 108('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 109('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 110('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 111('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 112('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 113('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 114('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 115('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 116('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 117('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 118('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 119('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 120('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 121('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 122('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 123('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 124('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 125('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 126('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 127('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 128('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 129('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 130('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 131('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 132('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 133('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 134('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 135('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 136('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 137('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 138('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 139('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 140create index idx_t1_0 on t1 (a1); 141create index idx_t1_1 on t1 (a1,a2,b,c); 142create index idx_t1_2 on t1 (a1,a2,b); 143analyze table t1; 144Table Op Msg_type Msg_text 145test.t1 analyze status Engine-independent statistics collected 146test.t1 analyze status Table is already up to date 147create table t2 ( 148a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' 149); 150insert into t2 select * from t1; 151insert into t2 (a1, a2, b, c, d) values 152('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 153('a','a','a',NULL,'xyz'), 154('a','a','b',NULL,'xyz'), 155('a','b','a',NULL,'xyz'), 156('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 157('d','b','b',NULL,'xyz'), 158('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 159('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), 160('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 161('a','a','a',NULL,'xyz'), 162('a','a','b',NULL,'xyz'), 163('a','b','a',NULL,'xyz'), 164('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 165('d','b','b',NULL,'xyz'), 166('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 167('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); 168create index idx_t2_0 on t2 (a1); 169create index idx_t2_1 on t2 (a1,a2,b,c); 170create index idx_t2_2 on t2 (a1,a2,b); 171analyze table t2; 172Table Op Msg_type Msg_text 173test.t2 analyze status Engine-independent statistics collected 174test.t2 analyze status Table is already up to date 175create table t3 ( 176a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' 177); 178insert into t3 (a1, a2, b, c, d) values 179('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 180('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 181('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 182('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 183('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 184('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 185('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 186('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 187('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 188('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 189('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 190('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 191insert into t3 (a1, a2, b, c, d) values 192('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 193('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 194('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 195('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 196('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 197('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 198('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 199('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 200('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 201('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 202('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 203('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 204insert into t3 (a1, a2, b, c, d) values 205('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 206('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 207('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 208('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 209('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 210('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 211('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 212('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 213('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 214('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 215('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 216('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 217insert into t3 (a1, a2, b, c, d) values 218('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 219('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 220('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 221('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 222('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 223('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 224('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 225('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 226('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 227('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 228('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 229('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 230create index idx_t3_0 on t3 (a1); 231create index idx_t3_1 on t3 (a1,a2,b,c); 232create index idx_t3_2 on t3 (a1,a2,b); 233analyze table t3; 234Table Op Msg_type Msg_text 235test.t3 analyze status Engine-independent statistics collected 236test.t3 analyze status Table is already up to date 237explain select a1,a2,b,c,min(c), max(c) from t1 238where exists ( select * from t2 239where t2.c in (select c from t3 where t3.c > t1.b) and 240t2.c > 'b1' ) 241group by a1,a2,b; 242id select_type table type possible_keys key key_len ref rows Extra 2431 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2442 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2452 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) 246select a1,a2,b,c,min(c), max(c) from t1 247where exists ( select * from t2 248where t2.c in (select c from t3 where t3.c > t1.b) and 249t2.c > 'b1' ) 250group by a1,a2,b; 251a1 a2 b c min(c) max(c) 252a a a a111 a111 d111 253a a b e112 e112 h112 254a b a i121 i121 l121 255a b b m122 m122 p122 256b a a a211 a211 d211 257b a b e212 e212 h212 258b b a i221 i221 l221 259b b b m222 m222 p222 260c a a a311 a311 d311 261c a b e312 e312 h312 262c b a i321 i321 l321 263c b b m322 m322 p322 264d a a a411 a411 d411 265d a b e412 e412 h412 266d b a i421 i421 l421 267d b b m422 m422 p422 268explain select a1,a2,b,c,min(c), max(c) from t1 269where exists ( select * from t2 270where t2.c in (select c from t3 where t3.c > t1.c) and 271t2.c > 'b1' ) 272group by a1,a2,b; 273id select_type table type possible_keys key key_len ref rows Extra 2741 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2752 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2762 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) 277select a1,a2,b,c,min(c), max(c) from t1 278where exists ( select * from t2 279where t2.c in (select c from t3 where t3.c > t1.c) and 280t2.c > 'b1' ) 281group by a1,a2,b; 282a1 a2 b c min(c) max(c) 283a a a a111 a111 d111 284a a b e112 e112 h112 285a b a i121 i121 l121 286a b b m122 m122 p122 287b a a a211 a211 d211 288b a b e212 e212 h212 289b b a i221 i221 l221 290b b b m222 m222 p222 291c a a a311 a311 d311 292c a b e312 e312 h312 293c b a i321 i321 l321 294c b b m322 m322 o322 295d a a a411 a411 d411 296d a b e412 e412 h412 297d b a i421 i421 l421 298d b b m422 m422 o422 299drop table t1, t2, t3; 300CREATE TABLE t1 ( a INT ); 301INSERT INTO t1 VALUES (7),(0); 302CREATE TABLE t2 ( b INT ); 303INSERT INTO t2 VALUES (0),(8); 304SELECT * FROM t1 WHERE 305EXISTS ( SELECT * FROM t2 WHERE b = a ) 306OR a > 0; 307a 3087 3090 310explain extended 311SELECT * FROM t1 WHERE 312EXISTS ( SELECT * FROM t2 WHERE b = a ) 313OR a > 0; 314id select_type table type possible_keys key key_len ref rows filtered Extra 3151 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3162 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 317Warnings: 318Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 319Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`)))) or `test`.`t1`.`a` > 0 320drop tables t1,t2; 321CREATE TABLE t1 ( a INT ); 322INSERT INTO t1 VALUES (1),(5); 323CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; 324INSERT INTO t2 VALUES (1); 325CREATE TABLE t3 ( c INT ); 326INSERT INTO t3 VALUES (4),(5); 327SET optimizer_switch='exists_to_in=on,subquery_cache=off,materialization=on,in_to_exists=off,semijoin=off'; 328explain extended 329SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 330id select_type table type possible_keys key key_len ref rows filtered Extra 3311 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 3322 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3333 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where 334Warnings: 335Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 336Note 1003 /* select#1 */ select (/* select#2 */ select 1 from dual where !(1 is not null and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` 337SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 338( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 3391 3401 341SET optimizer_switch='exists_to_in=on,subquery_cache=off'; 342explain extended 343SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 344id select_type table type possible_keys key key_len ref rows filtered Extra 3451 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 3462 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3473 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where 348Warnings: 349Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 350Note 1003 /* select#1 */ select (/* select#2 */ select 1 from dual where !(1 is not null and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` 351SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 352( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 3531 3541 355SET optimizer_switch='exists_to_in=off,subquery_cache=off'; 356explain extended 357SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 358id select_type table type possible_keys key key_len ref rows filtered Extra 3591 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 3602 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3613 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 362Warnings: 363Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 364Note 1003 /* select#1 */ select (/* select#2 */ select 1 from dual where !exists(/* select#3 */ select `test`.`t3`.`c` from `test`.`t3` where `test`.`t3`.`c` = 1 limit 1)) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` 365SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; 366( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 3671 3681 369set optimizer_switch=default; 370set optimizer_switch='exists_to_in=on'; 371drop table t1,t2,t3; 372# multi condition test 373CREATE TABLE t1 ( a varchar(1), a1 varchar(1)) ; 374INSERT INTO t1 VALUES ('c', 'c'), ('b', 'b'); 375CREATE TABLE t3 ( a int NOT NULL , b varchar(1), b1 varchar(1)) ; 376INSERT INTO t3 VALUES (29,'c','c'); 377INSERT INTO t3 VALUES (2,'c','c'); 378alter table t1 add index aa (a,a1); 379alter table t3 add index bb (b,b1); 380-- EXIST to IN then semijoin (has priority over IN to EXISTS) 381set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; 382SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 383a a1 384c c 385explain extended 386SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 387id select_type table type possible_keys key key_len ref rows filtered Extra 3881 PRIMARY t3 index bb bb 8 NULL 2 100.00 Using where; Using index; LooseScan 3891 PRIMARY t1 ref aa aa 8 test.t3.b,test.t3.b1 2 100.00 Using index 390Warnings: 391Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 392Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 393Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a1` AS `a1` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`a1` = `test`.`t3`.`b1` 394-- EXIST to IN then IN to EXISTS 395set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; 396SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 397a a1 398c c 399explain extended 400SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 401id select_type table type possible_keys key key_len ref rows filtered Extra 4021 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 4032 DEPENDENT SUBQUERY t3 index_subquery bb bb 8 func,func 2 100.00 Using index; Using where 404Warnings: 405Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 406Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 407Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a1` AS `a1` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a1`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t3 on bb where <cache>(`test`.`t1`.`a`) = `test`.`t3`.`b` and <cache>(`test`.`t1`.`a1`) = `test`.`t3`.`b1`))) 408-- EXIST2IN then MATERIALIZATION 409set optimizer_switch='exists_to_in=on,in_to_exists=off,semijoin=off,materialization=on,subquery_cache=off'; 410SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 411a a1 412c c 413explain extended 414SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 415id select_type table type possible_keys key key_len ref rows filtered Extra 4161 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 4172 MATERIALIZED t3 index NULL bb 8 NULL 2 100.00 Using index 418Warnings: 419Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 420Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 421Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a1` AS `a1` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a1`),(`test`.`t1`.`a`,`test`.`t1`.`a1`) in ( <materialize> (/* select#2 */ select `test`.`t3`.`b`,`test`.`t3`.`b1` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b` and `test`.`t1`.`a1` = `<subquery2>`.`b1`))) 422-- NO EXIST2IN 423set optimizer_switch='exists_to_in=off,subquery_cache=off'; 424SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 425a a1 426c c 427explain extended 428SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); 429id select_type table type possible_keys key key_len ref rows filtered Extra 4301 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 4312 DEPENDENT SUBQUERY t3 ref bb bb 8 test.t1.a,test.t1.a1 2 100.00 Using index 432Warnings: 433Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 434Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 435Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a1` AS `a1` from `test`.`t1` where exists(/* select#2 */ select 1 from `test`.`t3` where `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`b1` = `test`.`t1`.`a1` limit 1) 436set optimizer_switch=default; 437set optimizer_switch='exists_to_in=on'; 438drop table t1,t3; 439# 440# MDEV-159 Assertion about not marked for read failed in 441# String* Field_varstring::val_str(String*, String*) 442# 443SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on'); 444SET optimizer_switch='exists_to_in=on'; 445CREATE TABLE t1 ( a VARCHAR(1) ); 446INSERT INTO t1 VALUES ('k'),('m'); 447CREATE TABLE t2 ( b INT, 448c VARCHAR(1), 449d VARCHAR(1) NOT NULL ); 450INSERT INTO t2 VALUES 451(4,'j','j'),(6,'v','v'); 452CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t2 WHERE b < 1; 453SELECT c FROM v 454WHERE EXISTS ( 455SELECT * FROM t1, t2 456WHERE a <= v.d AND b = v.b 457); 458c 459explain extended 460SELECT c FROM v 461WHERE EXISTS ( 462SELECT * FROM t1, t2 463WHERE a <= v.d AND b = v.b 464); 465id select_type table type possible_keys key key_len ref rows filtered Extra 4661 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 4672 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 4682 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 469Warnings: 470Note 1276 Field or reference 'v.d' of SELECT #2 was resolved in SELECT #1 471Note 1276 Field or reference 'v.b' of SELECT #2 was resolved in SELECT #1 472Note 1003 /* select#1 */ select `test`.`t2`.`c` AS `c` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <= `test`.`t2`.`d` and <cache>(`test`.`t2`.`b`) = `test`.`t2`.`b`)) and `test`.`t2`.`b` < 1 473set optimizer_switch=default; 474set optimizer_switch='exists_to_in=on'; 475drop view v; 476drop table t1,t2; 477# 478# MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON 479# 480SET optimizer_switch = 'in_to_exists=on,subquery_cache=on,exists_to_in=on'; 481CREATE TABLE t1 ( 482a VARCHAR(3) NOT NULL, 483b VARCHAR(50) 484); 485INSERT INTO t1 VALUES 486('USA','Chinese'),('USA','English'), 487('FRA','French'),('ITA','Italian'); 488CREATE TABLE t2 ( c VARCHAR(3) ); 489INSERT INTO t2 VALUES ('USA'),('FRA'); 490SELECT * FROM t1 AS alias1, t1 AS alias2 491WHERE EXISTS ( 492SELECT * FROM t1, t2 493WHERE a <= alias2.a AND c = alias1.b 494) OR alias1 .a = 'foo'; 495a b a b 496SELECT * FROM t1 AS alias1, t1 AS alias2 497WHERE EXISTS ( 498SELECT * FROM t1, t2 499WHERE a <= alias2.a AND c = alias1.a 500) OR alias1 .a = 'foo'; 501a b a b 502USA Chinese USA Chinese 503USA English USA Chinese 504FRA French USA Chinese 505USA Chinese USA English 506USA English USA English 507FRA French USA English 508USA Chinese FRA French 509USA English FRA French 510FRA French FRA French 511USA Chinese ITA Italian 512USA English ITA Italian 513FRA French ITA Italian 514drop table t1,t2; 515set optimizer_switch=default; 516set optimizer_switch='exists_to_in=on'; 517# 518# MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON 519# 520SET optimizer_switch = 'in_to_exists=on,subquery_cache=on,exists_to_in=on'; 521CREATE TABLE t1 ( 522a VARCHAR(3) NOT NULL, 523b VARCHAR(50) 524); 525INSERT INTO t1 VALUES 526('USA','Chinese'),('USA','English'), 527('FRA','French'),('ITA','Italian'); 528CREATE TABLE t2 ( c VARCHAR(3) ); 529INSERT INTO t2 VALUES ('USA'),('FRA'); 530SELECT * FROM t1 AS alias1, t1 AS alias2 531WHERE EXISTS ( 532SELECT * FROM t1, t2 533WHERE a <= alias2.a AND c = alias1.b 534) OR alias1 .a = 'foo'; 535a b a b 536explain extended 537SELECT * FROM t1 AS alias1, t1 AS alias2 538WHERE EXISTS ( 539SELECT * FROM t1, t2 540WHERE a <= alias2.a AND c = alias1.b 541) OR alias1 .a = 'foo'; 542id select_type table type possible_keys key key_len ref rows filtered Extra 5431 PRIMARY alias1 ALL NULL NULL NULL NULL 4 100.00 5441 PRIMARY alias2 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 5452 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 5462 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 547Warnings: 548Note 1276 Field or reference 'test.alias2.a' of SELECT #2 was resolved in SELECT #1 549Note 1276 Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1 550Note 1003 /* select#1 */ select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where <expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`a`>(<in_optimizer>(`test`.`alias1`.`b`,<exists>(/* select#2 */ select `test`.`t2`.`c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <= `test`.`alias2`.`a` and <cache>(`test`.`alias1`.`b`) = `test`.`t2`.`c`))) or `test`.`alias1`.`a` = 'foo' 551drop table t1,t2; 552set optimizer_switch=default; 553set optimizer_switch='exists_to_in=on'; 554# 555# MDEV-245 Exists2In: Wrong result (extra rows) with 556# exists_to_in=ON, materialization=OFF, NOT EXISTS subquery 557# 558SET optimizer_switch='materialization=off,exists_to_in=on'; 559CREATE TABLE t1 ( a INT ) ; 560INSERT INTO t1 VALUES (0),(8),(1); 561CREATE TABLE t2 ( b INT ) ; 562INSERT INTO t2 VALUES (1),(2),(3); 563SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a ); 564a 5650 5668 567explain extended 568SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a ); 569id select_type table type possible_keys key key_len ref rows filtered Extra 5701 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5712 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where 572Warnings: 573Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 574Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !(`test`.`t1`.`a` is not null and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where `test`.`t2`.`b` is not null and <cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`)))) 575drop table t1,t2; 576set optimizer_switch=default; 577set optimizer_switch='exists_to_in=on'; 578# 579# MDEV-243 Wrong result (extra or missing rows) with 580# exists_to_in + materialization, EXISTS subquery 581# 582SET optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=on'; 583CREATE TABLE t1 ( a VARCHAR(1), b VARCHAR(1) ); 584INSERT INTO t1 VALUES ('v','v'),('s','v'); 585SELECT * FROM t1 AS alias 586WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 587a b 588s v 589explain extended 590SELECT * FROM t1 AS alias 591WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 592id select_type table type possible_keys key key_len ref rows filtered Extra 5931 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 5941 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(alias); Using join buffer (flat, BNL join) 595Warnings: 596Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 597Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 598Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`alias`.`b` and `test`.`alias`.`b` > `test`.`alias`.`a` 599SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on'); 600SET optimizer_switch = 'exists_to_in=on,semijoin=off'; 601SELECT * FROM t1 AS alias 602WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 603a b 604s v 605explain extended 606SELECT * FROM t1 AS alias 607WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 608id select_type table type possible_keys key key_len ref rows filtered Extra 6091 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 6102 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 611Warnings: 612Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 613Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 614Note 1003 /* select#1 */ select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` where <in_optimizer>(`test`.`alias`.`b`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` > `test`.`alias`.`a` and <cache>(`test`.`alias`.`b`) = `test`.`t1`.`a`)) 615SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=on'; 616SELECT * FROM t1 AS alias 617WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 618a b 619s v 620explain extended 621SELECT * FROM t1 AS alias 622WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); 623id select_type table type possible_keys key key_len ref rows filtered Extra 6241 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 6251 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary 626Warnings: 627Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 628Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 629Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`alias`.`b` and `test`.`alias`.`b` > `test`.`alias`.`a` 630drop table t1; 631set optimizer_switch=default; 632set optimizer_switch='exists_to_in=on'; 633# 634# MDEV-403 Wrong result (missing rows) with subquery in 635# EXISTS and an OR condition outside 636# 637CREATE TABLE t1 (a INT); 638INSERT INTO t1 VALUES (2),(3); 639CREATE TABLE t2 (b INT); 640INSERT INTO t2 VALUES (1),(3); 641SET optimizer_switch = 'exists_to_in=off,in_to_exists=on'; 642SELECT * FROM t1 AS alias1, t2 AS alias2 643WHERE EXISTS ( 644SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b 645) OR a = 5; 646a b 6473 1 648SET optimizer_switch = 'exists_to_in=on,in_to_exists=on'; 649SELECT * FROM t1 AS alias1, t2 AS alias2 650WHERE EXISTS ( 651SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b 652) OR a = 5; 653a b 6543 1 655explain extended 656SELECT * FROM t1 AS alias1, t2 AS alias2 657WHERE EXISTS ( 658SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b 659) OR a = 5; 660id select_type table type possible_keys key key_len ref rows filtered Extra 6611 PRIMARY alias1 ALL NULL NULL NULL NULL 2 100.00 6621 PRIMARY alias2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 6632 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 664Warnings: 665Note 1276 Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1 666Note 1276 Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1 667Note 1003 /* select#1 */ select `test`.`alias1`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where <expr_cache><`test`.`alias1`.`a`,`test`.`alias2`.`b`>(<in_optimizer>(`test`.`alias1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where `test`.`t2`.`b` > `test`.`alias2`.`b` and <cache>(`test`.`alias1`.`a`) = `test`.`t2`.`b`))) or `test`.`alias1`.`a` = 5 668drop table t1, t2; 669set optimizer_switch=default; 670set optimizer_switch='exists_to_in=on'; 671# 672# MDEV-404: Wrong result (extra rows) with STRAIGHT_JOIN, 673# EXISTS subquery, NOT NULL column 674# (same as above) 675# 676SET optimizer_switch = 'exists_to_in=on,in_to_exists=on'; 677CREATE TABLE t1 (a INT, b VARCHAR(1) NOT NULL); 678INSERT INTO t1 VALUES (1,'s'),(2,'e'); 679SELECT STRAIGHT_JOIN * FROM t1 AS alias1, t1 AS alias2 680WHERE EXISTS ( SELECT 1 FROM t1 WHERE b < alias2.b AND a = alias1.a ); 681a b a b 6822 e 1 s 683drop table t1; 684set optimizer_switch=default; 685set optimizer_switch='exists_to_in=on'; 686# 687# MDEV-3800: ORDER BY doesn't work with exists_to_in=ON on 688# a query with EXISTS subquery and OR condition 689# 690SET optimizer_switch = 'in_to_exists=on,exists_to_in=on'; 691CREATE TABLE t1 (a INT, b VARCHAR(1)) ENGINE=MyISAM; 692INSERT INTO t1 VALUES (4,'j'),(6,'v'),(3,'c'); 693CREATE TABLE t2 (c VARCHAR(1)) ENGINE=MyISAM; 694INSERT INTO t2 VALUES ('b'),('y'); 695SELECT a FROM t1 696WHERE EXISTS ( 697SELECT 1 FROM t2 WHERE c = b 698) OR b NOT IN ('U') 699ORDER BY a; 700a 7013 7024 7036 704select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` in (select `test`.`t2`.`c` from `test`.`t2` where 1 ) or (`test`.`t1`.`b` <> 'U') order by `test`.`t1`.`a`; 705a 7063 7074 7086 709drop table t1,t2; 710set optimizer_switch=default; 711set optimizer_switch='exists_to_in=on'; 712# 713# correct calculation of reserved items (postreview-fix) 714# 715create table t1 (col1 int, col2 int, col3 int); 716insert into t1 values (1,2,3),(2,3,4),(4,5,6); 717create table t2 as select * from t1; 718explain extended 719select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); 720id select_type table type possible_keys key key_len ref rows filtered Extra 7211 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 7221 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 7232 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 724Warnings: 725Note 1276 Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1 726Note 1276 Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1 727Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2`,`test`.`t1`.`col3` AS `col3` from `test`.`t1` semi join (`test`.`t2`) where 1 728select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); 729col1 col2 col3 7301 2 3 7312 3 4 7324 5 6 733drop table t1,t2; 734# 735# MDEV-3879: Exists2In: Wrong result (extra row) and unexpected 736# warning with exists_to_in=on and a NOT EXISTS subquery 737# 738SET optimizer_switch = 'exists_to_in=on'; 739CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; 740INSERT INTO t1 VALUES (3,'y'),(6,'w'); 741CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM; 742INSERT INTO t2 VALUES (3,'y'),(6,'d'); 743SELECT * FROM t1 744WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 ); 745a1 b1 7466 w 747drop table t1, t2; 748# 749# MDEV-3880: Wrong result (missing rows) with exists_to_in=on, 750# LEFT JOIN and NOT EXISTS subquery. 751# (Duplicate of above MDEV-3879). 752# 753SET optimizer_switch = 'exists_to_in=on'; 754CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; 755INSERT INTO t1 VALUES (4,'b'),(5,'y'); 756CREATE TABLE t2 (b2 CHAR(1)) ENGINE=MyISAM; 757INSERT INTO t2 VALUES ('z'),('b'); 758CREATE TABLE t3 (a3 INT, b3 CHAR(1)) ENGINE=MyISAM; 759INSERT INTO t3 VALUES (4,'j'),(6,'v'); 760SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 ) 761WHERE NOT EXISTS ( SELECT * FROM t3 WHERE b3 = b2 AND a3 = a1 ) ; 762a1 b1 b2 7634 b b 7645 y NULL 765drop table t1, t2, t3; 766# 767# MDEV-3881: Endless loop and crash in Item_ref::real_item with 768# exists_to_in=on, NOT EXISTS subquery, merge view or from subquery, 769# constant table 770# 771SET optimizer_switch = 'exists_to_in=on'; 772CREATE TABLE t1 (a INT) ENGINE=MyISAM; 773INSERT INTO t1 VALUES (1),(7); 774CREATE TABLE t2 (b INT) ENGINE=MyISAM; 775INSERT INTO t2 VALUES (8); 776CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; 777CREATE TABLE t3 (c INT) ENGINE=MyISAM; 778INSERT INTO t3 VALUES (4),(6); 779SELECT * FROM t1, v1 WHERE NOT EXISTS ( SELECT * FROM t3 WHERE c = b ) AND a = b; 780a b 781drop view v1; 782drop table t1, t2, t3; 783CREATE TABLE t1 (a INT) ENGINE=MyISAM; 784INSERT INTO t1 VALUES (1),(7); 785CREATE TABLE t2 (b INT) ENGINE=MyISAM; 786INSERT INTO t2 VALUES (8); 787CREATE TABLE t3 (c INT) ENGINE=MyISAM; 788INSERT INTO t3 VALUES (4),(6); 789SELECT * FROM t1, ( SELECT * FROM t2 ) alias WHERE NOT EXISTS ( SELECT * FROM t3 WHERE c = b ) AND a = b; 790a b 791drop table t1, t2, t3; 792# 793# MDEV-3906: Server crashes in Dependency_marker::visit_field 794# on 2nd execution of PS with exists_to_in and NOT EXISTS subquery 795# 796SET optimizer_switch='exists_to_in=on'; 797CREATE TABLE t1 (a INT) ENGINE=MyISAM; 798INSERT INTO t1 VALUES (1),(7); 799PREPARE stmt FROM ' 800SELECT * FROM t1 AS alias 801WHERE NOT EXISTS ( SELECT * FROM t1 WHERE t1.a = alias.a ) 802'; 803EXECUTE stmt; 804a 805EXECUTE stmt; 806a 807drop table t1; 808# 809# MDEV-3904: Assertion `in_subs->has_strategy()' failed in 810# JOIN::choose_subquery_plan on 2nd execution of PS with 811# exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery 812# 813SET optimizer_switch='in_to_exists=on,semijoin=on,exists_to_in=on'; 814CREATE TABLE t1 (a INT) ENGINE=MyISAM; 815INSERT INTO t1 VALUES (1),(2); 816CREATE TABLE t2 (b INT) ENGINE=MyISAM; 817INSERT INTO t2 VALUES (4),(6); 818CREATE ALGORITHM=MERGE VIEW v AS 819SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ); 820PREPARE stmt FROM ' SELECT * FROM v '; 821EXECUTE stmt; 822a 823EXECUTE stmt; 824a 825drop view v; 826drop table t1,t2; 827set optimizer_switch=default; 828set optimizer_switch='exists_to_in=on'; 829# 830# MDEV-3903: Server crashes in Item_cond::fix_fields on 2nd execution 831# of a prepared stmt with exists_to_in+materialization+semijoin, 832# EXISTS subquery, STRAIGHT_JOIN 833# 834SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on'; 835CREATE TABLE t1 (a INT) ENGINE=MyISAM; 836INSERT INTO t1 VALUES (1),(2); 837CREATE TABLE t2 (b INT) ENGINE=MyISAM; 838INSERT INTO t2 VALUES (3),(4); 839PREPARE stmt FROM 840'SELECT STRAIGHT_JOIN * FROM t1 841WHERE EXISTS ( SELECT * FROM t2 WHERE b = a )'; 842EXECUTE stmt; 843a 844EXECUTE stmt; 845a 846drop table t1,t2; 847set optimizer_switch=default; 848set optimizer_switch='exists_to_in=on'; 849# 850# MDEV-4152: Wrong result (missing rows) with exists_to_in=on, 851# inner joins 852# 853SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on'; 854CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM; 855INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d'); 856CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM; 857INSERT INTO t2 VALUES ('m'), ('b'); 858CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM; 859INSERT INTO t3 VALUES ('b'),('c'); 860SELECT * FROM t1, t2 outer_t2 861WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field ); 862i c1 c2 t1_field t2_field 8631 test1 test2 f b 8642 test3 test4 d b 865drop table t1,t2,t3; 866set optimizer_switch=default; 867set optimizer_switch='exists_to_in=on'; 868# 869#MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with 870#exists_to_in=on, MERGE view or a SELECT SQ 871# 872CREATE TABLE t1 (a INT) ENGINE=MyISAM; 873INSERT INTO t1 VALUES (1),(2); 874CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 875CREATE TABLE t2 (b INT) ENGINE=MyISAM; 876INSERT INTO t2 VALUES (2),(3); 877SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a ); 878a 8792 880PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )"; 881EXECUTE stmt; 882a 8832 884EXECUTE stmt; 885a 8862 887deallocate prepare stmt; 888drop view v1; 889drop table t1,t2; 890# 891#MDEV-10053: EXIST to IN transformation turned down 892# 893CREATE TABLE t1 ( 894pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) 895ENGINE=MyISAM; 896INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); 897set @optimizer_switch_save=@@optimizer_switch; 898set optimizer_switch='exists_to_in=off'; 899explain extended SELECT STRAIGHT_JOIN sq1.f2 900FROM ( SELECT * FROM t1 ) AS sq1 901WHERE EXISTS ( SELECT * FROM t1 AS sq2 902WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 903id select_type table type possible_keys key key_len ref rows filtered Extra 9041 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 9053 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 9063 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 9074 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 908Warnings: 909Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 910Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 911Note 1003 /* select#1 */ select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(exists(/* select#3 */ select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`sq2`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`pk` = `test`.`t1`.`f1` limit 1)) 912SELECT STRAIGHT_JOIN sq1.f2 913FROM ( SELECT * FROM t1 ) AS sq1 914WHERE EXISTS ( SELECT * FROM t1 AS sq2 915WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 916f2 917foo 918set optimizer_switch='exists_to_in=on'; 919explain extended SELECT STRAIGHT_JOIN sq1.f2 920FROM ( SELECT * FROM t1 ) AS sq1 921WHERE EXISTS ( SELECT * FROM t1 AS sq2 922WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 923id select_type table type possible_keys key key_len ref rows filtered Extra 9241 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 9253 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 9263 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 9274 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 928Warnings: 929Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 930Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 931Note 1003 /* select#1 */ select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(/* select#3 */ select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`t1`.`pk` = `test`.`t1`.`f1` and <cache>(`test`.`t1`.`f1`) = `test`.`sq2`.`f1`))) 932SELECT STRAIGHT_JOIN sq1.f2 933FROM ( SELECT * FROM t1 ) AS sq1 934WHERE EXISTS ( SELECT * FROM t1 AS sq2 935WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); 936f2 937foo 938set optimizer_switch= @optimizer_switch_save; 939DROP TABLE t1; 940# 941# MDEV-14164: Unknown column error when adding aggregate to function 942# in oracle style procedure FOR loop 943# 944CREATE TABLE t1(id INT, val INT); 945CREATE PROCEDURE p1() 946BEGIN 947DECLARE cur1 CURSOR FOR SELECT * FROM ( 948SELECT DISTINCT id FROM t1) a 949WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b 950WHERE a.id=b.id); 951OPEN cur1; 952CLOSE cur1; 953OPEN cur1; 954CLOSE cur1; 955END; 956// 957CALL p1(); 958DROP PROCEDURE p1; 959DROP TABLE t1; 960CREATE TABLE t1(id INT, val INT); 961CREATE PROCEDURE p1() 962BEGIN 963SELECT * FROM (SELECT DISTINCT id FROM t1) a 964WHERE NOT a.id IN (SELECT b.id FROM t1 b); 965SELECT * FROM (SELECT DISTINCT id FROM t1) a 966WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id); 967END; 968// 969CALL p1(); 970id 971id 972CALL p1(); 973id 974id 975DROP PROCEDURE p1; 976DROP TABLE t1; 977# End of 10.0 tests 978# 979# MDEV-23221: A subquery causes crash 980# 981create table t1 ( 982location_code varchar(10), 983country_id varchar(10) 984); 985insert into t1 values ('HKG', 'HK'); 986insert into t1 values ('NYC', 'US'); 987insert into t1 values ('LAX', 'US'); 988create table t2 ( 989container_id varchar(10), 990cntr_activity_type varchar(10), 991cntr_dest varchar(10) 992); 993insert into t2 values ('AAAA1111', 'VSL', 'NYC'); 994insert into t2 values ('AAAA1111', 'CUV', 'NYC'); 995insert into t2 values ('BBBB2222', 'VSL', 'LAX'); 996insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); 997# Must not crash or return an error: 998select 999(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, 1000(select 1001max(container_id) 1002from t2 as cl2 1003where 1004cl2.container_id = cl1.container_id and 1005cl2.cntr_activity_type = 'CUV' and 1006exists (select location_code 1007from t1 1008where 1009location_code = cl2.cntr_dest and 1010country_id = dest_cntry) 1011) as CUV 1012from 1013t2 cl1; 1014dest_cntry CUV 1015US AAAA1111 1016US AAAA1111 1017US NULL 1018US NULL 1019prepare s from "select 1020(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, 1021(select 1022max(container_id) 1023from t2 as cl2 1024where 1025cl2.container_id = cl1.container_id and 1026cl2.cntr_activity_type = 'CUV' and 1027exists (select location_code 1028from t1 1029where 1030location_code = cl2.cntr_dest and 1031country_id = dest_cntry) 1032) as CUV 1033from 1034t2 cl1"; 1035execute s; 1036dest_cntry CUV 1037US AAAA1111 1038US AAAA1111 1039US NULL 1040US NULL 1041execute s; 1042dest_cntry CUV 1043US AAAA1111 1044US AAAA1111 1045US NULL 1046US NULL 1047drop table t1,t2; 1048# 1049# MDEV-20557: SQL query with duplicate table aliases consistently crashes server 1050# (Just a testcase) 1051# 1052create table t1 (id int, id2 int); 1053create table t2 (id int, id2 int, a int); 1054create table t3 (id int); 1055create table t4 (id int); 1056select (select 1 from t1 where (exists 1057(select 1 from t2 1058where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt 1059from t3; 1060ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' 1061drop table t1,t2,t3,t4; 1062# 1063# MDEV-21649: Crash when using nested EXISTS 1064# (Just a testcase) 1065# 1066CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)); 1067CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id)); 1068CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id)); 1069SELECT 1070W0.`id` 1071FROM 1072`t1` W0 1073WHERE ( 1074EXISTS( 1075SELECT 1076V0.`id` 1077 FROM 1078`t2` V0 1079WHERE ( 1080EXISTS( 1081SELECT 1082U0.`id` 1083 FROM 1084`t2` U0 1085INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`) 1086WHERE ( 1087U0.`ip_id` = V0.`ip_id` 1088 AND U4.`storage_method_id` = ( 1089SELECT 1090U5.`storage_method_id` 1091 FROM 1092`t3` U5 1093WHERE 1094U5.`storage_target_id` = V0.`id` 1095 LIMIT 10961 1097) 1098) 1099) 1100) 1101) 1102); 1103id 1104drop table t1,t2,t3; 1105# 1106# MDEV-25407: EXISTS subquery with correlation in ON expression crashes 1107# 1108create table t10(a int primary key); 1109insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1110create table t11(a int primary key); 1111insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; 1112create table t1 (a int, b int); 1113insert into t1 select a,a from t10; 1114create table t2 (a int, b int); 1115insert into t2 select a,a from t11; 1116create table t3 as select * from t2; 1117explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); 1118id select_type table type possible_keys key key_len ref rows Extra 11191 PRIMARY t1 ALL NULL NULL NULL NULL 10 11201 PRIMARY t2 ALL NULL NULL NULL NULL 1000 Using where; Start temporary; Using join buffer (flat, BNL join) 11211 PRIMARY t3 ALL NULL NULL NULL NULL 1000 Using where; End temporary; Using join buffer (incremental, BNL join) 1122drop table t1, t2, t3, t10, t11; 1123set optimizer_switch=default; 1124