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