combining querysets with isnull
24 views
Skip to first unread message
Dan Watson
Oct 14, 2010, 5:07:59 PM10/14/10
to Django developers
There seems to be some inconsistent behavior when combining querysets
that use isnull on a ForeignKey. I'm not sure how to explain it well
in plain english, so here's a boiled-down example:
# Models
class Item (models.Model):
title = models.CharField( max_length=100 )
class PropertyValue (models.Model):
label = models.CharField( max_length=100 )
class Property (models.Model):
item = models.ForeignKey( Item, related_name='props' )
key = models.CharField( max_length=100 )
value = models.ForeignKey( PropertyValue, null=True )
# Example
item = Item.objects.create(title='Some Item')
pv = PropertyValue.objects.create(label='Some Value')
item.props.create(key='a', value=pv)
item.props.create(key='b')
q1 = Q(props__key='a', props__value=pv)
q2 = Q(props__key='b', props__value__isnull=True)
qs1 = Item.objects.filter(q1) & Item.objects.filter(q2)
qs2 = Item.objects.filter(q2) & Item.objects.filter(q1)
I would have expected qs1 and qs2 to yield the same result, but they
do not. They should both return the single item, but qs1 returns
nothing. The SQL for qs1 looks like:
SELECT "app_item"."id", "app_item"."title" FROM "app_item"
INNER JOIN "app_property" ON ("app_item"."id" =
"app_property"."item_id")
LEFT OUTER JOIN "app_property" T4 ON ("app_item"."id" = T4."item_id")
LEFT OUTER JOIN "app_propertyvalue" T5 ON ("app_property"."value_id" =
T5."id")
WHERE (("app_property"."value_id" = 1 AND "app_property"."key" =
'a' ) AND (T5."id" IS NULL AND T4."key" = 'b' ))
The first app_property join corresponds to q1, and the second
corresponds to q2. However, the app_propertyvalue join (corresponding
to the isnull from q2) refers to app_property.value_id (i.e. q1)
instead of T4.value_id (i.e. q2). I think fixing #10790 would fix
this, since removing the join to app_propertyvalue and simply checking
"T4.value_id IS NULL" works as expected, but I'm not sure if this is a
separate problem worthy of another ticket or not. Also (less
importantly), since both criteria (q1 and q2) are checking
props__key='something', I would imagine both joins could be INNER
JOINs, but the isnull seems to promote the second to a LEFT OUTER
JOIN.
I guess I was just wondering if I should open a separate ticket for
this, or is this simply a symptom of #10790? Or am I missing
something?
Regards,
Dan
that use isnull on a ForeignKey. I'm not sure how to explain it well
in plain english, so here's a boiled-down example:
# Models
class Item (models.Model):
title = models.CharField( max_length=100 )
class PropertyValue (models.Model):
label = models.CharField( max_length=100 )
class Property (models.Model):
item = models.ForeignKey( Item, related_name='props' )
key = models.CharField( max_length=100 )
value = models.ForeignKey( PropertyValue, null=True )
# Example
item = Item.objects.create(title='Some Item')
pv = PropertyValue.objects.create(label='Some Value')
item.props.create(key='a', value=pv)
item.props.create(key='b')
q1 = Q(props__key='a', props__value=pv)
q2 = Q(props__key='b', props__value__isnull=True)
qs1 = Item.objects.filter(q1) & Item.objects.filter(q2)
qs2 = Item.objects.filter(q2) & Item.objects.filter(q1)
I would have expected qs1 and qs2 to yield the same result, but they
do not. They should both return the single item, but qs1 returns
nothing. The SQL for qs1 looks like:
SELECT "app_item"."id", "app_item"."title" FROM "app_item"
INNER JOIN "app_property" ON ("app_item"."id" =
"app_property"."item_id")
LEFT OUTER JOIN "app_property" T4 ON ("app_item"."id" = T4."item_id")
LEFT OUTER JOIN "app_propertyvalue" T5 ON ("app_property"."value_id" =
T5."id")
WHERE (("app_property"."value_id" = 1 AND "app_property"."key" =
'a' ) AND (T5."id" IS NULL AND T4."key" = 'b' ))
The first app_property join corresponds to q1, and the second
corresponds to q2. However, the app_propertyvalue join (corresponding
to the isnull from q2) refers to app_property.value_id (i.e. q1)
instead of T4.value_id (i.e. q2). I think fixing #10790 would fix
this, since removing the join to app_propertyvalue and simply checking
"T4.value_id IS NULL" works as expected, but I'm not sure if this is a
separate problem worthy of another ticket or not. Also (less
importantly), since both criteria (q1 and q2) are checking
props__key='something', I would imagine both joins could be INNER
JOINs, but the isnull seems to promote the second to a LEFT OUTER
JOIN.
I guess I was just wondering if I should open a separate ticket for
this, or is this simply a symptom of #10790? Or am I missing
something?
Regards,
Dan
Johannes Dollinger
Oct 14, 2010, 7:03:35 PM10/14/10
to django-d...@googlegroups.com
Dan Watson
Apr 13, 2011, 5:09:01 PM4/13/11
to django-d...@googlegroups.com
Looking at this further, I think the problem is slightly different. #11052 [1] describes a situation where joins are not promoted where they should be. The attached patch also did not produce correct results for my test case. I think #10790 [2] is more closely related, but still isn't exactly the issue (and again, the attached patch does not fix my test case). The issue I'm seeing is that, when combining queries, the join conditions do not reference aliases from the "correct" side in all cases. In my example above, the join to T5 should have referenced T4 since they both came from the right side of the combination. However, the combine method does not check to see if the left side of the join condition (for tables on the right) has already been re-aliased. I've opened ticket #15823 [3] with a patch that passes all tests, and a new regression test. As these internals get pretty hairy, I'd love if someone could take a look and make sure this is the correct approach.
Dan
Reply all
Reply to author
Forward
0 new messages