diff options
Diffstat (limited to 'mysql-test/t/subselect_sj.test')
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 650 |
1 files changed, 644 insertions, 6 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 33f3e936482..66c7b1bc549 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -3,8 +3,16 @@ # --disable_warnings drop table if exists t0, t1, t2, t3, t4, 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'; +# The 'default' value within the scope of this test: +set @save_optimizer_switch=@@optimizer_switch; + # # 1. Subqueries that are converted into semi-joins # @@ -224,7 +232,8 @@ INSERT INTO WORKS VALUES ('E4','P2',20); INSERT INTO WORKS VALUES ('E4','P4',40); INSERT INTO WORKS VALUES ('E4','P5',80); -set optimizer_switch='default,materialization=off'; +set optimizer_switch=@save_optimizer_switch; +set optimizer_switch='materialization=off'; explain SELECT EMPNUM, EMPNAME FROM STAFF @@ -240,7 +249,7 @@ WHERE EMPNUM IN WHERE PNUM IN (SELECT PNUM FROM PROJ)); -set optimizer_switch='default'; +set optimizer_switch=@save_optimizer_switch; drop table STAFF,WORKS,PROJ; @@ -308,7 +317,7 @@ 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 ( @@ -322,7 +331,7 @@ 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 @@ -359,7 +368,7 @@ drop table t1, t2; drop view v1; drop procedure p1; -set SESSION optimizer_switch='default'; +set SESSION optimizer_switch=@save_optimizer_switch; --echo # End of bug#46744 @@ -526,7 +535,7 @@ DROP TABLE t1,t2; DROP VIEW v1,v2; DROP PROCEDURE p1; -set SESSION optimizer_switch='default'; +set SESSION optimizer_switch=@save_optimizer_switch; --echo # End of BUG#48834 @@ -640,6 +649,7 @@ CREATE TABLE it1 ( ); 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); @@ -935,3 +945,631 @@ 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 @save_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=@save_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#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; + +# The following command must be the last one the file +set optimizer_switch=@subselect_sj_tmp; |