id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36352	values() raises a FieldError when multiple values() of annotated values are chained	Joseph Yu	JaeHyuckSa	"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')))
}}}
"	Cleanup/optimization	closed	Database layer (models, ORM)	5.0	Normal	fixed	.values, distinct	Joseph Yu Simon Charette	Ready for checkin	1	0	0	0	0	0