#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 ontowhee):
[https://github.com/django/django/pull/19196 Draft PR]
I opened a draft PR. This table shows what is implemented. It strays a bit
from the [https://modern-sql.com/caniuse/any_value chart]:
{{{
+-----------------------------+---------------+------------+------------+
| | MySQL | Oracle | PostgreSQL |
+-----------------------------+---------------+------------+------------+
| ANY_VALUE() | Yes | Yes | Yes |
| ANY_VALUE() FILTER() | No, uses CASE | No | Yes |
| ANY_VALUE() OVER() | No | Yes | Yes |
| ANY_VALUE() FILTER() OVER() | No, uses CASE | No | Yes |
+-----------------------------+---------------+------------+------------+
}}}
- The over clause has not been implemented for AnyValue(). The tests are
using the Window function and partition_by parameter to achieve this.
- **Question:** Is this a good approach? Or should AnyValue()
implement over clause?
- The filter clause is leveraging the existing filter implementation for
expressions. For MySQL, this means it creates CASE() instead of FILTER().
MySQL does not support [https://modern-sql.com/feature/filter filter
clause].
- The chart in the article shows a lightning bolt MySQL. I may be
misunderstanding what that means, but I have not been able to directly run
a “ANY_VALUE() FILTER() OVER()” expression. I tried to keep the scope of
this ticket simple by not addressing this one for now.
- For the specific case described in this ticket, instead of creating the
django expression `AnyValue(Least("min_pages", "greatest_page"))` , the
tests are creating `Least("min_pages", AnyValue("greatest_page"))`. It
wraps AnyValue() on "greatest_page" instead of the entire expression. This
avoids the error that is raised `Cannot compute ... is an aggregate` error
in `resolve_expression`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34262#comment:17>
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/010701951fc8d87c-9dffce17-4aa7-4296-a3c8-aacf041fa5b9-000000%40eu-central-1.amazonses.com.
