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 generation	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 entries.  If the extra() components rely on those joins, the 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:
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:
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:
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 
inner_query.select_related = False
To 
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	new	Database layer (models, ORM)	1.8beta1	Normal		extra distinct aggregate count annotate		Unreviewed	0	0	0	0	1	0