Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#9406 closed (fixed)

Query.as_sql() generates invalid ORDER BY clause

Reported by: egenix_viktor Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: Query as_sql ORDER clause ordering duplicate wrong SQL generate generates
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Backend: SQLite

Test case: regressiontests/model_inheritance_regress

Statement: c1.get_next_by_pub_date()

The Query.as_sql() method in django.db.models.sql.query generates two ordering items for the same "model_inheritance_regress_article"."pub_date" field:

(The generated query has been split into multiple lines for readability.)

SELECT
    "model_inheritance_regress_article"."id",
    "model_inheritance_regress_article"."headline",
    "model_inheritance_regress_article"."pub_date",
    "model_inheritance_regress_articlewithauthor"."article_ptr_id",
    "model_inheritance_regress_articlewithauthor"."author"
FROM "model_inheritance_regress_articlewithauthor"
INNER JOIN "model_inheritance_regress_article"
ON ("model_inheritance_regress_articlewithauthor"."article_ptr_id" = "model_inheritance_regress_article"."id")
WHERE (
    "model_inheritance_regress_article"."pub_date" > %s OR  (
        "model_inheritance_regress_articlewithauthor"."article_ptr_id" > %s AND
        "model_inheritance_regress_article"."pub_date" = %s ))
ORDER BY
    "model_inheritance_regress_article"."pub_date" ASC,
    "model_inheritance_regress_article"."pub_date" DESC,
    "model_inheritance_regress_article"."headline" ASC
LIMIT 1

Parameters:

(u'2005-08-01 03:00:00', 1, u'2005-08-01 03:00:00')

It works on SQLite for some reason, but such an ORDER BY clause causes an error message on many database servers, such as on MS SQL Server 2005 (when used with another backend, certainly).

Change History (4)

comment:1 Changed 8 years ago by egenix_viktor

Component: UncategorizedDatabase layer (models, ORM)
Keywords: Query as_sql ORDER clause ordering duplicate wrong SQL generate generates added
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

comment:2 Changed 8 years ago by Malcolm Tredinnick

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

Nice catch. Looks like bad SQL indeed.

comment:3 Changed 8 years ago by Malcolm Tredinnick

Resolution: fixed
Status: assignedclosed

(In [9251]) Fixed #9406 -- Ensure that each database column is only represented once in the
"ORDER BY" clause of an SQL statement.

comment:4 Changed 8 years ago by Malcolm Tredinnick

(In [9252]) [1.0.X] Fixed #9406 -- Ensure that each database column is only represented
once in the "ORDER BY" clause of an SQL statement.

Backport of r9251 from trunk.

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