#35339: Ordering and filtering a Postgres ArrayAgg with parameters inverts SQL
param order
-------------------------------------+-------------------------------------
Reporter: Chris M | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 5.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 |
-------------------------------------+-------------------------------------
When trying to build an ArrayAgg annotation that has a `filter` with
parameters and a `ordering` with parameters, the SQL that is built will
invert the parameters, putting the filter parameters before the ordering
parameters. I was not able to find an existing ticket for this issue.
I have verified this against the current dev branch as well as version
3.2.
I was able to reproduce this in a test in the test_aggregates.py file.
{{{#!python
def test_array_agg_filter_and_ordering_params(self):
values = AggregateTestModel.objects.aggregate(
arrayagg=ArrayAgg(
"char_field",
filter=Q(json_field__has_key="lang"),
ordering=LPad(Cast("integer_field", CharField()), 2,
Value("0")),
)
)
self.assertEqual(values, {"arrayagg": ["Foo2", "Foo4"]})
}}}
The resulting error is something like
{{{
Traceback (most recent call last):
File "/Users/camuthig/projects/oss/django/django/db/backends/utils.py",
line 105, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: function lpad(character varying,
integer, integer) does not exist
LINE 1: ...s_tests_aggregatetestmodel"."char_field" ORDER BY LPAD(("pos...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
}}}
The issue is that the result of the `OrderableAggMixin.as_sql` function is
SQL:
{{{
ARRAY_AGG("postgres_tests_aggregatetestmodel"."char_field" ORDER BY
LPAD(("postgres_tests_aggregatetestmodel"."integer_field")::varchar, %s,
%s)) FILTER (WHERE "postgres_tests_aggregatetestmodel"."json_field" ? %s)
}}}
Parameters
{{{
[ "lang", 2, "0"]
}}}
So we are trying to use "lang" and 2 as the values for the ordering
function, and "0" as the parameter for the filtering function. This is
made a bit more confusing if the expression you are aggregating also has a
parameter, because that should be before the ordering parameters. It
should be
1. Expression parameters
2. Ordering parameters
3. Filtering parameters
This happens because both the expression and filtering parameters come
from the standard Aggregate parent class, and are then put in front of the
ordering parameters in the Postgres-specific orderable mixin.
I have been able to resolve this issue locally by altering the
`OrderableAggMixin.as_sql` function to retrieve the parameters from the
parent and then split them manually.
{{{#!python
class OrderableAggMixin:
# ... other functions
def as_sql(self, compiler, connection):
if self.order_by is not None:
order_by_sql, order_by_params =
compiler.compile(self.order_by)
else:
order_by_sql, order_by_params = "", ()
sql, expression_params = super().as_sql(compiler, connection,
ordering=order_by_sql)
filter_params = ()
if self.filter:
try:
_, filter_params = self.filter.as_sql(compiler,
connection)
expression_params =
expression_params[:-len(filter_params)]
except FullResultSet:
pass
return sql, (*expression_params, *order_by_params, *filter_params)
}}}
This solution technically works, but it feels a bit clunky, so I am open
to suggestions on how to improve it. I can also create a pull request with
the change if you would like to see it, though my changes are all captured
here already.
--
Ticket URL: <https://code.djangoproject.com/ticket/35339>
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/0107018e855e5e26-a8034f57-cc14-4365-92a7-186c14ea8ddb-000000%40eu-central-1.amazonses.com.
