#31568: Alias used in aggregate filtering is incorrect
-------------------------------------+-------------------------------------
Reporter: Gagaro | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
With the following queryset:
{{{
IndicatorValue.objects
.values("freight")
.annotate(
loading_time=Min("datetime",
filter=Q(type=IndicatorValue.TYPE_FREIGHT_CREATED)) - Max("datetime",
filter=Q(type=IndicatorValue.TYPE_FREIGHT_COMPLETED)),
has_top_loading=Exists(OrderItemResult.objects.order_by().filter(order_line__order__freight=OuterRef("freight"),
loading_arm__loading_type=LoadingArm.LOADING_TYPE_TOP, ).values('pk')),
has_bottom_loading=Exists(OrderItemResult.objects.order_by().filter(order_line__order__freight=OuterRef("freight"),
loading_arm__loading_type=LoadingArm.LOADING_TYPE_BOTTOM, ).values('pk'))
)
.aggregate(
top_min=Min("loading_time", filter=Q(has_top_loading=True,
has_bottom_loading=False))
)
}}}
I get the following SQL generated for the aggregate (notice that both
alias used are the same in the SQL, whereas they are not in the queryset):
{{{
MIN("loading_time") FILTER (WHERE ("has_top_loading" = false AND
"has_top_loading" = true))
}}}
The full SQL generated is:
{{{
SELECT MIN("loading_time") FILTER (WHERE ("has_top_loading" = false AND
"has_top_loading" = true)) FROM (SELECT
"indicators_indicatorvalue"."freight_id" AS Col1,
(MIN("indicators_indicatorvalue"."datetime") FILTER (WHERE
"indicators_indicatorvalue"."type" = \'freight_created\') -
MAX("indicators_indicatorvalue"."datetime") FILTER (WHERE
"indicators_indicatorvalue"."type" = \'freight_completed\')) AS
"loading_time", EXISTS(SELECT U0."id" FROM "orders_orderitemresult" U0
INNER JOIN "loading_terminal_loadingarm" U1 ON (U0."loading_arm_id" =
U1."id") INNER JOIN "orders_orderitem" U2 ON (U0."order_line_id" =
U2."id") INNER JOIN "orders_order" U3 ON (U2."order_id" = U3."id") WHERE
(U1."loading_type" = \'TOP\' AND U3."freight_id" =
"indicators_indicatorvalue"."freight_id")) AS "has_top_loading",
EXISTS(SELECT U0."id" FROM "orders_orderitemresult" U0 INNER JOIN
"loading_terminal_loadingarm" U1 ON (U0."loading_arm_id" = U1."id") INNER
JOIN "orders_orderitem" U2 ON (U0."order_line_id" = U2."id") INNER JOIN
"orders_order" U3 ON (U2."order_id" = U3."id") WHERE (U1."loading_type" =
\'BOTTOM\' AND U3."freight_id" =
"indicators_indicatorvalue"."freight_id")) AS "has_bottom_loading" FROM
"indicators_indicatorvalue" WHERE "indicators_indicatorvalue"."deleted" IS
NULL GROUP BY "indicators_indicatorvalue"."freight_id", "has_top_loading",
"has_bottom_loading") subquery
}}}
It works fine with Django 2.2 (which does not use alias there if I'm not
mistaken).
--
Ticket URL: <https://code.djangoproject.com/ticket/31568>
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.85adb155076707f3207cee394f74a343%40djangoproject.com.
