[Django] #34440: AND operator on queryset with joins gives wrong results
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume | Owner: nobody
LEBRETON |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) |
Severity: Normal | Keywords: orm and operato
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
From the doc https://docs.djangoproject.com/en/4.1/ref/models/querysets
/#operators-that-return-new-querysets, 2 queryset with a single filter
argument merged by a "&" operator is equivalent to queryset with a filter
with 2 arguments.
But with lookup with joins it do not seems to work that way:
{{{
# models.py
class Person(models.Model):
name = models.CharField(max_length=10, blank=True, null=True)
class QuantitativeAttribute(models.Model):
value = models.PositiveIntegerField()
name = models.CharField(max_length=10)
person = models.ForeignKey(Person, on_delete=models.CASCADE)
# tests.py
class QuantitativeTestCase(TestCase):
@classmethod
def setUpTestData(cls):
cls.p1 = Person.objects.create(name='p1')
cls.p2 = Person.objects.create(name='p2')
QuantitativeAttribute.objects.create(
person=cls.p1,
value=27,
name='age',
)
QuantitativeAttribute.objects.create(
person=cls.p1,
value=55,
name='concentration',
)
QuantitativeAttribute.objects.create(
person=cls.p2,
value=27,
name='concentration',
)
QuantitativeAttribute.objects.create(
person=cls.p2,
value=55,
name='age',
)
def test_combine_AND(self):
expected_result = [
{'name': 'p1'}
]
with self.subTest('single filter qs'):
qs =
Person.objects.filter(quantitativeattribute__name='age',
quantitativeattribute__value=27)
self.assertQuerysetEqual(qs.values('name'),
expected_result)
with self.subTest('combined AND filter qs'):
qs1 =
Person.objects.filter(quantitativeattribute__name='age')
qs2 =
Person.objects.filter(quantitativeattribute__value=27)
qs = qs1 & qs2
self.assertQuerysetEqual(qs.values('name'),
expected_result)
}}}
Here, the first subtest,
`Person.objects.filter(quantitativeattribute__name='age',
quantitativeattribute__value=27)` gives the desired result.
But the second subtest,
`Person.objects.filter(quantitativeattribute__name='age') &
Person.objects.filter(quantitativeattribute__value=27)` which according to
the doc should be equal to the first subtest, is in fact equivalent to a
chained filter query:
`Person.objects.filter(quantitativeattribute__name='age').filter(quantitativeattribute__value=27)`
As it is explained here
https://docs.djangoproject.com/en/4.1/topics/db/queries/#spanning-multi-
valued-relationships in the docs, chained filters is not the query that i
want.
I tried to look for existing similar issue, i also went IRC #django but i
did'nt find any answer yet.
I think this ORM behavior is quite inconsistent, and at least it sould be
stated in the doc for the "&" operator that the behavior may difer with
joins
--
Ticket URL: <https://code.djangoproject.com/ticket/34440>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Sanders):
Hi,
Yes 2 querysets joined with the & operator are effectively chained
together. Whilst I can see that it can be misleading in this case the
linked example shown isn't technically "wrong", though I'd recommend that
the docs be updated here to avoid confusion.
If you'd like to propose that there be some way to join querysets in the
way you'd like I'd encourage you to start a discussion on the Django
developer's mailing list: DevelopersMailingList.
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:1>
Django
Reporter: Guillaume LEBRETON | Owner: nobody
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by David Sanders):
* type: Bug => Cleanup/optimization
* component: Database layer (models, ORM) => Documentation
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:2>
Django
--------------------------------------+------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------------+------------------------------------
Changes (by David Sanders):
* has_patch: 0 => 1
Comment:
Docs PR: https://github.com/django/django/pull/16692
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:3>
Django
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by David Sanders):
* owner: nobody => David Sanders
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Guillaume LEBRETON):
Replying to [comment:1 David Sanders]:
> Hi,
>
> Yes 2 querysets joined with the & operator are effectively chained
together. Whilst I can see that it can be misleading in this case the
linked example shown isn't technically "wrong", though I'd recommend that
the docs be updated here to avoid confusion.
>
> If you'd like to propose that there be some way to join querysets in the
way you'd like I'd encourage you to start a discussion on the Django
developer's mailing list: DevelopersMailingList.
Hello David, thanks for your help. Your are right, chained filters and
multiple kwargs filters are equivalent ONLY when there is no joins
involved, and that was misleading for me.
Here is another test to show that in case of no joins these 3 queries are
equivalent, where it's not the case with joins.
{{{
def test_combine_AND_without_join(self):
expected_result = [
{'name': 'age'}
]
with self.subTest('single filter qs'):
qs = QuantitativeAttribute.objects.filter(name='age',
value=27)
self.assertQuerysetEqual(qs.values('name'), expected_result)
with self.subTest('combined AND filter qs'):
qs1 = QuantitativeAttribute.objects.filter(name='age')
qs2 = QuantitativeAttribute.objects.filter(value=27)
qs = qs1 & qs2
self.assertQuerysetEqual(qs.values('name'), expected_result)
with self.subTest('combined AND filter qs'):
qs =
QuantitativeAttribute.objects.filter(name='age').filter(value=27)
self.assertQuerysetEqual(qs.values('name'), expected_result)
}}}
Perhaps one line of text in the doc should explain that difference with
joins, along with your modification ?
Something like `Model.objects.filter(x=1, y=2) <=>
Model.objects.filter(x=1).filter(y=2)`
but `Model.objects.filter(related__x=1, related__y=1) !=
Model.objects.filter(related__x=1).filter(related__y=1)`
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
Keywords: orm and operato | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 4.1
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Component: Documentation | Version: 4.1
Severity: Normal | Resolution: fixed
Keywords: orm and operato | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"0494efddc422716431b92896899284b6afebb23a" 0494efd]:
{{{
#!CommitTicketReference repository=""
revision="0494efddc422716431b92896899284b6afebb23a"
Fixed #34440 -- Doc'd that & queryset operator works similar to chaining.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: David
Type: | Sanders
Cleanup/optimization | Status: closed
Component: Documentation | Version: 4.1
Severity: Normal | Resolution: fixed
Keywords: orm and operato | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"9967faab0b664306c377fee931317e0dccb134ea" 9967faa]:
{{{
#!CommitTicketReference repository=""
revision="9967faab0b664306c377fee931317e0dccb134ea"
[4.2.x] Fixed #34440 -- Doc'd that & queryset operator works similar to
chaining.
Backport of 0494efddc422716431b92896899284b6afebb23a from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34440#comment:9>