MySQL Bugs: #11918: SP does not accept variables in LIMIT clause

Bug #11918	SP does not accept variables in LIMIT clause
Submitted:	13 Jul 2005 19:09	Modified:	8 Feb 2006 13:38
Reporter:	Markus Popp
Status:	Verified
Category:	Server: SP	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Any
Assigned to:		Target Version:
Tags:	variables, limit, Parameters, PS, SP
Triage:	Triaged: D5 (Feature request)

Description:
Due to Per-Erik Martin's recommendation in the forum at
http://forums.mysql.com/read.php?98,28212 I'm filing the following behavior of SP as bug
report.

SPs don't accept variables in the LIMIT clause. If such a SP is created, the error
message

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 'l1, l2;

is thrown.

How to repeat:
Create following table with following data:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `ct` char(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `t1` VALUES (1, 'a');
INSERT INTO `t1` VALUES (2, 'b');
INSERT INTO `t1` VALUES (3, 'c');
INSERT INTO `t1` VALUES (4, 'd');
INSERT INTO `t1` VALUES (5, 'e');
INSERT INTO `t1` VALUES (6, 'f');
INSERT INTO `t1` VALUES (7, 'g');
INSERT INTO `t1` VALUES (8, 'h');

Creating this SP works fine:

DELIMITER //

DROP PROCEDURE IF EXISTS limittest //
CREATE PROCEDURE limittest (IN l1 INT, IN l2 INT)
BEGIN
select l1;
select l2;
select * from t1 order by id;
END //

DELIMITER ;

If you add these two variables in the LIMIT clause, you get an error:

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS limittest //
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE PROCEDURE limittest (IN l1 INT, IN l2 INT)
    -> BEGIN
    -> select l1;
    -> select l2;
    -> select * from t1 order by id limit l1, l2;
    -> END //
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 'l1, l
2;

This isn't specific to stored routines.
LIMIT doesn't allow variables in any context.
Its arguments must be integer constants.

However, I think this would be a useful feature (e.g. for paging in websites).

Like noticed by Paul. It isn't a bug but you would change it for feature request.

> but you would change it

Do I have to take any action to make a feature request out of it?

I changed it, now just to wait if that is possible from dev.

Thank you for the report.

It is a feature request. It is SP specifik in the sense that it requires a special fix for
SP variables. (It has already been fixed for '?' in prepared statements.)
If it's fixed for stored procedure variables, it should also be considered for user
variables as well.

Has this been added yet?

I've found a workaround for this problem. 
It's ugly, time consuming and... mainly ugly. But it's working. 

------SQL-------- 
DELIMITER $ 
CREATE PROCEDURE `tmp`() 
BEGIN 
PREPARE STMT FROM "SELECT * FROM users LIMIT ?,?"; 
END$ 
DELIMITER; 

SET @a=2; 
SET @b=1; 

CALL tmp(); 
EXECUTE STMT USING @a, @b; 
------SQL-------- 

It's tested to be working on 5.0.15. 
Hope if helps anyone.

Suggested workaround: use Dynamic SQL in stored procedures.
User variables are accepted arguments of LIMIT clause in prepared statements, and SQL
syntax for prepared statements can be used in stored procedures.

I know about this workaround, but still I would believe that directly assigning the
parameters in the LIMIT clause would be much more convenient for users.

Hi.  Any  progress on this?  I also feel this would be an extremely useful feature.  To be
honest I was quite shocked that one could pass in an order by column name via an input
param but not a limit or offset value.  I expected this to be already present.  I don't
think it is unreasonable request.  It would be an intuitive feature and immensely useful
for pagination.  The lack of this feature may mean I have to forsake using SPs for my
data layer which is a huge sacrifice.  Any thoughts?

Has this been fixed or anywhere close? All data access in my web application are done
using stored procedures and without being able to use variables in LIMIT clause means we
simply cant implement pagination. This would really be a very useful feature.

I disagree that this is a feature request. It is bug that results in a serious limitation
on functionality and means that paging is not possible when using stored procedures.

Is there any version where this bug has been fixed 5.1.x ?

I've found a workaround to this that isn't too ugly:

create procedure foo ( limit_val int )
begin
  set @sql = concat('select * from myTable limit ', limit_val);
  prepare stmt from @sql; execute stmt;
end
//

I'm using MySQL 5.0.24. Apparently, you can't use a declared VARCHAR variable for the
concatenated SQL string, only a user variable.

Any news on this one? I'm not resorting to using prepared statements. The main reason for
this is that if I use prepared statements, I feel that I just as well could use texted
SQL. Which I'm never going to do again! :)

Using a prepared statement is that bad of a work around. If you're worried about security,
use the limit as the only variable and select from a temp table that you've created with
in the sproc.

I just made up my own workaround, without using any texted sql. I have also done som
performance testing, and it seems to be doing fine... :)

