MySQL Bugs: #31098: HANDLER names 5.0.45.

Bug #31098	HANDLER names 5.0.45.
Submitted:	19 Sep 2007 14:23	Modified:	1 Oct 2007 10:26
Reporter:	Peter Johansson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	handler, regression

Description:
Handler is not working when you do an "update" and then a "Handler .. next" you lose position when update table and Handler has the same name.

This differ from 4.1 Server.

How to repeat:
the Data
dim1,charge,recid
202,10,1
203,10,2
203,10,3
203,10,4
208,10,5
208,10,6 

Ex code1. 

HANDLER TESTH OPEN               
HANDLER TESTH READ INDEX_1 (203) // pos record 2
HANDLER TESTH READ NEXT          // pos record 3
HANDLER TESTH READ NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER TESTH READ NEXT          // pos record 1

Ex code2 this works if you open the handler whith a different name then the update table name. 

HANDLER TESTH  OPEN AS OKHAND                
HANDLER OKHAND READ INDEX_1 = (203) // pos record 2
HANDLER OKHAND READ NEXT          // pos record 3
HANDLER OKHAND READ NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER OKHAND READ NEXT          // pos record 5

Suggested fix:
use different name on handler.

http://dev.mysql.com/doc/refman/5.0/en/handler.html says:

"HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by the current thread or other threads) and these modifications might be only partially visible to HANDLER ... NEXT or HANDLER ... PREV scans."

The code1 should be this. sorry.

HANDLER TESTH OPEN               
HANDLER TESTH READ INDEX_1 =  (203)      // pos record 2
HANDLER TESTH READ INDEX_1 NEXT          // pos record 3
HANDLER TESTH READ INDEX_1 NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER TESTH READ INDEX_1 NEXT          // pos record 1

The handler is not traversing the index after the update if the handlers name is the same as the table name and even if the records effected is 0 and only one thread.

When a Ex. “HANDLER  NAME read index_1 next” is executed after an update you get the first record in that index. 

Tested on a 4.1 server and it is working OK.
Tested on a 5.0.19 server and it is working OK.
On 5.0.45 community server this function differs.

Now I have tested this on windows XP.
Version 5.0.20a OK.
Version 5.0.27  OK
Version 5.0.37 The error/bug or change appears.

The bug seem to appear in version 5.0.37.

Verified just as described. Indeed, explicitely named handler works differently.