id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
22434	Ordering is cleared in Subqueries with limits	maciej.pawlisz@…	justhamade	"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)
}}}
"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed			Accepted	1	0	0	1	0	0