Bug #565 return inaccurate result when using both LIMIT and ORDER BY in the same query
Submitted: 1 Jun 2003 21:07 Modified: 2 Jun 2003 7:58
Reporter: John Lam Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.3 Beta OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[1 Jun 2003 21:07] John Lam
Description:
The result will be not accurate when using both LIMIT and ORDER BY in a same query.

How to repeat:
For example,
select COL1,COL2 from TABLE1 order by COL1 DESC limit 100

The COL1 of the first row MAY NOT equals max(COL1) since MySQL will not sort the whole table when LIMIT is used.
[2 Jun 2003 7:58] Alexander Keremidarski
Please note that 4.0.3-beta is very old release. Current in 4.0 branch is 4.0.13   which is second Stable release after 4.0.12

Your How-to-repeat is not complete. Query only is not enough. You said:

> The COL1 of the first row MAY NOT equals max(COL1) since MySQL will not
> sort the whole table when LIMIT is used. 

This statement is incorrect. MySQL will sort whole table in descending order and just then will limit result according to LIMIT clause. 

mysql> SELECT col1, col2 FROM table1;
+------+------+
| col1 | col2 |
+------+------+
|  674 |  588 |
|  903 |  384 |
|  209 |  896 |
|  296 |  769 |
|  412 |  557 |
|  994 |  377 |
|  542 |  260 |
|  548 |   69 |
|  852 |  570 |
|  920 |  834 |
|  958 |  483 |
|  701 |  300 |
+------+------+

mysql> SELECT col1, col2 FROM table1 ORDER BY col1 DESC LIMIT 10;
+------+------+
| col1 | col2 |
+------+------+
|  994 |  377 |
|  958 |  483 |
|  920 |  834 |
|  903 |  384 |
|  852 |  570 |
|  701 |  300 |
|  674 |  588 |
|  548 |   69 |
|  542 |  260 |
|  412 |  557 |
+------+------+