Bug #60220 Handler READ with no match + HANDLER NEXT produces wrong results for InnoDB
Submitted: 23 Feb 2011 10:38 Modified: 19 Nov 2013 17:32
Reporter: Michael Widenius Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.55/5.5.10 OS:Linux
Assigned to: CPU Architecture:Any
Tags: handler, innodb
Triage: Needs Triage: D1 (Critical)

[23 Feb 2011 10:38] Michael Widenius
Description:
Handler READ with no match +  HANDLER NEXT produces wrong results for InnoDB.

This bug affects all HANDLER and HandlerSocket users.

How to repeat:
CREATE TABLE t1 (  no1 smallint(5) NOT NULL default '0',  no2 int(10) NOT NULL default '0',  PRIMARY KEY (no1,no2)) engine=innodb;
INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
HANDLER t1 OPEN;
HANDLER t1 READ `primary` = (1, 1000);
HANDLER t1 READ `primary` NEXT;

The last read returns:

+-----+-----+
| no1 | no2 |
+-----+-----+
|   2 |   8 |
+-----+-----+

When the right result should be 2 6

MyISAM returns the correct result.

Suggested fix:
Fix InnoDB to remember that last read call did not match and return for handler next the previously found row.
[23 Feb 2011 11:04] Miguel Solorzano
Thank you for the bug report. Verified as described:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.10 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >USE D1
Database changed
mysql 5.5 >DROP TABLE t1;
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >CREATE TABLE t1 (  no1 smallint(5) NOT NULL default '0',  no2 int(10) NOT NULL default
    -> '0',  PRIMARY KEY (no1,no2)) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 >INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.5 >HANDLER t1 OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >HANDLER t1 READ `primary` = (1, 1000);
Empty set (0.00 sec)

mysql 5.5 >HANDLER t1 READ `primary` NEXT;
+-----+-----+
| no1 | no2 |
+-----+-----+
|   2 |   8 |
+-----+-----+
1 row in set (0.00 sec)

mysql 5.5 >DROP TABLE t1;
Query OK, 0 rows affected (0.04 sec)

mysql 5.5 >CREATE TABLE t1 (  no1 smallint(5) NOT NULL default '0',  no2 int(10) NOT NULL default
    -> '0',  PRIMARY KEY (no1,no2)) engine=mYisam;
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.5 >HANDLER t1 OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >HANDLER t1 READ `primary` = (1, 1000);
Empty set (0.00 sec)

mysql 5.5 >HANDLER t1 READ `primary` NEXT;
+-----+-----+
| no1 | no2 |
+-----+-----+
|   2 |   6 |
+-----+-----+
1 row in set (0.00 sec)

mysql 5.5 >
[23 Feb 2011 11:13] Jonas Oreland
Hi,

I read the documentation for HANDLER (http://dev.mysql.com/doc/refman/5.1/en/handler.html), and find no description of what position a handler should be
in if READ key does not find a record.

So my interpretation is that this is UNDEFINED, and that NEXT can return any
value...or maybe an error... (/me have a patch that adds HANDLER to ndb...and we
would return error...in current version of patch)

Or can you find it described anywhere ?
[28 Mar 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[29 Apr 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 May 2011 13:30] Jonas Oreland
What feedback is this waiting on ?
[19 Nov 2013 17:32] Daniel Price
This bug was addressed in a bug fix for a related bug (Oracle Bug#14621190). The fix is noted in 5.5.35, 5.6.15, and 5.7.3 changelogs. The changelog text for the related bug, as noted in the 5.5.35, 5.6.15, and 5.7.3 changelogs, is:

When "index_read_map" is called for an exact search and fails to return a
record due to non-matching search criteria, the cursor would be positioned
on the next record after the searched key. A subsequent call to
"index_next " would return the next record instead of returning the
previous non-matching row, thereby skipping a record. 

Thank you for the bug report.