Opened 4 years ago

Closed 4 years ago

Last modified 3 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: master
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 Changed 4 years ago by justhamade

Owner: changed from nobody to justhamade
Status: newassigned

comment:2 Changed 4 years ago by justhamade

Triage Stage: UnreviewedAccepted
Version: 1.6master

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

comment:3 Changed 4 years ago by Simon Charette

Has patch: set
Patch needs improvement: set

Left comments on the PR.

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

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 Changed 4 years ago by Simon Charette <charette.s@…>

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 Changed 3 years ago 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?

comment:7 Changed 3 years ago by Tim Graham

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