[Django] #36352: Weird behavior of .values after a distinct in 5.1.8

[Django]

#36352: Weird behavior of .values after a distinct in 5.1.8
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
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
-------------------------------------+-------------------------------------
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36352>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#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:

New description:

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]

>>> 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)}]>
}}}

--
--

Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:2>

[Django]

#36352: Weird behavior of .values after a distinct in 5.1.8
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: needsinfo

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 Sarah Boyce):

* resolution: => needsinfo
* status: new => closed

Comment:

{{{#!python
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
--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:3>

[Django]

#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]

#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
-------------------------------------+-------------------------------------

Comment (by Joseph Yu):

Finally stopped lazy and reproduced it locally. Updated the ticket to have
accurate models.
Replying to [comment:3 Sarah Boyce]:

> {{{#!python
> 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

--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:5>

[Django]

#36352: Weird behavior of .values after a distinct in 5.1.8
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: needsinfo

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 Sarah Boyce):

* resolution: => needsinfo
* status: new => closed

Comment:

`TenantMixin`?
I also assume I need to create some data? Still failing on 4.2 for me so
far
--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:6>

[Django]

#36352: Weird behavior of .values after a distinct in 5.1.8
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
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
-------------------------------------+-------------------------------------

Comment (by Joseph Yu):

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

[[Image(https://cdn.discordapp.com/attachments/1052945012159156254/1365691442466656298/image.png?ex=680e3ac5&is=680ce945&hm=19dddbeec7a362b339a3c75417d6157f51425080f73e85c9caa4ab93a58c4fab&)]]

[[Image(https://cdn.discordapp.com/attachments/1052945012159156254/1365691528747548794/image.png?ex=680e3ad9&is=680ce959&hm=1e16ac4f83879a4b37db6c246e9a4bc75396f63a0d4e8d780c48aa789e5cb776&)]]
--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:7>

[Django]

#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

--
Ticket URL: <https://code.djangoproject.com/ticket/36352#comment:8>