#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):
Thank you so much for the detailed clarifications! It helps me understand
the context. The chain of thoughts for the user that you outlined makes a
lot of sense, and it is what I would prefer in my own workflow, too.
----
I have WIP in this
[https://github.com/django/django/compare/main...ontowhee:django:ticket-34262
branch]. I’ve added AnyValue, along with some tests.
One thing I'm not sure about is, in MySQL, the expression
`ANY_VALUE(LEAST(MIN(pages), GREATEST(pages, 600)))` is valid, but
creating the expression in django with `AnyValue(Least("min_pages",
"greatest_page"))` leads to the following error during the resolving
phase,
{{{
Cannot compute AnyValue(’Least(F(min_pages), F(greatest_pages))’):
‘Least(F(min_pages), F(greatest_pages))’ is an aggregate
}}}
For Postgresql and Oracle, it makes sense to raise this error, because
ANY_VALUE is considered an aggregate function, and nested aggregates are
not allowed. However, for MySQL, would it make sense to not raise the
error? Would that mean changing how
[https://github.com/django/django/blob/main/django/db/models/aggregates.py#L81
resolve_expression] is checking the `contains_aggregate` attribute? The
resolving phase is backend agnostic. Trying to catch it in the compilation
phase in `as_mysql()` would be too late, because the error would have
already been raise. I don't have a clear path forward at this point, but
will dig around and think about this more. Any ideas and suggestions are
welcomed, of course!
----
I'm also a bit stuck on understanding how to write the SQL statement for
`ANY_VALUE(…) FILTER(…) OVER(…)`. I was running statements directly in
MySQL to better understand the different combinations of the FILTER, OVER,
GROUP BY clauses, trying add support according to the chart in the [https
://modern-sql.com/caniuse/any_value article], but I'm getting syntax
errors.
Here is a simple example I was running,
{{{
CREATE TABLE person(id int, first_name varchar(255), dob date);
INSERT INTO person(id, first_name, dob)
VALUES(1, 'alice', '2025-01-01'),
(2, 'alice', '2024-02-02'),
(3, 'alice', '2025-03-03'),
(4, 'bob', '2025-04-04'),
(5, 'charlie', '2025-05-05');
}}}
These statement worked in MySQL.
{{{
SELECT ANY_VALUE(id) FROM person;
SELECT ANY_VALUE(id) FROM person GROUP BY first_name;
}}}
These statements did not work. They returned a syntax error. I expected
the last one, `ANY_VALUE(..) FILTER(...) OVER(...)`, to be valid,
according to the chart, but was surprised to see the syntax error. All the
statements work in Postgresql.
{{{
SELECT ANY_VALUE(id) OVER() FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) OVER() FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) OVER(partition by first_name)
FROM person;
}}}
MySQL does not support [https://modern-sql.com/feature/filter FILTER
clause], and django will emulate using
[https://github.com/django/django/blob/main/django/db/models/aggregates.py#L142
CASE] instead. I wonder if the chart in the article is accurate, or if the
FILTER clause in ANY_VALUE is a special case.
If anyone can see what I'm doing wrong here, and give an example of a
valid statement with `ANY_VALUE(..) FILTER(...) OVER(...)`, that would be
helpful!
--
Ticket URL: <https://code.djangoproject.com/ticket/34262#comment:16>
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/01070194fd101f24-379fa6b5-07cc-4d1e-ba2d-c88034d26d9f-000000%40eu-central-1.amazonses.com.
