#34978: Annotating through an aggregate with RawSQL() raises 1056 "Can't group
on"
on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
> I get a lot of 1055 errors, even with a query that's only e.g.
`Play.objects.annotate(Count('authors'))`, without any `RawSQL`, I get
`(1055, "'theatricalia.plays_play.title' isn't in GROUP BY")`, but assume
that's my issue somehow.
That's interesting. If it's happening for models of the form
{{{#!python
class Author(models.Model):
pass
class Play(models.Model):
title = models.CharField()
authors = models.ManyToManyField(Author)
}}}
I would expect `Play.objects.annotate(Count('authors'))` to generate
{{{#!sql
SELECT play.id, play.name, COUNT(author.id)
FROM play
LEFT JOIN play_authors ON (play_authors.play_id = play.id)
LEFT JOIN author ON (play_authors.author_id = author.id)
GROUP BY play.id
}}}
And by [https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
MySQL docs]
> MySQL implements detection of functional dependence. If the
`ONLY_FULL_GROUP_BY` SQL mode is enabled (which it is by default), MySQL
rejects queries for which the select list, HAVING condition, or ORDER BY
list refer to nonaggregated columns that are neither named in the `GROUP
BY` clause '''nor are functionally dependent on them'''.
So in this case `play.name` ''is'' functionally dependant on `play.id` (as
it's the primary key of `play`) so if you're using a version of MySQL
supported on Django 4.2 we'd definitely like to learn more about it as
it's unexpected.
--
Ticket URL: <https://code.djangoproject.com/ticket/34978#comment:7>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018bee70043c-95596d93-f827-45c3-a0a5-9cb75287f760-000000%40eu-central-1.amazonses.com.
