Opened 17 years ago

Closed 16 years ago

Last modified 13 years ago

#7277 closed (fixed)

after queryset refactor, specifying > 2 OR'ed conditions that span many-to-many relationships returns broken SQL

Reported by: Tobias McNulty Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: qsrf-cleanup
Cc: django@…, omat@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)
"""

Change History (8)

comment:1 by omat, 17 years ago

I am also having trouble OR'ing query sets that span more than 2 tables as I have explained here:
http://groups.google.com/group/django-users/browse_thread/thread/48cd89cd0ab099e1

This may be a different issue, as I am getting KeyError.

This line, in django.db.query (line 321 svn version 7547):
promote = (rhs.alias_map[alias][JOIN_TYPE] == self.LOUTER)

complains that the second extra table's name is not in the rhs.alias_map dictionary.

I may open a seperate ticket or post the details here.

comment:2 by anonymous, 16 years ago

Cc: django@… added

I also got some problems doing OR lookups. Apparently, as discussed in [1], unions with an empty queryset always result in an empty queryset... Should a separate ticket be opened for that or is it a problem directly related to this ticket?

[1] http://groups.google.com/group/django-users/browse_thread/thread/f7c0603f08909945

comment:3 by anonymous, 16 years ago

Cc: omat@… added

comment:4 by George Vilches, 16 years ago

Keywords: qsrf-cleanup added

comment:5 by Jacob, 16 years ago

milestone: 1.0

comment:6 by jwinter, 16 years ago

This looks like a dupe of #7290, which has been marked a duplicate of #7125.

comment:7 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: newclosed

I didn't believe it at first, but this did turn out to be the same problem as #7125. So it was fixed in [7778] and a test added to verify that in [7779].

comment:8 by Jacob, 13 years ago

milestone: 1.0

Milestone 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top