diff options
Diffstat (limited to 'mysql-test/t/join.test')
| -rw-r--r-- | mysql-test/t/join.test | 19 |
1 files changed, 19 insertions, 0 deletions
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 43b373c9703..cb865528aa2 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -851,4 +851,23 @@ ENGINE=MERGE UNION=(t1,t2); SELECT t1.a FROM mm1,t1; DROP TABLE t1, t2, mm1; +--echo # +--echo # BUG#47217 Lost optimization caused slowdown & wrong result. +--echo # +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; +EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v; +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; +SHOW STATUS LIKE 'Handler_read_%'; +DROP TABLE t1, t2; + --echo End of 5.1 tests |