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

Change History (13)

comment:1 by Joseph Yu, 4 months ago

Description: modified (diff)

comment:2 by Joseph Yu, 4 months ago

Description: modified (diff)

comment:3 by Sarah Boyce, 4 months ago

Resolution: needsinfo
Status: newclosed
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:4 by Joseph Yu, 4 months ago

Description: modified (diff)
Resolution: needsinfo
Status: closednew

in reply to:  3 comment:5 by Joseph Yu, 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 Sarah Boyce, 4 months ago

Resolution: needsinfo
Status: newclosed

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

comment:7 by Joseph Yu, 4 months ago

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

https://i.imgur.com/VRqjrm7.png

https://i.imgur.com/pSFJU7R.png

Last edited 4 months ago by Joseph Yu (previous) (diff)

comment:8 by Joseph Yu, 4 months ago

Resolution: needsinfo
Status: closednew

comment:9 by Sarah Boyce, 4 months ago

Cc: Simon Charette added
Summary: Weird behavior of .values after a distinct in 5.1.8values() raises a FieldError when multiple values() of annotated values are chained
Triage Stage: UnreviewedAccepted
Version: 5.15.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 Simon Charette, 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?

Last edited 4 months ago by Simon Charette (previous) (diff)

comment:11 by Sarah Boyce, 4 months ago

Type: BugCleanup/optimization

Good point, happy to have a more helpful error message

comment:12 by JaeHyuckSa, 4 months ago

Has patch: set
Owner: set to JaeHyuckSa
Status: newassigned

comment:13 by ontowhee, 3 months ago

Patch needs improvement: set
Note: See TracTickets for help on using tickets.
Back to Top