Bug #8931 MySQL doesn't support parameters in LIMIT clause
Submitted: 3 Mar 2005 20:49 Modified: 3 Mar 2005 21:33
Reporter: Amir Pashazadeh Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.8 OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[3 Mar 2005 20:49] Amir Pashazadeh
Description:
it seems mysql cannot compile a ? (parameter placeholder) in LIMIT clause of a select query.

it seems JDBC 3.0.14 connector handled prepared statements internaly so there was no problem, but JDBC 3.1.16 delegates the prepared statement handling to database, and MySQL 4.1.8 returns an error telling SQL is malformed.

How to repeat:
try to execute a prepared statement query with parameterized LIMIT clause using JDBC 3.0.14 and 3.1.16 on MySQL 4.1.8, the first one returns a correct result while the second one throws an exception.

Suggested fix:
MySQL query parser should accept parameterized LIMIT clause in prepared statements.
[3 Mar 2005 21:12] MySQL Verification Team
That is the expected behavior currently:

http://dev.mysql.com/doc/mysql/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
[3 Mar 2005 21:33] MySQL Verification Team
Sorry yes it is a bug and duplicate of:

http://bugs.mysql.com/bug.php?id=4718