path: root/mysql-test/main/subselect_exists2in.test

--source include/default_optimizer_switch.inc

--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(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on');
SET optimizer_switch='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(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on');
SET optimizer_switch = 'exists_to_in=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

--echo #
--echo # MDEV-23221: A subquery causes crash
--echo #
create table t1 (
location_code varchar(10),
country_id varchar(10)
);
insert into t1 values ('HKG', 'HK');
insert into t1 values ('NYC', 'US');
insert into t1 values ('LAX', 'US');

create table t2 (
container_id varchar(10),
cntr_activity_type varchar(10),
cntr_dest varchar(10)
);
insert into t2 values ('AAAA1111', 'VSL', 'NYC');
insert into t2 values ('AAAA1111', 'CUV', 'NYC');
insert into t2 values ('BBBB2222', 'VSL', 'LAX');
insert into t2 values ('BBBB2222', 'XYZ', 'LAX');

let $query=
select
  (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
  (select
     max(container_id)
   from t2 as cl2
   where
    cl2.container_id = cl1.container_id and
    cl2.cntr_activity_type = 'CUV' and
    exists (select location_code
            from t1
            where
               location_code = cl2.cntr_dest and
               country_id = dest_cntry)
   ) as CUV
from
  t2 cl1;

--echo # Must not crash or return an error:
eval $query;

eval prepare s from "$query";
execute s;
execute s;

drop table t1,t2;

--echo # 
--echo # MDEV-20557: SQL query with duplicate table aliases consistently crashes server
--echo # (Just a testcase)
--echo #

create table t1 (id int, id2 int);
create table t2 (id int, id2 int, a int);
create table t3 (id int);
create table t4 (id int);

--error ER_NOT_SUPPORTED_YET
select (select 1 from t1 where (exists 
	(select 1 from t2  
	where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
from t3;
 
drop table t1,t2,t3,t4;


--echo # 
--echo # MDEV-21649: Crash when using nested EXISTS
--echo # (Just a testcase)
--echo # 
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
 
SELECT
    W0.`id`
FROM
    `t1` W0
WHERE (
    EXISTS(
        SELECT
            V0.`id`
        FROM
            `t2` V0
        WHERE (
            EXISTS(
                SELECT
                    U0.`id`
                FROM
                    `t2` U0
                    INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`)
                WHERE (
                        U0.`ip_id` = V0.`ip_id`
                        AND U4.`storage_method_id` = (
                            SELECT
                                U5.`storage_method_id`
                            FROM
                                `t3` U5
                            WHERE
                                U5.`storage_target_id` = V0.`id`
                            LIMIT
                                1
                        )
                )
            )
        )
    )
);

drop table t1,t2,t3;

#restore defaults
set optimizer_switch=default;