[Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
ElRoberto538 |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: Expression, Window,
Severity: Normal | aggregate, annotation, partition
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34987>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Simon Charette (added)
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:1>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: ElRoberto538 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* severity: Normal => Release blocker
Comment:
Settings as release blocker since the bisected revision was released in
4.2a1
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:2>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:3>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: ElRoberto538 | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:4>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: ElRoberto538 | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"e76cc93b0168fa3abbafb9af1ab4535814b751f0" e76cc93b]:
{{{
#!CommitTicketReference repository=""
revision="e76cc93b0168fa3abbafb9af1ab4535814b751f0"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:5>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: ElRoberto538 | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"95dec210e89a2fb46ffde2ba29ff7876e1d5f86e" 95dec21]:
{{{
#!CommitTicketReference repository=""
revision="95dec210e89a2fb46ffde2ba29ff7876e1d5f86e"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:6>
Django
on query that also has aggregates.
-------------------------------------+-------------------------------------
Reporter: ElRoberto538 | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: Expression, Window, | Triage Stage: Accepted
aggregate, annotation, partition |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"cf95de9d24c987beff51b54979c02aeef1f98050" cf95de9]:
{{{
#!CommitTicketReference repository=""
revision="cf95de9d24c987beff51b54979c02aeef1f98050"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34987#comment:7>