Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#11347 closed (wontfix)

isnull=False returns incorrect results for reverse relations

Reported by: mrts Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:



Assume the following model:

from django.db import models

class A(models.Model):
    name = models.CharField(max_length=10)

    def __unicode__(self):

class B(models.Model):
    a = models.ForeignKey(A)
    name = models.CharField(max_length=10)

    def __unicode__(self):

and the following sample data:

>>> from reverserelation.models import A, B
>>> a1 = A.objects.create(name='first')
>>> a2 = A.objects.create(name='second')
>>> b1 = B.objects.create(a=a1, name='x')
>>> b2 = B.objects.create(a=a1, name='y')
>>> b3 = B.objects.create(a=a1, name='z')

The problem

Filtering A objects that have no associated Bs works as expected:

>>> A.objects.filter(b__isnull=True)
[<A: second>]

The opposite, filtering A objects that have at least one B that refers to it, doesn't.


>>> A.objects.filter(b__isnull=False)
[<A: first>]


>>> A.objects.filter(b__isnull=False)
[<A: first>, <A: first>, <A: first>]

Why is this happening:

>>> A.objects.filter(b__isnull=False).query.as_sql()
('SELECT "reverserelation_a"."id", "reverserelation_a"."name"
 FROM "reverserelation_a"
INNER JOIN "reverserelation_b" ON ("reverserelation_a"."id" = "reverserelation_b"."a_id")
WHERE "reverserelation_b"."id" IS NOT NULL', ())

A SELECT DISTINCT is required in this case.

Change History (3)

comment:1 Changed 6 years ago by mrts

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

As this can be achieved with distinct(), the ticket can be wontfixed, i.e. the following works:

>>> A.objects.filter(b__isnull=False).distinct()
[<A: first>]

comment:2 Changed 6 years ago by Alex

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

When you traverse a one-to-many relation in a way that can generate duplicate results it is your responsibility to use distinct().

comment:3 Changed 6 years ago by mrts

Indeed, but as the need to use explicit distinct() wasn't obvious to me at first glance, but the need to pick up objects that have at least one other thing referring to them seems (the infamous generalization) quite common, it should perhaps be documented somewhere around .filter()?

P.S. Regarding the seems common remark:

  • display authors that have written at least one book (assuming books have references to authors)
  • display goods that have been ordered at least once (assuming order/order items have references to goods)


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