#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
-------------------------------------+-------------------------------------
Description changed by Joseph Yu:
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.
> }}}
New 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
{{{
>>> c = b.filter(id=OuterRef('id')).values('schema', 'created')
>>> d =
Tenant.objects.values('schema_name').annotate(created_at=Subquery(c.values('created')[:1]))
>>> d[:1]
<QuerySet [{'schema_name': 'sample', 'created_at': datetime.date(2024, 11,
4)}]>
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:1>
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/010701966cfc190c-f54c0f8a-2478-41bc-8b5a-bd9ad3c5f43a-000000%40eu-central-1.amazonses.com.
