Weird behavior of .values after a distinct in 5.1.8

[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