[Django] #22096: Incorrect JOIN when using annotate and multiple filters

[Django]

#22096: Incorrect JOIN when using annotate and multiple filters
----------------------------------------------+--------------------
Reporter: tomo.otsuka@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
When using chained filters with annotate, the emitted SQL has an
extraneous and incorrect join causing the result to be a cross-product of
itself.

Please see the following two QuerySets. They are logically the same, but
first QuerySet uses one filter, and the second QuerySet uses two filters.

{{{
# good example with using only one filter
pubs_one_filter = Publisher.objects.filter(book__pages__gt=100,
book__price__lt=10)
print pubs_one_filter.query
# SELECT "tester_publisher"."id", "tester_publisher"."name",
"tester_publisher"."num_awards" FROM "tester_publisher" INNER JOIN
"tester_book" ON ( "tester_publisher"."id" = "tester_book"."publisher_id"
) WHERE ("tester_book"."price" < 10 AND "tester_book"."pages" > 100 )

# bad example with using two chained filters
pubs_two_filter =
Publisher.objects.filter(book__pages__gt=100).filter(book__price__lt=10)
print pubs_two_filter.query
# SELECT "tester_publisher"."id", "tester_publisher"."name",
"tester_publisher"."num_awards" FROM "tester_publisher" INNER JOIN
"tester_book" ON ( "tester_publisher"."id" = "tester_book"."publisher_id"
) INNER JOIN "tester_book" T3 ON ( "tester_publisher"."id" =
T3."publisher_id" ) WHERE ("tester_book"."pages" > 100 AND T3."price" <
10 )
}}}

The JOIN with T3 and the JOIN immediately preceding it is the same JOIN.
It effectively is a cross-product.

--
Ticket URL: <https://code.djangoproject.com/ticket/22096>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#22096: Incorrect JOIN when using annotate and multiple filters

-------------------------------------+-------------------------------------

Reporter: tomo.otsuka@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.6
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by tomo.otsuka@…):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

I should have mentioned that I used the models from the django docs for
aggregation
([https://docs.djangoproject.com/en/dev/topics/db/aggregation/])

--
Ticket URL: <https://code.djangoproject.com/ticket/22096#comment:1>

[Django]

#22096: Incorrect JOIN when using annotate and multiple filters

-------------------------------------+-------------------------------------
Reporter: tomo.otsuka@… | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 1.6

(models, ORM) | Resolution: invalid

Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by shai):

* status: new => closed
* resolution: => invalid

Comment:

This is intentional,
[https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-
valued-relationships documented] behavior.

--
Ticket URL: <https://code.djangoproject.com/ticket/22096#comment:2>