#350 closed defect (fixed)
"offset" keyword does not work on MySQL 3.x
Reported by: | 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 )
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 , 19 years ago
Status: | new → assigned |
---|
comment:2 by , 19 years ago
Description: | modified (diff) |
---|
comment:4 by , 19 years ago
comment:5 by , 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 , 19 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
(Fixed formatting in description.)