Ordering is cleared in Subqueries with limits

[maciej.pawlisz@…]

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)

[justhamade]

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

[Simon Charette]

Left comments on the PR.

[Simon Charette <charette.s@…>]

In a13df671a5a5b867397ce05f73a59a3f4504868a:

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

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

[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

[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?

[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?