diff options
Diffstat (limited to 'mysql-test/t/subselect_sj2.test')
| -rw-r--r-- | mysql-test/t/subselect_sj2.test | 146 |
1 files changed, 139 insertions, 7 deletions
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index ede631f32be..92fc500cf55 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -78,13 +78,7 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; -# -# InnoDB does not use join buffer here, XtraDB does -# (despite the comment above which says "no join buffering", -# because it does not hold when this file is included -# into subselect_sj2_jcl6.test) -# -#--replace_regex /Using join buffer// +--replace_column 9 # explain select * from t3 where b in (select a from t0); select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -1093,5 +1087,143 @@ SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b); DROP TABLE t1,t2; +--echo # +--echo # BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() +--echo # with index_merge+index_merge_sort_union+loosescan+semijoin +--echo # +CREATE TABLE t1 ( + a INT, b VARCHAR(1), c INT, + KEY(a), KEY(b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES +(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), +(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), +(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), +(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); + +CREATE TABLE t2 ( + pk INT, d VARCHAR(1), e INT, + PRIMARY KEY(pk), KEY(d,e) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES +(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), +(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), +(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), +(15,'g',6),(16,'x',7),(17,'f',8); + +explain +SELECT * FROM t1 WHERE b IN ( + SELECT d FROM t2, t1 + WHERE a = d AND ( pk < 2 OR d = 'z' ) +); +SELECT * FROM t1 WHERE b IN ( + SELECT d FROM t2, t1 + WHERE a = d AND ( pk < 2 OR d = 'z' ) +); + +DROP TABLE t1, t2; + +--echo # +--echo # BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +--echo # +CREATE TABLE t1 ( + a VARCHAR(1), + b VARCHAR(1) NOT NULL, + KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +--echo # This query returned 6 rows instead of 19 +--sorted_result +SELECT * FROM v1 +WHERE ( a, a ) IN ( + SELECT alias2.b, alias2.a + FROM t1 AS alias1, t1 AS alias2 + WHERE alias2.b = alias1.a + AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); + +--echo # Another testcase, without the VIEW: +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t2 SELECT * FROM t1; +EXPLAIN +SELECT * FROM t2 + WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 + WHERE + alias2.b = alias1.a AND + (alias1.b >= alias1.a OR alias2.b = 'z')); + +--sorted_result +SELECT * FROM t2 + WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 + WHERE + alias2.b = alias1.a AND + (alias1.b >= alias1.a OR alias2.b = 'z')); + +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # +--echo # BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR +--echo # (this is a regression caused by the fix for BUG#951937) +CREATE TABLE t1 ( a INT, b INT, c INT, d INT ); +INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8); + +SELECT * FROM t1 +WHERE a = d AND ( b = 50 AND b = d OR a = c ); +DROP TABLE t1; + +--echo # +--echo # BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery +--echo # +set @tmp_951283=@@optimizer_prune_level; +SET optimizer_prune_level=0; + +CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; +INSERT INTO t1 VALUES + (10),(11),(12),(13),(14),(15),(16), + (17),(18),(19),(20),(21),(22),(23); + +CREATE TABLE t2 ( + b INT PRIMARY KEY, + c VARCHAR(1), + d VARCHAR(1), + KEY(c) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES + (1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'), + (5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'), + (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), + (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), + (17,'q','q'),(18,'w','w'),(19,'d','d'); + +EXPLAIN +SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 +WHERE alias3.d IN ( + SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 + WHERE alias5.b = alias4.b + AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) +); + +SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 +WHERE alias3.d IN ( + SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 + WHERE alias5.b = alias4.b + AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) +); + +set optimizer_prune_level=@tmp_951283; +DROP TABLE t1,t2; + --echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; |