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:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.15 OS:Windows (Windows XP SP2)
Assigned to: Assigned Account CPU Architecture:Any

[13 May 2006 16:24] clifford campo
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.
[13 May 2006 16:37] clifford campo
Raised severity to serious, because I can't think of any workaround using just MySQL Queries.
[13 May 2006 18:48] Hartmut Holzgraefe
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;

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

  SET @skip=1; SET @numrows=5;
  EXECUTE STMT USING @skip, @numrows;
[22 May 2006 15:29] Konstantin Osipov
thank you for your bug report.

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


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.
[30 Sep 2008 14:59] Konstantin Osipov
Duplicate of Bug#8094