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 17:09	Modified:	24 Sep 2010 19:59
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	limit, Parameters, PS, SP, variables

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

Any news on this?

LIMIT statement STILL does not accept variable.
It seems that this BUG has been intentionally ignored by mySQL developers. Why ?

I just created a SP that was supposed to use this feature, but it seems that hasn't been implemented (5.1.42-community-log).

Could we please get a status update on this request? Is there any chance to be implemented?

Thanks

I want this feature too.

Seems everybody need this feature (me too)
But, during it's not implemented we can use another ways.

In my case, i use stored functions and i can't use dynamic SQL.
But, it's possible to use subqueries like below

CREATE FUNCTION myfunction()
  RETURNS text CHARSET cp1251
BEGIN
  DECLARE i INT;
  DECLARE f_count,f_id,f_parent_id

  -- set f_count, f_parent_id by some logic...

  SET i = 1;
  WHILE i <= f_count DO
    SET @rownum = 0;
    SELECT id INTO f_id FROM (
      SELECT @rownum=@rownum+1 AS rownum,id FROM mytable WHERE parent_id = f_parent_id ORDER BY id
    )z WHERE rownum = f_index;

    -- equal to SELECT id FROM mytable WHERE parent_id=f_parent_id LIMIT 0,f_index

    -- some logic

    -- next
    SET i = i + 1;
  END WHILE;

  -- ...
END

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103316

2993 Konstantin Osipov	2010-03-15
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      Pending tests, including tests of replication.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105573

3013 Konstantin Osipov	2010-04-13
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105578

3013 Konstantin Osipov	2010-04-14
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105579

3015 Konstantin Osipov	2010-04-14
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

This was queued without a code review and is present in 5.5.6.
Now you can use stored procedure variables in the LIMIT clause (but not user variables). I filed a separate bug for user variables in LIMIT, 
see Bug#56811.

Still have the code review comments to handle, see http://lists.mysql.com/commits/105585

Noted in 5.5.6, 6.0.14 changelogs.

Within stored programs, LIMIT clauses now accept integer-valued
routine parameters or local variables as parameters.

That's correct, Paul.

Now what would be really useful (to me at least) is if this worked with cursors.

For example:

CREATE DEFINER=`matt`@`%` PROCEDURE `exampleLimit`()
proc:BEGIN

DECLARE iEof INT DEFAULT 0;
DECLARE iLimit INT;

DECLARE iExampleId INT;
DECLARE sName VARCHAR(255);

DECLARE cExamples CURSOR FOR
	SELECT exampleId, `name`
	FROM example
	LIMIT 0, iLimit;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET iEof=1;

-- TODO: Programatically determine the required limit
SET iLimit=10;

OPEN cExamples;
exampleLoop:LOOP
	SET iEof=0;
	FETCH cExamples INTO iExampleId, sName;
	IF iEof THEN
		LEAVE exampleLoop;
	END IF;

	-- TODO: Something useful...
	SELECT iExampleId, sName;

END LOOP exampleLoop;

END proc