MySQL Bugs: #65307: Problem with order by and limit offset

Bug #65307	Problem with order by and limit offset
Submitted:	14 May 2012 4:02	Modified:	14 May 2012 13:20
Reporter:	Michelle Sollicito	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am trying to write a query that does an order by and a limit query with an offset (for paging).

I cannot get any of the following to work in mysql 5.5:

ORDER BY VolumeName DESC LIMIT 10 OFFSET 10;

ORDER BY VolumeName DESC LIMIT 10;

ORDER BY 1 DESC LIMIT 10;

LIMIT 10;

..for my query.. yet these work:

 ORDER BY 1;
 ORDER BY VolumeName;
 ORDER BY VolumeName DESC;

How to repeat:
Just try to do any query with ORDER BY 1 DESC LIMIT 10; at the end of it in sql workbench using mysql 5.5  and you get this error:

	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 1000' at line 61

It seems to be related to sql workbench adding its own LIMIT 0, 1000 on to the end of all my queries?

The weird thing is that when I put the same code into a stored procedure, it parses CORRECTLY but the results I get back suggest that it is ignoring the limit!!!!

Suggested fix:
There probably is a fix for this already but as I cannot find it I am hoping you will let me know what it is!

I am hoping there is a way to switch off the mysql limit

For me everything works:

mysql> select user from mysql.user order by user;
+------+
| user |
+------+
| root |
| root |
| ue   |
+------+
3 rows in set (0.03 sec)

mysql> select user from mysql.user order by user limit 1;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.01 sec)

mysql> select user from mysql.user order by user limit 1 offset 1;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

mysql> select user from mysql.user order by 1 limit 1 offset 1;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.23-log |
+------------+
1 row in set (0.00 sec)

So, please, provide complete test case, with CREATE TABLE and exact SELECT you try to use.

It seems it is when there is a union in the query only
e.g 
select a,b,c
from table1
where a>10
union
select a,b,c
from table1
where a<3
order by a
limit 10 offset 10;

Then the following manual page applies, http://dev.mysql.com/doc/refman/5.5/en/union.html:

"To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one."

Look:

mysql> select user from mysql.user union select 'a' from dual;
+------+
| user |
+------+
| root |
|      |
| u1   |
| a    |
+------+
4 rows in set (0.46 sec)

mysql> select user from mysql.user union select 'a' from dual order by 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by 1' at line 1
mysql> select user from mysql.user union select 'a' from dual order by user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by user' at line 1

But:

mysql> (select user from mysql.user) union (select 'a' from dual) order by user; 
+------+
| user |
+------+
|      |
| a    |
| root |
| u1   |
+------+
4 rows in set (0.61 sec)

mysql> (select user from mysql.user) union (select 'a' from dual) order by user limit 1;
+------+
| user |
+------+
|      |
+------+
1 row in set (0.00 sec)

mysql> (select user from mysql.user) union (select 'a' from dual) order by user limit 1 offset 2;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

This is not a bug.