MySQL Bugs: #19795: The LIMIT clause fails when a SQL Variable is used as a LIMIT parameter

Bug #19795	The LIMIT clause fails when a SQL Variable is used as a LIMIT parameter
Submitted:	13 May 2006 16:24	Modified:	30 Sep 2008 14:59
Reporter:	Clifford Campo	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.15	OS:	Windows (Windows XP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
SELECT queries using the LIMIT clause do not work when a SQL variable (e.g. @variable) is used as a parameter in the LIMIT clause.

How to repeat:
SELECT * from tbl_sales LIMIT 1, 18;  /* This SELECT will return all rows in tbl_sales except the first row.  MySQL Works as expected.*/

SELECT @numb := count(*) FROM tbl_sales;  /* Let us set a variable, called @numb, to the number of rows in the tbl_sales table. My SQL Words as expected; so far, so good. */

SELECT * from tbl_sales LIMIT 1, @numb;  /* However, when we use the @numb variable in the limited select statement, the SELECT query fails. */
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 '@numb' at line 1. 

Suggested fix:
It is incovienient not to use a sql variable as the parameter in the SELECT statement's LIMIT clause.

Raised severity to serious, because I can't think of any workaround using just MySQL Queries.

You can't use variables right away but you can use 
prepared statements on the SQL level to work around this, 
see http://dev.mysql.com/doc/refman/5.0/en/select.html

   For prepared statements, you can use placeholders
   (supported as of MySQL version 5.0.7). The following 
   statements will return one row from the tbl table:

  SET @a=1;
  PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
  EXECUTE STMT USING @a;

  The following statements will return the second to sixth 
  row from the tbl table:

  SET @skip=1; SET @numrows=5;
  PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
  EXECUTE STMT USING @skip, @numrows;

Clifford,
thank you for your bug report.

Just like in Bug#19621, this has never been supported, and works as documented.

http://dev.mysql.com/doc/refman/5.0/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 both be non-negative integer constants (except when using prepared statements).

This is a valid feature request.

Duplicate of Bug#8094