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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (Linux SLES9)
Assigned to: CPU Architecture:Any

[29 Dec 2006 21:42] [ name withheld ]
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

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:
But we would like to use it to avoid locking the T1/T2 tables for too long as the reads take a while.
[30 Dec 2006 11:48] Valeriy Kravchuk
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.
[31 Jan 2007 0:00] Bugs System
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".