[Django] #26658: "When Case" query generating rule in duplicate
Django
----------------------------------------------+--------------------
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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)'''.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(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 jarshwah):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
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,
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:1>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:2>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:3>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by artemnesterenko):
* status: closed => new
* resolution: needsinfo =>
Comment:
I confirm that this behavior is still taking place in Django 2.2.
{{{#!python
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:
{{{#!python
Model.objects.annotate_statuses().values("status").annotate(count=Count("status"))
}}}
generates the following sql:
{{{#!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:
{{{#!sql
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;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:4>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by artemnesterenko):
* version: 1.9 => 2.2
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:5>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:6>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Artem Nesterenko):
Replying to [comment:6 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
{{{#!python
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)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:7>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:8>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* status: new => closed
* resolution: => fixed
Comment:
Confirmed this is fixed against `master` with models matching the queried
fields.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:9>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: fixed
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Artem Nesterenko):
Replying to [comment:9 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:10>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: fixed
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
No, it doesn't qualify for the backport.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:11>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: fixed
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Artem Nesterenko):
Replying to [comment:11 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:12>
Django
Reporter: imaia | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* version: 2.2 => master
Comment:
Django 3.0 should appear in December 2019.
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:13>