"When Case" query generating rule in duplicate

[imaia]

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).

[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,

[Artem Nesterenko]

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;

[Simon Charette]

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

[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)

[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.

[Simon Charette]

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

[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?

[Mariusz Felisiak]

No, it doesn't qualify for the backport.

[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.

[Mariusz Felisiak]

Django 3.0 should appear in December 2019.