12

So what happens when you use a negative value in a LIMIT clause? Will there be negative consequences (slight pun intended)?

I don't see any documentation on this, and MySQL forums, as I recall, sux.

Background: I'm using MySQL CE 5.5.20. I wrote a stored procedure that take an int parameter and uses it for the LIMIT clause. I needed a way to return all rows if I wanted, so I tried passing in -1 for the limit parameter in my store procedure/routine, and that worked.

b01
  • 4,076
  • 2
  • 30
  • 30
  • Why not use a very big integer instead of -1 if you really really want to put a limit clause ? – Denys Séguret Apr 23 '12 at 13:55
  • @DenysSéguret Because using -1 or 0 is common (or a least a familiar practice in the programming world to indicate "no limit". Which is why I tried it in the first place. Otherwise my choice would be random. – b01 May 20 '20 at 14:26

2 Answers2

9

According to the documentation, it must be a non-negative integer

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 (except when using prepared statements).

It sounds to me like what you are experiencing is an undocumented bug, in that mysql should throw an error if the parameters are not inline with what the documentation requires.

My suggestion would be to use syntactically corrected SQL in your stored procedure. You could still use a negative int as a flag to show all though. Something like this

IF myparam >= 0 THEN
    SELECT * FROM `mytable` LIMIT myparam;
ELSE
    SELECT * FROM `mytable`;
END IF;
wmarbut
  • 4,595
  • 7
  • 42
  • 72
  • I'm accepting this answer, as this is correct. I was hoping someone would point out that it was just an undocumented feature or a bug of early versions. – b01 May 20 '20 at 14:28
2

When using this on other versions of MySQL, it might generate an error.

The error in MySQL

ONOZ
  • 1,390
  • 2
  • 11
  • 19
  • Okay, I just checked that if you are using negative limit in a normal query, it gives error. But when you are passing limit as a parameter in a procedure and use that parameter in limit, then negative value of parameter is returning all rows. – No Sound Feb 18 '20 at 10:00