Opened 4 months ago
Last modified 3 months ago
#36352 assigned Cleanup/optimization
values() raises a FieldError when multiple values() of annotated values are chained
Reported by: | Joseph Yu | Owned by: | JaeHyuckSa |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 5.0 |
Severity: | Normal | Keywords: | .values, distinct |
Cc: | Joseph Yu, Simon Charette | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Was upgrading from 4.2.20 to 5.1.8 when tests were failing with a certain query in our app.
Models:
class Foo(models.Model): name = models.CharField(max_length=255) class Mapping(models.Model): field = models.CharField(max_length=255) foo = models.ForeignKey(Foo, null=True, blank=True, on_delete=models.PROTECT) class Tenant(TenantMixin): created_at = models.DateField(auto_now_add=True) mapping = models.ForeignKey(Mapping, null=True, blank=True, on_delete=models.PROTECT)
Code:
a = Mapping.objects.filter(id=1).values('id').annotate(foo=F('foo__name'),foo_id=F('foo_id')) b = Mapping.objects.filter(id=2).values('id').annotate(foo=F('foo__name'),foo_id=F('foo_id')) c = (a|b).distinct() d = c.filter(id=OuterRef('mapping_id')).values('foo') e = Tenant.objects.values('id').annotate(foo_id=Subquery(d.values('foo_id')))
This throws an error in 5.1.8:
Traceback (most recent call last): File "<console>", line 1, in <module> File "/Users/user/.pyenv/versions/3.13.0/lib/python3.13/site-packages/django/db/models/query.py", line 1360, in values clone = self._values(*fields, **expressions) File "/Users/user/.pyenv/versions/3.13.0/lib/python3.13/site-packages/django/db/models/query.py", line 1355, in _values clone.query.set_values(fields) ~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^ File "/Users/user/.pyenv/versions/3.13.0/lib/python3.13/site-packages/django/db/models/sql/query.py", line 2462, in set_values raise FieldError( ...<2 lines>... ) django.core.exceptions.FieldError: Cannot select the 'foo_id' alias. Use annotate() to promote it.
Current workaround is to include the annotated field in .values like so
d = c.filter(id=OuterRef('mapping_id')).values('foo', 'foo_id') e = Tenant.objects.values('id').annotate(foo_id=Subquery(d.values('foo_id')))
Change History (13)
comment:1 by , 4 months ago
Description: | modified (diff) |
---|
comment:2 by , 4 months ago
Description: | modified (diff) |
---|
follow-up: 5 comment:3 by , 4 months ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
comment:4 by , 4 months ago
Description: | modified (diff) |
---|---|
Resolution: | needsinfo |
Status: | closed → new |
comment:5 by , 4 months ago
Finally stopped lazy and reproduced it locally. Updated the ticket to have accurate models.
Replying to Sarah Boyce:
from django.db import models from django.db.models import ( F, OuterRef, Subquery, ) from django.test import TestCase class Tenant(models.Model): schema_name = models.CharField(max_length=150) created_at = models.DateTimeField(auto_now_add=True) class Test36352(TestCase): def test_ticket_36352(self): t1 = Tenant.objects.create(schema_name="Test 1") t2 = Tenant.objects.create(schema_name="Test 2") t3 = Tenant.objects.create(schema_name="Test 3") a = Tenant.objects.filter(id__in=[t1.id, t1.id]).values('id').annotate( schema=F('schema_name'), created=F('created_at') ) b = Tenant.objects.filter(id=t1.id).values('id').annotate( schema=F('schema_name'), created=F('created_at') ) c = (a | b).distinct() d = c.filter(id=OuterRef('id')).values('schema') e = Tenant.objects.values('schema_name').annotate( created_at=Subquery(d.values('created')) )So yes this fails, but it seems to fail in Django 4.2 as well. It might help sharing the model as well. It would also be useful to do a git bisect to identify when this started failing
comment:6 by , 4 months ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
TenantMixin
?
I also assume I need to create some data? Still failing on 4.2 for me so far
comment:7 by , 4 months ago
comment:8 by , 4 months ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
comment:9 by , 4 months ago
Cc: | added |
---|---|
Summary: | Weird behavior of .values after a distinct in 5.1.8 → values() raises a FieldError when multiple values() of annotated values are chained |
Triage Stage: | Unreviewed → Accepted |
Version: | 5.1 → 5.0 |
Thank you Joseph! Appreciate the back and forth
Bisected it cb13792938f2c887134eb6b5164d89f8d8f9f1bd a change in 5.0
I can also simplify the query a little bit to Mapping.objects.values('id').annotate(foo=F('foo__name'), foo_id=F('foo_id')).values('foo').values('foo_id')
and so have tried to rewrite the ticket title
comment:10 by , 4 months ago
It looks like we could error out with a more appropriate message but we've made some significant changes in since 4.2 to prevent obscuring existing field references with annotations.
In other words I would expect calls of the form Mapping.objects.annotate(foo=F('foo__name'))
and Mapping.objects.annotate(foo_id=F('foo_id'))
to always be problematic to some extent even when values is used as they turn follow up references to such annotations ambiguous.
For example, what should annotate(foo=F('foo__name')).filter(foo__name="Bar")
resolve to or surface as error now that foo
is no longer a ForeignKey(Foo)
in the context of the query?
comment:11 by , 4 months ago
Type: | Bug → Cleanup/optimization |
---|
Good point, happy to have a more helpful error message
comment:12 by , 4 months ago
Has patch: | set |
---|---|
Owner: | set to |
Status: | new → assigned |
comment:13 by , 3 months ago
Patch needs improvement: | set |
---|
So yes this fails, but it seems to fail in Django 4.2 as well. It might help sharing the model as well. It would also be useful to do a git bisect to identify when this started failing