id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36860	Annotate after Union using django-cte breaks with django 5.2	benjfield		"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

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."	Bug	new	Database layer (models, ORM)	5.2	Normal		union		Unreviewed	0	0	0	0	0	0