[Django] #33838: Querying issue with ForeignKeys where db_constraint=False
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
MrAlexBailey |
Type: Bug | Status: new
Component: Database | Version: 4.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
models.py
{{{
from django.db import models
class Person(models.Model):
name = models.CharField(max_length=25)
class Book(models.Model):
foo = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='foo')
bar = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='bar')
}}}
setup
{{{
from .models import Book, Person
alex = Person.objects.create(name='Alex')
Book.objects.create(foo=alex, bar=alex)
Book.objects.create(foo=alex, bar_id=999)
Book.objects.create(foo_id=999, bar=alex)
}}}
shell
{{{
>>> from django.db.models import Q
#Correct: Returns both books where foo__name == 'Alex'
>>> Book.objects.filter(foo__name='Alex')
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
#Correct: Returns both books where bar__name == 'Alex'
>>> Book.objects.filter(bar__name='Alex')
<QuerySet [<Book: Book object (1)>, <Book: Book object (3)>]>
#Only returns one book where both foo__name and bar__name == 'Alex'
>>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex'))
<QuerySet [<Book: Book object (1)>]>
}}}
I am working with an existing DB where it is possible for a `Person` to no
longer exist, hence the lack of `db_constraint` in the `ForeignKey`.
`Books` can refer to non-existent `Persons` but it still seems like
expected behavior would be for the queryset OR to return all 3 books. I
believe this is coming from the Django ORM doing an INNER JOIN.
--
Ticket URL: <https://code.djangoproject.com/ticket/33838>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
models.py
{{{
from django.db import models
class Person(models.Model):
name = models.CharField(max_length=25)
class Book(models.Model):
foo = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='foo')
bar = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='bar')
}}}
setup
{{{
from .models import Book, Person
alex = Person.objects.create(name='Alex')
Book.objects.create(foo=alex, bar=alex)
Book.objects.create(foo=alex, bar_id=999)
Book.objects.create(foo_id=999, bar=alex)
}}}
shell
{{{
>>> from django.db.models import Q
#Correct: Returns both books where foo__name == 'Alex'
>>> Book.objects.filter(foo__name='Alex')
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
#Correct: Returns both books where bar__name == 'Alex'
>>> Book.objects.filter(bar__name='Alex')
<QuerySet [<Book: Book object (1)>, <Book: Book object (3)>]>
#Only returns one book, expected to return all 3.
>>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex'))
<QuerySet [<Book: Book object (1)>]>
}}}
I am working with an existing DB where it is possible for a `Person` to no
longer exist, hence the lack of `db_constraint` in the `ForeignKey`.
`Books` can refer to non-existent `Persons` but it still seems like
expected behavior would be for the queryset OR to return all 3 books. I
believe this is coming from the Django ORM doing an INNER JOIN. The 3rd
query should be a simple union of the first 2 queries.
All 3 books fit into the expected query of `books WHERE foo__name = 'Alex'
OR bar__name = 'Alex'` but the Django behavior does not reflect this.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33838#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Alex Bailey | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alex Bailey):
I just noticed that adding `null=True` to the ForeignKeys produces the
expected result on the last query:
{{{
class Book(models.Model):
foo = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='foo', null=True)
bar = models.ForeignKey(Person, db_constraint=False,
on_delete=models.DO_NOTHING, related_name='bar', null=True)
}}}
{{{
>>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex'))
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book
object (3)>]>
}}}
Django switched to using LEFT OUTER JOIN in this case. Should this same
logic apply to ForeignKeys where `db_constraint=False`?
--
Ticket URL: <https://code.djangoproject.com/ticket/33838#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Alex Bailey | Owner: nobody
Component: Database layer | Version: 4.0
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Closing as duplicate of #33608.
While Django allows for database constraints to be omitted it doesn't make
any assumptions with regards to database integrity implications.
In other words, if you break integrity expectations of `ForeignKey` you
must also adjust your code to do so and in your particular case if a
`Book` is still allowed to exist without a person the proper way to
represent that is by setting `null=True` on your field as you've
discovered.
Adapting the ORM to special case `db_constraint=False` like it does with
`null=True` has a few implications that
[https://docs.djangoproject.com/en/4.0/internals/contributing/bugs-and-
features/#requesting-features should be debated on the mailing list]
before a decision is taken because
[https://github.com/django/django/commit/b55cde054ee7dd22f93c3522a8ddb1d04193bcac
it was not done when the change was merged].
--
Ticket URL: <https://code.djangoproject.com/ticket/33838#comment:3>