Bug #12199 LIMIT 0,-1 syntax error in MySQL 4.1.12

• Submitted: 27 Jul 2005 9:28
• Modified: 27 Jul 2005 10:23
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: MySQL 4.1.12
• OS: Linux (Linux)
• CPU Architecture: Any

[27 Jul 2005 9:28] [ name withheld ]

Description:
MySQL 4.1.12 gives syntax error when using LIMIT 0,-1

Other (previous) versions do allow this select option.

Full MySQL server version detail: MySQL 4.1.12-Debian_0.dotdeb.0-log

How to repeat:
SELECT * FROM <table> LIMIT 0,-1

Suggested fix:
Resolve this bug

[27 Jul 2005 10:23] Valeriy Kravchuk

According to the manual (http://dev.mysql.com/doc/mysql/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 be integer constants.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):"

So, when you are asking:  select * from t1 limit 0,-1;

you ask for up to -1 row to return. This is a (logical) error. If you need all rows, use some large number for the second parameter (as explained in the manual).

[27 Jul 2005 10:39] [ name withheld ]

According to the product, this has worked until 4.1.10. Why is this feature removed, since its a very common way to list all records when adding always a LIMIT parameter to a SELECT?!