Merge.

1 files changed, 19 insertions, 0 deletions

diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 05d630edfb2..cf431ace24c 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -921,4 +921,23 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 
+--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