#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):
See #26602 ''Provide a way to manage grouping with RawSQL'' which was
closed because of a justified use case.
I would say that the same can be said about this ticket as the reported
problem can be fixed in multiple ways with the provided ORM interfaces.
The most obvious and non-invasive one is to use `Min(RawSQL(...))` instead
{{{#!python
annotate(
best_date=Min(
RawSQL(
'IFNULL(productions_place.press_date,
IF(productions_place.end_date!="", productions_place.end_date,
productions_place.start_date))',
(),
),
)
)
}}}
a second one is to use expressions entirely
{{{#!python
annotate(best_date=Min(Coalesce("press_date", Case(When(end_date="",
then=F("start_date")), default=F("end_date"))))
}}}
a third one, assuming the user wants to stick to `IF` and `IFNULL`
{{{#!python
from django.db.models import Func
class If(Func):
function = "IF"
class IfNull(Func):
function = "IFNULL"
annotate(best_date=Min(
IfNull("press_date", If(end_date="", "start_date", "end_date"))
))
}}}
-----
> This one's interesting because we can't dig into RawSQL to determine
whether it's valid in a GROUP BY or not.
That's right David, it's the crux of the issue.
The reason why 041551d716b69ee7c81199eee86a2d10a72e15ab broke the reported
use that is that prior to this change the ORM supported a non-standard
feature of MySQL [https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
disabled in recent versions] that allowed grouping solely by the primary
key of the first entry in `FROM`. It's important to note that using
`RawSQL` to aggregate was only working on MySQL due to this feature and
never worked on any of the other backends that follow the functional
dependency detection in `GROUP BY` clauses as specified by the `SQL:1999`
standard.
The nail in the coffin of this feature was went it was discovered that it
had a peculiarity when dealing with subqueries #31331 that would have
required a significant amount of work to get working.
I could see us go three ways about dealing with this issue
1. Revert the changes made in 041551d716b69ee7c81199eee86a2d10a72e15ab
while making `allows_group_by_pk` based on the absence of
`ONLY_FULL_GROUP_BY`. Note that this won't resolve the aggregation over
the annotation of a dependant subquery but will restore the usage of
`RawSQL` for aggregation on MySQL only when `ONLY_FULL_GROUP_BY` is
disabled.
2. 1 + adjustments to the `allows_group_by_pk` to special case dependant
subquery annotations
3. Adjust the 4.2 existing release notes about this change to let them
know that this version of Django removed support for doing `RawSQL`
aggregations on MySQL and that they should use proper expressions instead
going forward.
Due to lack of demonstration that some aggregates or window function
cannot be expressed using the advanced ORM primitives, that
non-`ONLY_FULL_GROUP_BY` model is a non standard MySQL feature that is not
enabled by default since 8.0, and that this change happen to standardize
the usage of `RawSQL` for aggregations on all backends I'd be inclined to
go with 3.
--
Ticket URL: <https://code.djangoproject.com/ticket/34978#comment:4>
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018bed53dd79-516330ee-b1cd-4e47-921f-109770bcb1c8-000000%40eu-central-1.amazonses.com.