DELIMITER $$

DROP PROCEDURE IF EXISTS `[database]`.`[table]List` $$
CREATE PROCEDURE `[database]`.`[table]List` (_limit int, _offset int)
BEGIN
set @rownum:=0;

select * from(
 select
   (@rownum:=@rownum+1) as Rownumber,
   [table].*
 from
  [table]
 order by
   id desc
) as t
where
 Rownumber > _offset AND
 Rownumber <= (_offset + _limit);
END $$

DELIMITER ;

Just replace [database] with your database, and [table] with the table you want. And you
might want to modify the name of the stored procedure, too... :)

If you need to change anything (sorting, selection or whatever) do it in the NESTED
STATEMENT, not the outer statement, or it may not work!

Why has this not been fixed yet?
It's a shame that this bug has existed for so long, with no updates.

I think this is a very important feature. Instead of using paging i your application, yo
can do it in th database layer (where it belongs).

Currently I'm working on a sp that should return one row, show it to the user och ask if
he/she want to se next row.
But since this feature isen't added yet, I have to send all rows to the application and
sort it out there.

as you probobly understand this isn't very efficient at all.

this feuture enables paging to be run at the database layer where it belongs.

Regards,
Rikard von Zweigbergk
DBA

A nicer workaround from Kian Ryan:

DROP PROCEDURE IF EXISTS get_product_range $$
CREATE PROCEDURE get_product_range (
IN _START INTEGER,
IN _LIMIT INTEGER
)
BEGIN
PREPARE STMT FROM
" SELECT *
FROM products LIMIT ?,? ";
SET @START = _START;
SET @LIMIT = _LIMIT;
EXECUTE STMT USING @START, @LIMIT; /* Known bug in mySQL5 - exists as feature req. */
END $$

Why is this not fixed yet?!?

Any target date to fix this and make our lifes easier and better:)?

I personnaly found a fix here : http://bugs.mysql.com/bug.php?id=33856 . But this fix is
incomplete (see the comment)

Just to bump up the timestamp of this bug :) (and probably get some attention from the dev
team and then TAAAA TA RA TA TAAAA ... and the issue is fixed )

I started to use stored procedures today for the first time and was shocked about this
limitation/bug.

Jesus, get it fixed already please.

Fix please ASAP guys. This is a severely limiting lack of feature/bug that has been
requested for over 3 years now, and, correct me if I'm wrong, shouldn't too hard to
implement.

Still an issue in 5.1.26-rc

This really needs to be fixed. Please fix this.

any update on this bug/feature?  I am using 5.1.28....

Just a reminder to developers that this issue still persist.

COME ON!!! What the F#@% is going on here?
This ESSENTIAL feature has been missing for over 3 years now!! THREE YEARS!!

One would have to assume that there have been many less essential features that have been
added during that time. And that means that somebody in a senior position has deliberately
decided to withhold this essential functionality.

Why would somebody do that! Does somebody have a vendetta against stored procedures?

This is not a feature request this is essential functionality. (I suppose that if the IF
THEN END IF statement was missing you would call that a feature request too would you?)

Come on!! This is beyond ridiculous. How do we get this escalated to someone at a more
senior level that can finally get this thing done.

Is somebody on staff reading this? If there is someone on staff reading this please
respond to at least let us know that you are aware of this problem ... and hopefully
explaining why this hasn't been done yet and when you expect to get it done.

This bug (and the complete lack of support for LINQ/Entity Framework) has made me change
to SQL Express. That's what you get for neglecting your users.

can't believe this bug is not getting fixed. adding the functionality to return arrays
from selects in stored procedure could be called a feature request. the lack of this
function is rather a bug than a feature because no one would assume variables would not
work in the limit clause when they work everywhere else in the statement.

If anyone on the development team reads this thread, could you please let us know if a fix
is being considered or not for an upcoming release? This is a killer feature, glaring in
its omission, and for it to have been ignored for nearly four (4) years this summer is
truly a kick in the groin.

C'mon, make us happy! Thanks! :)

Here's one more user giving two cents.  This missing feature is an embarrassment to MySQL;
it needs to be added.  I try to support open source software with my clients, but when
they have technical staff I feel like I have to put a paper bag over my code to hide ugly
workarounds like this.  Please make it happen.

While I agree that this is annoying, we have just been informed that there is a much
better approach then what I've seen so far.  Rather than put the limit clause in your SQL
simply do the following:

set SQL_SELECT_LIMIT = p_LimitNum;

Issue your select but with no limit clause

set SQL_SELECT_LIMIT = default;

