From: Date: June 4 2007 12:55pm
Subject: bk commit into 5.1 tree (mhansson:1.2518) BUG#28570
List-Archive: http://lists.mysql.com/commits/28051
X-Bug: 28570
Message-Id: <20070604105540.B37BF36427@linux-st28.site>

Below is the list of changes that have just been committed into a local
5.1 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-06-04 13:55:30+03:00, mhansson@stripped +4 -0
  bug#28570: handler::index_read() is called with different find_flag when ORDER BY is used
  
  Problem 1: When called with a range representing a ref access, the handler calls the 
  engine with the find flag HA_READ_KEY_OR_NEXT when it should use HA_READ_KEY_EXACT, which 
  causes lock waits on InnoDB. This happens when ORDER BY is used along with an equality
  predicate.
  
  Problem 2: The range analysis module does not correctly signal to the handler that a 
  range represents a ref (EQ_RANGE flag)
  
  Problem 2 fixed by setting EQ_RANGE for all range accesses that represent an equality 
  predicate. 
  
  Problem 1 fixed by making the handler call the storage engine with HA_READ_KEY_EXACT when
  a range really represents a ref.

  mysql-test/r/innodb_mysql.result@stripped, 2007-06-04 13:55:22+03:00, mhansson@stripped +18 -0
    bug#28570: Test Result

  mysql-test/t/innodb_mysql.test@stripped, 2007-06-04 13:55:22+03:00, mhansson@stripped +30 -0
    bug#28570: Test Case

  sql/handler.cc@stripped, 2007-06-04 13:55:22+03:00, mhansson@stripped +7 -1
    bug#28570: Added case to read only exact match for the search key if the range is the 
    interval [x, x], which is more efficient on MyISAM, and avoids lock wait on InnoDB.

  sql/opt_range.cc@stripped, 2007-06-04 13:55:22+03: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 represents a ref access.

# 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:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/5.1o-bug28570

--- 1.309/sql/handler.cc	2007-05-24 14:11:20 +03:00
+++ 1.310/sql/handler.cc	2007-06-04 13:55:22 +03:00
@@ -3204,7 +3204,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 execution.
     sorted		Set to 1 if result should be sorted per key
 
   NOTES
@@ -3235,6 +3236,11 @@ int handler::read_range_first(const key_
 
   if (!start_key)			// Read first record
     result= index_first(table->record[0]);
+  else if(eq_range_arg)
+    result= index_read(table->record[0],
+                       start_key->key,
+                       start_key->keypart_map,
+                       HA_READ_KEY_EXACT);
   else
     result= index_read(table->record[0],
 		       start_key->key,

--- 1.274/sql/opt_range.cc	2007-05-24 01:39:21 +03:00
+++ 1.275/sql/opt_range.cc	2007-06-04 13:55:22 +03:00
@@ -7805,8 +7805,7 @@ QUICK_RANGE_SELECT *get_quick_select_for
   range->min_keypart_map= range->max_keypart_map=
     make_prev_keypart_map(ref->key_parts);
   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)))

--- 1.34/mysql-test/r/innodb_mysql.result	2007-05-29 15:57:28 +03:00
+++ 1.35/mysql-test/r/innodb_mysql.result	2007-06-04 13:55:22 +03:00
@@ -818,3 +818,21 @@ alter table t2 modify i int default 4, r
 unlock tables;
 drop table t1;
 End of 5.1 tests
+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;

--- 1.3/mysql-test/t/innodb_mysql.test	2006-09-18 17:55:52 +03:00
+++ 1.4/mysql-test/t/innodb_mysql.test	2007-06-04 13:55:22 +03:00
@@ -12,3 +12,33 @@ let $other_engine_type= MEMORY;
 let $test_foreign_keys= 1;
 
 --source include/mix1.inc
+
+#
+# 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;