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
26148	Queries different between Django 1.8 and 1.9	Grant McConnaughey	nobody	"I'm having trouble with a query in Django 1.9.1. There was no issue in Django 1.8.8. Here's the error I'm getting:

{{{
Request Method: GET
Request URL:    http://localhost:8000/explorer/
Django Version: 1.9.1
Exception Type: ProgrammingError
Exception Value:    
column ""explorer_query.title"" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT ""explorer_query"".""id"", ""explorer_query"".""title"", ""exp...
}}}

The error is with this queryset: 

{{{
Query.objects.prefetch_related('created_by_user').all().annotate(run_count=Count('querylog'))
}}}

And the relevant models:

{{{#!python
class Query(models.Model):
    title = models.CharField(max_length=255)
    sql = models.TextField()
    description = models.TextField(null=True, blank=True)
    created_by_user = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    last_run_date = models.DateTimeField(auto_now=True)


class QueryLog(models.Model):

    sql = models.TextField()
    query = models.ForeignKey(Query, null=True, blank=True, on_delete=models.SET_NULL)
    is_playground = models.BooleanField(default=False)
    run_by_user = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, blank=True)
    run_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['-run_at']
}}}

I fired up a Django shell on Django 1.9.1 and this is the query being generated (I formatted the SQL for readability):

{{{#!sql
SELECT ""explorer_query"".""id"", 
       ""explorer_query"".""title"", 
       ""explorer_query"".""sql"", 
       ""explorer_query"".""description"", 
       ""explorer_query"".""created_by_user_id"", 
       ""explorer_query"".""created_at"", 
       ""explorer_query"".""last_run_date"", 
       ""explorer_query"".""snapshot"", 
       Count(""explorer_querylog"".""id"") AS ""run_count"" 
FROM   ""explorer_query"" 
       LEFT OUTER JOIN ""explorer_querylog"" 
                    ON ( ""explorer_query"".""id"" = 
                       ""explorer_querylog"".""query_id"" ) 
GROUP  BY ""explorer_query"".""id"" 
ORDER  BY ""explorer_query"".""title"" ASC
}}}

Sure enough, only explorer_query.id is in the GROUP BY clause. Here's the result of me running the same code on Django 1.8.8 (formatted for readability):

{{{#!sql
SELECT ""explorer_query"".""id"", 
       ""explorer_query"".""title"", 
       ""explorer_query"".""sql"", 
       ""explorer_query"".""description"", 
       ""explorer_query"".""created_by_user_id"", 
       ""explorer_query"".""created_at"", 
       ""explorer_query"".""last_run_date"", 
       ""explorer_query"".""snapshot"", 
       Count(""explorer_querylog"".""id"") AS ""run_count"" 
FROM   ""explorer_query"" 
       LEFT OUTER JOIN ""explorer_querylog"" 
                    ON ( ""explorer_query"".""id"" = ""explorer_querylog"".""query_id"" ) 
GROUP  BY ""explorer_query"".""id"", 
          ""explorer_query"".""title"", 
          ""explorer_query"".""sql"", 
          ""explorer_query"".""description"", 
          ""explorer_query"".""created_by_user_id"", 
          ""explorer_query"".""created_at"", 
          ""explorer_query"".""last_run_date"", 
          ""explorer_query"".""snapshot"" 
ORDER  BY ""explorer_query"".""title"" ASC
}}}

All of the fields are in the GROUP BY clause now. Is this an issue with how Django is building the SQL? Did something change between 1.8 and 1.9 that would cause this?"	Bug	closed	Database layer (models, ORM)	1.9	Normal	invalid			Unreviewed	0	0	0	0	0	0