MySQL Bugs: #787: HANDLER without INDEX doesn't work with deleted rows

Bug #787	HANDLER without INDEX doesn't work with deleted rows
Submitted:	3 Jul 2003 13:05	Modified:	4 Jul 2003 3:55
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Not sure if this is an expected behaviour (and should be documented) or a bug.
When using handler without an index, no record is found (error 134).

How to repeat:
mysql> create table a (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> delete from a limit 2;
Query OK, 2 rows affected (0.01 sec)

mysql> handler a open;
Query OK, 0 rows affected (0.00 sec)

mysql> handler a read first limit 0,1;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> handler a read first limit 1,1;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> handler a read first limit 2,1;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> handler a read first limit 3,1;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> handler a read first limit 4,1;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql>

It's a bug, I think.
These queries:

handler a read first limit 2,1;
handler a read first limit 3,1;
handler a read first limit 4,1;

should return 3, 4, 5 correspondingly.

IRC discussion:
<serg> I think 787 should not produce an error
<ramil> just return "empty set"?
<serg> error *could* possibly appear if DELETE was used between HANDLER READ and HANDLER NEXT - but even there it would be nice to avoid an error
<serg> but in that test case I can see absolutely no reason for an error
<ramil> ok, should "handler a read first limit 2,1;" return 3?
<serg> why ? I think "handler a read first" should return 3
<ramil> or "handler a read first limit 0,1;" should return 3?
<ramil> opps, was too late :)
<ramil> so we should just skip deleted records scanning table, right?
<serg> of course

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html