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
19720	Invalid SQL statement generated when deleting rows in Meta ordered tables (found using Oracle)	dylan.klomparens@…	nobody	"Given the following example model, invalid SQL deletes are composed in Django's ORM. This breaks Django's interaction with Oracle, producing error 'ORA-00907'.

{{{
from django.db import models
class OrderedModel(models.Model):
	name = models.CharField(max_length=100)
	class Meta:
		ordering = ['name']
	def __unicode__(self):
		return unicode(self.name)
}}}

Django's source code comments state that a QuerySet delete is broken into two SQL queries: one to find the items to delete, and the second to perform the deletion. The general form of this is:

{{{DELETE FROM ""TABLE"" WHERE ""ID"" IN (SEARCH_STATEMENT)}}}

Thus, there is no reason to order {{{SEARCH_STATEMENT}}}. However, Django provides an {{{ORDER BY}}} clause in the {{{SEARCH_STATEMENT}}} when Meta ordering is specified. Using the example model shown above, deleting an object from the admin interface produces this SQL {{{SEARCH_STATEMENT}}}:

{{{SELECT ""Test_orderedmodel"".""id"", ""Test_orderedmodel"".""name"" FROM ""Test_orderedmodel"" WHERE ""Test_orderedmodel"".""id"" IN (5, 6) ORDER BY ""Test_orderedmodel"".""name"" ASC}}}

This bug can be remedied by patching function {{{delete()}}} in class {{{QuerySet}}} in file {{{$DJANGO_HOME/db/models/query.py}}}, line 525.

The buggy line is: {{{del_query.query.clear_ordering()}}}
And it should be: {{{del_query.query.clear_ordering(force_empty=True)}}}

After the code is patched the example SQL statement becomes:

{{{SELECT ""Test_orderedmodel"".""id"", ""Test_orderedmodel"".""name"" FROM ""Test_orderedmodel"" WHERE ""Test_orderedmodel"".""id"" IN (5, 6)}}}

-----

Further testing reveals that this is an isolated bug.

I looked for other uses of the {{{clear_ordering()}}} function in Django's source code that might cause similar bugs. I found two other usage of the function in {{{$DJANGO_HOME/db/models/query.py}}}: one in the {{{latest()}}} function and the other in the {{{order_by()}}} function. After extensive testing I concluded that Meta ordering '''does not''' adversely affect these functions. That said, it may be prudent to make the {{{force_empty}}} parameter of the {{{clear_ordering()}}} function an explicit parameter instead of providing a default of {{{False}}}. All other uses of the {{{clear_ordering()}}} function that I found specify {{{force_empty=True}}}.

I am 99% confident this bug does not adversely affect any other portions of Django's code."	Bug	closed	Database layer (models, ORM)	1.5-beta-1	Release blocker	fixed	ORM SQL ordering Meta		Accepted	1	0	0	0	1	0