#35235: ArrayAgg() doesn't return default when filter contains __in=[].
----------------------------------+--------------------------------------
Reporter: Per Carlsen | Owner: Sharon Woo
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution:
Keywords: ArrayAgg | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
Comment (by David Sanders):
I may have misled you there… on GH I mentioned not needing test data but
you do actually need at least one row present in the table to get the
wrong result. (The reason why my simplified test worked is because
there's data in `setupTestData()`)
To explain:
The source of the issue is when the aggregate filter is testing for a
"contradiction" (ie something that's guaranteed to be false), eg:
{{{
ArrayAgg(…, filter=Q(whatever__in=[]))
}}}
here it's guaranteed that the filter will always be false. Django has a
chance to do some optimisations when this occurs.
Normally when you do
{{{
Foo.objects.filter(whatever__in=[])
}}}
Django will raise an `EmptyResultSet` and the catching code will skip
calling the db altogether so as not to run an unnecessary query & save on
time.
However, when it's within an annotation, Django still needs to run the
query. The optimisation that occurs in this case is that Django will
simplify the expression instead.
If you observe the query (by doing `print(queryset.query)`) you'll see
that the annotation has been optimised to:
{{{
SELECT …, COALESCE(NULL, '{}') …
}}}
where Django has deliberately replaced the input to `COALESCE` with
`NULL`.
This reveals the source of the problem: the expression `COALESCE(NULL,
'{}')` is of type string. It _should_ be something along the lines of
`COALESCE(NULL, '{}'::integer[])` to force the expression to be of type
integer array.
Hope that helps? Sorry for the long-winded explanation but it took me
yonks to realise what was happening under the hood with Django so thought
you could use the primer 👍😊
--
Ticket URL: <https://code.djangoproject.com/ticket/35235#comment:9>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/0107018dcb1a1aca-b5631506-cda8-4695-8e06-35b1ad659756-000000%40eu-central-1.amazonses.com.
