Code

Opened 6 years ago

Closed 6 years ago

Last modified 3 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 Owned by: nobody
Component: Database layer (models, ORM) Version: master
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: UI/UX:

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

Attachments (0)

Change History (8)

comment:1 Changed 6 years ago by omat

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 6 years ago by anonymous

  • 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 Changed 6 years ago by anonymous

  • Cc omat@… added

comment:4 Changed 6 years ago by gav

  • Keywords qsrf-cleanup added

comment:5 Changed 6 years ago by jacob

  • milestone set to 1.0

comment:6 Changed 6 years ago by jwinter

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

comment:7 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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 Changed 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.