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
24431	Combining extra(), annotate() or distinct() and count() can generate invalid SQL	briankrznarich	nobody	"When applying a `count()` to a queryset that contains `extra()` values and either an `annotation` or a `distinct()` call, `get_aggregation()` discards any existing select_related tables.  If the `extra()` components rely on those tables, the generated SQL will be invalid.

The failures are new to 1.8, and do not appear to be present in 1.7. (I discovered the problem when upgrading fom 1.7 to beta for testing). 

Example:
{{{#!python
Permission.objects.all().select_related('content_type').extra(select={'foo':""django_content_type.app_label>'q'""}).distinct().count()
}}}
{{{
ProgrammingError: missing FROM-clause entry for table ""django_content_type""
LINE 1: SELECT COUNT('*') FROM (SELECT DISTINCT (django_content_type.
}}}
Remove the `distinct()`, or the `count()`, and it's fine (this is a contrived example, I know it makes no practical sense).

Another Example with some toy models:
{{{#!python
class Address(models.Model):
    city = models.CharField(max_length=40)
class Author(models.Model):
    name = models.CharField(max_length=40)
    address = models.ForeignKey(Address)
class BlogPost(models.Model):
    data = models.CharField(max_length=40)
    author = models.ForeignKey(Author)
}}}
For each author, select a count of their total blog entries, as well as a custom boolean value based on their address:
{{{#!python
Author.objects.all().annotate(blogpost_count=Count('blogpost')).select_related('address').extra(select={'in_maryland':""address.state='MD'""}).count()
}}}
This is fine until you append the `count()`

We hit this issue where TastyPie applies `count()` to then end of some complicated querysets, far far away from where we constructed them.   In our case, we're using `extra()` values for an order_by that requires several comparisons.   

A potential fix is in `django/db/models/sql/query.py` line `396` in `get_aggregation()`. 
Change 
{{{#!python
inner_query.select_related = False
}}}
To 
{{{#!python
if not self._extra: #Skip optimization, extra() fields might need these joins
    inner_query.select_related = False
}}}
The cause appears to be a performance optimization (don't do joins if the results don't appear the affect the aggregate). 

This has similar keywords and errors to #11329, but I think the underlying cause is different and possibly easier to address."	Bug	closed	Database layer (models, ORM)	1.8beta1	Release blocker	invalid	extra distinct aggregate count annotate		Accepted	0	0	0	0	1	0