Bug #32919 LIMIT is restricted to constants
Submitted: 3 Dec 2007 0:08 Modified: 3 Dec 2007 5:39
Reporter: Jens Bollmann Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 2007 0:08] Jens Bollmann 
Description:
Trying to use LIMIT with variables throws an error. 

How to repeat:
SERVER root@localhost [test]> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

SERVER root@localhost [test]> set @a=2;
Query OK, 0 rows affected (0.00 sec)

SERVER root@localhost [test]> set @b=3;
Query OK, 0 rows affected (0.00 sec)

SERVER root@localhost [test]> select * from t limit @a,@b;
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 '@a,@b' at line 1

Suggested fix:
Allow variables with LIMIT.
[3 Dec 2007 0:15] Paul DuBois 
This might be a feature request, but it's not a bug. That LIMIT takes constants is 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)."
[3 Dec 2007 5:39] Valeriy Kravchuk 
You should use prepared statements if you want variable LIMITs. Read http://dev.mysql.com/doc/refman/5.0/en/sqlps.html for the details.