AND operator on queryset with joins gives wrong results

[Guillaume LEBRETON]

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

[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.

[David Sanders]

Docs PR: ​https://github.com/django/django/pull/16692

[Guillaume LEBRETON]

Replying to 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)

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0494efd:

Fixed #34440 -- Doc'd that & queryset operator works similar to chaining.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9967faa:

[4.2.x] Fixed #34440 -- Doc'd that & queryset operator works similar to chaining.

Backport of 0494efddc422716431b92896899284b6afebb23a from main