EVOLUTION-MANAGER

Edit File: subselect_exists2in.result

drop table if exists t1,t2,t3; set optimizer_switch='exists_to_in=on'; # # LP BUG#884644 exists2in broke name resolution # CREATE TABLE t1 (f1 integer); SELECT * FROM t1 WHERE EXISTS (SELECT NO_SUCH_TABLE.NO_SUCH_FIELD FROM t1); ERROR 42S22: Unknown column 'NO_SUCH_TABLE.NO_SUCH_FIELD' in 'field list' drop table t1; # # LP BUG#884657 Wrong result with exists2in , correlated subquery # CREATE TABLE t1 ( a varchar(1)) ; INSERT INTO t1 VALUES ('c'),('b'); CREATE TABLE t2 ( b varchar(1)) ; INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r'); CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; INSERT INTO t3 VALUES (29,'c'); SELECT * FROM t1, t2 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a AND t3.b <> t2.b ); a b c v c v c x c i c e c p c s c j c z c a c q c y c r c v c r INSERT INTO t3 VALUES (2,'c'); alter table t1 add index aa (a); alter table t3 add index bb (b); -- EXIST to IN then semijoin (has priority over IN to EXISTS) set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); a c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index aa aa 4 NULL 2 100.00 Using index 1 PRIMARY t3 ALL bb NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` -- EXIST to IN then IN to EXISTS set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); a c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL bb NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 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`)) -- EXIST2IN then MATERIALIZATION set optimizer_switch='exists_to_in=on,in_to_exists=off,semijoin=off,materialization=on,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); a c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 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`))) -- NO EXIST2IN set optimizer_switch='exists_to_in=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); a c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 4 NULL 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL bb NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 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`) set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; drop table t1,t2,t3; # # From group_min_max.test # create table t1 ( a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' ); insert into t1 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); create index idx_t1_0 on t1 (a1); create index idx_t1_1 on t1 (a1,a2,b,c); create index idx_t1_2 on t1 (a1,a2,b); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date create table t2 ( a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' ); insert into t2 select * from t1; insert into t2 (a1, a2, b, c, d) values ('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), ('a','a','a',NULL,'xyz'), ('a','a','b',NULL,'xyz'), ('a','b','a',NULL,'xyz'), ('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), ('d','b','b',NULL,'xyz'), ('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), ('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), ('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), ('a','a','a',NULL,'xyz'), ('a','a','b',NULL,'xyz'), ('a','b','a',NULL,'xyz'), ('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), ('d','b','b',NULL,'xyz'), ('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), ('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); create index idx_t2_0 on t2 (a1); create index idx_t2_1 on t2 (a1,a2,b,c); create index idx_t2_2 on t2 (a1,a2,b); analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Table is already up to date create table t3 ( a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' ); insert into t3 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); insert into t3 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); insert into t3 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); insert into t3 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); create index idx_t3_0 on t3 (a1); create index idx_t3_1 on t3 (a1,a2,b,c); create index idx_t3_2 on t3 (a1,a2,b); analyze table t3; Table Op Msg_type Msg_text test.t3 analyze status Table is already up to date explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and t2.c > 'b1' ) group by a1,a2,b; a1 a2 b c min(c) max(c) a a a a111 a111 d111 a a b e112 e112 h112 a b a i121 i121 l121 a b b m122 m122 p122 b a a a211 a211 d211 b a b e212 e212 h212 b b a i221 i221 l221 b b b m222 m222 p222 c a a a311 a311 d311 c a b e312 e312 h312 c b a i321 i321 l321 c b b m322 m322 p322 d a a a411 a411 d411 d a b e412 e412 h412 d b a i421 i421 l421 d b b m422 m422 p422 explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and t2.c > 'b1' ) group by a1,a2,b; a1 a2 b c min(c) max(c) a a a a111 a111 d111 a a b e112 e112 h112 a b a i121 i121 l121 a b b m122 m122 p122 b a a a211 a211 d211 b a b e212 e212 h212 b b a i221 i221 l221 b b b m222 m222 p222 c a a a311 a311 d311 c a b e312 e312 h312 c b a i321 i321 l321 c b b m322 m322 o322 d a a a411 a411 d411 d a b e412 e412 h412 d b a i421 i421 l421 d b b m422 m422 o422 drop table t1, t2, t3; CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES (7),(0); CREATE TABLE t2 ( b INT ); INSERT INTO t2 VALUES (0),(8); SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ) OR a > 0; a 7 0 explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ) OR a > 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 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 drop tables t1,t2; CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES (1),(5); CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; INSERT INTO t2 VALUES (1); CREATE TABLE t3 ( c INT ); INSERT INTO t3 VALUES (4),(5); SET optimizer_switch='exists_to_in=on,subquery_cache=off,materialization=on,in_to_exists=off,semijoin=off'; explain extended SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 Note 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` SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 1 1 SET optimizer_switch='exists_to_in=on,subquery_cache=off'; explain extended SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 Note 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` SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 1 1 SET optimizer_switch='exists_to_in=off,subquery_cache=off'; explain extended SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 Note 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)) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 1 1 set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; drop table t1,t2,t3; # multi condition test CREATE TABLE t1 ( a varchar(1), a1 varchar(1)) ; INSERT INTO t1 VALUES ('c', 'c'), ('b', 'b'); CREATE TABLE t3 ( a int NOT NULL , b varchar(1), b1 varchar(1)) ; INSERT INTO t3 VALUES (29,'c','c'); INSERT INTO t3 VALUES (2,'c','c'); alter table t1 add index aa (a,a1); alter table t3 add index bb (b,b1); -- EXIST to IN then semijoin (has priority over IN to EXISTS) set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); a a1 c c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 index bb bb 8 NULL 2 100.00 Using where; Using index; LooseScan 1 PRIMARY t1 ref aa aa 8 test.t3.b,test.t3.b1 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 Note 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` -- EXIST to IN then IN to EXISTS set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); a a1 c c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 index_subquery bb bb 8 func,func 2 100.00 Using index; Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 Note 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`))) -- EXIST2IN then MATERIALIZATION set optimizer_switch='exists_to_in=on,in_to_exists=off,semijoin=off,materialization=on,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); a a1 c c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 2 MATERIALIZED t3 index NULL bb 8 NULL 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 Note 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`))) -- NO EXIST2IN set optimizer_switch='exists_to_in=off,subquery_cache=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); a a1 c c explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL aa 8 NULL 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ref bb bb 8 test.t1.a,test.t1.a1 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1 Note 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`) set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; drop table t1,t3; # # MDEV-159 Assertion about not marked for read failed in # String* Field_varstring::val_str(String*, String*) # SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); SET optimizer_switch='in_to_exists=on,exists_to_in=on'; CREATE TABLE t1 ( a VARCHAR(1) ); INSERT INTO t1 VALUES ('k'),('m'); CREATE TABLE t2 ( b INT, c VARCHAR(1), d VARCHAR(1) NOT NULL ); INSERT INTO t2 VALUES (4,'j','j'),(6,'v','v'); CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t2 WHERE b < 1; SELECT c FROM v WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= v.d AND b = v.b ); c explain extended SELECT c FROM v WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= v.d AND b = v.b ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'v.d' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'v.b' of SELECT #2 was resolved in SELECT #1 Note 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 set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; drop view v; drop table t1,t2; # # MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON # SET optimizer_switch = 'in_to_exists=on,subquery_cache=on,exists_to_in=on'; CREATE TABLE t1 ( a VARCHAR(3) NOT NULL, b VARCHAR(50) ); INSERT INTO t1 VALUES ('USA','Chinese'),('USA','English'), ('FRA','French'),('ITA','Italian'); CREATE TABLE t2 ( c VARCHAR(3) ); INSERT INTO t2 VALUES ('USA'),('FRA'); SELECT * FROM t1 AS alias1, t1 AS alias2 WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= alias2.a AND c = alias1.b ) OR alias1 .a = 'foo'; a b a b SELECT * FROM t1 AS alias1, t1 AS alias2 WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= alias2.a AND c = alias1.a ) OR alias1 .a = 'foo'; a b a b USA Chinese USA Chinese USA English USA Chinese FRA French USA Chinese USA Chinese USA English USA English USA English FRA French USA English USA Chinese FRA French USA English FRA French FRA French FRA French USA Chinese ITA Italian USA English ITA Italian FRA French ITA Italian drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON # SET optimizer_switch = 'in_to_exists=on,subquery_cache=on,exists_to_in=on'; CREATE TABLE t1 ( a VARCHAR(3) NOT NULL, b VARCHAR(50) ); INSERT INTO t1 VALUES ('USA','Chinese'),('USA','English'), ('FRA','French'),('ITA','Italian'); CREATE TABLE t2 ( c VARCHAR(3) ); INSERT INTO t2 VALUES ('USA'),('FRA'); SELECT * FROM t1 AS alias1, t1 AS alias2 WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= alias2.a AND c = alias1.b ) OR alias1 .a = 'foo'; a b a b explain extended SELECT * FROM t1 AS alias1, t1 AS alias2 WHERE EXISTS ( SELECT * FROM t1, t2 WHERE a <= alias2.a AND c = alias1.b ) OR alias1 .a = 'foo'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 4 100.00 1 PRIMARY alias2 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.alias2.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1 Note 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' drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-245 Exists2In: Wrong result (extra rows) with # exists_to_in=ON, materialization=OFF, NOT EXISTS subquery # SET optimizer_switch='materialization=off,exists_to_in=on'; CREATE TABLE t1 ( a INT ) ; INSERT INTO t1 VALUES (0),(8),(1); CREATE TABLE t2 ( b INT ) ; INSERT INTO t2 VALUES (1),(2),(3); SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a ); a 0 8 explain extended SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 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`)))) drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-243 Wrong result (extra or missing rows) with # exists_to_in + materialization, EXISTS subquery # SET 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'; CREATE TABLE t1 ( a VARCHAR(1), b VARCHAR(1) ); INSERT INTO t1 VALUES ('v','v'),('s','v'); SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); a b s v explain extended SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(alias); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 Note 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` SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=off'; SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); a b s v explain extended SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 Note 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`)) SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=on'; SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); a b s v explain extended SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary Warnings: Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 Note 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` drop table t1; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-403 Wrong result (missing rows) with subquery in # EXISTS and an OR condition outside # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (2),(3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(3); SET optimizer_switch = 'exists_to_in=off,in_to_exists=on'; SELECT * FROM t1 AS alias1, t2 AS alias2 WHERE EXISTS ( SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b ) OR a = 5; a b 3 1 SET optimizer_switch = 'exists_to_in=on,in_to_exists=on'; SELECT * FROM t1 AS alias1, t2 AS alias2 WHERE EXISTS ( SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b ) OR a = 5; a b 3 1 explain extended SELECT * FROM t1 AS alias1, t2 AS alias2 WHERE EXISTS ( SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b ) OR a = 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1 Note 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 drop table t1, t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-404: Wrong result (extra rows) with STRAIGHT_JOIN, # EXISTS subquery, NOT NULL column # (same as above) # SET optimizer_switch = 'exists_to_in=on,in_to_exists=on'; CREATE TABLE t1 (a INT, b VARCHAR(1) NOT NULL); INSERT INTO t1 VALUES (1,'s'),(2,'e'); SELECT STRAIGHT_JOIN * FROM t1 AS alias1, t1 AS alias2 WHERE EXISTS ( SELECT 1 FROM t1 WHERE b < alias2.b AND a = alias1.a ); a b a b 2 e 1 s drop table t1; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-3800: ORDER BY doesn't work with exists_to_in=ON on # a query with EXISTS subquery and OR condition # SET optimizer_switch = 'in_to_exists=on,exists_to_in=on'; CREATE TABLE t1 (a INT, b VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (4,'j'),(6,'v'),(3,'c'); CREATE TABLE t2 (c VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('b'),('y'); SELECT a FROM t1 WHERE EXISTS ( SELECT 1 FROM t2 WHERE c = b ) OR b NOT IN ('U') ORDER BY a; a 3 4 6 select `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`; a 3 4 6 drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # correct calculation of reserved items (postreview-fix) # create table t1 (col1 int, col2 int, col3 int); insert into t1 values (1,2,3),(2,3,4),(4,5,6); create table t2 as select * from t1; explain extended select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1 Note 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 select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); col1 col2 col3 1 2 3 2 3 4 4 5 6 drop table t1,t2; # # MDEV-3879: Exists2In: Wrong result (extra row) and unexpected # warning with exists_to_in=on and a NOT EXISTS subquery # SET optimizer_switch = 'exists_to_in=on'; CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (3,'y'),(6,'w'); CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES (3,'y'),(6,'d'); SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 ); a1 b1 6 w drop table t1, t2; # # MDEV-3880: Wrong result (missing rows) with exists_to_in=on, # LEFT JOIN and NOT EXISTS subquery. # (Duplicate of above MDEV-3879). # SET optimizer_switch = 'exists_to_in=on'; CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (4,'b'),(5,'y'); CREATE TABLE t2 (b2 CHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('z'),('b'); CREATE TABLE t3 (a3 INT, b3 CHAR(1)) ENGINE=MyISAM; INSERT INTO t3 VALUES (4,'j'),(6,'v'); SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 ) WHERE NOT EXISTS ( SELECT * FROM t3 WHERE b3 = b2 AND a3 = a1 ) ; a1 b1 b2 4 b b 5 y NULL drop table t1, t2, t3; # # MDEV-3881: Endless loop and crash in Item_ref::real_item with # exists_to_in=on, NOT EXISTS subquery, merge view or from subquery, # constant table # SET optimizer_switch = 'exists_to_in=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(7); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (8); CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; CREATE TABLE t3 (c INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (4),(6); SELECT * FROM t1, v1 WHERE NOT EXISTS ( SELECT * FROM t3 WHERE c = b ) AND a = b; a b drop view v1; drop table t1, t2, t3; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(7); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (8); CREATE TABLE t3 (c INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (4),(6); SELECT * FROM t1, ( SELECT * FROM t2 ) alias WHERE NOT EXISTS ( SELECT * FROM t3 WHERE c = b ) AND a = b; a b drop table t1, t2, t3; # # MDEV-3906: Server crashes in Dependency_marker::visit_field # on 2nd execution of PS with exists_to_in and NOT EXISTS subquery # SET optimizer_switch='exists_to_in=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(7); PREPARE stmt FROM ' SELECT * FROM t1 AS alias WHERE NOT EXISTS ( SELECT * FROM t1 WHERE t1.a = alias.a ) '; EXECUTE stmt; a EXECUTE stmt; a drop table t1; # # MDEV-3904: Assertion `in_subs->has_strategy()' failed in # JOIN::choose_subquery_plan on 2nd execution of PS with # exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery # SET optimizer_switch='in_to_exists=on,semijoin=on,exists_to_in=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (4),(6); CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ); PREPARE stmt FROM ' SELECT * FROM v '; EXECUTE stmt; a EXECUTE stmt; a drop view v; drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-3903: Server crashes in Item_cond::fix_fields on 2nd execution # of a prepared stmt with exists_to_in+materialization+semijoin, # EXISTS subquery, STRAIGHT_JOIN # SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (3),(4); PREPARE stmt FROM 'SELECT STRAIGHT_JOIN * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a )'; EXECUTE stmt; a EXECUTE stmt; a drop table t1,t2; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # # MDEV-4152: Wrong result (missing rows) with exists_to_in=on, # inner joins # SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on'; CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d'); CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('m'), ('b'); CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('b'),('c'); SELECT * FROM t1, t2 outer_t2 WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field ); i c1 c2 t1_field t2_field 1 test1 test2 f b 2 test3 test4 d b drop table t1,t2,t3; set optimizer_switch=default; set optimizer_switch='exists_to_in=on'; # #MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with #exists_to_in=on, MERGE view or a SELECT SQ # CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (2),(3); SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a ); a 2 PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )"; EXECUTE stmt; a 2 EXECUTE stmt; a 2 deallocate prepare stmt; drop view v1; drop table t1,t2; # #MDEV-10053: EXIST to IN transformation turned down # CREATE TABLE t1 ( pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); set @optimizer_switch_save=@@optimizer_switch; set optimizer_switch='exists_to_in=off'; explain extended SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1 WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 Note 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`)) SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1 WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo set optimizer_switch='exists_to_in=on'; explain extended SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1 WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 Note 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`))) SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1 WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo set optimizer_switch= @optimizer_switch_save; DROP TABLE t1; # # MDEV-14164: Unknown column error when adding aggregate to function # in oracle style procedure FOR loop # CREATE TABLE t1(id INT, val INT); CREATE PROCEDURE p1() BEGIN DECLARE cur1 CURSOR FOR SELECT * FROM ( SELECT DISTINCT id FROM t1) a WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b WHERE a.id=b.id); OPEN cur1; CLOSE cur1; OPEN cur1; CLOSE cur1; END; // CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; CREATE TABLE t1(id INT, val INT); CREATE PROCEDURE p1() BEGIN SELECT * FROM (SELECT DISTINCT id FROM t1) a WHERE NOT a.id IN (SELECT b.id FROM t1 b); SELECT * FROM (SELECT DISTINCT id FROM t1) a WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id); END; // CALL p1(); id id CALL p1(); id id DROP PROCEDURE p1; DROP TABLE t1; # End of 10.0 tests set optimizer_switch=default;