#36352: Weird behavior of .values after a distinct in 5.1.8
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: .values, distinct | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Joseph Yu):
* resolution: needsinfo =>
* status: closed => new
Old description:
> Was upgrading from 4.2.20 to 5.1.8 when tests were failing with a certain
> query in our app.
>
> Sample code (not actual code and table; only used one table in this
> case):
>
> {{{
> >>> a = Tenant.objects.filter(id__in=[1,
> 3]).values('id').annotate(schema=F('schema_name'),
> created=F('created_at'))
> >>> b =
> Tenant.objects.filter(id=2).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')))
> }}}
>
> 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 'created' alias. Use
> annotate() to promote it.
> }}}
>
> Current workaround is to include the annotated field in .values like so
>
> {{{
> >>> d = c.filter(id=OuterRef('id')).values('schema', 'created')
> >>> e =
> Tenant.objects.values('schema_name').annotate(created_at=Subquery(c.values('created')[:1]))
> >>> e[:1]
> <QuerySet [{'schema_name': 'sample', 'created_at': datetime.date(2024,
> 11, 4)}]>
> }}}
New description:
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')))
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070196725c8be8-018d3ef9-756a-4c77-b95b-c9127eb58cf8-000000%40eu-central-1.amazonses.com.
