MySQL Bugs: #38576: Incorrect LIMIT behavior with ORDER BY

Bug #38576	Incorrect LIMIT behavior with ORDER BY
Submitted:	5 Aug 2008 16:07	Modified:	5 Aug 2008 19:20
Reporter:	Adam Erickson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Linux (CentOS release 5 (Final))
Assigned to:		CPU Architecture:	Any
Tags:	limit, optimzer, order by, result size

Description:
We have a table with 50M~ rows averaging 51 bytes in length per row.  This 2.5GB table is regularly polled for reporting purposes.  One of these queries returns zero results when LIMIT 100 is used.  If the LIMIT is removed, or increased to a minimum of 60_000 rows the server returns a proper (although huge) result set.

There are other workarounds, for example, changing the column used in the ORDER BY to the primary key or removing the order by clause entirely.  Wrapping the base query as a sub-query and using LIMIT 100 on the results behaves properly as well.

I have tested this with both MyISAM and InnoDB engines and both are affected.

How to repeat:
Use the supplied SQL script to create the test table "limbug".  Run the query against it:

SELECT 
  ID,
  cdate,
  vc_3,
  float_1,
  double_1
FROM limbug 
WHERE vc_1 = '8b10e4'
  and vc_3 IS NULL
  and vc_2 ='d9df825203'
  and cdate <= 1217305000000
ORDER BY cdate DESC 
LIMIT 100;

You should get an empty result set.  Change:

LIMIT 100 to LIMIT 60000

You will get 60,000 rows returned.

Suggested fix:
Query should return 100 results, not zero.

Test case SQL script is large, will upload to FTP and note in bug.

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.26, and inform about the results.

5.1.26 appears to fix the problem, you can close this.

Not repeatable with 5.1.26.