id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35613	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."	Bug	closed	contrib.postgres	5.0	Normal	duplicate			Unreviewed	0	0	0	0	0	0