diff options
Diffstat (limited to 'mysql-test/main/subselect_exists2in.test')
| -rw-r--r-- | mysql-test/main/subselect_exists2in.test | 832 |
1 files changed, 832 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test new file mode 100644 index 00000000000..5a8ddb3612f --- /dev/null +++ b/mysql-test/main/subselect_exists2in.test @@ -0,0 +1,832 @@ + +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +set optimizer_switch='exists_to_in=on'; +--echo # +--echo # LP BUG#884644 exists2in broke name resolution +--echo # + +CREATE TABLE t1 (f1 integer); + +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 WHERE EXISTS (SELECT NO_SUCH_TABLE.NO_SUCH_FIELD FROM t1); + +drop table t1; + +--echo # +--echo # LP BUG#884657 Wrong result with exists2in , correlated subquery +--echo # + +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 +); + + +INSERT INTO t3 VALUES (2,'c'); +alter table t1 add index aa (a); +alter table t3 add index bb (b); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +drop table t1,t2,t3; + +--echo # +--echo # From group_min_max.test +--echo # +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; + +# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and +# one more nullable attribute + +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; +# add few rows with NULL's in the MIN/MAX column +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 t3 is the same as t1, but with smaller column lenghts. +# This allows to test different branches of the cost computation procedure +# when the number of keys per block are less than the number of keys in the +# sub-groups formed by predicates over non-group attributes. + +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; + + +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; + +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; + +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; + +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; + +drop table t1, t2, t3; + +# +# LP BUG#901835 - incorrect semi-join conversion after exists2in +# +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; + +explain extended +SELECT * FROM t1 WHERE + EXISTS ( SELECT * FROM t2 WHERE b = a ) + OR a > 0; + +drop tables t1,t2; + +# +# NOT EXISTS test +# +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; +SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +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; +SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +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; +SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; +drop table t1,t2,t3; + +--echo # 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); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); +--echo -- 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); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1); +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +drop table t1,t3; + +--echo # +--echo # MDEV-159 Assertion about not marked for read failed in +--echo # String* Field_varstring::val_str(String*, String*) +--echo # + +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 +); + +explain extended +SELECT c FROM v +WHERE EXISTS ( + SELECT * FROM t1, t2 + WHERE a <= v.d AND b = v.b +); + +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; +drop view v; +drop table t1,t2; + + +--echo # +--echo # MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON +--echo # + +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'; + +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'; + +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON +--echo # +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'; + +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'; + +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-245 Exists2In: Wrong result (extra rows) with +--echo # exists_to_in=ON, materialization=OFF, NOT EXISTS subquery +--echo # +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 ); +explain extended +SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a ); + +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-243 Wrong result (extra or missing rows) with +--echo # exists_to_in + materialization, EXISTS subquery +--echo # + +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 ); +explain extended +SELECT * FROM t1 AS alias +WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); + +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 ); +explain extended +SELECT * FROM t1 AS alias +WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); +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 ); +explain extended +SELECT * FROM t1 AS alias +WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); + +drop table t1; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-403 Wrong result (missing rows) with subquery in +--echo # EXISTS and an OR condition outside +--echo # +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; + +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; + +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; + +drop table t1, t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-404: Wrong result (extra rows) with STRAIGHT_JOIN, +--echo # EXISTS subquery, NOT NULL column +--echo # (same as above) +--echo # +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 ); + +drop table t1; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + + +--echo # +--echo # MDEV-3800: ORDER BY doesn't work with exists_to_in=ON on +--echo # a query with EXISTS subquery and OR condition +--echo # +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; + +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`; + +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + + +--echo # +--echo # correct calculation of reserved items (postreview-fix) +--echo # +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); +select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); +drop table t1,t2; + +--echo # +--echo # MDEV-3879: Exists2In: Wrong result (extra row) and unexpected +--echo # warning with exists_to_in=on and a NOT EXISTS subquery +--echo # +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 ); + +drop table t1, t2; + +--echo # +--echo # MDEV-3880: Wrong result (missing rows) with exists_to_in=on, +--echo # LEFT JOIN and NOT EXISTS subquery. +--echo # (Duplicate of above MDEV-3879). +--echo # + +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 ) ; + +drop table t1, t2, t3; + + + +--echo # +--echo # MDEV-3881: Endless loop and crash in Item_ref::real_item with +--echo # exists_to_in=on, NOT EXISTS subquery, merge view or from subquery, +--echo # constant table +--echo # +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; + +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; + +drop table t1, t2, t3; + +--echo # +--echo # MDEV-3906: Server crashes in Dependency_marker::visit_field +--echo # on 2nd execution of PS with exists_to_in and NOT EXISTS subquery +--echo # +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; +EXECUTE stmt; + +drop table t1; + +--echo # +--echo # MDEV-3904: Assertion `in_subs->has_strategy()' failed in +--echo # JOIN::choose_subquery_plan on 2nd execution of PS with +--echo # exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery +--echo # +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; +EXECUTE stmt; + +drop view v; +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-3903: Server crashes in Item_cond::fix_fields on 2nd execution +--echo # of a prepared stmt with exists_to_in+materialization+semijoin, +--echo # EXISTS subquery, STRAIGHT_JOIN +--echo # + +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; +EXECUTE stmt; + +drop table t1,t2; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo # MDEV-4152: Wrong result (missing rows) with exists_to_in=on, +--echo # inner joins +--echo # +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 ); + +drop table t1,t2,t3; +set optimizer_switch=default; +set optimizer_switch='exists_to_in=on'; + +--echo # +--echo #MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with +--echo #exists_to_in=on, MERGE view or a SELECT SQ +--echo # +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 ); + +PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )"; +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; + +drop view v1; +drop table t1,t2; + +--echo # +--echo #MDEV-10053: EXIST to IN transformation turned down +--echo # + +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); + +let $q= +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 ); + +set @optimizer_switch_save=@@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +eval explain extended $q; +eval $q; +set optimizer_switch='exists_to_in=on'; +eval explain extended $q; +eval $q; + +set optimizer_switch= @optimizer_switch_save; + +DROP TABLE t1; + +--echo # +--echo # MDEV-14164: Unknown column error when adding aggregate to function +--echo # in oracle style procedure FOR loop +--echo # + +CREATE TABLE t1(id INT, val INT); +DELIMITER //; +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; +// +DELIMITER ;// +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +CREATE TABLE t1(id INT, val INT); +DELIMITER //; +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; +// +DELIMITER ;// +CALL p1(); +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # End of 10.0 tests + +#restore defaults +set optimizer_switch=default; |