Annotate after Union using a CTE breaks with django 5.2 - django.core.exceptions.FieldError: Cannot select the 'underlying_id' alias. Use annotate() to promote it. #133
Description
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."