sql_select.cc:

  Fixed bug #12144.
  Added an optimization that avoids key access with null keys for the 'ref'
  method when used in outer joins. The regilar optimization with adding
  IS NOT NULL expressions is not applied for outer join on expressions as
  the predicates of these expressions are not pushed down in 4.1.
null_key.result, null_key.test:
  Added a test case for bug #12144.

1 files changed, 26 insertions, 0 deletions

diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 9b346a181bf..0eff53ac371 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -191,3 +191,29 @@ select * from t1 where id2 is null or id2 > 0;
 delete from t1 where id <=> NULL;
 select * from t1;
 drop table t1;
+
+#
+# Test for bug #12144: optimizations for key access with null keys 
+#                      used for outer joins
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+CREATE TABLE t3 (b int, INDEX idx(b));
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (1, 1), (3, 1);
+INSERT INTO t3 VALUES 
+  (NULL), (NULL), (NULL), (NULL), (NULL),
+  (NULL), (NULL), (NULL), (NULL), (NULL),
+  (2);
+ANALYZE table t1, t2, t3;
+
+EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+                                             LEFT JOIN t3 ON t2.b=t3.b;
+
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+                                     LEFT JOIN t3 ON t2.b=t3.b;
+
+SELECT FOUND_ROWS();
+
+DROP TABLE t1,t2,t3;