Re: combining querysets with isnull
On Thursday, October 14, 2010 7:03:35 PM UTC-4, Johannes Dollinger wrote: > > > Am 14.10.2010 um 23:07 schrieb Dan Watson: > > > 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? > > Your problem looks like #11052 [1]. > > [1] http://code.djangoproject.com/ticket/11052 > > __ > Johannes > 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 [1] http://code.djangoproject.com/ticket/11052 [2] http://code.djangoproject.com/ticket/10790 [3] http://code.djangoproject.com/ticket/15823 -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
Re: combining querysets with isnull
Am 14.10.2010 um 23:07 schrieb Dan Watson: > 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? Your problem looks like #11052 [1]. [1] http://code.djangoproject.com/ticket/11052 __ Johannes -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-develop...@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
combining querysets with isnull
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 -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-develop...@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.