diff options
Diffstat (limited to 'mysql-test/r/join.result')
| -rw-r--r-- | mysql-test/r/join.result | 135 |
1 files changed, 127 insertions, 8 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 25dcb1cc5c0..e95ed8c7cf2 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3; +drop view if exists v1,v2; CREATE TABLE t1 (S1 INT); CREATE TABLE t2 (S1 INT); INSERT INTO t1 VALUES (1); @@ -404,7 +405,7 @@ SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e ORDER BY t1.b, t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer +1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e ORDER BY t1.b, t1.c; e @@ -846,7 +847,7 @@ select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; a b a a explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index We expect rnd_next=5, and read_key must be 0 because of short-cutting: @@ -890,8 +891,8 @@ Z vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1 show status like '%cost%'; Variable_name Value @@ -911,7 +912,7 @@ INSERT INTO t1 SELECT a + 64, b FROM t1; INSERT INTO t2 SELECT a, b FROM t1; EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra @@ -925,7 +926,7 @@ a b c d 2 NULL 2 NULL EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using where; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra @@ -1096,11 +1097,11 @@ ON t4.a = t5.a ON t1.a = t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t3 ref a a 5 test.t1.a 2 Using index +1 SIMPLE t3 ref a a 5 test.t1.a 2 Using where; Using index 1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 0 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) SELECT * FROM t1 JOIN t2 ON t1.a = t2.a @@ -1221,4 +1222,122 @@ f1 2 DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug LP:798597: Incorrect "Duplicate entry" error with views and +# GROUP BY +# +CREATE TABLE t1 ( f1 int NOT NULL , f2 int NOT NULL ) ; +INSERT INTO t1 VALUES (214,0),(6,6); +CREATE TABLE t2 ( f2 int) ; +INSERT INTO t2 VALUES (88),(88); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0) ; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0 or t1.f2 is null) ; +SELECT f1 , MIN(f2) FROM v1 GROUP BY f1; +f1 MIN(f2) +214 88 +SELECT f1 , MIN(f2) FROM v2 GROUP BY f1; +f1 MIN(f2) +214 88 +drop table t1,t2; +drop view v1,v2; +# +# BUG#47217 Lost optimization caused slowdown & wrong result. +# +CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk)); +CREATE INDEX ix1 ON t1(v); +CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk)); +CREATE INDEX ix2 ON t2(v); +INSERT INTO t1 VALUES (1,'a'),(2,NULL); +INSERT INTO t2 VALUES (1,NULL); +EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +INSERT INTO t1 VALUES (3,'b'),(4,NULL),(5,'c'),(6,'cc'),(7,'d'), +(8,'dd'),(9,'e'),(10,'ee'); +INSERT INTO t2 VALUES (2,NULL); +FLUSH STATUS; +SELECT * FROM t1 JOIN t2 ON t1.v = t2.v WHERE t2.v IS NULL ORDER BY 1; +pk v pk v +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 2 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1 +DROP TABLE t1, t2; End of 5.1 tests +# +# BUG#724275: Crash in JOIN::optimize in maria-5.3 +# +create table t1 (a int); +insert into t1 values (1),(2); +insert into t1 select * from t1; +create table t2 (a int, b int, key(a,b)); +insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9); +insert into t2 select * from t2; +insert into t2 select * from t2; +insert into t2 select * from t2; +create table t3 (a int, b int, key(a)); +insert into t3 values (1,1),(2,2); +select * from +t3 straight_join t1 straight_join t2 force index(a) +where t2.a=1 and t2.b=t1.a and t1.a=t3.b and t3.a=1; +a b a a b +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +drop table t1,t2,t3; +# +# BUG#729067/730466: unexpected 'Range checked for each record' +# for queries with OR in WHERE clause +# +CREATE TABLE t1 (f1 int, f2 int) ; +INSERT INTO t1 VALUES (4,0),(5,1); +CREATE TABLE t2 (f1 int, f2 int, KEY (f2)) ; +INSERT INTO t2 VALUES (5,7), (8,9); +EXPLAIN +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ALL f2 NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; +f1 f2 f1 f2 +5 1 5 7 +DROP TABLE t1,t2; +CREATE TABLE t1(f1 int PRIMARY KEY, f2 int) ; +INSERT INTO t1 VALUES (9,4), (10,9); +CREATE TABLE t2(f1 int PRIMARY KEY, f2 int) ; +INSERT INTO t2 VALUES (9,4), (10,9); +EXPLAIN +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; +f1 f2 f1 f2 +9 4 10 9 +DROP TABLE t1,t2; |