In our testing this works great and the only modifications you have to do to your queries
is to remove the limit clause entirely since we are setting it at the top.

Sean

Sean, I've been using the nested select solution as per Gert Brigsted (19 Feb 2007). It
works well.

If the inner select statement would return 50 records but my web site is displaying 10
records per grid page, is it possible to use SQL_SELECT_LIMIT to page through the
sequential sets of 10 records without using a nested select construct? Sorry for my
laziness but I have a SQL Server background and I can't see a way to do this off the top
of my head. Thanks.

I just ran into this problem myself last night and wasted 3 hours trying to find a
workaround that did not involve user variables and preparing the statement first. The
diagnostics generated are reminiscent of the late 70's (syntax error).

1. The LIMIT clause should accept procedure variables.
2. The PREPARE statement should accept a procedure variable.
3. The EXECUTE statement should accept procedure variable(s).

THERE IS NO REASON THIS SHOULD BE CALLED A "FEATURE REQUEST"

MySQL/Sun should be ashamed for letting such a severe problem languish here for OVER FOUR
YEARS!!!! C'mon guys, this is ludicrous. Do your damn job and fix this.

I am reopening this bug as a non-feature request.
The SP protocol should allow any statement that is possible in the prepared statement
protocol. Since prepared statements allow LIMIT parameters, so should stored procedures.
Hence, it's a bug.

How to repeat:

set @q = 'select * from t1 limit ?,?';
set @l1=10, @l2=1;
prepare q from @q;
execute q using @l1,@l2;
# succeeds 

create procedure p1 (l1 int,l2 int) deterministic  select * from t1 limit l1, l2;
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 'l1,l2' at line
1

Please read the MySQL Reference Manual:

"
LIMIT takes one or two numeric arguments, which must both be nonnegative integer
constants (except when using prepared statements).
"

http://dev.mysql.com/doc/refman/5.1/en/select.html

So? All that means is the manual needs to be updated as well.

Patches are welcome here. It is not a difficult change -- a change in the grammar should
be almost sufficient.

Setting back to Feature request. This is still a feature request and should be
handled/escalated as such. Setting it to a bug is wrong.

I was quite surprised to discover LIMIT takes only constants.  Thought I was doing
something wrong.  Hope this gets resolved soon.  The workaround I am using in the
meantime is to use prepared statements in my stored procedures.

This need so much to be fixed can't believe it isn't already...

This is a NEEDED feature for web apps, specifically for web paging.

Haven't find a good workaround for this "issue/feature request".

The Limit clause its so cool for paging, but this make it useless in Stored Procedures.

I don't f* care if its a bug or a feature, we need it..

By the way, I'm stil trying to do a workaround. I see that PREPARE statements inside
Stored Procedures could be useful, and there can be edited with the session variables....
but.. ALL EXAMPLES I have found the query returns only a row...

How can I get a Result Set and iterate it INSIDE a MySQL's Stored Procedure???? i.e. How
can I loop over multiple rows for a Prepare Statement??

Please post here any code sniped or send it to me edgarsalvadoraguilar at yahoo dot com

Many thx in advance..

P.D.
Sadly considering migrate to PostgreSQL

I ran into this issue today and I feel I must echo the desires of the other commenters
that this should be implemented ASAP.  After perusing the current bugs for mysql I can
see why this has been categorized as a feature request, and I can also see why it has
languished for 5 years.  I'm new to mysql, but I have to say that it is disheartening to
see an issue like this, which seems very important, buried under 3,000 other bugs which
for the most part seem even worse.  Like I said I'm new to mysql and open source in
general, but if there was a way I could get into the source code and implement this one
feature I'd jump on it.  Outside of that happening I don't see much chance of this
getting done.

Other notes:

Why has the thread discussing this issue been closed to comments?  Shouldn't there at
least be a statement on that thread instructing commenters to transfer all discussion to
this bug report?
http://forums.mysql.com/read.php?98,28212,260408#REPLY

SQL_SELECT_LIMIT won't help you if you're looking to use 2 different limits.  Say I want
to select a random sample of 40,000 rows from the top 75% of rows based on some ranking
column (say price of transaction at a store) in a table whose size I don't know.

select cast(count(1)*.75 as unsigned) into @top75 from table;
select * from (select * from table order by rank_column limit @top75) a order by rand()
limit 40000;

There's absolutely no excuses why this is not fixed after more than 4 years! It's
fundamental basics of SQL-scripting and stored procedures! Stop ignoring the problem now!

Ugggghhhh, 

Is this fixed ?  Not even a Target Version ??? If your not going to do it, just kill it
then, or just another sign Sun purchase, and coming Oracle purchase is really bad for us
usere ? "Feature Request" ??  Humorous.

Bob