bk commit into 4.1 tree (igor:1.2347) BUG#12144

[igor]

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2347 05/07/28 13:31:15 igor@stripped +3 -0
  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.

  sql/sql_select.cc
    1.423 05/07/28 13:24:42 igor@stripped +8 -1
    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.

  mysql-test/r/null_key.result
    1.29 05/07/28 13:21:06 igor@stripped +31 -0
    Added a test case for bug #12144.

  mysql-test/t/null_key.test
    1.10 05/07/28 13:20:08 igor@stripped +26 -0
    Added a test case for bug #12144.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-4.1-0

--- 1.422/sql/sql_select.cc	Tue Jul 19 09:24:59 2005
+++ 1.423/sql/sql_select.cc	Thu Jul 28 13:24:42 2005
@@ -2255,7 +2255,9 @@
   */
   (*key_fields)->null_rejecting= (cond->functype() == Item_func::EQ_FUNC)
&&
                                  ((*value)->type() == Item::FIELD_ITEM) &&
-                                 ((Item_field*)*value)->field->maybe_null();
+
+                        (((Item_field*)*value)->field->maybe_null() ||
+			 ((Item_field *)*value)->field->table->maybe_null);
   (*key_fields)++;
 }
 
@@ -6310,6 +6312,11 @@
   int error;
   TABLE *table= tab->table;
 
+  for (uint i= 0 ; i < tab->ref.key_parts ; i++)
+  {
+    if ((tab->ref.null_rejecting & 1 << i) &&
tab->ref.items[i]->is_null())
+        return -1;
+  } 
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key);
   if (cp_buffer_from_ref(tab->join->thd, &tab->ref))

--- 1.28/mysql-test/r/null_key.result	Tue Sep  7 12:30:23 2004
+++ 1.29/mysql-test/r/null_key.result	Thu Jul 28 13:21:06 2005
@@ -364,3 +364,34 @@
 id	id2
 1	1
 drop table t1;
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+LEFT JOIN t3 ON t2.b=t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t2.b	1	Using index
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+LEFT JOIN t3 ON t2.b=t3.b;
+a	a	b	b
+1	1	1	NULL
+2	NULL	NULL	NULL
+3	3	1	NULL
+4	NULL	NULL	NULL
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+DROP TABLE t1,t2,t3;

--- 1.9/mysql-test/t/null_key.test	Tue Dec  9 20:30:37 2003
+++ 1.10/mysql-test/t/null_key.test	Thu Jul 28 13:20:08 2005
@@ -191,3 +191,29 @@
 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;