Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by felixxm):

 Replying to [comment:4 Simon Charette]:
 > Submitted a patch but we should also fix `Subquery.__eq__` which was
 broken by 691def10a0197d83d2d108bd9043b0916d0f09b4. Right now
 `Subquery(qs1) == Subquery(qs2)`.

 I will create a separate issue for this.

-- 
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/064.cc0340e59feedf9ec5c7d686976337c9%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak ):

 In [changeset:"49bbf6570d9f0880b836f741d79e4cdb6e061ea2" 49bbf657]:
 {{{
 #!CommitTicketReference repository=""
 revision="49bbf6570d9f0880b836f741d79e4cdb6e061ea2"
 [3.0.x] Fixed #31568 -- Fixed alias reference when aggregating over
 multiple subqueries.

 691def10a0197d83d2d108bd9043b0916d0f09b4 made all Subquery() instances
 equal to each other which broke aggregation subquery pushdown which
 relied on object equality to determine which alias it should select.

 Subquery.__eq__() will be fixed in an another commit but
 Query.rewrite_cols() should haved used object identity from the start.

 Refs #30727, #30188.

 Thanks Makina Corpus for the report.

 Backport of adfbf653dc1c1d0e0dacc4ed46602d22ba28b004 from master
 }}}

-- 
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/064.c511bf781797cf08c9a74c5904aa5ad2%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak ):

 In [changeset:"3913acdb29d09109ec82ce789b592e6281aa7be9" 3913acdb]:
 {{{
 #!CommitTicketReference repository=""
 revision="3913acdb29d09109ec82ce789b592e6281aa7be9"
 [3.1.x] Fixed #31568 -- Fixed alias reference when aggregating over
 multiple subqueries.

 691def10a0197d83d2d108bd9043b0916d0f09b4 made all Subquery() instances
 equal to each other which broke aggregation subquery pushdown which
 relied on object equality to determine which alias it should select.

 Subquery.__eq__() will be fixed in an another commit but
 Query.rewrite_cols() should haved used object identity from the start.

 Refs #30727, #30188.

 Thanks Makina Corpus for the report.

 Backport of adfbf653dc1c1d0e0dacc4ed46602d22ba28b004 from master
 }}}

