diff options
Diffstat (limited to 'mysql-test/r/select.result')
| -rw-r--r-- | mysql-test/r/select.result | 42 |
1 files changed, 40 insertions, 2 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index b5d059be4c5..0c62d3f570f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2328,9 +2328,9 @@ explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t4 const id4 NULL NULL NULL 1 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1 -1 SIMPLE t4 ALL id4 NULL NULL NULL 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id1 id2 id3 id4 id44 @@ -3479,3 +3479,41 @@ Warning 1466 Leading spaces are removed from name ' a ' execute stmt; a 1 +CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); +CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); +INSERT INTO t2 VALUES +(1), (1), (1), (1), (1), (1), (1), (1), +(2), (2), (2), (2), +(3), (3), +(4); +EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 ref idx idx 4 const 7 Using index +EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 ref idx idx 4 const 1 Using index +DROP TABLE t1, t2; +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); +INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); +CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); +INSERT INTO t2 VALUES (2,1), (3,2); +CREATE TABLE t3 (d int, e int, INDEX idx1(d)); +INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50); +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id +WHERE t1.id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const idx1 NULL NULL NULL 1 +1 SIMPLE t3 ref idx1 idx1 5 const 3 Using where +SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id +WHERE t1.id=2; +id a b c d e +2 NULL NULL NULL 2 10 +2 NULL NULL NULL 2 20 +2 NULL NULL NULL 2 40 +2 NULL NULL NULL 2 50 +DROP TABLE t1,t2,t3; |