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
7277	after queryset refactor, specifying > 2 OR'ed conditions that span many-to-many relationships returns broken SQL	Tobias McNulty	nobody	"For a prettier code view, see http://dpaste.com/hold/51756/

{{{
class Person(models.Model):
	name = models.CharField(max_length=100)
	
	def __unicode__(self):
		return self.name

class Group(models.Model):
	name = models.CharField(max_length=100)
	parent = models.ForeignKey('Group', null=True, related_name='children')
	
	def __unicode__(self):
		return self.name

class Node(models.Model):
	name = models.CharField(max_length=100)
	group = models.ForeignKey(Group, related_name='nodes')
	
	people = models.ManyToManyField(Person, related_name='nodes')
	
	def __unicode__(self):
		return self.name

p = Person.objects.create(name='Joe')
g = Group.objects.create(name='Group 1')

# this looks good:
nodes = p.nodes.filter( Q(group=g) | Q(group__children=g) )
""""""
SELECT ""smart_node"".""id"",
       ""smart_node"".""name"",
       ""smart_node"".""group_id""
FROM   ""smart_node""
       LEFT OUTER JOIN ""smart_node_people""
         ON (""smart_node"".""id"" = ""smart_node_people"".""node_id"")
       INNER JOIN ""smart_group""
         ON (""smart_node"".""group_id"" = ""smart_group"".""id"")
       LEFT OUTER JOIN ""smart_group"" T5
         ON (""smart_group"".""id"" = T5.""parent_id"")
WHERE  ""smart_node_people"".""person_id"" = 2
       AND (""smart_node"".""group_id"" = 1
             OR T5.""id"" = 1)
""""""

# where is the last condition ???
nodes = p.nodes.filter( Q(group=g) | Q(group__children=g) | Q(group__children__children=g) )
""""""
SELECT ""smart_node"".""id"",
       ""smart_node"".""name"",
       ""smart_node"".""group_id""
FROM   ""smart_node""
       LEFT OUTER JOIN ""smart_node_people""
         ON (""smart_node"".""id"" = ""smart_node_people"".""node_id"")
       INNER JOIN ""smart_group""
         ON (""smart_node"".""group_id"" = ""smart_group"".""id"")
       LEFT OUTER JOIN ""smart_group"" T5
         ON (""smart_group"".""id"" = T5.""parent_id"")
WHERE  ""smart_node_people"".""person_id"" = 2
       AND (""smart_node"".""group_id"" = 1
             OR T5.""id"" = 1)
""""""

# it's not a problem with children__children, as it works fine here:
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children=g) )
""""""
SELECT ""smart_node"".""id"",
       ""smart_node"".""name"",
       ""smart_node"".""group_id""
FROM   ""smart_node""
       LEFT OUTER JOIN ""smart_node_people""
         ON (""smart_node"".""id"" = ""smart_node_people"".""node_id"")
       INNER JOIN ""smart_group""
         ON (""smart_node"".""group_id"" = ""smart_group"".""id"")
       LEFT OUTER JOIN ""smart_group"" T5
         ON (""smart_group"".""id"" = T5.""parent_id"")
       LEFT OUTER JOIN ""smart_group"" T6
         ON (T5.""id"" = T6.""parent_id"")
WHERE  ""smart_node_people"".""person_id"" = 2
       AND (""smart_node"".""group_id"" = 1
             OR T6.""id"" = 1)
""""""

# specifying the second two conditions in reverse order seems to fix it!
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children=g) | Q(group__children=g) )
""""""
SELECT ""smart_node"".""id"",
       ""smart_node"".""name"",
       ""smart_node"".""group_id""
FROM   ""smart_node""
       LEFT OUTER JOIN ""smart_node_people""
         ON (""smart_node"".""id"" = ""smart_node_people"".""node_id"")
       INNER JOIN ""smart_group""
         ON (""smart_node"".""group_id"" = ""smart_group"".""id"")
       LEFT OUTER JOIN ""smart_group"" T5
         ON (""smart_group"".""id"" = T5.""parent_id"")
       LEFT OUTER JOIN ""smart_group"" T6
         ON (T5.""id"" = T6.""parent_id"")
WHERE  ""smart_node_people"".""person_id"" = 2
       AND (""smart_node"".""group_id"" = 1
             OR T6.""id"" = 1
             OR T5.""id"" = 1)
""""""

# however, adding one more condition breaks it again (T6 is missing from OR statements):
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children__children=g) | Q(group__children__children=g) | Q(group__children=g) )
""""""
SELECT ""smart_node"".""id"",
       ""smart_node"".""name"",
       ""smart_node"".""group_id""
FROM   ""smart_node""
       LEFT OUTER JOIN ""smart_node_people""
         ON (""smart_node"".""id"" = ""smart_node_people"".""node_id"")
       INNER JOIN ""smart_group""
         ON (""smart_node"".""group_id"" = ""smart_group"".""id"")
       LEFT OUTER JOIN ""smart_group"" T5
         ON (""smart_group"".""id"" = T5.""parent_id"")
       LEFT OUTER JOIN ""smart_group"" T6
         ON (T5.""id"" = T6.""parent_id"")
       LEFT OUTER JOIN ""smart_group"" T7
         ON (T6.""id"" = T7.""parent_id"")
WHERE  ""smart_node_people"".""person_id"" = 2
       AND (""smart_node"".""group_id"" = 1
             OR T7.""id"" = 1
             OR T5.""id"" = 1)
""""""
}}}"		closed	Database layer (models, ORM)	dev		fixed	qsrf-cleanup	django@… omat@…	Unreviewed	0	0	0	0	0	0