#24692: QuerySet.extra(select=...) is silently dropped with aggregations
-------------------------------+--------------------------------------
Reporter: jdelic | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
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
-------------------------------+--------------------------------------
Description changed by jdelic:
Old description:
> Here is a minimal example:
> {{{
> #!python
> from django.db import models
>
> class Service(models.Model):
> name = models.TextField()
>
> class ServiceAction(models.Model):
> performed = models.DateTimeField(auto_now_add=True)
> service = models.ForeignKey(Service)
> }}}
>
> Then add some models:
> {{{
> #!python
> >>> from dbtest.models import Service, ServiceAction
> >>> from django.db.models import Min, Max
> >>> s1 = Service.objects.create(name='test1')
> >>> s2 = Service.objects.create(name='test2')
> >>> ServiceAction.objects.create(service_id=s1.id)
> >>> ServiceAction.objects.create(service_id=s1.id)
> >>> ServiceAction.objects.create(service_id=s1.id)
> >>> ServiceAction.objects.create(service_id=s2.id)
> >>> ServiceAction.objects.create(service_id=s2.id)
> >>> ServiceAction.objects.create(service_id=s2.id)
> }}}
>
> ...and query them using aggregations:
> {{{
> #!python
> >>> qs = ServiceAction.objects.extra(select={'period':
> '(MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed"))'}).values('service_id').annotate(first_action=Min('performed'),
> last_action=Max('performed'))
> >>> print(qs.query)
> SELECT "dbtest_serviceaction"."service_id",
> MAX("dbtest_serviceaction"."performed") AS "last_action",
> MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
> "dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
> }}}
>
> As you can see the {{{.extra(select=...)}}} has been dropped from the
> query. I assume this happens on Django 1.6.x, 1.7.x and 1.8.x, but I have
> only tested 1.6.11 and 1.8.
>
> The expected outcome would have been:
>
> {{{
> SELECT "dbtest_serviceaction"."service_id",
> MAX("dbtest_serviceaction"."performed") AS "last_action",
> MIN("dbtest_serviceaction"."performed") AS "first_action",
> MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed")
> as "period" FROM "dbtest_serviceaction" GROUP BY
> "dbtest_serviceaction"."service_id"
> }}}
>
> In Django 1.8 this exact query can be performed with the ORM itself
> through the use of chained aggregations:
>
> {{{
> #!python
> >>> qs =
> ServiceAction.objects.values('service_id').annotate(first_action=Min('performed'),
> last_action=Max('performed'), period=Max('performed')-Min('performed'))
> >>> print(qs.query)
> SELECT "dbtest_serviceaction"."service_id",
> (MAX("dbtest_serviceaction"."performed") -
> MIN("dbtest_serviceaction"."performed")) AS "period",
> MAX("dbtest_serviceaction"."performed") AS "last_action",
> MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
> "dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
> }}}
New description:
Here is a minimal example:
{{{
#!python
from django.db import models
class Service(models.Model):
name = models.TextField()
class ServiceAction(models.Model):
performed = models.DateTimeField(auto_now_add=True)
service = models.ForeignKey(Service)
}}}
Then add some models:
{{{
#!python
>>> from dbtest.models import Service, ServiceAction
>>> from django.db.models import Min, Max
>>> s1 = Service.objects.create(name='test1')
>>> s2 = Service.objects.create(name='test2')
>>> ServiceAction.objects.create(service_id=s1.id)
>>> ServiceAction.objects.create(service_id=s1.id)
>>> ServiceAction.objects.create(service_id=s1.id)
>>> ServiceAction.objects.create(service_id=s2.id)
>>> ServiceAction.objects.create(service_id=s2.id)
>>> ServiceAction.objects.create(service_id=s2.id)
}}}
...and query them using aggregations:
{{{
#!python
>>> qs = ServiceAction.objects.extra(select={'period':
'(MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed"))'}).values('service_id').annotate(first_action=Min('performed'),
last_action=Max('performed'))
>>> print(qs.query)
SELECT "dbtest_serviceaction"."service_id",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
"dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
}}}
As you can see the {{{.extra(select=...)}}} has been dropped from the
query. I assume this happens on Django 1.6.x, 1.7.x and 1.8.x, but I have
only tested 1.6.11 and 1.8.
The expected outcome would have been:
{{{
SELECT "dbtest_serviceaction"."service_id",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action",
MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed")
as "period" FROM "dbtest_serviceaction" GROUP BY
"dbtest_serviceaction"."service_id"
}}}
Using Django 1.8 this exact query can be performed with the ORM itself
through the use of the newly added operators for aggregations:
{{{
#!python
>>> qs =
ServiceAction.objects.values('service_id').annotate(first_action=Min('performed'),
last_action=Max('performed'), period=Max('performed')-Min('performed'))
>>> print(qs.query)
SELECT "dbtest_serviceaction"."service_id",
(MAX("dbtest_serviceaction"."performed") -
MIN("dbtest_serviceaction"."performed")) AS "period",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
"dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24692#comment:3>
Django <https://code.djangoproject.com/>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.76751de90e461e3fbcecd2827b4bcd88%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
