[Django] #30854: FilteredRelation on ManyToOne generates wrong result
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
mortezaPRK |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) | Keywords: FilteredRelation
Severity: Normal | annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When using FilteredRelation multiple times in single query, results are
wrong.
consider following example (with Django 2.2.6):
The models are:
{{{#!python
from django.db import models
class UserType(models.Model):
pass
class Commodity(models.Model):
pass
class CommodityCount(models.Model):
commodity = models.ForeignKey(Commodity, on_delete=models.CASCADE)
user_type = models.ForeignKey(UserType, on_delete=models.CASCADE)
small_box = models.IntegerField(default=0)
class Meta:
unique_together = ('commodity', 'user_type')
class CommodityDiscount(models.Model):
commodity = models.ForeignKey(Commodity, on_delete=models.CASCADE)
user_type = models.ForeignKey(UserType, on_delete=models.CASCADE)
small_box = models.IntegerField(default=0)
class Meta:
unique_together = ('commodity', 'user_type')
}}}
following query is what I want:
{{{#!python
query = Commodity.objects.annotate(
cnt=FilteredRelation('commoditycount',
condition=Q(commoditycount__user_type_id=1)),
dst=FilteredRelation('commoditydiscount',
condition=Q(commoditydiscount__user_type_id=1))
).filter(
cnt__isnull=False,
dst__isnull=False
).select_related('cnt','dst')
}}}
resulting query is ok:
{{{#!sql
SELECT
"ma_commodity"."id",
cnt."id",
cnt."commodity_id",
cnt."user_type_id",
cnt."small_box",
dst."id",
dst."commodity_id",
dst."user_type_id",
dst."small_box"
FROM
"ma_commodity"
INNER JOIN "ma_commoditycount" cnt ON (
"ma_commodity"."id" = cnt."commodity_id"
AND (cnt."user_type_id" = 1)
)
INNER JOIN "ma_commoditydiscount" dst ON (
"ma_commodity"."id" = dst."commodity_id"
AND (dst."user_type_id" = 1)
)
WHERE
(
cnt."id" IS NOT NULL
AND dst."id" IS NOT NULL
)
}}}
but results are not ok:
{{{#!python
first_item = query.first()
print(dir(first_item))
>>> [ ... '_state', 'check', 'clean', 'clean_fields',
'commoditycount_set', 'commoditydiscount_set',
'date_error_message', 'delete', 'dst', 'from_db', 'full_clean',
'get_deferred_fields', 'id', 'objects',
'pk', 'prepare_database_save', 'refresh_from_db', 'save', 'save_base',
'serializable_value', 'unique_error_message', 'validate_unique'
]
}}}
as you can see, there is dst, but no cnt.
if I remove dst and its related FilteredRelation, cnt re appear.
--
Ticket URL: <https://code.djangoproject.com/ticket/30854>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: FilteredRelation | Triage Stage:
annotate | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Morteza PRK (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
annotate |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.2 => master
* stage: Unreviewed => Accepted
Comment:
Thanks for this report. I attached a simple test.
Reproduced at dafdfd6a60638c4edcca7c4e65d11c0af654d759.
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: FilteredRelation | Triage Stage: Accepted
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "test-30854.diff" added.
Test.
Django
-------------------------------------+-------------------------------------
| Ramezani
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: FilteredRelation | Triage Stage: Accepted
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Hasan Ramezani
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: Hasan
| Ramezani
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: FilteredRelation | Triage Stage: Accepted
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: Hasan
| Ramezani
Component: Database layer | Version: master
(models, ORM) |
Keywords: FilteredRelation | Triage Stage: Accepted
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"6a75cea76a98c08bf2e20d787be9b14c2cd94860" 6a75cea7]:
{{{
#!CommitTicketReference repository=""
revision="6a75cea76a98c08bf2e20d787be9b14c2cd94860"
Fixed #30854 -- Fixed QuerySet.select_related() with multiple
FilteredRelations.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: Hasan
| Ramezani
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"e1ae2b00504ba30481285b2bd767d1ad561bf4be" e1ae2b0]:
{{{
#!CommitTicketReference repository=""
revision="e1ae2b00504ba30481285b2bd767d1ad561bf4be"
Refs #30854 -- Moved local_setter() outside the loop in
SQLCompiler.get_related_selections().
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Morteza PRK | Owner: Hasan
| Ramezani
Component: Database layer | Version: master
(models, ORM) |
Keywords: FilteredRelation | Triage Stage: Accepted
annotate |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"c1cfec6b500c16200a4c2ef5676439c3cc987bc2" c1cfec6b]:
{{{
#!CommitTicketReference repository=""
revision="c1cfec6b500c16200a4c2ef5676439c3cc987bc2"
[3.0.x] Fixed #30854 -- Fixed QuerySet.select_related() with multiple
FilteredRelations.
Backport of 6a75cea76a98c08bf2e20d787be9b14c2cd94860 from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30854#comment:7>