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
27193	ORDER BY clause not included in subqueries using select_for_update()	sqwishy	nobody	"This was tested using Django 1.9.5. I believe the code below fails to generate the correct SQL. I explicitly set an ordering in the subquery but it is not expressed in the generated SQL. This alters the behaviour of the SELECT statement when the FOR UPDATE clause is used. My use case is to lock rows in a particular order with the goal of preventing deadlocks.

Example code:
{{{
with transaction.atomic():
    print(User.objects.filter(id__in=User.objects.order_by('id').select_for_update()))
}}}

PostgreSQL logs:
{{{
2016-09-08 02:59:43 ACWST [30398-20] testsystem@testsystem LOG:  statement: BEGIN
2016-09-08 02:59:43 ACWST [30398-21] testsystem@testsystem LOG:  statement: SELECT ""testapp_user"".""id"", ""testapp_user"".""password"", ... FROM ""testapp_user"" WHERE ""testapp_user"".""id"" IN (SELECT ""testapp_user"".""id"" FROM ""testapp_user"" FOR UPDATE) LIMIT 21
2016-09-08 02:59:43 ACWST [30398-22] testsystem@testsystem LOG:  statement: COMMIT
}}}

It looks like the order_by is being cleared on the queryset object here https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L476 

I can verify this as it looks like ORDER BY does show up when I include distinct() in the subquery - which isn't a suitable workaround as DISTINCT is not compatible with FOR UPDATE - and by slicing the subquery as to set an offset or a limit.

For now, an acceptable workaround seems to make the SELECT FOR UPDATE in a separate query instead of a subquery. That at least seems to accomplish the goal I am trying to achieve."	New feature	closed	Database layer (models, ORM)	1.9	Normal	fixed		Shai Berger	Accepted	1	0	0	0	0	0