[Django] #22434: Ordering is cleared in Subqueries with limits
Django
----------------------------------------------+--------------------
Reporter: maciej.pawlisz@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
This can lead to unexpected and unpredictable results. In my case I had a
method on ModelManager that returned custom query. I was interested only
in the most recent result:
{{{
In [76]: pr = Program.objects.get_all_for_user(98,True)
In [77]: print pr[:1].query
SELECT "training_program"."id", "training_program"."name",
"training_program"."d
escription", "training_program"."created", "training_program"."modified",
"train
ing_program"."created_by_id", "training_program"."status" FROM
"training_program
" INNER JOIN "training_programuser" ON ( "training_program"."id" =
"training_pro
gramuser"."program_id" ) WHERE ("training_program"."status" = published
AND "tr
aining_programuser"."user_id" IN (98) AND
"training_programuser"."available_sinc
e" <= 2014-04-11 08:40:19.680000 ) ORDER BY
"training_programuser"."user_id" DES
C, "training_programuser"."available_since" DESC LIMIT 1
}}}
The resulting query is as expected. Unfortunately when using this query in
another query produces sql which does not give results as expected:
{{{
In [78]: print Session.objects.filter(programs__in=pr[:1]).query
SELECT "training_session"."id", "training_session"."name",
"training_session"."d
escription", "training_session"."created", "training_session"."modified",
"train
ing_session"."created_by_id", "training_session"."areas" FROM
"training_session"
INNER JOIN "training_program_sessions" ON ( "training_session"."id" =
"training
_program_sessions"."session_id" ) WHERE
"training_program_sessions"."program_id"
IN (SELECT "training_program"."id" FROM "training_program" INNER JOIN
"training
_programuser" ON ( "training_program"."id" =
"training_programuser"."program_id"
) WHERE ("training_program"."status" = published AND
"training_programuser"."u
ser_id" IN (98) AND "training_programuser"."available_since" <= 2014-04-10
13:48
:57.228000 ) LIMIT 1)
}}}
There is no ORDER BY clause in sql, so the resulting Program is different
then the one in previous example.
Quick fix that worked for me was changing line 408 in
django/db/modelse/sql/where.py, to:
{{{
if query.low_mark == 0 and query.high_mark is None:
query.clear_ordering(True)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/22434>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: assigned
(models, ORM) | Version: 1.6
Severity: Normal | Resolution:
Keywords: | Triage Stage:
Has patch: 0 | Unreviewed
Needs tests: 0 | Needs documentation: 0
Easy pickings: 0 | Patch needs improvement: 0
| UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by justhamade):
* owner: nobody => justhamade
* needs_docs: => 0
* status: new => assigned
* needs_tests: => 0
* needs_better_patch: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:1>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by justhamade):
* version: 1.6 => master
* stage: Unreviewed => Accepted
Comment:
Wrote test and fix in pull request
https://github.com/django/django/pull/2550
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:2>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: assigned
(models, ORM) | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
Left comments on the PR.
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:3>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
(models, ORM) | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Simon Charette <charette.s@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"a13df671a5a5b867397ce05f73a59a3f4504868a"]:
{{{
#!CommitTicketReference repository=""
revision="a13df671a5a5b867397ce05f73a59a3f4504868a"
Fixed #22434 -- Retain ordering on related sliced subqueries.
Thanks maciej.pawlisz for the report, and charettes for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:4>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: closed
(models, ORM) | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Simon Charette <charette.s@…>):
In [changeset:"7f8bd1a4b39ed834dffa0adc8fa19ef6b807f45d"]:
{{{
#!CommitTicketReference repository=""
revision="7f8bd1a4b39ed834dffa0adc8fa19ef6b807f45d"
[1.7.x] Fixed #22434 -- Retain ordering on related sliced subqueries.
Thanks maciej.pawlisz for the report, and charettes for the review.
Backport of a13df671a5 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:5>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: closed
(models, ORM) | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by giovannibajo):
This bug represents a regression; we just upgraded to from Django 1.4 to
Django 1.6, we hit this bug, and we had to manually apply the patch to fix
it; is there any plan to apply the patch to the 1.6 serie?
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:6>
Django
Reporter: maciej.pawlisz@… | Owner:
Type: Bug | justhamade
Component: Database layer | Status: closed
(models, ORM) | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by timo):
If the tests that were added here pass on 1.4 we can do the backport. The
backport isn't clean though, could you send a PR along with an added
mention in the 1.6.6 release notes?
--
Ticket URL: <https://code.djangoproject.com/ticket/22434#comment:7>