MySQL Bugs: #50510: Multiple LIMIT clauses not interpeted correctly

Bug #50510	Multiple LIMIT clauses not interpeted correctly
Submitted:	21 Jan 2010 15:23	Modified:	18 Jul 22:06
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0+, 5.1+, 6.0-codebase	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
An invalid double LIMIT clause is accepted.  Strange enough a triple LIMIT returns an error.

How to repeat:
USE sakila;
SELECT * FROM actor LIMIT 1; -- 1 row returned
(SELECT * FROM actor LIMIT 1) LIMIT 4; -- 4 rows returned
((SELECT * FROM actor LIMIT 1) LIMIT 4) LIMIT 8; -- syntax error 1064

Suggested fix:
The 2nd example should at least return only 1 row, but if this was valid also 3rd example should be valid and also return only one row. 

But I think this is a plain parser bug. 2nd and 3rd should both return error. Also such double LIMIT is not documented.

Thank you for the bug report. Verified just as described:

77-52-24-143:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.90-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1 from mysql.db limit 1;
+---+
| 1 |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

mysql> (select 1 from mysql.db limit 1) limit 2;
+---+
| 1 |
+---+
| 1 | 
| 1 | 
+---+
2 rows in set (0.00 sec)

mysql> ((select 1 from mysql.db limit 1) limit 2) limit 3;
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 'limit 2) limit 3' at line 1

Verified with latest 5.1.44 and 6.0.14 from bzr also.

Just for reference, this is documented. See SELECT Syntax: "If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one: [..]".

Fixed in MySQL 8.0.0 by refactoring of the parser, documented as follows:

    Queries with multiple LIMIT clauses were not always interpreted
    in accordance with the SQL standard.

    For example, each of the following queries now returns two rows,
    as expected:

  SELECT * FROM t LIMIT 2;

  (SELECT * FROM t LIMIT 2) LIMIT 4;

  ((SELECT * FROM t LIMIT 2) LIMIT 4) LIMIT 3;

Closed.