[Django] #36860: Annotate after Union using django-cte breaks with django 5.2

[Django]

#36860: Annotate after Union using django-cte breaks with django 5.2
-------------------------------------+-------------------------------------
Reporter: benjfield | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I'm not sure whether the fault here lies with django-cte or django, but
django 5.2 has broken a feature in django-cte.

I was using a cte to create a union and then use annotation to aggregate a
value. An example is here:

{{{
from django.db.models import F, Sum
from django_cte import CTE, with_cte

class UnderlyingDemo(models.Model):
pass

class Demo(models.Model):
name = models.CharField()

underlying = models.ForeignKey(
UnderlyingDemo,
on_delete=models.CASCADE,
)

positive = models.BooleanField()

number = models.IntegerField()

def __str__(self):
return self.name

#Doesn't work natively in django - cannot run annotate after union
def query_no_cte():
return Demo.objects.filter(
positive=True,
).values(
"underlying_id",
signed_number=F("number")
).union(
Demo.objects.filter(
positive=False,
).values(
"underlying_id",
signed_number=F("number") * -1
),
all=True,
).values(
"underlying_id"
).annotate(
summed_total_number=Sum("signed_number")
)

#Breaks between django 5.1 and django 5.2
def query_with_cte():
demo_cte = CTE(
Demo.objects.all().filter(
positive=True,
).values(
"underlying_id",
signed_number=F("number")
).union(
Demo.objects.all().filter(
positive=False,
).values(
"underlying_id",
signed_number=F("number") * -1
),
all=True,
),
name="union"
)
return with_cte(
demo_cte,
select=demo_cte.queryset(),
).values(
"underlying_id"
).annotate(
summed_total_number=Sum("signed_number")
)
}}}

This previously worked but now returns "django.core.exceptions.FieldError:
Cannot select the 'underlying_id' alias. Use annotate() to promote it.".
The problem seems to continue with 6.0
--
Ticket URL: <https://code.djangoproject.com/ticket/36860>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36860: Annotate after Union using django-cte breaks with django 5.2
-------------------------------------+-------------------------------------

Reporter: benjfield | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Old description:

New description:

I should note that while I appreciate this may well be "Won't Fix" if the
problem lies with django-cte, it would be very helpful to know what the
error means - the documentation does not seem to use "promote" in concert
with "annotate()" at any point I could find.

--
Comment (by benjfield):

I should note that while I appreciate this may well be "Won't Fix", it
would be very helpful to know what the error means - the documentation
does not seem to use "promote" in concert with "annotate()" at any point I
could find.
--
Ticket URL: <https://code.djangoproject.com/ticket/36860#comment:1>

[Django]

#36860: Annotate after Union using django-cte breaks with django 5.2
-------------------------------------+-------------------------------------
Reporter: benjfield | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

If it helps, I can quickly answer the question about "promote" -- it means
somewhere (maybe in `django-cte`?) an `.alias()` call should be replaced
with `.annotate()`. Agree that the error wording is not so helpful. When
doing more fulsome triage later today I'll look into whether this is
expected.
--
Ticket URL: <https://code.djangoproject.com/ticket/36860#comment:2>

[Django]

#36860: Annotate after Union using django-cte breaks with django 5.2
-------------------------------------+-------------------------------------
Reporter: benjfield | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by benjfield):

There doesn't look a direct alias call in django-cte, although it may well
do something that has the same effect. I've got a slightly gross
workaround for now in terms of annotating it to a different name and then
annotating it back after the aggregation.
--
Ticket URL: <https://code.djangoproject.com/ticket/36860#comment:3>

[Django]

#36860: Annotate after Union using django-cte breaks with django 5.2
-------------------------------------+-------------------------------------
Reporter: benjfield | Owner: (none)

Type: Bug | Status: closed

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

Severity: Normal | Resolution: invalid

Keywords: union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

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

Comment:

Bisected to 65ad4ade74dc9208b9d686a451cd6045df0c9c3a, which just in your
case drove more traffic into #36352.

> There doesn't look a direct alias call in django-cte, although it may
well do something that has the same effect.

+1 on ''same effect'', looks like some motion in `django-cte` on this
[https://github.com/dimagi/django-cte/issues/123 here].

"promote" may not be the most friendly wording, but it is
[https://docs.djangoproject.com/en/6.0/ref/models/querysets/#:~:text=promote%20it%20to%20an
documented] in `alias()`, which is where `django-cte` is doing things of
the same effect.
--
Ticket URL: <https://code.djangoproject.com/ticket/36860#comment:4>