#34262: Queryset grouped by annotation with aggregates on another annotated
expression crashes on MySQL with sql_mode=only_full_group_by.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
only_full_group_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
> Should a new ticket be created for adding support for ANY_VALUE()?
I think this that this ticket can be re-purposed for the introduction of
`AnyValue(Aggregate)` given that the reported problem here is that MySQL
query planer isn't smart enough to transitively infer the presence of
members in the `GROUP BY` clause.
> For MySQL, since ANY_VALUE() is not an aggregate function, would it be
implemented as an Aggregate or Func or a different expression type?
It's effectively not an aggregate function per-se on MySQL but more of a
sentinel to tell the query planner to ignore the `only_full_group_by`
check only for a single expression but in the Django sense it must be
defined as an `Aggregate` subclass otherwise it will be included in the
`GROUP BY` clause. In other words, it should be implemented as an
`Aggregate` subclass even if MySQL doesn't follow the SQL spec by clearly
defining what kind of function `ANY_VALUE` is.
> Just thinking out loud here. If option 2 is the path forward,
I think that raising awareness is the way to go here but I don't think
that the proposed implementation of capturing errors and emitting warning
is the way to go here. First because it's brittle given we don't know the
extent of this problem and the MySQL implementation could change and
secondly because it's not an approach we've taken with this class of
problems.
IMO this problem is very similar to how we let inappropriate casting
errors bubble through and expect users to use `CAST` where necessary from
the error messages over trying to detect such errors and point them
directly at `django.db.models.functions.Cast`. In other words I think that
it's better to have the user go through the following chain of thoughts
- Encounter type / `only_full_group_by` error
- Do their research on the subject and learn about `CAST` / `ANY_VALUE`
- Search for Django `CAST` / `ANY_VALUE` and endup on the `Cast` /
`AnyValue` docs
than committing to building a bullet proof solution that hides away these
details from users. For all we know MySQL could finally implement
functional dependency detection properly and all of our efforts (and bugs
trying to get it right but failing) would be wasted efforts.
For these reasons I believe that ''raising awareness'' is effectively the
way to go but I think the way to do so is
1. Introduce an `AnyValue(Aggregate)`
2. Make sure the documentation mentions that its usage might be necessary
on MySQL when mixing aggregate and non-aggregate functions when
`sql_mode=only_full_group_by` in a `.. note`
3. Link to `AnyValue` documentation from the
[https://docs.djangoproject.com/en/5.1/topics/db/aggregation/#aggregating-
annotations aggregating annotations] section of the docs
--
Ticket URL: <https://code.djangoproject.com/ticket/34262#comment:15>
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 visit
https://groups.google.com/d/msgid/django-updates/01070194eb3ef4ef-c5d90a9b-0f50-4bb9-b0b7-1daf95849d50-000000%40eu-central-1.amazonses.com.
