MySQL Bugs: #60220: Handler READ with no match + HANDLER NEXT produces wrong results for InnoDB

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

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.

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 >

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 ?

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".

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".

What feedback is this waiting on ?

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.