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
31568	Alias used in aggregate filtering is incorrect.	Gagaro	Simon Charette	"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)."	Bug	closed	Database layer (models, ORM)	3.0	Release blocker	fixed		Simon Charette	Accepted	0	0	0	0	0	0