MySQL Bugs: #25335: SELECT SQL_BUFFER_RESULT with LIMIT and OFFSET gives wrong data

Bug #25335	SELECT SQL_BUFFER_RESULT with LIMIT and OFFSET gives wrong data
Submitted:	29 Dec 2006 21:42	Modified:	30 Jan 2007 11:48
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (Linux SLES9)
Assigned to:		CPU Architecture:	Any

Description:
In a large myisam table of data (T1), we read (SELECT) rows around 10,000 a shot, by using a combination of LIMIT and OFFSET. We cannot read all data in one shot, due to its large size.  Queries join to another table T2 to filer the data we need. The queries have the form for each shot (i) :

SELECT a,T2.b,c,d,e... FROM T1, T2 WHERE T1.b=T2.b and T2.y=123 ORDER BY a,b LIMIT 10000 OFFSET n(i)

n(1) = 0
n(2) = 10,000
etc.

If we use SELECT SQL_BUFFER_RESULT, then reads after the first get confused and return wrong rows.

Seems there is some problem when SQL_BUFFER_RESULT is used with LIMIT and OFFSET (and maybe order by).

How to repeat:
Per above.

Suggested fix:
Do not use SQL_BUFFER_RESULT.
But we would like to use it to avoid locking the T1/T2 tables for too long as the reads take a while.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27, and inform about the results. In case of the same problem, please, send SHOW CREATE TABLE results for tables involved.

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