#35339: Ordering and filtering a Postgres ArrayAgg with parameters inverts SQL
param order
-------------------------------------+-------------------------------------
Reporter: Chris M | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres arrayagg | Triage Stage: Accepted
ordering |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Thank you for the detailed report Chris and for the regression test
Natalia this is very useful!
Since this a long standing issue and we thus don't have to deal with a
backport I would suggest we bite the bullet and refactor things up to have
`OrderableAggMixin` merged in `Aggregate` and have a flag similar to
`allow_distinct`.
The crux of the issue here is that we try be clever and have the
`get_source_expressions` / `set_source_expressions` signature change
depending on whether or not a `filter` and and `order_by` are assigned
(the `len` of expressions will change) so we can satisfy
`list[Expression]`. We tried doing that for a while with `Window` but it
became clear that using `None` as placeholders for unspecified expressions
was much easier to reason about pretty much everything expects
`get_expressions` to be `list[Expression | None]`. Doing so avoids the
conditional shenanigans that are causing out of order due to
`expressions.pop` and such.
Another big plus to having all the logic lives in `Aggregate` is that it
makes implementing things like `STRING_AGG` (AKA `GROUP_CONCAT`) and
[https://sqlite.org/releaselog/3_44_0.html other expressions that gain
support for ordering overnight much easier] and tested in a generic way.
If that's something you're interesting in working on Chris I'd be happy to
provide you reviewing support.
--
Ticket URL: <https://code.djangoproject.com/ticket/35339#comment:2>
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/0107018e91ed5543-a80dbe46-c2a4-4e59-a70f-b646d53ee013-000000%40eu-central-1.amazonses.com.
