From: Date: December 8 2007 2:15am Subject: bk commit into 5.0 tree (igor:1.2595) BUG#32815 List-Archive: http://lists.mysql.com/commits/39594 X-Bug: 32815 Message-Id: <20071208011504.DA153540DCF@igor-laptop.mysql.com> 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) {