summaryrefslogtreecommitdiff
path: root/mysql-test/t/subselect_sj.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/subselect_sj.test')
-rw-r--r--mysql-test/t/subselect_sj.test314
1 files changed, 314 insertions, 0 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index f34cf5ba338..6b8a757b9e8 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1136,6 +1136,262 @@ WHERE (int_nokey, pk)
DROP TABLE t1, t2, t3;
+--echo #
+--echo # BUG#53060: LooseScan semijoin strategy does not return all rows
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=on,materialization=off';
+set optimizer_switch='firstmatch=off,loosescan=on';
+
+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=@save_optimizer_switch;
+
+--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 @save_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=@save_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 @save_optimizer_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=@save_optimizer_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 @save_optimizer_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=@save_optimizer_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 @save_optimizer_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=@save_optimizer_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 @save_optimizer_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=@save_optimizer_switch;
+
+DROP TABLE t1, t2, t3 ;
+
+--echo #
--echo #
--echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3
--echo #
@@ -2017,5 +2273,63 @@ SELECT a, COUNT(*) FROM t1
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, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+ (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+
+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 #
+--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;
+
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
View Lorry Controller Status