bk commit into 5.0 tree (mhansson:1.2502) BUG#28570

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin 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-08-14 09:10:20+02:00, mhansson@stripped +4 -0
  bug#28570: handler::index_read() is called with different find_flag when 
  ORDER BY is used
  
  The range analysis module did not correctly signal to the 
  handler that a range represents a ref (EQ_RANGE flag). This causes 
  non-range queries like 
  SELECT ... FROM ... WHERE keypart_1=const, ..., keypart_n=const 
  ORDER BY ... FOR UPDATE
  to wait for a lock unneccesarily if another running transaction uses
  SELECT ... FOR UPDATE on the same table.
  
  Fixed by setting EQ_RANGE for all range accesses that represent 
  an equality predicate. 

  mysql-test/r/innodb_mysql.result@stripped, 2007-08-14 09:10:14+02:00, mhansson@stripped +18 -0
    bug#28570: Test Result

  mysql-test/t/innodb_mysql.test@stripped, 2007-08-14 09:10:14+02:00, mhansson@stripped +29 -0
    bug#28570: Test Case

  sql/handler.cc@stripped, 2007-08-14 09:10:14+02:00, mhansson@stripped +2 -1
    bug#28570: Updated comment

  sql/opt_range.cc@stripped, 2007-08-14 09:10:14+02:00, mhansson@stripped +1 -2
    bug#28570: Removed the criterion that key has to be unique (HA_NOSAME) in 
    order for the EQ_RANGE flag to be set. It is sufficient that the range 
    represent a ref access.

diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-07-23 16:07:28 +02:00
+++ b/mysql-test/r/innodb_mysql.result	2007-08-14 09:10:14 +02:00
@@ -1007,4 +1007,22 @@ CALL p1();
 CALL p1();
 DROP PROCEDURE p1;
 DROP TABLE t1;
+CREATE TABLE t1 (
+a INT,
+b INT,
+KEY (b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=20 FOR UPDATE;
+a	b
+2	20
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
+a	b
+1	10
+2	10
+ROLLBACK;
+ROLLBACK;
+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-07-23 16:07:28 +02:00
+++ b/mysql-test/t/innodb_mysql.test	2007-08-14 09:10:14 +02:00
@@ -840,5 +840,34 @@ DISCONNECT con2;
 DROP PROCEDURE p1;
 DROP TABLE t1;
 
+#
+# Bug #28570: handler::index_read() is called with different find_flag when 
+# ORDER BY is used
+#
+
+CREATE TABLE t1 (
+  a INT,
+  b INT,
+  KEY (b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
+
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=20 FOR UPDATE;
+
+--connect (conn2, localhost, root,,test)
+
+# This statement gives a "failed: 1205: Lock wait timeout exceeded; try 
+# restarting transaction" message when the bug is present.
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
+ROLLBACK;
+
+--disconnect conn2
+--connection default
+
+ROLLBACK;
+DROP TABLE t1;
 
 --echo End of 5.0 tests
diff -Nrup a/sql/handler.cc b/sql/handler.cc
--- a/sql/handler.cc	2007-07-31 14:23:23 +02:00
+++ b/sql/handler.cc	2007-08-14 09:10:14 +02:00
@@ -2610,7 +2610,8 @@ int handler::read_multi_range_next(KEY_M
     read_range_first()
     start_key		Start key. Is 0 if no min range
     end_key		End key.  Is 0 if no max range
-    eq_range_arg	Set to 1 if start_key == end_key
+    eq_range_arg	Set to 1 if start_key == end_key and the range endpoints
+                        will not change during query execution.
     sorted		Set to 1 if result should be sorted per key
 
   NOTES
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2007-07-17 22:29:20 +02:00
+++ b/sql/opt_range.cc	2007-08-14 09:10:14 +02:00
@@ -6369,8 +6369,7 @@ QUICK_RANGE_SELECT *get_quick_select_for
   range->min_key=range->max_key=(char*) ref->key_buff;
   range->min_length=range->max_length=ref->key_length;
   range->flag= ((ref->key_length == key_info->key_length &&
-		 (key_info->flags & (HA_NOSAME | HA_END_SPACE_KEY)) ==
-		 HA_NOSAME) ? EQ_RANGE : 0);
+		 (key_info->flags & HA_END_SPACE_KEY) == 0) ? EQ_RANGE : 0);
 
   if (!(quick->key_parts=key_part=(KEY_PART *)
 	alloc_root(&quick->alloc,sizeof(KEY_PART)*ref->key_parts)))