Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#22434 closed Bug (fixed)

Ordering is cleared in Subqueries with limits

Reported by: maciej.pawlisz@… Owned by: justhamade
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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)

Change History (7)

comment:1 by justhamade, 10 years ago

Owner: changed from nobody to justhamade
Status: newassigned

comment:2 by justhamade, 10 years ago

Triage Stage: UnreviewedAccepted
Version: 1.6master

Wrote test and fix in pull request https://github.com/django/django/pull/2550

comment:3 by Simon Charette, 10 years ago

Has patch: set
Patch needs improvement: set

Left comments on the PR.

comment:4 by Simon Charette <charette.s@…>, 10 years ago

Resolution: fixed
Status: assignedclosed

In a13df671a5a5b867397ce05f73a59a3f4504868a:

Fixed #22434 -- Retain ordering on related sliced subqueries.

Thanks maciej.pawlisz for the report, and charettes for the review.

comment:5 by Simon Charette <charette.s@…>, 10 years ago

In 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

comment:6 by giovannibajo, 10 years ago

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?

comment:7 by Tim Graham, 10 years ago

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?

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