Bug #28570 handler::index_read() is called with different find_flag when ORDER BY is used
Submitted: 21 May 2007 17:09 Modified: 31 Aug 2007 1:56
Reporter: Vasil Dimov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.20-BK OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: bfsm_2007_05_31, bfsm_2007_06_21, bfsm_2007_06_28

[21 May 2007 17:09] Vasil Dimov
Description:
Hi,

handler::index_read() is called with find_flag=HA_READ_KEY_OR_NEXT when ORDER BY is used and a WHERE clause like secondary_key='exact_value' but it should be called with find_flag=HA_READ_KEY_EXACT as it is when no ORDER BY is specified.

This causes unexpected lock waits, see "How to repeat".

How to repeat:
CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `bkey` (`b`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1,10), (2,20), (3,30);

transaction 1:
-- lock one row, no matter if ORDER BY is used
SELECT * FROM t WHERE b=20 FOR UPDATE;

transaction 2:
-- lock wait occurs, without ORDER BY it does not occur
SELECT * FROM t WHERE b=10 ORDER BY a FOR UPDATE;

Here is snippet from gdb:

/* without ORDER BY */
Breakpoint 3, handler::index_read (this=0x805b3d838, buf=0x805b3da20 "�\002",
    key=0x805a40c58 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at handler.h:1242

/* with ORDER BY */
Breakpoint 3, handler::index_read (this=0x805b3d838, buf=0x805b3da20 "�\002",
    key=0x805a056c8 "", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT)
    at handler.h:1242

(notice the value of find_flag)
[22 May 2007 12:57] Vasil Dimov
Bug#28591 may be related to this.
[26 May 2007 7:49] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with latest 5.1.20-BK on Linux.

With ORDER BY:

#0  0x0841704c in ha_innobase::index_read (this=0x8eff140, buf=0x8eff278 "э",
    key_ptr=0x8f16be0 "", key_len=5, find_flag=HA_READ_KEY_OR_NEXT)
    at ha_innodb.cc:3972

Without ORDER BY:

#0  0x0841704c in ha_innobase::index_read (this=0x8eff178, buf=0x8eff2b0 "э",
    key_ptr=0x8f16ae8 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
    at ha_innodb.cc:3972
[30 May 2007 13:17] Martin Hansson
Repeatable in 5.0.42-debug also.
[4 Jun 2007 11:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28051

ChangeSet@1.2518, 2007-06-04 13:55:30+03:00, mhansson@linux-st28.site +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.
[13 Aug 2007 9:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32436

ChangeSet@1.2502, 2007-08-13 11:50:02+02:00, mhansson@linux-st28.site +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).
  
  Fixed by setting EQ_RANGE for all range accesses that represent 
  an equality predicate.
[14 Aug 2007 7:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32491

ChangeSet@1.2502, 2007-08-14 09:10:20+02:00, mhansson@linux-st28.site +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.
[24 Aug 2007 7:20] Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 7:22] Bugs System
Pushed into 5.0.48
[31 Aug 2007 1:56] Paul DuBois
Noted in 5.0.48, 5.1.22 changelogs.

Non-range queries of the form SELECT ... FROM ... WHERE 
keypart_1=const, ..., keypart_n=const ORDER BY ... FOR UPDATE
sometimes were unneccesarily blocked waiting for a lock if another
transaction was using SELECT ... FOR UPDATE on the same table.