Bug #8094 Variables in the LIMIT Clause
Submitted: 23 Jan 2005 0:19 Modified: 8 Aug 2007 12:53
Reporter: Robert Seaborn Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.01 OS:Linux (Suse Linux)
Assigned to: Marc ALFF CPU Architecture:Any

[23 Jan 2005 0:19] Robert Seaborn
I'd like it if the LIMIT clause could use variables instead of only accepting constants.  The reason why is that I would like to use them in a stored procedure, and return a specific page of results.  I guess I could use a cursor, but I like the simplicity of the LIMIT clause.

How to repeat:
LIMIT clause does not allow variables.  LIMIT clause only accepts constant numbers.

Suggested fix:
SELECT * FROM PageTable LIMIT OffSet, RecordsPerPage
[1 Mar 2006 13:03] Valeriy Kravchuk
Yes, cursors and, may be, prepared statements (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html) will allow to solve that task. So, I am not sure that this feature will be added in the foreseeable future.
[9 Jan 2007 3:25] Paul Medynski
Here's a forum post showing an example of prepared statements letting you assign a variable value to the limit clause:


However, I think this bug should get some attention because I can't imagine that prepared statements within a procedure will allow for any procedure-compile-time optimizations.  I have a feeling that prepared statements are compiled and executed at the runtime of the procedure, which probaby has a negative impact on efficiency.  If the limit clause could accept normal procedure variables (say, a procedure argument), then the database could still perform compile-time optimizations on the rest of the query, within the procedure.  This would likely yield faster execution of the procedure.  I'm no expert though.

[3 Aug 2007 16:07] Konstantin Osipov
See also Bug #6673 Expressions not available as LIMIT parameters
[8 Aug 2007 12:53] Konstantin Osipov
Thank you for a valid feature request.
[21 Mar 2008 22:16] Anthony Taylor
Unless I am looking at this wrong, it seems as if the prepared statement approach could create a serious security hole in an application.  In conventional  stored procedures, everything is compiled so a user should not be able to perform basic sql injection attacks.  In the prepared statement approach, a user could pass in anything that may slip past data validation.
[26 Mar 2008 10:20] Susanne Ebrecht
Set bug #33856 as duplicate to this bug here.
[2 Jun 2008 7:35] James Low
Hi, There are 4 versions of this bug or feature request (which ever you want to call it), clearly showing lots of people want it. It is such a useful feature, and the implementation should be simple, I really hope you will consider doing it soon.
[2 Jun 2008 7:36] Janakan Arulkumarasan
Please do this, it would be reallllllllllllllllllllllllllllllllly helpful.
[27 Jun 2008 17:01] Mark Callaghan
I want this feature too.
[9 Jul 2008 3:09] Ilkka Huotari
Please add this. Not having this leads to horrible code in stored procedures.
[30 Sep 2008 15:00] Konstantin Osipov
Bug#19795 was marked a duplicate of this bug.
[4 Oct 2008 23:14] Konstantin Osipov
Bug#21761 limit interval was marked a duplicate of this bug.
[19 Feb 2010 17:40] Sergei Shirokov
I want this feature too.
[7 Apr 2010 1:28] Nirmal Thangaraj
This feature would be helpful.
[6 Sep 2016 16:56] Krishan Kumar
I am also looking this feature, this was very old bug though we are on 5.6 version
Is this feature available in 5.6?