Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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 ):

 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bfa965f43-c6c127c8-495a-4cce-9c01-b20767b159da-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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 ):

 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bfa961851-2901d20e-ecca-4b43-ac75-dd9d2ee9e0ca-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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
-+-
Changes (by GitHub ):

 * 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bfa950f3a-ccf051b4-9545-4a46-95fb-10b95fe9b24e-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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   |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * has_patch:  0 => 1


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bf8dd4bb6-20fdd8ca-29cb-4965-b9e9-4fd9c6a5c088-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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   |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bf8a5e024-000f0ce2-12bf-4055-b684-4d650cf2a1cb-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
on query that also has aggregates.
-+-
 Reporter:  ElRoberto538 |Owner:  nobody
 Type:  Bug  |   Status:  new
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
-+-
Changes (by Natalia Bidart):

 * severity:  Normal => Release blocker


Comment:

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

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bf6ca76d1-881a7781-83b3-4944-877e-c648032d2834-00%40eu-central-1.amazonses.com.

Re: [Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
on query that also has aggregates.
-+-
 Reporter:  ElRoberto538 |Owner:  nobody
 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
-+-
Changes (by David Sanders):

 * 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bf53a928e-18850e28-37ea-4d40-b84a-00d024a2407b-00%40eu-central-1.amazonses.com.

[Django] #34987: "Expression contains mixed types" exception when performing window function on query that also has aggregates.

[Django]

#34987: "Expression contains mixed types" exception when performing window 
function
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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018bf50d067a-8a90647a-e65e-4b5d-be93-ce19d93681d7-00%40eu-central-1.amazonses.com.