bzr commit into mysql-5.1 branch (mhansson:2664) Bug#37333

[Martin Hansson]

#At file:///data0/martin/bzr/5.1bt-bug37333/

 2664 Martin Hansson	2008-06-17
      Bug#37333: Optimizer uses wrong index for the right table in 
      LEFT JOIN
      
      The range optimizer assumed that an index can't be used on an
      IS NULL predicate on the outer table in an outer join, and refrained
      from calculating the correct cost. This lead to a wrong (too low) 
      cost value for this type of access.
      
      Fixed by not assuming that indexes can't be used for such cases.
modified:
  mysql-test/r/range.result
  mysql-test/t/range.test
  sql/opt_range.cc

per-file messages:
  mysql-test/r/range.result
    Bug#37333: Test result.
  mysql-test/t/range.test
    Bug#37333: Test case.
  sql/opt_range.cc
    Bug#37333: The fix: We can't assume that an index won't be used in this case.
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2008-03-27 02:18:46 +0000
+++ b/mysql-test/r/range.result	2008-06-17 14:35:45 +0000
@@ -1219,3 +1219,23 @@ explain select * from t2 where a=1000 an
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using where
 drop table t1, t2;
+CREATE TABLE t1 ( a INT UNIQUE );
+CREATE TABLE t2 ( a INT, b INT DEFAULT NULL, KEY ( a ), KEY ( b ) );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+INSERT INTO t1 VALUES (1000);
+INSERT INTO t2( a ) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+INSERT INTO t2( a ) SELECT a + 10   FROM t2;
+INSERT INTO t2( a ) SELECT a + 20   FROM t2;
+INSERT INTO t2( a ) SELECT a + 40   FROM t2;
+INSERT INTO t2( a ) SELECT a + 80   FROM t2;
+INSERT INTO t2( a ) SELECT a + 160  FROM t2;
+INSERT INTO t2( a ) VALUES (1000),(1000),(1000),(1000),(1000),
+(1000),(1000),(1000),(1000),(1000),
+(1000);
+EXPLAIN
+SELECT COUNT(*) 
+FROM t1 LEFT JOIN t2 USING ( a )
+WHERE t2.b IS NULL AND t2.a = 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	a	a	5	const	1	Using index
+1	SIMPLE	t2	ref	a,b	a	5	const	15	Using where

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2008-03-27 02:18:46 +0000
+++ b/mysql-test/t/range.test	2008-06-17 14:35:45 +0000
@@ -1046,3 +1046,28 @@ explain select * from t2 where a=1000 an
 
 drop table t1, t2;
 
+#
+# Bug#37333: Optimizer uses wrong index for the right table in LEFT JOIN
+#
+CREATE TABLE t1 ( a INT UNIQUE ); 
+CREATE TABLE t2 ( a INT, b INT DEFAULT NULL, KEY ( a ), KEY ( b ) );
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+INSERT INTO t1 VALUES (1000);
+
+INSERT INTO t2( a ) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+INSERT INTO t2( a ) SELECT a + 10   FROM t2;
+INSERT INTO t2( a ) SELECT a + 20   FROM t2;
+INSERT INTO t2( a ) SELECT a + 40   FROM t2;
+INSERT INTO t2( a ) SELECT a + 80   FROM t2;
+INSERT INTO t2( a ) SELECT a + 160  FROM t2;
+
+INSERT INTO t2( a ) VALUES (1000),(1000),(1000),(1000),(1000),
+                           (1000),(1000),(1000),(1000),(1000),
+                           (1000);
+
+EXPLAIN
+SELECT COUNT(*) 
+FROM t1 LEFT JOIN t2 USING ( a )
+WHERE t2.b IS NULL AND t2.a = 1000;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2008-04-14 10:58:53 +0000
+++ b/sql/opt_range.cc	2008-06-17 14:35:45 +0000
@@ -5576,8 +5576,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
   param->thd->mem_root= param->old_root;
   if (!value)					// IS NULL or IS NOT NULL
   {
-    if (field->table->maybe_null)		// Can't use a key on this
-      goto end;
     if (!maybe_null)				// Not null field
     {
       if (type == Item_func::ISNULL_FUNC)