#26148: Queries different between Django 1.8 and 1.9
----------------------------------------------+--------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/060.f467463cf2e311325e22bbe261fb1b69%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
