Opened 19 years ago

Closed 19 years ago

Last modified 18 years ago

#350 closed defect (fixed)

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

Reported by: ronan@… Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version:
Severity: major Keywords: mysql database offset
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Adrian Holovaty)

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.

Change History (7)

comment:1 by Adrian Holovaty, 19 years ago

Status: newassigned

comment:2 by Adrian Holovaty, 19 years ago

Description: modified (diff)

(Fixed formatting in description.)

comment:3 by Adrian Holovaty, 19 years ago

So for MySQL, it should be:

LIMIT 5, 4

...instead of:

LIMIT 5 OFFSET 4

comment:4 by Adrian Holovaty, 19 years ago

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

comment:5 by Adrian Holovaty, 19 years ago

My previous comment is inaccurate. It should be this:

LIMIT 4, 5

That's the equivalent of this in PostgreSQL:

LIMIT 5 OFFSET 4

comment:6 by Adrian Holovaty, 19 years ago

Resolution: fixed
Status: assignedclosed

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

comment:7 by ronan@…, 19 years ago

Thanks a lot Adrian, this is fantastic.

Note: See TracTickets for help on using tickets.
Back to Top