[Django] #26148: Queries different between Django 1.8 and 1.9
Django
----------------------------------------------+--------------------
Reporter: grantmcconnaughey | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26148>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: grantmcconnaughey | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by chrisclark):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26148#comment:1>
Django
Reporter: grantmcconnaughey | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
As chrisclark pointed out I suppose you are using PostgreSQL < 9.1 which
[https://docs.djangoproject.com/en/1.9/releases/1.9/#dropped-support-for-
postgresql-9-0 Django 1.9 dropped support for as upstream support for
PostgreSQL 9.0 ended in September 2015].
--
Ticket URL: <https://code.djangoproject.com/ticket/26148#comment:2>
Django
Reporter: grantmcconnaughey | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by grantmcconnaughey):
Yep, looks like a Postgres issue. Works on Postgres 9.3 but not 9.0.
--
Ticket URL: <https://code.djangoproject.com/ticket/26148#comment:3>
Django
Reporter: grantmcconnaughey | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* status: new => closed
* resolution: => invalid
Comment:
Closing as we dropped support for Postgres 9.0 in Django 1.9.
--
Ticket URL: <https://code.djangoproject.com/ticket/26148#comment:4>