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
11387	order_by over a GenericRelation generates incorrect SQL	Andy Durdin	Malcolm Tredinnick	"When a queryset is ordered by a field in a related model (related by a GenericForeignKey/GenericRelation pair), the SQL generated for the query is incorrect: it has a LEFT JOIN to the related table, whereas it should have an INNER JOIN and also limit the join by the content type. In contrast, a queryset that is filtered by the same field in the related model generates correct SQL.

As a result, the query will return unexpected and/or duplicate rows.

Here's a simple example that demonstrates the problem:

'''models.py:'''
{{{
from django.db import models
from django.contrib.contenttypes import generic
from django.contrib.contenttypes.models import ContentType

class Foo(models.Model):
    bar = generic.GenericRelation(""Bar"")

class Bar(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey()

class Baz(models.Model):
    bar = generic.GenericRelation(""Bar"")
}}}

'''test code:'''
{{{
>>> from foo.models import *
>>> foo = Foo.objects.create()
>>> baz = Baz.objects.create()
>>> __ = Bar.objects.create(content_object=foo)
>>> __ = Bar.objects.create(content_object=baz)
>>> from django.db import connection
>>> #
>>> # This queryset has incorrect SQL
>>> qs = Foo.objects.order_by('bar__id')
>>> qs.query.as_sql()
('SELECT ""foo_foo"".""id"" FROM ""foo_foo"" LEFT OUTER JOIN ""foo_bar"" ON (""foo_foo"".""id"" = ""foo_bar"".""object_id"") ORDER BY ""foo_bar"".""id"" ASC', ())
>>> list(qs)
[<Foo: Foo object>, <Foo: Foo object>]
>>> #
>>> # Correct behaviour for comparison
>>> Foo.objects.filter(bar__id=1)
[<Foo: Foo object>]
>>> Foo.objects.filter(bar__id=1).query.as_sql()
('SELECT ""foo_foo"".""id"" FROM ""foo_foo"" INNER JOIN ""foo_bar"" ON (""foo_foo"".""id"" = ""foo_bar"".""object_id"") WHERE (""foo_bar"".""id"" = %s  AND ""foo_bar"".""content_type_id"" = %s )', (1, 8))
}}}

Tested against SVN trunk revision 11103"	Bug	closed	contrib.contenttypes	dev	Normal	fixed		me@…	Accepted	0	0	1	1	0	0