"Expression contains mixed types" exception when performing window function on query that also has aggregates.

[ElRoberto538]

We're currently upgrading from Django 3.2.x -> 4.2.7 and have encountered a weird bug when using both Window functions and aggregates in a query annotation, the example below works fine in Django 3.2. For now I've cast all the fields in the Window partition to CharField, but it's a bit of a hack... I tested this with both mssql and sqlite connectors.

class TestModel(models.Model):
    field_1 = models.IntegerField()
    field_2 = models.IntegerField()
    field_3 = models.DateField()

TestModel.objects.annotate(
    xyz=Window(
        expression=Sum('field_1'),
        partition_by=[F('field_2'), F('field_3')]
    ),
    yxz=Max('field_2')
)

django.core.exceptions.FieldError: Expression contains mixed types: AutoField, DateField. You must set output_field.

[David Sanders]

Hi ElRoberto538,

Thanks for the report 🏆

Bisected to f387d024fc75569d2a4a338bfda76cc2f328f627

@Simon ticket appears similar to #34717 which is fixed, though can still replicate this issue on main.

[Natalia Bidart]

Settings as release blocker since the bisected revision was released in 4.2a1

[Simon Charette]

I'm pretty sure that the issue is due to Window's output field resolving is considering partition_by as part of its output when it shouldn't.

[GitHub <noreply@…>]

In e76cc93b:

Fixed #34987 -- Fixed queryset crash when mixing aggregate and window annotations.

Regression in f387d024fc75569d2a4a338bfda76cc2f328f627.

Just like OrderByList the ExpressionList expression used to wrap
Window.partition_by must implement get_group_by_cols to ensure the
necessary grouping when mixing window expressions with aggregate
annotations is performed against the partition members and not the
partition expression itself.

This is necessary because while partition_by is implemented as
a source expression of Window it's actually a fragment of the WINDOW
expression at the SQL level and thus it should result in a group by its
members and not the sum of them.

Thanks ElRoberto538 for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 95dec21:

[5.0.x] Fixed #34987 -- Fixed queryset crash when mixing aggregate and window annotations.

Regression in f387d024fc75569d2a4a338bfda76cc2f328f627.

Just like OrderByList the ExpressionList expression used to wrap
Window.partition_by must implement get_group_by_cols to ensure the
necessary grouping when mixing window expressions with aggregate
annotations is performed against the partition members and not the
partition expression itself.

This is necessary because while partition_by is implemented as
a source expression of Window it's actually a fragment of the WINDOW
expression at the SQL level and thus it should result in a group by its
members and not the sum of them.

Thanks ElRoberto538 for the report.
Backport of e76cc93b0168fa3abbafb9af1ab4535814b751f0 from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In cf95de9:

[4.2.x] Fixed #34987 -- Fixed queryset crash when mixing aggregate and window annotations.

Regression in f387d024fc75569d2a4a338bfda76cc2f328f627.

Just like OrderByList the ExpressionList expression used to wrap
Window.partition_by must implement get_group_by_cols to ensure the
necessary grouping when mixing window expressions with aggregate
annotations is performed against the partition members and not the
partition expression itself.

This is necessary because while partition_by is implemented as
a source expression of Window it's actually a fragment of the WINDOW
expression at the SQL level and thus it should result in a group by its
members and not the sum of them.

Thanks ElRoberto538 for the report.
Backport of e76cc93b0168fa3abbafb9af1ab4535814b751f0 from main