Merge magare.gmz:/home/kgeorge/mysql/work/B27531-5.0-opt

into  magare.gmz:/home/kgeorge/mysql/work/B27531-5.1-opt

1 files changed, 20 insertions, 0 deletions

diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 10856142701..0a29b4cb325 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -831,3 +831,23 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
 SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
 
 DROP TABLE t1,t2;
+
+#
+# Bug 28188: 'not exists' optimization for outer joins 
+#
+ 
+CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
+CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
+INSERT INTO t1 VALUES
+  (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
+INSERT INTO t2 VALUES
+  (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
+
+EXPLAIN
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+show status like 'Handler_read%';
+
+DROP TABLE t1,t2;