MySQL Bugs: #56765: select from subquery with user variable gets wrong result

Bug #56765	select from subquery with user variable gets wrong result
Submitted:	14 Sep 2010 7:48	Modified:	14 Sep 2010 17:48
Reporter:	Xindong Su	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.50-community	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The attachment has 3 tables. The difference between table "cyd" and "cydbak" is only one thing: "cydbak" has one more field than "cyd". The fields of "cyd" and "cydbak" with same name have exactly the same content.

I found that runing same query on these two tables with join to table "cydfk" will get different result. See below:

SET @i=0;
SELECT KeyPos, indexno FROM (
SELECT @i:=@i+1 AS KeyPos, cyd.IndexNO FROM cyd LEFT JOIN cydfk ON cyd.IndexNO=cydfk.IndexNO WHERE (IsHide="否") AND (Paid="否" )  ORDER BY cyd.IndexNO DESC
) AS a WHERE a.IndexNO="食专2010-04-0100" 

this query will get right result:
KeyPos	indexno
18	食专2010-04-0100

if you pick the subquery out and run it you will find keypos=18 is the right result.

but on table cydbak, we will get the wrong result:
SET @i=0;
SELECT KeyPos, indexno FROM (
SELECT @i:=@i+1 AS KeyPos, cydbak.IndexNO FROM cydbak LEFT JOIN cydfk ON cydbak.IndexNO=cydfk.IndexNO WHERE (IsHide="否") AND (Paid="否" )  ORDER BY cydbak.IndexNO DESC
) AS a WHERE a.IndexNO="食专2010-04-0100" 

this query will return following result:
KeyPos	indexno
9507	食专2010-04-0100

How to repeat:
run the query and you'll get the same result every time.

attachment is uploaded to ftp. file name is bug-data-56765.zip

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You assign values to a variable while query is running. This value depends from execution plan which is different for these 2 queries.