Opened 10 years ago

Closed 10 years ago

Last modified 8 years ago

#350 closed defect (fixed)

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

Reported by: ronan@… Owned by: adrian
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: UI/UX:

Description (last modified by adrian)

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 Changed 10 years ago by adrian

  • Status changed from new to assigned

comment:2 Changed 10 years ago by adrian

  • Description modified (diff)

(Fixed formatting in description.)

comment:3 Changed 10 years ago by adrian

So for MySQL, it should be:

LIMIT 5, 4

...instead of:

LIMIT 5 OFFSET 4

comment:4 Changed 10 years ago by adrian

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

comment:5 Changed 10 years ago by adrian

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 Changed 10 years ago by adrian

  • Resolution set to fixed
  • Status changed from assigned to closed

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

comment:7 Changed 10 years ago by ronan@…

Thanks a lot Adrian, this is fantastic.

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