MySQL Bugs: #8094: Variables in the LIMIT Clause

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:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.01	OS:	Linux (Suse Linux)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
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:
CREATE PROCEDURE SPdemo(IN OffSet INT, IN RecordsPerPage INT)
BEGIN
SELECT * FROM PageTable LIMIT OffSet, RecordsPerPage
END

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.

Here's a forum post showing an example of prepared statements letting you assign a variable value to the limit clause:

http://forums.mysql.com/read.php?98,126379,133966#msg-133966

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.

-Paul

See also Bug #6673 Expressions not available as LIMIT parameters

Thank you for a valid feature request.

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.

Set bug #33856 as duplicate to this bug here.

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.
http://bugs.mysql.com/bug.php?id=6673
http://bugs.mysql.com/bug.php?id=8094
http://bugs.mysql.com/bug.php?id=11918
http://bugs.mysql.com/bug.php?id=33856

Please do this, it would be reallllllllllllllllllllllllllllllllly helpful.

I want this feature too.

Please add this. Not having this leads to horrible code in stored procedures.

Bug#19795 was marked a duplicate of this bug.

Bug#21761 limit interval was marked a duplicate of this bug.

I want this feature too.

This feature would be helpful.

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?