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
5937	Generic Relations backward lookups generate invalid SQL	Max Naude	Malcolm Tredinnick	"Given the following model is would seem that the Generic Relations' GenericRelatedObjectManager generates invalid SQL, as the doctest in the following snippet proves:

{{{
from django.db import models
from django.contrib.contenttypes.generic import GenericForeignKey, GenericRelation
from django.contrib.contenttypes.models import ContentType

#==============================================================================
class Alert(models.Model):
    """"""
    Alerts for all kinds of widgets
    
    # create a blue widget and add an alert
    >>> blue = BlueWidget.objects.create(name='Blue 1')
    >>> alert = Alert(name='alert 1')
    >>> alert.object = blue
    >>> alert.save()
    
    # create a red widget and add an alert
    >>> red = RedWidget.objects.create(name='Red 1')
    >>> alert = Alert(name='alert 2')
    >>> alert.object = red
    >>> alert.save()
    
    # create a red widget with no alert
    >>> red = RedWidget.objects.create(name='Red 2')
    
    # create a blue widget and add an alert
    >>> blue = BlueWidget.objects.create(name='Blue 2')
    >>> alert = Alert(name='alert 3')
    >>> alert.object = blue
    >>> alert.save()
    
    # get a list of red widgets with alerts - there should be only 1
    >>> reds = RedWidget.objects.filter(alerts__name__contains='alert').distinct()
    >>> reds
    [<RedWidget: RedWidget Red 1>]
    
    # this currently yields: [<RedWidget: RedWidget Red 1>, <RedWidget: RedWidget Red 2>]
    
    """"""
    created = models.DateTimeField(auto_now_add=True)
    name = models.CharField(maxlength=128)
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    object = GenericForeignKey('content_type', 'object_id')
    
    #--------------------------------------------------------------------------
    def __str__(self):
        return '%s alert for %s widget' % (self.name, self.object)
    
#==============================================================================
class RedWidget(models.Model):
    """"""
    The red kind of widget
    """"""
    created = models.DateTimeField(auto_now_add=True)
    name = models.CharField(maxlength=128)
    alerts = GenericRelation(Alert)
    
    #--------------------------------------------------------------------------
    def __str__(self):
        return 'RedWidget %s' % (self.name)
    
#==============================================================================
class BlueWidget(models.Model):
    """"""
    The blue kind of widget
    """"""
    created = models.DateTimeField(auto_now_add=True)
    name = models.CharField(maxlength=128)
    alerts = GenericRelation(Alert)
    
    #--------------------------------------------------------------------------
    def __str__(self):
        return 'BlueWidget %s' % (self.name)    
    
}}}

Following the doctest above, the generated SQL after the last statement in the doctest:
{{{
reds = RedWidget.objects.filter(alerts__name__contains='alert').distinct()
}}}
is this:
{{{
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name` 
FROM `testapp_redwidget` 
LEFT OUTER JOIN `testapp_alert` AS `m2m_testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `m2m_testapp_redwidget__alerts`.`object_id` 
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `m2m_testapp_redwidget__alerts`.`id` = `testapp_redwidget__alerts`.`id` 
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
}}}

That is - although it is linking to the right table (redwidget) it is not filtering on content type in the alert table.  As a result it picks up the id of the second blue widget (2), which matches the id of the second, alert-less red widget (2) and links it in the query to produce the buggy result set.   

One would expect this SQL to rather look like this:
{{{
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name` 
FROM `testapp_redwidget` 
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `testapp_redwidget__alerts`.`object_id` 
INNER JOIN `django_content_type` AS `testapp_redwidget__django_content_type` ON `testapp_redwidget__alerts`.`content_type_id` = `testapp_redwidget__django_content_type`.`id`
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
AND (`testapp_redwidget__django_content_type`.`app_label` = 'testapp')
AND (`testapp_redwidget__django_content_type`.`model` = 'redwidget')
}}}

.. or, even better (and because I don't quite understand the reason for the LEFT OUTER join):
{{{
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name` 
FROM `testapp_redwidget` 
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `testapp_redwidget__alerts`.`object_id` 
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
AND (`testapp_redwidget__alerts`.`content_type_id` = 9)
}}}

The only current workaround for this is to explicitly filter on content type id - this works:
{{{
>>> ctype = ContentType.objects.get_for_model(RedWidget)
>>> reds = RedWidget.objects.filter(alerts__name__contains='alert', alerts__content_type=ctype).distinct()
>>> reds
[<RedWidget: RedWidget Red 1>]
}}}"		closed	Contrib apps	dev		fixed	generic relation, qs-rf	jag@… drackett@… aaron@… matt@… wonlay@…	Accepted	1	0	0	0	0	0