"offset" keyword does not work on MySQL 3.x

[ronan@…]

When you use the Django API to query your data using the get_items(offset=X) style call, the SQL that is generated seems to be in Postgres style that is only recently supported in MySQL (4.x and later, as far as I can tell.) The SQL generated is something like the following:

SELECT ... ORDER BY ... DESC LIMIT 5 OFFSET 4

On MySQL 3.x this gives the following error:

"You have an error in your SQL syntax near 'OFFSET 4 '"

It works fine on my 4.1.

As far as I can tell from the MySQL manual, there is an alternate form of query that should work on all versions of MySQL. Here's the relevant snippet from the SELECT format:

 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Since 3.23 is the default MySQL version for Fedora Core etc, this could affect quite a few people.

[Adrian Holovaty]

(Fixed formatting in description.)

[Adrian Holovaty]

So for MySQL, it should be:

LIMIT 5, 4

...instead of:

LIMIT 5 OFFSET 4

[Adrian Holovaty]

(In [540]) Added 'limit' and 'offset' unit tests, one of which fails in MySQL 3 (refs #350)

[Adrian Holovaty]

My previous comment is inaccurate. It should be this:

LIMIT 4, 5

That's the equivalent of this in PostgreSQL:

LIMIT 5 OFFSET 4

[Adrian Holovaty]

(In [541]) Fixed #350 -- 'offset' DB API parameter now works in MySQL 3. Tests from [540] pass. Thanks, ronan@…

[ronan@…]

Thanks a lot Adrian, this is fantastic.