#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:
> 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
New 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.".
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.
--
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 <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/0107019bb30642c5-bdcec23b-a743-4463-a268-c2a7483db9c9-000000%40eu-central-1.amazonses.com.
