Queries different between Django 1.8 and 1.9

[Grant McConnaughey]

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:

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):

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):

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?

[Chris Clark]

Looks like a result of the fix for: https://code.djangoproject.com/ticket/19259

What version of what DB are you using? Perhaps there is an issue with the DB feature detection thinking that this backend supports 'allows_group_by_selected_pks', when it in fact does not.

[Simon Charette]

As chrisclark pointed out I suppose you are using PostgreSQL < 9.1 which ​Django 1.9 dropped support for as upstream support for PostgreSQL 9.0 ended in September 2015.

[Grant McConnaughey]

Yep, looks like a Postgres issue. Works on Postgres 9.3 but not 9.0.

[Simon Charette]

Closing as we dropped support for Postgres 9.0 in Django 1.9.