bzr commit into mysql-5.5-bugfixing branch (martin.hansson:3204) Bug#37333

[Martin Hansson]

#At file:///data0/martin/bzr/bug37333/5.5bf/ based on revid:mats.kindahl@stripped

 3204 Martin Hansson	2010-09-28
      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 cost for it. Since range access estimates are reused for ref type access,
      this lead to a wrong (too low) cost value for the latter access type.
            
      Fixed by removing the bespoke assumption and thus calculating a cost for the
      range scan anyway.

    modified:
      mysql-test/r/range.result
      mysql-test/t/range.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2010-08-26 12:35:38 +0000
+++ b/mysql-test/r/range.result	2010-09-28 08:41:24 +0000
@@ -1667,3 +1667,36 @@ c_key	c_notkey
 3	3
 DROP TABLE t1;
 End of 5.1 tests
+#
+# 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;
+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
+FLUSH STATUS;
+SELECT COUNT(*) 
+FROM t1 LEFT JOIN t2 USING ( a )
+WHERE t2.b IS NULL AND t2.a = 1000;
+COUNT(*)
+11
+SHOW STATUS LIKE 'Handler_read_next';
+Variable_name	Value
+Handler_read_next	11
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2010-08-24 15:51:32 +0000
+++ b/mysql-test/t/range.test	2010-09-28 08:41:24 +0000
@@ -1326,3 +1326,38 @@ SELECT * FROM t1 WHERE 2 NOT BETWEEN c_n
 DROP TABLE t1;
 
 --echo End of 5.1 tests
+--echo #
+--echo # Bug#37333: Optimizer uses wrong index for the right table in LEFT JOIN
+--echo #
+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;
+
+FLUSH STATUS;
+
+SELECT COUNT(*) 
+FROM t1 LEFT JOIN t2 USING ( a )
+WHERE t2.b IS NULL AND t2.a = 1000;
+
+SHOW STATUS LIKE 'Handler_read_next';
+
+DROP TABLE t1, t2;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-08-26 12:35:38 +0000
+++ b/sql/opt_range.cc	2010-09-28 08:41:24 +0000
@@ -5765,8 +5765,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)


Attachment: [text/bzr-bundle] bzr/[e-mail]