-- 
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/064.9cb89849b6e2418f242890640d55a9ae%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak ):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 In [changeset:"adfbf653dc1c1d0e0dacc4ed46602d22ba28b004" adfbf653]:
 {{{
 #!CommitTicketReference repository=""
 revision="adfbf653dc1c1d0e0dacc4ed46602d22ba28b004"
 Fixed #31568 -- Fixed alias reference when aggregating over multiple
 subqueries.

 691def10a0197d83d2d108bd9043b0916d0f09b4 made all Subquery() instances
 equal to each other which broke aggregation subquery pushdown which
 relied on object equality to determine which alias it should select.

 Subquery.__eq__() will be fixed in an another commit but
 Query.rewrite_cols() should haved used object identity from the start.

 Refs #30727, #30188.

 Thanks Makina Corpus 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/064.2a55e3043de11847dfe74aca93fa50ce%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Submitted a patch but we should also fix `Subquery.__eq__` which was
 broken by 691def10a0197d83d2d108bd9043b0916d0f09b4. Right now
 `Subquery(qs1) == Subquery(qs2)`.

-- 
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/064.973083111e8e9dfc658c6e4e90f5435c%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect.

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Gagaro):

 Replying to [comment:1 Simon Charette]:
 > Thank you for your report. Did you manage to reproduce against Django
 3.0.5 as well? If it's the case could you try reducing your model set and
 queryset interactions to a minimal that still trigger the issue. That'll
 help tremendously in reproducing the regression and ensure it gets
 addressed in a timely manner. Thanks!

 I tried to reduce it as much as I could in my context without having to
 try with whole new models. Do you still need me to do that as felixxm
 apparently was able to reproduce it?

 Thanks.

-- 
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/064.75bbfdef5a286789aae8d03a69b20e69%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect. (was: Alias used in aggregate filtering is incorrect)

[Django]

#31568: Alias used in aggregate filtering is incorrect.
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * severity:  Normal => Release blocker
 * stage:  Unreviewed => Accepted


Comment:

 Thanks for this ticket.

 Regression in 691def10a0197d83d2d108bd9043b0916d0f09b4.
 Reproduced at 46fe506445666d8097945f0c1e8be11cfd644b28.

-- 
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/064.b3860a7de7e7217cd9747a22b69ba615%40djangoproject.com.

Re: [Django] #31568: Alias used in aggregate filtering is incorrect

[Django]

#31568: Alias used in aggregate filtering is incorrect
-+-
 Reporter:  Gagaro   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * cc: Simon Charette (added)
 * owner:  nobody => Simon Charette
 * status:  new => assigned


Comment:

 Thank you for your report. Did you manage to reproduce against Django
 3.0.5 as well? If it's the case could you try reducing your model set and
 queryset interactions to a minimal that still trigger the issue. That'll
 help tremendously in reproducing the regression and ensure it gets
 addressed in a timely manner. Thanks!

-- 
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/064.7491d86210fac84f04db9ee7aa6e18f0%40djangoproject.com.

[Django] #31568: Alias used in aggregate filtering is incorrect

[Django]

#31568: Alias used in aggregate filtering is incorrect
-+-
   Reporter:  Gagaro |  Owner:  nobody
   Type:  Bug| Status:  new
  Component:  Database   |Version:  3.0
  layer (models, ORM)|
   Severity:  Normal |   Keywords:
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 With the following queryset:

 {{{
 IndicatorValue.objects
 .values("freight")
 .annotate(
 loading_time=Min("datetime",
 filter=Q(type=IndicatorValue.TYPE_FREIGHT_CREATED)) - Max("datetime",
 filter=Q(type=IndicatorValue.TYPE_FREIGHT_COMPLETED)),
 
has_top_loading=Exists(OrderItemResult.objects.order_by().filter(order_line__order__freight=OuterRef("freight"),
 loading_arm__loading_type=LoadingArm.LOADING_TYPE_TOP, ).values('pk')),
 
has_bottom_loading=Exists(OrderItemResult.objects.order_by().filter(order_line__order__freight=OuterRef("freight"),
 loading_arm__loading_type=LoadingArm.LOADING_TYPE_BOTTOM, ).values('pk'))
 )
 .aggregate(
 top_min=Min("loading_time", filter=Q(has_top_loading=True,
 has_bottom_loading=False))
 )
 }}}

 I get the following SQL generated for the aggregate (notice that both
 alias used are the same in the SQL, whereas they are not in the queryset):

 {{{
 MIN("loading_time") FILTER (WHERE ("has_top_loading" = false AND
 "has_top_loading" = true))
 }}}

 The full SQL generated is:

 {{{
 SELECT MIN("loading_time") FILTER (WHERE ("has_top_loading" = false AND
 "has_top_loading" = true)) FROM (SELECT
 "indicators_indicatorvalue"."freight_id" AS Col1,
 (MIN("indicators_indicatorvalue"."datetime") FILTER (WHERE
 "indicators_indicatorvalue"."type" = \'freight_created\') -
 MAX("indicators_indicatorvalue"."datetime") FILTER (WHERE
 "indicators_indicatorvalue"."type" = \'freight_completed\')) AS
 "loading_time", EXISTS(SELECT U0."id" FROM "orders_orderitemresult" U0
 INNER JOIN "loading_terminal_loadingarm" U1 ON (U0."loading_arm_id" =
 U1."id") INNER JOIN "orders_orderitem" U2 ON (U0."order_line_id" =
 U2."id") INNER JOIN "orders_order" U3 ON (U2."order_id" = U3."id") WHERE
 (U1."loading_type" = \'TOP\' AND U3."freight_id" =
 "indicators_indicatorvalue"."freight_id")) AS "has_top_loading",
 EXISTS(SELECT U0."id" FROM "orders_orderitemresult" U0 INNER JOIN
 "loading_terminal_loadingarm" U1 ON (U0."loading_arm_id" = U1."id") INNER
 JOIN "orders_orderitem" U2 ON (U0."order_line_id" = U2."id") INNER JOIN
 "orders_order" U3 ON (U2."order_id" = U3."id") WHERE (U1."loading_type" =
 \'BOTTOM\' AND U3."freight_id" =
 "indicators_indicatorvalue"."freight_id")) AS "has_bottom_loading" FROM
 "indicators_indicatorvalue" WHERE "indicators_indicatorvalue"."deleted" IS
 NULL GROUP BY "indicators_indicatorvalue"."freight_id", "has_top_loading",
 "has_bottom_loading") subquery
 }}}

 It works fine with Django 2.2 (which does not use alias there if I'm not
 mistaken).

-- 
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/049.85adb155076707f3207cee394f74a343%40djangoproject.com.