diff options
Diffstat (limited to 'mysql-test/main/subselect_sj.test')
| -rw-r--r-- | mysql-test/main/subselect_sj.test | 3040 |
1 files changed, 3040 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test new file mode 100644 index 00000000..e4d02ed6 --- /dev/null +++ b/mysql-test/main/subselect_sj.test @@ -0,0 +1,3040 @@ +--source include/no_valgrind_without_big.inc +# +# Nested Loops semi-join subquery evaluation tests +# +--disable_warnings +drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12; +drop view if exists v1, v2, v3, v4; +drop procedure if exists p1; +--enable_warnings + +set @subselect_sj_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); +if (`select @join_cache_level_for_subselect_sj_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_sj_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_sj_test; +} + +# The 'default' value within the scope of this test: +set @local_optimizer_switch=@@optimizer_switch; + +# +# 1. Subqueries that are converted into semi-joins +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int, b int); +insert into t1 values (0,0),(1,1),(2,2); +create table t2 as select * from t1; + +create table t11(a int, b int); + +create table t10 (pk int, a int, primary key(pk)); +insert into t10 select a,a from t0; +create table t12 like t10; +insert into t12 select * from t10; + + +--echo Flattened because of dependency, t10=func(t1) +explain select * from t1 where a in (select pk from t10); +select * from t1 where a in (select pk from t10); + +--echo A confluent case of dependency +explain select * from t1 where a in (select a from t10 where pk=12); +select * from t1 where a in (select a from t10 where pk=12); + +explain select * from t1 where a in (select a from t10 where pk=9); +select * from t1 where a in (select a from t10 where pk=9); + +--echo An empty table inside +explain select * from t1 where a in (select a from t11); +select * from t1 where a in (select a from t11); + +explain select * from t1 where a in (select pk from t10) and b in (select pk from t10); +select * from t1 where a in (select pk from t10) and b in (select pk from t10); + +--echo flattening a nested subquery +explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); +select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); + +--echo flattening subquery w/ several tables +explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a); + +--echo subqueries within outer joins go into ON expr. +# TODO: psergey: check if case conversions like those are ok (it broke on windows) +--replace_result a A b B +explain extended +select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10)); + +# TODO: psergey: check if case conversions like those are ok (it broke on windows) +--echo t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)" +--replace_result a A b B +explain extended +select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)); + +set @save_join_buffer_size=@@join_buffer_size; +set join_buffer_size=8*1024; + +--echo we shouldn't flatten if we're going to get a join of > MAX_TABLES. +explain select * from + t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, + t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19, + t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29, + t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39, + t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49 +where + s00.a in ( + select m00.a from + t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09, + t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19 + ); + +select * from + t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) +where t1.a < 5; + +set join_buffer_size=@save_join_buffer_size; + +# +# Prepared statements +# +prepare s1 from + ' select * from + t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) + where t1.a < 5'; +execute s1; +execute s1; + +# Try I2O orders +insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B; +explain extended select * from t1 where a in (select pk from t10 where pk<3); + +drop table t0, t1, t2; +drop table t10, t11, t12; + +--echo +--echo Bug#37899: Wrongly checked optimization prerequisite caused failed +--echo assertion. +--echo +CREATE TABLE t1 ( + `pk` int(11), + `varchar_nokey` varchar(5) +); + +INSERT INTO t1 VALUES +(1,'qk'),(2,'j'),(3,'aew'); + +SELECT * +FROM t1 +WHERE varchar_nokey IN ( + SELECT + varchar_nokey + FROM + t1 +) XOR pk = 30; +drop table t1; + +--echo # +--echo # BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + int_nokey int(11) NOT NULL, + time_key time NOT NULL, + datetime_key datetime NOT NULL, + datetime_nokey datetime NOT NULL, + varchar_key varchar(1) NOT NULL, + varchar_nokey varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY time_key (time_key), + KEY datetime_key (datetime_key), + KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,0, '00:16:10','2008-09-03 14:25:40','2008-09-03 14:25:40','h','h'), +(2,7, '00:00:00','2001-01-13 00:00:00','2001-01-13 00:00:00','',''), +(3,0, '00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(4,2, '16:29:24','2000-10-16 01:39:08','2000-10-16 01:39:08','w','w'), +(5,1, '09:23:32','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'), +(6,3, '00:00:00','2007-12-02 00:00:00','2007-12-02 00:00:00','o','o'), +(7,3, '00:00:00','2008-09-11 00:00:00','2008-09-11 00:00:00','',''), +(8,0, '13:59:04','0000-00-00 00:00:00','0000-00-00 00:00:00','s','s'), +(9,7, '09:01:06','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'), +(10,5,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'), +(11,0,'21:06:46','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(12,2,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''), +(13,6,'14:45:34','2003-07-28 02:34:08','2003-07-28 02:34:08','w','w'), +(14,1,'15:04:12','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(15,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(16,0,'15:55:23','2004-03-17 00:32:27','2004-03-17 00:32:27','p','p'), +(17,1,'16:30:00','2004-12-27 19:20:00','2004-12-27 19:20:00','d','d'), +(18,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), +(19,0,'14:13:26','2008-11-09 05:53:48','2008-11-09 05:53:48','o','o'), +(20,0,'00:00:00','2009-10-11 06:58:04','2009-10-11 06:58:04','k','k'); + +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + int_nokey int(11) NOT NULL, + time_key time NOT NULL, + datetime_key datetime NOT NULL, + datetime_nokey datetime NOT NULL, + varchar_key varchar(1) NOT NULL, + varchar_nokey varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY time_key (time_key), + KEY datetime_key (datetime_key), + KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(10,0,'19:39:13','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'), +(11,8,'03:43:53','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'); +SELECT OUTR.datetime_nokey AS X FROM t1 AS OUTR +WHERE + OUTR.varchar_nokey IN (SELECT + INNR . varchar_nokey AS Y + FROM t2 AS INNR + WHERE + INNR . datetime_key >= INNR . time_key OR + INNR . pk = INNR . int_nokey + ) + AND OUTR . varchar_nokey <= 'w' +HAVING X > '2012-12-12'; +drop table t1, t2; + +--echo # +--echo # Bug#45191: Incorrectly initialized semi-join led to a wrong result. +--echo # +CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL, + EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); + +CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL, + PNAME CHAR(20), PTYPE CHAR(6), + BUDGET DECIMAL(9), + CITY CHAR(15)); + +CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, + PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5)); +INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); +INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); +INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); +INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); +INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); + +INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); +INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); +INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); +INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); +INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); +INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); + +INSERT INTO WORKS VALUES ('E1','P1',40); +INSERT INTO WORKS VALUES ('E1','P2',20); +INSERT INTO WORKS VALUES ('E1','P3',80); +INSERT INTO WORKS VALUES ('E1','P4',20); +INSERT INTO WORKS VALUES ('E1','P5',12); +INSERT INTO WORKS VALUES ('E1','P6',12); +INSERT INTO WORKS VALUES ('E2','P1',40); +INSERT INTO WORKS VALUES ('E2','P2',80); +INSERT INTO WORKS VALUES ('E3','P2',20); +INSERT INTO WORKS VALUES ('E4','P2',20); +INSERT INTO WORKS VALUES ('E4','P4',40); +INSERT INTO WORKS VALUES ('E4','P5',80); + +set optimizer_switch=@local_optimizer_switch; +set optimizer_switch='materialization=off'; + +explain SELECT EMPNUM, EMPNAME +FROM STAFF +WHERE EMPNUM IN + (SELECT EMPNUM FROM WORKS + WHERE PNUM IN + (SELECT PNUM FROM PROJ)); + +SELECT EMPNUM, EMPNAME +FROM STAFF +WHERE EMPNUM IN + (SELECT EMPNUM FROM WORKS + WHERE PNUM IN + (SELECT PNUM FROM PROJ)); + +set optimizer_switch=@local_optimizer_switch; + +drop table STAFF,WORKS,PROJ; + +--echo # End of bug#45191 + +--echo # +--echo # Bug#46550 Azalea returning duplicate results for some IN subqueries +--echo # w/ semijoin=on +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t0, t1, t2; +--enable_warnings + +CREATE TABLE t0 ( + int_key int(11) DEFAULT NULL, + varchar_key varchar(1) DEFAULT NULL, + varchar_nokey varchar(1) DEFAULT NULL, + KEY int_key (int_key), + KEY varchar_key (varchar_key,int_key) +); + +INSERT INTO t0 VALUES +(1,'m','m'), +(40,'h','h'), +(1,'r','r'), +(1,'h','h'), +(9,'x','x'), +(NULL,'q','q'), +(NULL,'k','k'), +(7,'l','l'), +(182,'k','k'), +(202,'a','a'), +(7,'x','x'), +(6,'j','j'), +(119,'z','z'), +(4,'d','d'), +(5,'h','h'), +(1,'u','u'), +(3,'q','q'), +(7,'a','a'), +(3,'e','e'), +(6,'l','l'); + +CREATE TABLE t1 ( + int_key int(11) DEFAULT NULL, + varchar_key varchar(1) DEFAULT NULL, + varchar_nokey varchar(1) DEFAULT NULL, + KEY int_key (int_key), + KEY varchar_key (varchar_key,int_key) +); +INSERT INTO t1 VALUES (7,NULL,NULL),(4,'x','x'); + +CREATE TABLE t2 ( + int_key int(11) DEFAULT NULL, + varchar_key varchar(1) DEFAULT NULL, + varchar_nokey varchar(1) DEFAULT NULL, + KEY int_key (int_key), + KEY varchar_key (varchar_key,int_key) +); +INSERT INTO t2 VALUES (123,NULL,NULL); + +SELECT int_key +FROM t0 +WHERE varchar_nokey IN ( + SELECT t1 .varchar_key from t1 +); +--disable_parsing # wrong duplicate results - LP BUG#702374 +SELECT t0.int_key +FROM t0 +WHERE t0.varchar_nokey IN ( + SELECT t1_1 .varchar_key + FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key +); + +SELECT t0.int_key +FROM t0, t2 +WHERE t0.varchar_nokey IN ( + SELECT t1_1 .varchar_key + FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key +); +--enable_parsing +DROP TABLE t0, t1, t2; + +--echo # End of bug#46550 + +--echo # +--echo # Bug #46744 Crash in optimize_semijoin_nests on empty view +--echo # with limit and procedure. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1 ( f1 int ); +CREATE TABLE t2 ( f1 int ); + +insert into t2 values (5), (7); + +CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2; + +create procedure p1() +select COUNT(*) +FROM v1 WHERE f1 IN +(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1)); + +SET SESSION optimizer_switch = 'semijoin=on'; +CALL p1(); +SET SESSION optimizer_switch = 'semijoin=off'; +CALL p1(); + +drop table t1, t2; +drop view v1; +drop procedure p1; + +set SESSION optimizer_switch=@local_optimizer_switch; + +--echo # End of bug#46744 + +--echo +--echo Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order +--echo with semijoin=on" +--echo +CREATE TABLE t1 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +); + +CREATE TABLE t2 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES + (NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'), + ('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'), + ('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'), + ('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'), + ('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'), + ('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'), + ('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'), + ('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z'); + +CREATE TABLE t3 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES + (NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'), + ('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y'); + +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3 + WHERE (varchar_key,varchar_key) + IN (SELECT t1.varchar_key, t2 .varchar_key + FROM t1 RIGHT JOIN t2 ON t1.varchar_key + ) + ); + +DROP TABLE t1, t2, t3; + + +--echo # +--echo # Bug#46556 Returning incorrect, empty results for some IN subqueries +--echo # w/semijoin=on +--echo # + +CREATE TABLE t0 ( + pk INTEGER, + vkey VARCHAR(1), + vnokey VARCHAR(1), + PRIMARY KEY (pk), + KEY vkey(vkey) +); + +INSERT INTO t0 +VALUES (1,'g','g'), (2,'v','v'), (3,'t','t'), (4,'u','u'), (5,'n','n'); + +EXPLAIN EXTENDED SELECT vkey FROM t0 WHERE pk IN + (SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey); + +SELECT vkey FROM t0 WHERE pk IN + (SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey); + +DROP TABLE t0; + +--echo # End of bug#46556 + +--echo +--echo Bug #48073 Subquery on char columns from view crashes Mysql +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE TABLE t1 ( + city VARCHAR(50) NOT NULL, + country_id SMALLINT UNSIGNED NOT NULL +); + +INSERT INTO t1 VALUES +('Batna',2), +('Bchar',2), +('Skikda',2), +('Tafuna',3), +('Algeria',2) ; + +CREATE TABLE t2 ( + country_id SMALLINT UNSIGNED NOT NULL, + country VARCHAR(50) NOT NULL +); + +INSERT INTO t2 VALUES +(2,'Algeria'), +(3,'American Samoa') ; + +CREATE VIEW v1 AS +SELECT country_id, country +FROM t2 +WHERE LEFT(country,1) = "A" +; + +SELECT city, country_id +FROM t1 +WHERE city IN ( + SELECT country + FROM t2 + WHERE LEFT(country, 1) = "A" +); + +SELECT city, country_id +FROM t1 +WHERE city IN ( + SELECT country + FROM v1 +); + +drop table t1, t2; +drop view v1; + +--echo # End of bug#48073 + +--echo +--echo Bug#48834: Procedure with view + subquery + semijoin=on +--echo crashes on second call. +--echo + +SET SESSION optimizer_switch ='semijoin=on'; + +CREATE TABLE t1 ( t1field integer, primary key (t1field)); +CREATE TABLE t2 ( t2field integer, primary key (t2field)); + +CREATE VIEW v1 AS + SELECT t1field as v1field + FROM t1 A + WHERE A.t1field IN (SELECT t1field FROM t2 ); + +CREATE VIEW v2 AS + SELECT t2field as v2field + FROM t2 A + WHERE A.t2field IN (SELECT t2field FROM t2 ); + +DELIMITER |; +CREATE PROCEDURE p1 () + BEGIN + SELECT v1field + FROM v1 + WHERE v1field IN ( SELECT v2field as vf_inner FROM v2 ); + END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (2),(3),(4); + +CALL p1; +CALL p1; + +DROP TABLE t1,t2; +DROP VIEW v1,v2; +DROP PROCEDURE p1; + +set SESSION optimizer_switch=@local_optimizer_switch; + +--echo # End of BUG#48834 + +--echo +--echo Bug#49097 subquery with view generates wrong result with +--echo non-prepared statement +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE TABLE t1 ( + city VARCHAR(50) NOT NULL, + country_id SMALLINT UNSIGNED NOT NULL +); + +INSERT INTO t1 VALUES +('Batna',2), +('Bchar',2), +('Skikda',2), +('Tafuna',3), +('Algeria',2) ; + +CREATE TABLE t2 ( + country_id SMALLINT UNSIGNED NOT NULL, + country VARCHAR(50) NOT NULL +); + +INSERT INTO t2 VALUES +(2,'Algeria'), +(3,'XAmerican Samoa') ; + +CREATE VIEW v1 AS +SELECT country_id, country +FROM t2 +WHERE LEFT(country,1) = "A" +; + +SELECT city, country_id +FROM t1 +WHERE country_id IN ( + SELECT country_id + FROM t2 + WHERE LEFT(country,1) = "A" +); + +SELECT city, country_id +FROM t1 +WHERE country_id IN ( + SELECT country_id + FROM v1 +); + +PREPARE stmt FROM +" +SELECT city, country_id +FROM t1 +WHERE country_id IN ( + SELECT country_id + FROM v1 +); +"; + +execute stmt; + +deallocate prepare stmt; +drop table t1, t2; +drop view v1; + +--echo # End of Bug#49097 + +--echo # +--echo # BUG#38075: Wrong result: rows matching a subquery with outer join not returned +--echo # + +--disable_warnings +DROP TABLE IF EXISTS ot1, it1, it2; +--enable_warnings + +CREATE TABLE it2 ( + int_key int(11) NOT NULL, + datetime_key datetime NOT NULL, + KEY int_key (int_key), + KEY datetime_key (datetime_key) +); +INSERT INTO it2 VALUES + (5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'), + (0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'), + (8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'), + (9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'), + (1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'), + (0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'), + (5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'), + (7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'), + (0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'), + (0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00'); +CREATE TABLE ot1 ( + int_nokey int(11) NOT NULL, + int_key int(11) NOT NULL, + KEY int_key (int_key) +); +INSERT INTO ot1 VALUES + (5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7), + (0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5); +CREATE TABLE it1 ( + int_nokey int(11) NOT NULL, + int_key int(11) NOT NULL, + KEY int_key (int_key) +); +INSERT INTO it1 VALUES + (9,5), (0,4); +--sorted_result +SELECT int_key FROM ot1 +WHERE int_nokey IN (SELECT it2.int_key + FROM it1 LEFT JOIN it2 ON it2.datetime_key); +EXPLAIN +SELECT int_key FROM ot1 +WHERE int_nokey IN (SELECT it2.int_key + FROM it1 LEFT JOIN it2 ON it2.datetime_key); +DROP TABLE ot1, it1, it2; + +--echo # End of BUG#38075 + +--echo # +--echo # BUG#31480: Incorrect result for nested subquery when executed via semi join +--echo # +create table t1 (a int not null, b int not null); +create table t2 (c int not null, d int not null); +create table t3 (e int not null); + +insert into t1 values (1,10); +insert into t1 values (2,10); +insert into t1 values (1,20); +insert into t1 values (2,20); +insert into t1 values (3,20); +insert into t1 values (2,30); +insert into t1 values (4,40); + +insert into t2 values (2,10); +insert into t2 values (2,20); +insert into t2 values (4,10); +insert into t2 values (5,10); +insert into t2 values (3,20); +insert into t2 values (2,40); + +insert into t3 values (10); +insert into t3 values (30); +insert into t3 values (10); +insert into t3 values (20); + +explain extended +select a from t1 +where a in (select c from t2 where d >= some(select e from t3 where b=e)); +show warnings; + +select a from t1 +where a in (select c from t2 where d >= some(select e from t3 where b=e)); + +drop table t1, t2, t3; + +--echo # +--echo # Bug#48213 Materialized subselect crashes if using GEOMETRY type +--echo # + +CREATE TABLE t1 ( + pk int, + a varchar(1), + b varchar(4), + c tinyblob, + d blob, + e mediumblob, + f longblob, + g tinytext, + h text, + i mediumtext, + j longtext, + k geometry, + PRIMARY KEY (pk) +); + +INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo','ffff','ffff','ffff','ffff','ffff','ffff',GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')), (2,'f','ffff','ffff','ffff', 'ffff','ffff','ffff','ffff','ffff','ffff',GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')); + +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','ffff','ffff',GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')), (2,'f','ffff','ffff','ffff','ffff','ffff','ffff','ffff','ffff','ffff',GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')); + +# Test that materialization is skipped for semijoins where materialized +# table would contain GEOMETRY or different kinds of BLOB/TEXT columns +let $query= +SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +let $query= +SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0); +eval EXPLAIN EXTENDED $query; +eval $query; + +DROP TABLE t1, t2; + +--echo # End of Bug#48213 + +--echo # +--echo # Bug#49198 Wrong result for second call of procedure +--echo # with view in subselect. +--echo # + +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); + +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; + +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); + +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; + +EXECUTE stmt; +EXECUTE stmt; + +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; + +--echo # End of Bug#49198 + +--echo # +--echo # Bug#45174: Incorrectly applied equality propagation caused wrong +--echo # result on a query with a materialized semi-join. +--echo # + +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `varchar_key` varchar(1) NOT NULL, + `varchar_nokey` varchar(1) NOT NULL, + PRIMARY KEY (`pk`), + KEY `varchar_key` (`varchar_key`) +); + +INSERT INTO `t1` VALUES (11,'m','m'),(12,'j','j'),(13,'z','z'),(14,'a','a'),(15,'',''),(16,'e','e'),(17,'t','t'),(19,'b','b'),(20,'w','w'),(21,'m','m'),(23,'',''),(24,'w','w'),(26,'e','e'),(27,'e','e'),(28,'p','p'); + +CREATE TABLE `t2` ( + `varchar_nokey` varchar(1) NOT NULL +); + +INSERT INTO `t2` VALUES ('v'),('u'),('n'),('l'),('h'),('u'),('n'),('j'),('k'),('e'),('i'),('u'),('n'),('b'),('x'),(''),('q'),('u'); + +EXPLAIN EXTENDED SELECT varchar_nokey +FROM t2 +WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( +SELECT `varchar_key` , `varchar_nokey` +FROM t1 +WHERE `varchar_nokey` < 'n' XOR `pk` ) ; + +SELECT varchar_nokey +FROM t2 +WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( +SELECT `varchar_key` , `varchar_nokey` +FROM t1 +WHERE `varchar_nokey` < 'n' XOR `pk` ) ; + +DROP TABLE t1, t2; + +--echo # End of the test for bug#45174. +--echo # +--echo # BUG#43768: Prepared query with nested subqueries core dumps on second execution +--echo # +create table t1 ( + id int(11) unsigned not null primary key auto_increment, + partner_id varchar(35) not null, + t1_status_id int(10) unsigned +); + +insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"), + ("3", "partner3", "10"), ("4", "partner4", "10"); + +create table t2 ( + id int(11) unsigned not null default '0', + t1_line_id int(11) unsigned not null default '0', + article_id varchar(20), + sequence int(11) not null default '0', + primary key (id,t1_line_id) +); + +insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"), + ("2", "2", "sup", "2"), ("2", "3", "sup", "3"), + ("2", "4", "imp", "4"), ("3", "1", "sup", "0"), + ("4", "1", "sup", "0"); +create table t3 ( + id int(11) not null default '0', + preceding_id int(11) not null default '0', + primary key (id,preceding_id) +); + +create table t4 ( + user_id varchar(50) not null, + article_id varchar(20) not null, + primary key (user_id,article_id) +); + +insert into t4 values("nicke", "imp"); +prepare stmt from +'select t1.partner_id +from t1 +where + t1.id in ( + select pl_inner.id + from t2 as pl_inner + where pl_inner.article_id in ( + select t4.article_id from t4 + where t4.user_id = \'nicke\' + ) + )'; + +execute stmt; +execute stmt; +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug#48623 Multiple subqueries are optimized incorrectly +--echo # + +CREATE TABLE t1(val VARCHAR(10)); +CREATE TABLE t2(val VARCHAR(10)); +CREATE TABLE t3(val VARCHAR(10)); + +INSERT INTO t1 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t2 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t3 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); + +EXPLAIN +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 + WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') + AND t1.val IN (SELECT t3.val FROM t3 + WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); + +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 + WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') + AND t1.val IN (SELECT t3.val FROM t3 + WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +--echo # End of Bug#48623 + +--echo # +--echo # LPBUG#602574: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, +--echo # uint): Assertion `join->best_read < +--echo # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='materialization=off'; +CREATE TABLE t1 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +); + +CREATE TABLE t2 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES + (NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'), + ('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'), + ('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'), + ('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'), + ('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'), + ('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'), + ('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'), + ('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z'); + +CREATE TABLE t3 ( + varchar_key varchar(1) DEFAULT NULL, + KEY varchar_key (varchar_key) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES + (NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'), + ('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y'); + +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3 + WHERE (varchar_key,varchar_key) + IN (SELECT t1.varchar_key, t2 .varchar_key + FROM t1 RIGHT JOIN t2 ON t1.varchar_key + ) + ); +set optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1, t2, t3; + +--echo # +--echo # Bug#46692 "Crash occurring on queries with nested FROM subqueries +--echo # using materialization." +--echo # +CREATE TABLE t1 ( + pk INTEGER PRIMARY KEY, + int_key INTEGER, + KEY int_key(int_key) +); +INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1); + +CREATE TABLE t2 ( + pk INTEGER PRIMARY KEY, + int_key INTEGER, + KEY int_key(int_key) +); +INSERT INTO t2 VALUES (1,7),(2,2); + +SELECT * FROM t1 WHERE (140, 4) IN + (SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key); + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query +--echo # causes crash." +--echo # +CREATE TABLE t1 ( + pk INTEGER PRIMARY KEY, + int_nokey INTEGER, + int_key INTEGER, + date_key DATE, + datetime_nokey DATETIME, + varchar_nokey VARCHAR(1) +); + +CREATE TABLE t2 ( + date_nokey DATE +); + +CREATE TABLE t3 ( + pk INTEGER PRIMARY KEY, + int_nokey INTEGER, + date_key date, + varchar_key VARCHAR(1), + varchar_nokey VARCHAR(1), + KEY date_key (date_key) +); + +SELECT date_key FROM t1 +WHERE (int_key, int_nokey) + IN (SELECT t3.int_nokey, t3.pk + FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key) + WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk + ) + AND (varchar_nokey <> 'f' OR NOT int_key < 7); + + +--echo # +--echo # Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery +--echo # + AND in outer query". +--echo # +INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'), + (11,7,0,'0000-00-00','0000-00-00 00:00:00','s'), + (12,4,0,'2003-07-14','2006-09-14 04:01:02','y'), + (13,0,4,'2002-07-25','0000-00-00 00:00:00','c'), + (14,1,8,'2007-07-03','0000-00-00 00:00:00','q'), + (15,6,5,'2001-11-12','0000-00-00 00:00:00',''), + (16,2,9,'0000-00-00','0000-00-00 00:00:00','j'), + (29,9,1,'0000-00-00','2003-08-11 00:00:00','m'); +INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'), + (2,2,'2002-09-17','h','h'); + +SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey +WHERE t1.varchar_nokey + IN (SELECT varchar_nokey FROM t1 + WHERE (pk) + IN (SELECT t3.int_nokey + FROM t3 LEFT JOIN t1 ON t1.varchar_nokey + WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26' + ) + ); + +DROP TABLE t1, t2, t3; + +--echo # +--echo # Bug#45219 "Crash on SELECT DISTINCT query containing a +--echo # LEFT JOIN in subquery" +--echo # + +CREATE TABLE t1 ( + pk INTEGER NOT NULL, + int_nokey INTEGER NOT NULL, + datetime_key DATETIME NOT NULL, + varchar_key VARCHAR(1) NOT NULL, + PRIMARY KEY (pk), + KEY datetime_key (datetime_key), + KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'), +(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'), +(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'), +(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'), +(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'), +(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'), +(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'), +(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'), +(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''), +(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u'); + +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES +(10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'), +(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'), +(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''), +(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'), +(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'), +(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'), +(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''), +(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'), +(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'), +(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x'); + +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 VALUES +(10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51',''); + +SELECT DISTINCT datetime_key FROM t1 +WHERE (int_nokey, pk) + IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key) + AND pk = 9; + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#53060: LooseScan semijoin strategy does not return all rows +--echo # + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off'; +set optimizer_switch='firstmatch=off,loosescan=on'; +set @tmp_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=4; + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); + +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); + +DROP TABLE t1, t2; + +set optimizer_switch=@tmp_optimizer_switch; +set @@optimizer_use_condition_selectivity=@tmp_optimizer_use_condition_selectivity; + +--echo # +--echo # BUG#49453: re-execution of prepared statement with view +--echo # and semijoin crashes +--echo # + +CREATE TABLE t1 (city VARCHAR(50), country_id INT); +CREATE TABLE t2 (country_id INT, country VARCHAR(50)); + +INSERT INTO t1 VALUES + ('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ; +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa'); + +CREATE VIEW v1 AS + SELECT country_id as vf_country_id + FROM t2 + WHERE LEFT(country,1) = "A"; + +PREPARE stmt FROM " +SELECT city, country_id +FROM t1 +WHERE country_id IN (SELECT vf_country_id FROM v1); +"; + +--echo +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2; +DROP VIEW v1; + +--echo # +--echo # Bug#54437 Extra rows with LEFT JOIN + semijoin +--echo # + +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); +insert into t1 values(1),(1); +insert into t2 values(1),(1),(1),(1); +insert into t3 values(2),(2); + +set @tmp_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='materialization=off'; + +set optimizer_switch='semijoin=off'; +explain +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); + +set optimizer_switch='semijoin=on'; +explain +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); + +set optimizer_switch=@tmp_optimizer_switch; + +drop table t1,t2,t3; + +--echo # +--echo # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN)) +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES(1),(1); +INSERT INTO t2 VALUES(1),(1); +INSERT INTO t3 VALUES(2),(2); + +set @tmp_optimzer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=off'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch='semijoin=off,materialization=on'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch='semijoin=on,materialization=off'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch=@tmp_optimzer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#52329 - Wrong result: subquery materialization, IN, +--echo # non-null field followed by nullable +--echo # + +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); + +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); + +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); + +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), + ('1 - 11', '2 - 21'), + ('1 - 12', '2 - 22'), + ('1 - 12', '2 - 22'), + ('1 - 13', '2 - 23'); + +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; + +set @tmp_optimzer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=on'; + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2a WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2b WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0'); + +set optimizer_switch=@tmp_optimzer_switch; + +DROP TABLE t1,t2a,t2b,t2c; + +--echo # +--echo # Bug#57623: subquery within before insert trigger causes crash (sj=on) +--echo # + +CREATE TABLE ot1(a INT); +CREATE TABLE ot2(a INT); +CREATE TABLE ot3(a INT); +CREATE TABLE it1(a INT); + +INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); +INSERT INTO ot2 VALUES(0),(2),(4),(6); +INSERT INTO ot3 VALUES(0),(3),(6); +INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); + +let $query= +SELECT * +FROM ot1 + LEFT JOIN + (ot2 JOIN ot3 on ot2.a=ot3.a) + ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1); + +set @tmp_optimzer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=on'; + +set optimizer_switch='materialization=off'; +eval explain $query; +eval $query; +eval prepare s from '$query'; +execute s; +execute s; +deallocate prepare s; + +set optimizer_switch='materialization=on'; +eval explain $query; +eval $query; +eval prepare s from '$query'; +execute s; +execute s; +deallocate prepare s; + +set optimizer_switch=@tmp_optimzer_switch; + +DROP TABLE ot1, ot2, ot3, it1; + +--echo # +--echo # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on +--echo # + +CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM; +CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM; +CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM; + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1,1), (2,1); +INSERT INTO t3 VALUES + (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3), + (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7); + +set @tmp_optimzer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=on'; +EXPLAIN +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); + +set optimizer_switch='semijoin=on,materialization=on'; +EXPLAIN +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); + +set optimizer_switch=@tmp_optimzer_switch; + +DROP TABLE t1, t2, t3 ; + +--echo # +--echo # +--echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3 +--echo # +CREATE TABLE t1 ( t1field integer, primary key (t1field)); +CREATE TABLE t2 ( t2field integer, primary key (t2field)); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (2),(3),(4); +explain +SELECT * FROM t1 A +WHERE + A.t1field IN (SELECT A.t1field FROM t2 B) AND + A.t1field IN (SELECT C.t2field FROM t2 C + WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); +SELECT * FROM t1 A +WHERE + A.t1field IN (SELECT A.t1field FROM t2 B) AND + A.t1field IN (SELECT C.t2field FROM t2 C + WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); +drop table t1,t2; + +--echo # +--echo # BUG#787299: Valgrind complains on a join query with two IN subqueries +--echo # +create table t1 (a int); +insert into t1 values (1), (2), (3); +create table t2 as select * from t1; +select * from t1 A, t1 B + where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +explain +select * from t1 A, t1 B + where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +drop table t1, t2; + +--echo # +--echo # BUG#784441: Abort on semijoin with a view as the inner table +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (1); + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (1), (1); + +CREATE VIEW v1 AS SELECT 1; + +EXPLAIN +SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); +SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); + +DROP VIEW v1; +DROP TABLE t1,t2; + +--echo # +--echo # BUG#751439 Assertion `!table->file || table->file->inited == handler::NONE' failed with subquery +--echo # +CREATE TABLE t1 ( f10 int, f11 int) ; +INSERT IGNORE INTO t1 VALUES (0,0),(0,0); + +CREATE TABLE t2 ( f11 int); +INSERT IGNORE INTO t2 VALUES (0),(0); + +CREATE TABLE t3 ( f11 int) ; +INSERT IGNORE INTO t3 VALUES (0); + +SELECT alias1.f11 AS field2 +FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 +WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) +GROUP BY field2 ; + +drop table t1, t2, t3; + +--echo # +--echo # BUG#778406 Crash in hp_movelink with Aria engine and subqueries +--echo # +CREATE TABLE t4 (f10 varchar(32) , KEY (f10)) ENGINE=Aria; +INSERT INTO t4 VALUES ('x'),('m'),('c'); + +CREATE TABLE t1 (f11 int) ENGINE=Aria; +INSERT INTO t1 VALUES (0),(0),(0); + +CREATE TABLE t2 ( f10 int) ENGINE=Aria; +INSERT INTO t2 VALUES (0),(0),(0); + +CREATE TABLE t3 ( f10 int, f11 int) ENGINE=Aria; + +SELECT * +FROM t4 +WHERE f10 IN +( SELECT t1.f11 +FROM t1 +LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 ); + +drop table t1,t2,t3,t4; + +--echo # +--echo # BUG#751484: Valgrind warning / sporadic crash in evaluate_join_record sql_select.cc:14099 with semijoin +--echo # + +CREATE TABLE t1 ( f10 int, f11 int, KEY (f10)); +INSERT IGNORE INTO t1 VALUES (0, 0),(0, 0); + +CREATE TABLE t3 ( f10 int); +INSERT IGNORE INTO t3 VALUES (0); + +set @tmp_751484= @@optimizer_switch; +set optimizer_switch='materialization=on'; +SELECT * FROM t1 +WHERE f11 IN ( + SELECT C_SQ1_alias1.f11 + FROM t1 AS C_SQ1_alias1 + JOIN t3 AS C_SQ1_alias2 + ON C_SQ1_alias2.f10 = C_SQ1_alias1.f10 +); +set optimizer_switch='materialization=off'; +SELECT * FROM t1 +WHERE f11 IN ( + SELECT C_SQ1_alias1.f11 + FROM t1 AS C_SQ1_alias1 + JOIN t3 AS C_SQ1_alias2 + ON C_SQ1_alias2.f10 = C_SQ1_alias1.f10 +); +set optimizer_switch=@tmp_751484; +drop table t1, t3; + +# +--echo # BUG#795530 Wrong result with subquery semijoin materialization and outer join +--echo # Simplified testcase that uses DuplicateElimination +--echo # +create table t1 (a int); +create table t2 (a int, b char(10)); + +insert into t1 values (1),(2); +insert into t2 values (1, 'one'), (3, 'three'); + +create table t3 (b char(10)); +insert into t3 values('three'),( 'four'); +insert into t3 values('three'),( 'four'); +insert into t3 values('three'),( 'four'); +insert into t3 values('three'),( 'four'); +explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); +select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); +drop table t1, t2, t3; + +--echo # +--echo # BUG#600958 RQG: Crash in optimize_semijoin_nests +--echo # +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_int_key (col_int_key), + KEY col_date_key (col_date_key), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (10,8,'2002-02-21',NULL); +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_int_key (col_int_key), + KEY col_date_key (col_date_key), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1,7,'1900-01-01','f'); + +SELECT col_date_key FROM t1 +WHERE 5 IN ( + SELECT SUBQUERY3_t1 .col_int_key + FROM t2 SUBQUERY3_t1 + LEFT JOIN t1 SUBQUERY3_t2 ON SUBQUERY3_t1 .col_varchar_key +); +drop table t2, t1; + + +--echo # +--echo # No BUG#: Duplicate weedout check is not done for outer joins +--echo # +create table t1 (a int); +create table t2 (a int); + +insert into t1 values (1),(1),(2),(2); +insert into t2 values (1); + +create table t0 (a int); +insert into t0 values (1),(2); + +set @tmp_20110622= @@optimizer_switch; +set optimizer_switch='firstmatch=off,loosescan=off,materialization=off'; +--echo # Check DuplicateWeedout + join buffer +explain +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); + +--echo # Check DuplicateWeedout without join buffer +set @tmp_jcl_20110622= @@join_cache_level; +set join_cache_level= 0; +explain +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); + + +--echo # Check FirstMatch without join buffer: +set optimizer_switch='firstmatch=on'; +explain +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); +select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a); + +--echo # +--echo # Now, check the same for multiple inner tables: +alter table t2 add b int; +update t2 set b=a; +create table t3 as select * from t2; + +set optimizer_switch='firstmatch=off'; +set join_cache_level= 0; +--echo # DuplicateWeedout without join buffer +explain +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +set @@join_cache_level=@tmp_jcl_20110622; +--echo # DuplicateWeedout + join buffer +explain +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +--echo # Now, let the inner join side have a 'partial' match +select * from t3; +insert into t3 values(2,2); + +explain +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +select * from t0 +where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a); + +set @@optimizer_switch=@tmp_20110622; + +drop table t0, t1, t2, t3; + +--echo # +--echo # BUG#802965: Crash in do_copy_not_null with semijoin=on in maria-5.3 +--echo # +set @save_802965= @@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off,firstmatch=off,loosescan=off'; + +CREATE TABLE t2 ( f1 int NOT NULL , PRIMARY KEY (f1)) ; +INSERT IGNORE INTO t2 VALUES (19),(20); + +CREATE TABLE t1 ( f1 int NOT NULL , PRIMARY KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES (21),(22),(23),(24); + +SELECT * +FROM t2 , t1 +WHERE t2.f1 IN +( + SELECT SQ1_alias1.f1 + FROM t1 AS SQ1_alias1 LEFT JOIN t2 AS SQ1_alias2 JOIN t2 AS SQ1_alias3 ON SQ1_alias3.f1 ON SQ1_alias3.f1 +) +AND t1.f1 = t2.f1 ; + +DROP TABLE t1, t2; +set optimizer_switch=@save_802965; + +--echo # +--echo # BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106 +--echo # +CREATE TABLE t1 ( f1 int) ; +INSERT INTO t1 VALUES (1),(1); + +CREATE TABLE t2 ( f2 int) ; +INSERT INTO t2 VALUES (1),(1); + +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (1),(1); + +SELECT * +FROM t1 +WHERE t1.f1 IN ( + SELECT t2.f2 + FROM t2 + LEFT JOIN ( + SELECT * + FROM t3 + ) AS alias1 + ON alias1.f3 = t2.f2 +); + +DROP TABLE t1,t2,t3; + + +--echo # +--echo # BUG#611704: Crash in replace_where_subcondition with nested subquery and semijoin=on +--echo # + +CREATE TABLE t1 ( f1 int) ; +CREATE TABLE t2 ( f1 int) ; +CREATE TABLE t3 ( f1 int) ; + +SELECT * FROM ( + SELECT t3.* + FROM t2 STRAIGHT_JOIN t3 + ON t3.f1 + AND (t3.f1 ) IN ( + SELECT t1.f1 + FROM t1 + ) +) AS alias1; +DROP TABLE t1,t2,t3; + +--echo # BUG#611704: another testcase: +CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ; +CREATE TABLE t2 ( f2 int(11), KEY (f2)); +CREATE TABLE t3 ( f4 varchar(1)) ; + +PREPARE st1 FROM ' +SELECT * +FROM t1 +STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 ) +ON (t1.f3) IN ( SELECT f4 FROM t1 ) +'; +EXECUTE st1; +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90 +--echo # (Original testcase) +--echo # + +CREATE TABLE t1 (f1 int, f2 int ); +INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL); + +CREATE TABLE t2 (f2 int, f3 int ); +INSERT INTO t2 VALUES (NULL,NULL),(0,0); + +CREATE TABLE t3 ( f1 int, f3 int ); +INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0); + +CREATE TABLE t4 ( f2 int, KEY (f2) ); +INSERT INTO t4 VALUES (0),(NULL); + +CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ; + +--echo # The following must not have outer joins: +explain extended +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); + +drop view v4; +drop table t1, t2, t3, t4; + +--echo # +--echo # BUG#803303: Wrong result with semijoin=on, outer join in maria-5.3-subqueries-mwl90 +--echo # + +--echo # Testcase#1: +set @tmp803303= @@optimizer_switch; +set optimizer_switch = 'semijoin=on,materialization=off,firstmatch=off,loosescan=off'; +CREATE TABLE t2 ( f1 int) ; +INSERT IGNORE INTO t2 VALUES (6),(8); +CREATE TABLE t1 ( f1 int, f2 int, f3 int) ; +INSERT IGNORE INTO t1 VALUES (8,0,0),(7,0,0),(9,0,0); +SELECT alias2.f1 +FROM t2 AS alias1 +LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 ) +ON alias3.f2 = alias2.f2 +WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ; +drop table t1,t2; +set optimizer_switch= @tmp803303; + +--echo # Testcase #2: +CREATE TABLE t1 ( f10 int) ; +INSERT INTO t1 VALUES (0),(0); + +CREATE TABLE t2 ( f10 int, f11 varchar(1)) ; +INSERT INTO t2 VALUES (0,'a'),(0,'b'); + +CREATE TABLE t3 ( f10 int) ; +INSERT INTO t3 VALUES (0),(0),(0),(0),(0); + +CREATE TABLE t4 ( f10 varchar(1), f11 int) ; +INSERT INTO t4 VALUES ('a',0),('b',0); + +SELECT * FROM t1 +LEFT JOIN ( t2 JOIN t3 ON t3.f10 = t2.f10 ) ON t1.f10 = t2.f10 +WHERE t2.f10 IN ( + SELECT t4.f11 + FROM t4 + WHERE t4.f10 != t2.f11 +); + +drop table t1,t2,t3,t4; + +--echo # +--echo # BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90 +--echo # +set @tmp803457=@@optimizer_switch; +set optimizer_switch='materialization=off'; +CREATE TABLE t1 (f1 int, f2 int ); +INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL); + +CREATE TABLE t2 (f2 int, f3 int ); +INSERT INTO t2 VALUES (NULL,NULL),(0,0); + +CREATE TABLE t3 ( f1 int, f3 int ); +INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0); + +CREATE TABLE t4 ( f2 int); +INSERT INTO t4 VALUES (0),(NULL); + +--echo # The following uses Duplicate Weedout, and "End temporary" must not be +--echo # in the middle of the inner side of an outer join: +explain +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4); +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4); + +DROP TABLE t1, t2, t3, t4; +set @tmp803457=@@optimizer_switch; + +--echo # +--echo # BUG#818280: crash in do_copy_not_null() in maria-5.3 with semijoin +--echo # +CREATE TABLE t1 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ; +INSERT IGNORE INTO t1 VALUES (2,7),(1,3),(5,6); + +CREATE TABLE t3 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ; +INSERT IGNORE INTO t3 VALUES (2,7),(1,3),(5,6); + +CREATE TABLE t2 ( c1 int NOT NULL , c5 int NOT NULL ); +INSERT IGNORE INTO t2 VALUES (2,2),(2,2),(5,6); + +SELECT * FROM t1 WHERE c1 IN ( SELECT t3.c1 FROM t3 LEFT JOIN t2 ON t2 .c1 = t3 .c1 WHERE t2.c5 != 0 ); + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING +--echo # +CREATE TABLE t1 ( d int ); +INSERT INTO t1 VALUES (2),(2),(0),(2),(2); + +CREATE TABLE t2 ( b int ); +INSERT INTO t2 VALUES (4),(3),(3); + +CREATE TABLE t3 ( a int ); + +SELECT * +FROM t3 +WHERE (t3.a) IN ( + SELECT t1.d + FROM t1 + HAVING ( 4 ) IN ( + SELECT t2.b + FROM t2 + ) +); +drop table t1, t2,t3; + +--echo # +--echo # BUG#834758: Wrong result with innner join, LooseScan, two-column IN() predicate +--echo # + +set @tmp835758=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off'; + +CREATE TABLE t1 (b int) ; +INSERT INTO t1 VALUES (1),(5); + +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ; +INSERT INTO t2 VALUES (6),(10); + +CREATE TABLE t3 (a int, b int, KEY (b)) ; +INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5); + +--echo # This used to incorrectly pick a join order of (t1, LooseScan(t3), t2): +explain +SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); +SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); + +DROP TABLE t1, t2, t3; +set @@optimizer_switch= @tmp835758; + +--echo # +--echo # BUG#834739: Wrong result with 3-way inner join, LooseScan,multipart keys +--echo # +set @tmp834739=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off'; +CREATE TABLE t2 ( b int, c int, KEY (b)) ; +INSERT INTO t2 VALUES (1,0),(1,0),(9,0),(1,0),(5,0); +INSERT INTO t2 VALUES (2,0),(3,0),(8,0),(6,0),(5,0); + +CREATE TABLE t3 ( a int); +INSERT INTO t3 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); + +CREATE TABLE t4 ( a int); +INSERT INTO t4 VALUES (0),(0),(0); + +CREATE TABLE t5 ( b int, a int , KEY (a,b)) ; +INSERT INTO t5 VALUES (7,0),(9,0); + +explain +SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); +SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); + +DROP TABLE t2, t3, t4, t5; +set @@optimizer_switch=@tmp834739; + +--echo # +--echo # BUG#830993: Crash in end_read_record with derived table +--echo # +set @tmp_830993=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=off,materialization=off,firstmatch=off'; +set @tmp_830993_jbs= @@join_buffer_size; +--disable_warnings +set join_buffer_size=160; +--enable_warnings +CREATE TABLE t1 ( + a int(11) NOT NULL AUTO_INCREMENT, + b int(11) DEFAULT NULL, + c int(11) DEFAULT NULL, + d time DEFAULT NULL, + e varchar(1) DEFAULT NULL, + f varchar(1) DEFAULT NULL, + PRIMARY KEY (a), + KEY c (c), + KEY d (d), + KEY e (e,c) +); +INSERT INTO t1 VALUES (10,NULL,8,'22:55:23','x','x'), + (11,8,7,'10:19:31','d','d'),(12,1,1,'14:40:36','r','r'), + (13,9,7,'04:37:47','f','f'),(14,4,9,'19:34:06','y','y'), + (15,3,NULL,'20:35:33','u','u'),(16,2,1,NULL,'m','m'), + (17,NULL,9,'14:43:37',NULL,NULL),(18,2,2,'02:23:09','o','o'), + (19,NULL,9,'01:22:45','w','w'),(20,6,2,'00:00:00','m','m'), + (21,7,4,'00:13:25','q','q'),(22,2,0,'03:47:16',NULL,NULL), + (23,5,4,'01:41:48','d','d'),(24,7,8,'00:00:00','g','g'), + (25,6,NULL,'22:32:04','x','x'),(26,6,NULL,'16:44:14','f','f'), + (27,2,0,'17:38:37','p','p'),(28,9,NULL,'08:46:48','j','j'), + (29,6,8,'14:11:27','c','c'); + +CREATE TABLE t2 like t1; +INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), + (2,150,62,'14:26:02','v','v'),(3,NULL,7,'14:03:03','c','c'), + (4,2,1,'01:46:09',NULL,NULL),(5,5,0,'16:21:18','x','x'), + (6,3,7,'18:56:33','i','i'),(7,1,7,NULL,'e','e'), + (8,4,1,'09:29:08','p','p'),(9,NULL,7,'19:11:10','s','s'), + (10,2,1,'11:57:26','j','j'),(11,6,5,'00:39:46','z','z'), + (12,6,2,'03:28:15','c','c'),(13,8,0,'06:44:18','a','a'), + (14,2,1,'14:36:39','q','q'),(15,6,8,'18:42:45','y','y'), + (16,8,1,'02:57:29',NULL,NULL),(17,3,1,'16:46:13','r','r'), + (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), + (20,6,5,'20:58:33','r','r'); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + +explain +SELECT + alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, + alias2.a as a2_a, alias2.b as a2_b, alias2.c as a2_c, alias2.d as a2_d, + alias2.e as a2_e, alias2.f as a2_f, + t2.a as t2_a, t2.b as t2_b, t2.c as t2_c, t2.d as t2_d, t2.e as t2_e, t2.f as t2_f +FROM + (SELECT * FROM t2) AS alias1, + t1 AS alias2, + t2 +WHERE + alias1.c IN (SELECT SQ3_alias1.b + FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) +LIMIT 100; + +create table t3 as +SELECT + alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, + alias2.a as a2_a, alias2.b as a2_b, alias2.c as a2_c, alias2.d as a2_d, + alias2.e as a2_e, alias2.f as a2_f, + t2.a as t2_a, t2.b as t2_b, t2.c as t2_c, t2.d as t2_d, t2.e as t2_e, t2.f as t2_f +FROM + (SELECT * FROM t2) AS alias1, + t1 AS alias2, + t2 +WHERE + alias1.c IN (SELECT SQ3_alias1.b + FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) +LIMIT 100; + +set optimizer_switch=@tmp_optimizer_switch; + +drop table t1,t2, t3; +set optimizer_switch=@tmp_830993; +set join_buffer_size= @tmp_830993_jbs; + +--echo # +--echo # BUG##849717: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin +--echo # +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int, b int) ; + +PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( SELECT b,a FROM t3)"; +EXECUTE st1; +EXECUTE st1; + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#849776: Wrong result with semijoin + "Impossible where" +--echo # +CREATE TABLE t1 ( b varchar(1), a integer) ; +INSERT INTO t1 VALUES ('z',8); + +CREATE TABLE t2 ( a integer, b varchar(1)) ; + +CREATE TABLE t4 ( a integer, b varchar(1)) ; + +CREATE TABLE t5 ( a integer) ; +INSERT INTO t5 VALUES (8); + +select * from t5 where (a) in ( + SELECT t1.a + FROM t1 LEFT JOIN t2 ON t1.a = t2.a + WHERE t2.b NOT IN (SELECT t4.b FROM t4 WHERE t4.b < t1.b) +); + +DROP TABLE t1, t2, t4, t5; + +--echo # +--echo # BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size +--echo # +#CREATE TABLE t1 ( f2 int) ; +#CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; +#INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), +# (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), +# (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), +# (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), +# (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), +# (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), +# (3888,20,'USA','Yonkers'); + +#CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; +#INSERT INTO t3 VALUES (86,'USA'); + +#CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; +#INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); + +#CREATE TABLE t5 ( f2 int) ; + +#CREATE TABLE t6 ( f4 varchar(3)); +#INSERT INTO t6 VALUES ('RUS'),('USA'); + +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set @tmp_mjs_861147= @@max_join_size; +SET max_join_size=10; +set @tmp_os_861147= @@optimizer_switch; +set @@optimizer_switch='semijoin=on,materialization=on'; + +--error ER_TOO_BIG_SELECT +explain +select * from t1 where a in (select max(A.a + B.a + C.a) from t1 A, t1 B, t1 C); + +set max_join_size= @tmp_mjs_861147; +set optimizer_switch= @tmp_os_861147; + +#DROP TABLE t1,t2,t3,t4,t5,t6; +drop table t1; + +--echo # +--echo # BUG#877288: Wrong result with semijoin + materialization + multipart key +--echo # +set @tmp_877288=@@optimizer_switch; +set optimizer_switch='semijoin=ON,materialization=ON'; +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (19),(19),(19),(20),(20),(20),(20),(20),(20); + +CREATE TABLE t2 ( b int NOT NULL , c int NOT NULL , KEY (b,c)) ; +INSERT INTO t2 VALUES (14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(20,1); + +CREATE TABLE t3 ( a int, d int) ; +INSERT INTO t3 VALUES (19,1),(7,1),(3,1),(3,1),(20,1),(3,1),(16,1),(17,1),(9,1),(4,1),(6,1),(15,1),(17,1); + +explain +SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); +SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); +DROP TABLE t1,t2,t3; +set optimizer_switch=@tmp_877288; + +--echo # +--echo # BUG#878753: Assertion '0' failed in replace_where_subcondition with derived_merge +--echo # +set @tmp878753= @@optimizer_switch; +set optimizer_switch= 'semijoin=on,derived_merge=on'; +CREATE TABLE t1 (b int(11)) ; +CREATE TABLE t2 (c int, b int, d varchar(52) NOT NULL) ; +CREATE TABLE t3 (b int(11)) ; + +PREPARE st1 FROM ' + SELECT * FROM t1 + JOIN ( + SELECT t2.* FROM t2 + WHERE t2.d <> "a" + AND t2.c IN ( + SELECT t3.b + FROM t3 + ) + ) AS alias2 + ON ( alias2.b = t1.b ); +'; +EXECUTE st1; +DROP TABLE t1,t2,t3; +set optimizer_switch=@tmp878753; + +--echo # +--echo # Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +--echo # + +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); + +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; + +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch= @tmp_otimizer_switch; + +drop table t1,t2; + +--echo # +--echo # Bug #887496: semijoin with IN equality for the second part of an index +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (9), (0), (8), (5); + +CREATE TABLE t2 (a int, b varchar(1), INDEX idx (b,a)); +INSERT INTO t2 VALUES (5,'r'), (5,'z'); + +CREATE TABLE t3 (a int, b varchar(1), INDEX idx (b,a)); +INSERT INTO t3 VALUES (5,'r'), (5,'z'); + +set @tmp_otimizer_switch= @@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on,firstmatch=on'; + +SET SESSION optimizer_switch='loosescan=off'; +EXPLAIN +SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b); +SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b); + +SET SESSION optimizer_switch='loosescan=on'; +EXPLAIN +SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b); +SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b); + +set optimizer_switch= @tmp_otimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #901312: materialized semijoin + right join +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (4), (1); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4), (1); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (4), (1); + +set @tmp_otimizer_switch= @@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on,materialization=on'; + +EXPLAIN +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); + +set optimizer_switch= @tmp_otimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #901709: assertion failure with record count == 0 +--echo # + +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; + +EXPLAIN +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); + +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0 +--echo # +set @opl_901399= @@optimizer_prune_level; +set @os_091399= @@optimizer_switch; +SET optimizer_prune_level=0; +SET optimizer_switch = 'materialization=off'; + +CREATE TABLE t1 ( c INT ) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (0),(1),(2),(3),(4),(5), + (6),(7),(8),(9),(10),(11),(12); +CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3,20),(2,21),(3,22); + +SELECT * +FROM t1 AS alias1, t1 AS alias2 +WHERE ( alias1.c, alias2.c ) + IN ( + SELECT alias3.a, alias3.a + FROM t2 AS alias3, t2 alias4 + WHERE alias3.b = alias4.b + ); +set optimizer_prune_level= @opl_901399; +set optimizer_switch= @os_091399; + +DROP TABLE t1,t2; + +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +--echo # +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5), + (6,3),(7,1),(8,4),(9,3),(10,2); + +CREATE TABLE t2 ( c INT, d INT, KEY(c) ); +INSERT INTO t2 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); + +analyze table t1,t2; +explain +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN + ( SELECT b, d FROM t1, t2 WHERE b = c ); +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN + ( SELECT b, d FROM t1, t2 WHERE b = c ); + +DROP TABLE t1, t2; + +--echo # Another testcase for the above that still uses LooseScan: + +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t10(a int primary key); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t1 ( + pk int primary key auto_increment, + kp1 int, + kp2 int, + filler char(100), + key (kp1, kp2) +); + +# 10 groups, each has 10 elements. +insert into t1 (kp1, kp2, filler) +select + A.a, B.a, 'filler-data' +from t0 A, t0 B; + +create table t2 (a int, filler char(100), key(a)); + +create table t3 (a int); +insert into t3 values (1),(2); + +insert into t2 +select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B; + +analyze table t1,t2,t3; +delete from t1 where kp2 in (1,3); + +--echo # Ref + LooseScan on t1: +explain select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); + +select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); + +drop table t0,t10; +drop table t1,t2,t3; + +--echo # +--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... +--echo # +# t1 should be MyISAM or InnoDB +CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g'); +CREATE TABLE t2 ( b INT, c VARCHAR(1) ); +INSERT INTO t2 VALUES (0,'j'),(8,'v'); + +#SET debug_optimizer_prefer_join_prefix= 'alias2,alias4,alias1,alias3'; + +SELECT * FROM t1 alias1, t2 alias2 +WHERE alias2.c IN ( + SELECT alias4.c FROM t1 alias3, t2 alias4 +); + +DROP TABLE t1, t2; + +--echo # +--echo # BUG#923246: Loosescan reports different result than other semijoin methods +--echo # +set @tmp_923246= @@optimizer_switch; +set optimizer_switch='mrr=on,materialization=off'; + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); +insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; +insert into t1 select * from t1 where kp1 < 20; + +create table t3 (a int); +insert into t3 select A.a + 10*B.a from t0 A, t0 B; + +select * from t3 where a in (select kp1 from t1 where kp1<20); +explain select * from t3 where a in (select kp1 from t1 where kp1<20); + +drop table t0,t1,t3; +set optimizer_switch= @tmp_923246; + +--echo # +--echo # BUG#952583: Server crashes in Item_field::fix_after_pullout on INSERT .. SELECT +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 ( b INT ); +INSERT INTO t2 VALUES (3),(4); + +INSERT INTO t1 +SELECT * FROM ( SELECT * FROM t1 ) AS alias +WHERE a IN ( SELECT b FROM t2 ); + +DROP TABLE t1, t2; + +--echo # +--echo # BUG#952372: Server crashes on 2nd execution of PS in find_field_in_tables with semijoin+materialization +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (2),(3); +CREATE VIEW v1 AS SELECT * FROM t1; + +CREATE TABLE t2 ( b VARCHAR(1) ); +INSERT INTO t2 VALUES ('v'),('v'); + +PREPARE pstmt FROM + 'SELECT DISTINCT a FROM v1, t2 + WHERE b IN ( SELECT MIN(b) FROM t2 )'; + +EXECUTE pstmt; +EXECUTE pstmt; + +DEALLOCATE PREPARE pstmt; +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # +--echo # BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF +--echo # + +set @tmp_jcl_978479= @@join_cache_level; +set join_cache_level=0; + +set @tmp_os_978479= @@optimizer_switch; +set optimizer_switch = 'derived_with_keys=on,loosescan=on,semijoin=on,materialization=off'; + +--echo # Part#1: make sure EXPLAIN is using LooseScan: +CREATE TABLE t1 ( a INT, b INT ); +INSERT INTO t1 VALUES + (4,0),(6,8),(3,1),(5,8),(3,9),(2,4), + (2,6),(9,1),(5,4),(7,7),(5,4); + +CREATE ALGORITHM=TEMPTABLE + VIEW v1 AS SELECT * FROM t1; + +--echo # This will use LooseScan: +EXPLAIN +SELECT * FROM t1 AS t1_1, t1 AS t1_2 + WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); + +SELECT * FROM t1 AS t1_1, t1 AS t1_2 + WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); + +DROP VIEW v1; +DROP TABLE t1; +set @@join_cache_level= @tmp_jcl_978479; +set @@optimizer_switch= @tmp_os_978479; + +--echo # +--echo # BUG#998236: Assertion failure or valgrind errors at best_access_path ... +--echo # +CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'), +('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'), +('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'), +('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'), +('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'), +('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'), +('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'), +('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'), +('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'), +('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'), +('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'), +('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'), +('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'), +('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'), +('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'), +('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'), +('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'), +('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'), +('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'), +('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'), +('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'), +('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'), +('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'), +('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'), +('USA','Inglewood'),('USA','Livonia'); + +CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English'); + +# Not reproducible with 2 rows +CREATE TABLE t2 (b1 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +SELECT alias1.a1 AS field1 +FROM t1 AS alias1, t1 AS alias2 +WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 ) +AND alias1.a1 IS NULL +AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 ) +GROUP BY field1; + +DROP TABLE t1,t3,t2; + +--echo # +--echo # BUG#1002630: Valgrind warnings 'Invalid read' in subselect_engine::calc_const_tables with SELECT +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(6); + +SELECT ( SELECT SUM(a) FROM t1 ) AS t1sum, b +FROM t2 +WHERE (1,1) IN ( SELECT MAX(a), MIN(a) FROM t1 ) +GROUP BY b +HAVING t1sum <> 1; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-3911: Assertion `fixed == 0' failed in Item_field::fix_fields +--echo # on 2nd execution of PS with semijoin=on and IN subquery +--echo # + +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,4),(8,6); + +CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7,1),(0,7); + +eval PREPARE stmt FROM ' SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) '; + +execute stmt; +execute stmt; + +deallocate prepare stmt; +drop table t1,t2; + +--echo # +--echo # MDEV-4335: Unexpected results when selecting on information_schema +--echo # +CREATE TABLE t1 (db VARCHAR(64) DEFAULT NULL); +INSERT INTO t1 VALUES ('mysql'),('information_schema'); +--sorted_result +SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA); +DROP TABLE t1; + +--echo # +--echo # MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (2),(3); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); + +CREATE TABLE t3 (c INT, INDEX(c)); +INSERT INTO t2 VALUES (5),(6); + +PREPARE stmt FROM +"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )"; +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MySQL Bug#13340270: assertion table->sort.record_pointers == __null +--echo # + +CREATE TABLE t1 ( + pk int NOT NULL, + col_int_key int DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + col_varchar_nokey varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_int_key (col_int_key), + KEY col_varchar_key (col_varchar_key, col_int_key) +); + +INSERT INTO t1 VALUES +(10,8,'x','x'), +(11,7,'d','d'), +(12,1,'r','r'), +(13,7,'f','f'), +(14,9,'y','y'), +(15,NULL,'u','u'), +(16,1,'m','m'), +(17,9,NULL,NULL), +(18,2,'o','o'), +(19,9,'w','w'), +(20,2,'m','m'), +(21,4,'q','q'); + +let $query= + SELECT alias1.col_varchar_nokey AS field1 + FROM t1 AS alias1 JOIN t1 AS alias2 + ON alias2.col_int_key = alias1.pk OR + alias2.col_int_key = alias1.col_int_key + WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' +; + +eval CREATE TABLE t2 + $query +; + +eval EXPLAIN SELECT * +FROM t2 +WHERE (field1) IN ($query); + +eval SELECT * +FROM t2 +WHERE (field1) IN ($query); + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-389: Wrong result (missing row) with semijoin, join_cache_level>4, LEFT JOIN... +--echo # (testcase only) +--echo # + +# Use another connection to get rid of this test's settings +connect (con1,localhost,root,,); +connection con1; + +SET join_cache_level = 5; +SET optimizer_switch = 'semijoin=on'; + +CREATE TABLE t1 (a INT NOT NULL, b CHAR(1), KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4,'p'),(1,'q'),(8,'e'); + +CREATE TABLE t2 (c INT, d CHAR(1), KEY(c), KEY(d)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'f'),(2,'i'),(5,'h'),(3,'q'),(1,'g'); + +SELECT a, COUNT(*) AS cnt +FROM t1 LEFT JOIN t2 ON (d = b) +WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) +GROUP BY a ORDER BY a, cnt LIMIT 2; + +drop table t1, t2; +connection default; +disconnect con1; + +--echo # +--echo # MDEV-4071: Valgrind warnings 'Invalid read' in subselect_engine::calc_const_tables with ... +--echo # +CREATE TABLE t1 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7,'v'),(0,'s'); + +CREATE TABLE t2 (a INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0),(8); + +SELECT c, SUM( DISTINCT b ) AS sm FROM t1 +WHERE ( 5, 108 ) IN ( SELECT MIN(a), MAX(a) FROM t2 ) +GROUP BY b +HAVING c <> ( SELECT MAX( c ) FROM t1 ) +ORDER BY sm; + +DROP TABLE t1,t2; + +--echo # +--echo # mdev-4173 ignored duplicate value when converting heap to temp table +--echo # could lead to extra rows in semijoin queries or missing row in union queries +--echo # +CREATE TABLE t1 (i1 INT) engine=myisam; +INSERT INTO t1 VALUES +(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4); + +CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam; +INSERT INTO t2 VALUES +(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4), +(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6); + +CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam; +INSERT INTO t3 VALUES +(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1); + +select @@max_heap_table_size into @tmp_max_heap_table_size; +select @@join_buffer_size into @tmp_join_buffer_size; +set max_heap_table_size=16*1024; + +--disable_query_log +let $n = 8; +while ($n) { + eval set join_buffer_size= 128 + 128*$n; + SELECT COUNT(*) FROM t1 outer_t1, t2 outer_t2, t3 + WHERE outer_t1.i1 IN ( + SELECT j2 FROM t2 LEFT JOIN t3 ON ( i3 = j2 ) + WHERE i2 <> outer_t2.j2 + ); + dec $n; +} +--enable_query_log + +DROP TABLE t1, t2, t3; +set join_buffer_size = @tmp_join_buffer_size; +set max_heap_table_size = @tmp_max_heap_table_size; + +--echo # +--echo # MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x'); + +CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('x'); + +CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('x'),('d'); + +--sorted_result +SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; + +--echo # This should show that "t1 left join t3" is still in the semi-join nest: +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-5059: Wrong result (missing row) wih semijoin, join_cache_level > 2, LEFT JOIN, ORDER BY +--echo # + +SET @tmp_mdev5059=@@join_cache_level; +# Any value > 2 will do +SET join_cache_level = 3; +set @tmp_os_mdev5059= @@optimizer_switch; +set optimizer_switch=@local_optimizer_switch; +CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,4),(2,5); + +CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'v'),(7,'s'); + +CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q'); + +CREATE TABLE t4 (i4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1); + +SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) +WHERE ( i2, pk1 ) IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2; + +SET join_cache_level=@tmp_mdev5059; +set optimizer_switch=@tmp_os_mdev5059; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # MDEV-7911: crash in Item_cond::eval_not_null_tables +--echo # + +create table t1(a int); +insert into t1 values(1),(2),(3),(null); +--error ER_CANT_AGGREGATE_2COLLATIONS +explain +select 1 from t1 where _cp932 "1" in (select '1' from t1); +drop table t1; + +--echo # +--echo # MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +--echo # +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); + +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); + +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); + +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); + +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; + +--echo # +--echo # MDEV-6859: scalar subqueries in a comparison produced unexpected result +--echo # +set @tmp_mdev6859=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 ( + project_number varchar(50) NOT NULL, + PRIMARY KEY (project_number) +) ENGINE=MyISAM; + +INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb'); + +CREATE TABLE t2 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + project_number varchar(50) NOT NULL, + history_date date NOT NULL, + country varchar(50) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; + +INSERT INTO t2 (id, project_number, history_date, country) VALUES +(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore'); + +CREATE TABLE t3 ( + region varchar(50) NOT NULL, + country varchar(50) NOT NULL +) ENGINE=MyISAM; + +INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france'); + +SELECT SQL_NO_CACHE a.project_number +FROM t1 a +WHERE ( SELECT z.country + FROM t2 z + WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' + ORDER BY z.id DESC LIMIT 1 + ) IN ( + SELECT r.country + FROM t3 r + WHERE r.region = 'eame' + ); + +drop table t1, t2, t3; +set optimizer_switch= @tmp_mdev6859; + +--echo # +--echo # MDEV-12675: subquery subject to semi-join optimizations +--echo # in ON expression of INNER JOIN +--echo # + +set @tmp_mdev12675=@@optimizer_switch; +set optimizer_switch=default; +create table t1 (a int) engine=myisam; +insert into t1 values (3),(2),(7),(2),(1); +create table t2 (b int, index idx(b)) engine=myisam; +insert into t2 values (2),(3),(2),(1),(3),(4),(1),(2),(1),(2); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +analyze table t1,t2; + +explain +select a from t1, t2 where b between 1 and 2 and a in (select b from t2); +explain +select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); + +drop table t1,t2; +set optimizer_switch= @tmp_mdev12675; + +--echo # +--echo # MDEV-12817: subquery NOT subject to semi-join optimizations +--echo # in ON expression of INNER JOIN +--echo # + +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); + +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); + +let $q1= +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); + +eval $q1; +eval EXPLAIN EXTENDED $q1; + +let $q2= +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); + +--echo # mdev-12820 +eval $q2; +eval EXPLAIN EXTENDED $q2; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # MDEV-13699: Assertion `!new_field->field_name.str || +--echo # strlen(new_field->field_name.str) == new_field->field_name.length' +--echo # failed in create_tmp_table on 2nd execution of PS with semijoin +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c INT); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (5),(6); + +PREPARE stmt FROM + "SELECT * FROM t1 + WHERE EXISTS ( + SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 ) + )"; +EXECUTE stmt; +EXECUTE stmt; +EXECUTE stmt; + +drop view v3; +drop table t1,t2,t3; + +--echo # +--echo # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT +--echo # + +set @@optimizer_switch= @subselect_sj_tmp; + +create table t1 (a1 varchar(25)); +create table t2 (a2 varchar(25)) ; +insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); + +drop table t1,t2; + + +--echo # +--echo # MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key) +--echo # + +CREATE TABLE `t1` ( + `Id` int(11) NOT NULL, + PRIMARY KEY (`Id`) +); + +INSERT INTO `t1` (`Id`) VALUES (1); + +CREATE TABLE `t2` ( + `t1_Id` int(11) NOT NULL DEFAULT 0, + `col1` int(11) DEFAULT NULL, + UNIQUE KEY `col1` (`col1`) +); + +INSERT INTO `t2` (`t1_Id`, `col1`) VALUES (1, NULL), (1, NULL); + +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +explain extended +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); + +DROP TABLE t1, t2; + +--echo # End of 5.5 test + +--echo # +--echo # MDEV-20109: Optimizer ignores distinct key created for materialized +--echo # semi-join subquery when searching for best execution plan +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; + +--echo # The following must not use this query plan that does a cross join: +--echo # | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +--echo # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +--echo # +--echo # Instead, it should use eq_ref on the materialized table. + +explain select * from t3 where a in (select a from t4); + +drop table t1, t2, t3, t4; + +--echo # +--echo # MDEV-20770: Server crashes in JOIN::transform_in_predicates_into_in_subq +--echo # upon 2nd execution of PS/SP comparing GEOMETRY with other types +--echo # + +CREATE TABLE t1 (a GEOMETRY); +CREATE TABLE t2 (b INT); + +INSERT INTO t1 VALUES (GeomFromText('POINT(0 0)')),(GeomFromText('POINT(1 1)')); +INSERT INTO t2 VALUES (1),(2); + +PREPARE stmt FROM "SELECT * from t1 WHERE a IN (SELECT b FROM t2)"; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-28871: Assert ... failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth +--echo # +set @tmp_28871=@@optimizer_search_depth; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); + +# Data is optional, fails either way +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t1 VALUES (3),(4); + +SET optimizer_search_depth= 1; + +SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1); +DROP TABLE t1, t2; +set optimizer_search_depth= @tmp_28871; + +# The following command must be the last one the file +set optimizer_switch=@subselect_sj_tmp; |