id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24692	QuerySet.extra(select=...) is silently dropped with aggregations	jdelic	nobody	"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=1)
>>> ServiceAction.objects.create(service_id=1)
>>> ServiceAction.objects.create(service_id=1)
>>> ServiceAction.objects.create(service_id=2)
>>> ServiceAction.objects.create(service_id=2)
>>> ServiceAction.objects.create(service_id=2)
}}}

...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""
}}}
"	Uncategorized	new	Uncategorized	1.8	Normal				Unreviewed	0	0	0	0	0	0