#113 MySQL 4.1 and LIMIT X, -1

[John Speno]

I'm reposting this here from the mailing list so it doesn't get lost:

From:     deelan@interplanet.it
Subject:    \[SQLObject\] MySQL 4.1 and LIMIT X, -1
Date:   September 17, 2005 11:53:38 AM EDT
To:       sqlobject-discuss@lists.sourceforge.net

Hi there,

I'm expericing a query syntax error using SQLObject 0.7b1
and MySql/Win 4.1.11.

Essentially I'm slicing a SelectResults object this way:

entry.revisions[1:]

Where revisions is a SQLMultipleJoin('Revision'), which generates
this query:

SELECT revision.id, ... FROM revision
WHERE (revision.entry_id = 2) ORDER BY date_created LIMIT 1, -1

AFAIK the forms LIMIT X, -1 and LIMIT -1 aren't supported
anymore starting from MySQL 4.1.x+ (i cannot track down the
exact release when this happened)

if i open up the mysql console it's easy to reproduce the bug:

mysql> select * from revision limit 0, -1;
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 '-1' at line 1

another table:

mysql> select * from user limit 0, -1;
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 '-1' at line 1

while this works:
mysql> select * from user limit 0, 1;
...

***

Maybe SQLObject could switch to the LIMIT/OFFSET alternative
notation:

'' For compatibility with PostgreSQL, MySQL also supports the LIMIT
row_count OFFSET offset syntax.'' [1]

but it's not clear to me if we have a way to specify an offset but
not a limit count like the old LIMIT n, -1

thanks in advance.

-- deelan

[1] http://dev.mysql.com/doc/mysql/en/select.html

[Oleg Broytman]

Logged In: YES
user_id=4799

The problem is here:

To retrieve all rows from a certain offset up to the end of
the result set, you can use some large number for the second
parameter. This statement retrieves all rows from the 96th
row to the last:
mysql> SELECT * FROM table LIMIT 95,18446744073709551615;

You can use neither -1 nor 0. I am loathing to add such a
huge but arbitarray constant limit to SQLObject. Don't know
what to do...