Integer in MySQL Subqueries in store procedure

Question

I made simple procedure just to demonstrate

 CREATE PROCEDURE `demo`(demo_int int)
BEGIN
 DECLARE minid INT;
 SELECT min(id) FROM (SELECT id FROM events LIMIT demo_int,9999999999999999) as hoo INTO minid;
END$$

The problem is with demo_int, if i change it to

LIMIT 1,9999999999999999

it works but

LIMIT demo_int,9999999999999999

Does not... It gives error

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 'demo_int,9999999999999999) as hoo INTO minid; END' at line 4 (errno: 1064)

Any clues?

Answer 1

The problem (whether its a bug or a feature is a contentious issue) is that you cannot use that parameter with a LIMIT statement. The LIMIT statement takes integer constants, the key point in this case is that they must be constants, not variables.

See here for a submitted bug report, and here for a possible workaround which involves using prepared statements.