Opened 3 months ago

Closed 3 months ago

Last modified 3 months ago

#34987 closed Bug (fixed)

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

Reported by: ElRoberto538 Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: Expression, Window, aggregate, annotation, partition
Cc: Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (7)

comment:1 by David Sanders, 3 months ago

Cc: Simon Charette added
Triage Stage: UnreviewedAccepted

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.

comment:2 by Natalia Bidart, 3 months ago

Severity: NormalRelease blocker

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

comment:3 by Simon Charette, 3 months ago

Owner: changed from nobody to Simon Charette
Status: newassigned

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.

comment:4 by Simon Charette, 3 months ago

Has patch: set

comment:5 by GitHub <noreply@…>, 3 months ago

Resolution: fixed
Status: assignedclosed

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.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

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

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

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

Note: See TracTickets for help on using tickets.
Back to Top