MySQL Bugs: #36161: Subquery in FROM clause accepts invalid syntax of LIMIT

Bug #36161	Subquery in FROM clause accepts invalid syntax of LIMIT
Submitted:	16 Apr 2008 22:44	Modified:	17 Apr 2008 3:28
Reporter:	Sarah Sproehnle	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0 and 5.1	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Using a subquery in the FROM clause allows invalid syntax.  Specifically, it accepts LIMIT before the ORDER BY keyword.  Tested on 5.1.22, 5.0.51, 5.0.45

How to repeat:
SELECT * FROM (SELECT * FROM City LIMIT 10 ORDER BY name) AS derived;

Suggested fix:
This should give an error during parsing.

also because an empty set is returned when limit is before order!

-- this returns 10 rows as expected
select *  from (select * from abstracts order by ab_id limit 10) as test;

-- this returns an empty set (and no error)
select *  from (select * from abstracts limit 10 order by ab_id) as test;

So obviously the 'derived table' (I believe this is a more correct term here than 'subquery') has no data in it!

Verified just as described with 5.0.58:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user from (select * from mysql.user order by user limit 2) as derived;
+--------+
| user   |
+--------+
| a23145 |
| junk   |
+--------+
2 rows in set (0.00 sec)

mysql> select user from (select * from mysql.user limit 2 order by user) as derived;
Empty set (0.03 sec)

mysql> select * from mysql.user limit 2 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