SQL params mismatch when using ArrayAgg with timezone override

[Claudia Onorato]

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.

[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 noticed.

I've confirmed with the following test

tests/postgres_tests/test_aggregates.py

@@ -704 +704 @@
             [self.aggs[0]],
         )
 
+    def test_foo_bar(self):
+        import zoneinfo
+        from django.utils import timezone
+        from django.contrib.postgres.aggregates import ArrayAgg
+        from django.db.models import Q
+
+        with (
+            self.settings(USE_TZ=True),
+            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.1b1.