Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#9406 closed (fixed)

Query.as_sql() generates invalid ORDER BY clause

Reported by: egenix_viktor Owned by: mtredinnick
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 7 years ago by egenix_viktor

  • Component changed from Uncategorized to Database 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 7 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick
  • Status changed from new to assigned

Nice catch. Looks like bad SQL indeed.

comment:3 Changed 7 years ago by mtredinnick

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

(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 7 years ago by mtredinnick

(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