values() raises a FieldError when multiple values() of annotated values are chained

[Joseph Yu]

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')))

[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

[Joseph Yu]

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

[Sarah Boyce]

TenantMixin?
I also assume I need to create some data? Still failing on 4.2 for me so far

[Joseph Yu]

You can ignore the mixin it's only the schema name. Here is the terminal output if that matters:

[Sarah Boyce]

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

[Simon Charette]

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?

[Sarah Boyce]

Good point, happy to have a more helpful error message