Opened 9 years ago

Closed 9 years ago

#26148 closed Bug (invalid)

Queries different between Django 1.8 and 1.9

Reported by: Grant McConnaughey Owned by: nobody
Component: Database layer (models, ORM) Version: 1.9
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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?

Change History (4)

comment:1 by Chris Clark, 9 years ago

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.

comment:2 by Simon Charette, 9 years ago

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.

comment:3 by Grant McConnaughey, 9 years ago

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

comment:4 by Simon Charette, 9 years ago

Resolution: invalid
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top