[Django] #35613: SQL params mismatch when using ArrayAgg with timezone override

[Django]

#35613: SQL params mismatch when using ArrayAgg with timezone override
--------------------------+--------------------------------------------
Reporter: conorato | Type: Bug
Status: new | Component: contrib.postgres
Version: 5.0 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------+--------------------------------------------
Hi! When updating from Django 4.0 to Django 5.0., I encountered an issue
related to the use of django.contrib.postgres.aggregates's ArrayAgg. I
also checked and this issue also happens on Django 4.1 and 4.2.

Given the following models:
{{{
from django.db import models


class Publisher(models.Model):
name = models.CharField(max_length=300)


class Book(models.Model):
name = models.CharField(max_length=300)
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
published_on = models.DateTimeField(null=True)
}}}

If I run the following in a shell

{{{
import zoneinfo
from django.utils import timezone
from django.contrib.postgres.aggregates import ArrayAgg
from django.db.models import Q
from library.models import Publisher

with timezone.override(zoneinfo.ZoneInfo('America/Toronto')):
Publisher.objects.annotate(books_date=ArrayAgg(
'book__published_on__date',
filter=Q(id=100),
ordering=('book__published_on__date',),
)).all()
}}}

I get an error saying there's an invalid input syntax. Looking at the
generated SQL, I see there's indeed an error, where the argument for the
filter was instead use to localize the field in the order by, and vice-
versa.

{{{
SELECT
"library_publisher"."id",
"library_publisher"."name",
ARRAY_AGG(
(
"library_book"."published_on" AT TIME ZONE 'America/Toronto'
) :: date
ORDER BY
("library_book"."published_on" AT TIME ZONE 100) :: date
) FILTER (
WHERE
"library_publisher"."id" = 'America/Toronto'
) AS "books_date"
FROM
"library_publisher"
LEFT OUTER JOIN "library_book" ON (
"library_publisher"."id" = "library_book"."publisher_id"
)
GROUP BY
"library_publisher"."id";
}}}

Let me know if you need any more information. I created a small django
project for this that I can share.
--
Ticket URL: <https://code.djangoproject.com/ticket/35613>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35613: SQL params mismatch when using ArrayAgg with timezone override

----------------------------------+--------------------------------------
Reporter: Claudia Onorato | Owner: (none)

Type: Bug | Status: new
Component: contrib.postgres | Version: 5.0

Severity: Normal | Resolution:

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------------+--------------------------------------
Comment (by Simon Charette):

Hey Claudia, thank you for your report I'm pretty sure this is a duplicate
of #35339 which was fixed by c8df2f994130d74ec35d32a36e30aad7d6ea8e3a
which will be part of the upcoming 5.1 release (expected August 2024). It
wasn't backported as when it was discovered, 4 months ago, the issue had
existed since the introduction of `ArrayAgg` as you've discovered.

I've confirmed with the following test

{{{#!diff
diff --git a/tests/postgres_tests/test_aggregates.py
b/tests/postgres_tests/test_aggregates.py
index b72310bdf1..9c2ba53cba 100644
--- a/tests/postgres_tests/test_aggregates.py
+++ b/tests/postgres_tests/test_aggregates.py
@@ -654,6 +654,24 @@ def
test_string_agg_filter_in_subquery_with_exclude(self):
[self.aggs[0]],
)

+ def test_ticket_35613(self):
+ import zoneinfo
+ from django.utils import timezone
+ from django.contrib.postgres.aggregates import ArrayAgg
+ from django.db.models import Q
+ from .models import Publisher
+
+ with timezone.override(zoneinfo.ZoneInfo("America/Toronto")):
+ list(
+ Room.objects.annotate(
+ books_date=ArrayAgg(
+ "hotelreservation__start__date",
+ filter=Q(id=100),
+ ordering=("hotelreservation__start__date",),
+ )
+ )
+ )
+
def test_ordering_isnt_cleared_for_array_subquery(self):
inner_qs = AggregateTestModel.objects.order_by("-integer_field")
qs = AggregateTestModel.objects.annotate(
}}}

Please re-open if you can reproduce against `Django==5.1.b1`.
--
Ticket URL: <https://code.djangoproject.com/ticket/35613#comment:1>

[Django]

#35613: SQL params mismatch when using ArrayAgg with timezone override
----------------------------------+--------------------------------------

Reporter: conorato | Owner: (none)
Type: Bug | Status: closed
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Changes (by Simon Charette):

* resolution: => duplicate
* status: new => closed

--
Ticket URL: <https://code.djangoproject.com/ticket/35613#comment:2>