bk commit into 5.0 tree (igor:1.2595) BUG#32815

[igor]

Below is the list of changes that have just been committed into a local
5.0 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@stripped, 2007-12-07 17:14:59-08:00, igor@stripped +3 -0
  Fixed bug #32815.
  The index (key_part_1, key_part-2) was erroneously considered as compatible
  with the required ordering in the function test_test_if_order_by_key when 
  a query with an ORDER BY clause contained a condition of the form
    key_part_1=const OR key_part_1 IS NULL 
  and the order list contained only key_part_2. This happened because the value
  of the const_key_parts field in the KEYUSE structure was not formed correctly
  for the keys that could be used for ref_or_null access. 
  This was fixed in the code of the update_ref_and_keys function.
  The problem could not manifest itself for MyISAM databases because the
  implementation of the keys_to_use_for_scanning() handler function always
  returns an empty bitmap for the MyISAM engine.

  mysql-test/r/innodb_mysql.result@stripped, 2007-12-07 17:14:54-08:00, igor@stripped
+12 -0
    Added a test case for bug #32815.

  mysql-test/t/innodb_mysql.test@stripped, 2007-12-07 17:14:54-08:00, igor@stripped +15
-0
    Added a test case for bug #32815.

  sql/sql_select.cc@stripped, 2007-12-07 17:14:54-08:00, igor@stripped +1 -1
    Fixed bug #32815.
    The index (key_part_1, key_part-2) was erroneously considered as compatible
    with the required ordering in the function test_test_if_order_by_key when 
    a query with an ORDER BY clause contained a condition of the form
      key_part_1=const OR key_part_1 IS NULL 
    and the order list contained only key_part_2. This happened because the value
    of the const_key_parts field in the KEYUSE structure was not formed correctly
    for the keys that could be used for ref_or_null access. 
    This was fixed in the code of the update_ref_and_keys function.

diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-11-29 21:32:02 -08:00
+++ b/mysql-test/r/innodb_mysql.result	2007-12-07 17:14:54 -08:00
@@ -1213,4 +1213,16 @@ a	b
 3	2
 1	1
 DROP TABLE t1;
+CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
+EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	Using where; Using filesort
+SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+id	type	d
+191	member	1
+NULL	member	3
+NULL	member	4
+DROP TABLE t1;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
--- a/mysql-test/t/innodb_mysql.test	2007-10-10 12:15:55 -07:00
+++ b/mysql-test/t/innodb_mysql.test	2007-12-07 17:14:54 -08:00
@@ -960,4 +960,19 @@ SELECT * FROM t1 ORDER BY b DESC, a ASC;
 
 DROP TABLE t1;
 
+#
+# Bug #32815: query with ORDER BY and a possible ref_or_null access
+#
+
+CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+  (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); 
+
+EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+
+DROP TABLE t1;
+
+
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-11-26 03:35:37 -08:00
+++ b/sql/sql_select.cc	2007-12-07 17:14:54 -08:00
@@ -3691,7 +3691,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
     found_eq_constant=0;
     for (i=0 ; i < keyuse->elements-1 ; i++,use++)
     {
-      if (!use->used_tables)
+      if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL)
 	use->table->const_key_parts[use->key]|= use->keypart_map;
       if (use->keypart != FT_KEYPART)
       {