MySQL Bugs: #565: return inaccurate result when using both LIMIT and ORDER BY in the same query

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

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.

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 |
+------+------+