Bug #63778 Can't use connection variable with LIMIT

• Submitted: 16 Dec 2011 20:18
• Modified: 16 Dec 2011 20:27
• Reporter: Greg Kemnitz
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5.14-enterprise-commercial-advanced-lo
• OS: Linux
• CPU Architecture: Any

[16 Dec 2011 20:18] Greg Kemnitz

Description:
I want to do something like

create table t (value float);
insert into t values (-1), (0), (1), (2), (3), (4), (5), (6);

set @lim=5;

select min(value) from (select value from t order by value limit @lim) x;

Unfortunately, I get a syntax error in the "limit" part of the above.

The actual operation is much more complicated - the "lim" is also derived from other tables, etc, so I was hoping this could be made to work.

Here's the notes from running mysql:

(gkemnitz_rw@stagedb01a) [ps_surge_1] >create table t (value float);
Query OK, 0 rows affected (0.00 sec)

(gkemnitz_rw@stagedb01a) [ps_surge_1] >insert into t values (-1), (0), (1), (2), (3), (4), (5), (6);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

(gkemnitz_rw@stagedb01a) [ps_surge_1] >
(gkemnitz_rw@stagedb01a) [ps_surge_1] >set @lim=5;
Query OK, 0 rows affected (0.00 sec)

(gkemnitz_rw@stagedb01a) [ps_surge_1] >
(gkemnitz_rw@stagedb01a) [ps_surge_1] >select min(value) from (select value from t order by value limit @lim) x;
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 '@lim) x' at line 1
(gkemnitz_rw@stagedb01a) [ps_surge_1] >select min(value) from (select value from t order by value limit 5) x;
+------------+
| min(value) |
+------------+
|         -1 |
+------------+
1 row in set (0.00 sec)

How to repeat:
Use the above example.

Suggested fix:
Fix limit (or the step in the parser that expands connection variables) to deal with this.

[16 Dec 2011 20:27] Valeriy Kravchuk

This is a documented limitation (see http://dev.mysql.com/doc/refman/5.5/en/select.html):

"The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

    - Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
    - Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6."