Opened 4 years ago

Closed 8 months ago

Last modified 8 months ago

#26658 closed Bug (fixed)

"When Case" query generating rule in duplicate

Reported by: imaia Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: josh.smeaton@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a query that looks like this:

Model.objects.values('field').annotate(value=When(Case(something=number, then=1), default=0, output_field=IntegerField())).annotate(count=Count('field'), mysum=Sum('value'))

The query above generates the "When Case" clause twice. Once for value and another time at the end of the query. That can be verified evaluating the str(queryset.query).

Change History (13)

comment:1 Changed 4 years ago by Josh Smeaton

Can you please provide more information, like the SQL you're seeing and the SQL you expect?

Also, When(Case()) isn't valid. When are child objects of Case. I think your ordering of values() clauses is also off. The query should be something like:

Model.objects.annotate(value=Case(When(something=number, then=1), default=0, output_field=IntegerField())).values('field', 'value').annotate(count=Count('field'), mysum=Sum('value'))

A values() clause implies the grouping (in GROUP BY). By annotating a non-aggregate later you mess with the grouping and the select list. Can you try this query above, and respond with the actual sql and desired sql if there's still an issue please?
I'm not sure if what you wrote is just a typo or not,

comment:2 Changed 4 years ago by Josh Smeaton

Cc: josh.smeaton@… added

comment:3 Changed 4 years ago by Tim Graham

Resolution: needsinfo
Status: newclosed

comment:4 Changed 8 months ago by Artem Nesterenko

Resolution: needsinfo
Status: closednew

I confirm that this behavior is still taking place in Django 2.2.

  class CustomQuerySet(QuerySet):
    def annotate_statuses(self):
        moment = now()
        return self.annotate(
            status=Case(
                When(
                    timestamp__lte=moment
                    - timedelta(seconds=settings.OFFLINE_SECONDS_THRESHOLD),
                    then=Value("OFFLINE"),
                ),
                When(
                    idle__gte=settings.IDLE_SECONDS_THRESHOLD,
                    then=Value("IDLE"),
                ),
                default=Value("ACTIVE"),
                output_field=CharField(),
            )
        )

Having this queryset the query:

Model.objects.annotate_statuses().values("status").annotate(count=Count("status"))

generates the following sql:

SELECT CASE
           WHEN "my_table"."timestamp" <= '2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
           WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
           ELSE 'ACTIVE' END        AS "status",
       COUNT(CASE
                 WHEN "my_table"."timestamp" <= '2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
                 WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
                 ELSE 'ACTIVE' END) AS "count"
FROM "my_table"
GROUP BY CASE
             WHEN "my_table"."timestamp" <= '2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
             WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
             ELSE 'ACTIVE' END;

I expect it to generate something similar to:

select count(*), status
from (select CASE
                 WHEN (timestamp <= '2019-04-19T09:23:04.681449+00:00'::timestamptz)
                     THEN 'OFFLINE'
                 WHEN (idle >= 300.0) THEN 'IDLE'
                 ELSE 'ACTIVE' END as status
      from my_table
     )
group by status;

comment:5 Changed 8 months ago by Artem Nesterenko

Version: 1.92.2

comment:6 Changed 8 months ago by Simon Charette

Could you try against the master branch? There was a few recent changes around grouping and aliasing that probably addressed this issue.

comment:7 in reply to:  6 Changed 8 months ago by Artem Nesterenko

Replying to Simon Charette:

Could you try against the master branch? There was a few recent changes around grouping and aliasing that probably addressed this issue.

I tried to do it by executing pip install -U git+https://github.com/django/django.git but got

Traceback (most recent call last):
  File "./src/manage.py", line 17, in <module>
    main()
  File "./src/manage.py", line 11, in main
    from configurations.management import execute_from_command_line
  File "/usr/local/lib/python3.7/site-packages/configurations/__init__.py", line 2, in <module>
    from .base import Configuration
  File "/usr/local/lib/python3.7/site-packages/configurations/base.py", line 4, in <module>
    from django.utils import six
ImportError: cannot import name 'six' from 'django.utils' (/usr/local/lib/python3.7/site-packages/django/utils/__init__.py)

comment:8 Changed 8 months ago by Simon Charette

It looks like one of your project's INSTALLED_APPS doesn't support Django master yet. Could you possible provide the exact set of models to perform the queryset operation on.

comment:9 Changed 8 months ago by Simon Charette

Resolution: fixed
Status: newclosed

Confirmed this is fixed against master with models matching the queried fields.

comment:10 in reply to:  9 Changed 8 months ago by Artem Nesterenko

Replying to Simon Charette:

Confirmed this is fixed against master with models matching the queried fields.

Great news! Will these changes be included in the 2.2.1 release?

comment:11 Changed 8 months ago by felixxm

No, it doesn't qualify for the backport.

comment:12 in reply to:  11 Changed 8 months ago by Artem Nesterenko

Replying to felixxm:

No, it doesn't qualify for the backport.

When it is expected to publish the changes to PyPi? I would like to use a subquery in my annotation and this bug will affect the request performance dramatically.

comment:13 Changed 8 months ago by felixxm

Version: 2.2master

Django 3.0 should appear in December 2019.

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