#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: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by ontowhee):
* needs_better_patch: 1 => 0
Comment:
I've pushed changes to the PR that reflect this table, and updated the
table to reflect what is supported. Does this look good?
{{{
+-----------------------------+--------+---------------+------------+------------+
| | SQLite | MySQL | Oracle |
PostgreSQL |
+-----------------------------+--------+---------------+------------+------------+
| ANY_VALUE() | Yes | Yes | Yes | Yes
|
| ANY_VALUE() FILTER() | Yes | No [*] | No | Yes
|
| ANY_VALUE() OVER() | No | No | No | No
|
| ANY_VALUE() FILTER() OVER() | No | No | No | No
|
+-----------------------------+--------+---------------+------------+------------+
[*] MySQL does not seem to support this, but Django will use CASE if the
filter clause is present.
}}}
>> The filter clause is leveraging the existing filter implementation for
expressions. For MySQL, this means it creates CASE() instead of FILTER().
>
> That's expected just like with any other aggregates. Is this causing
issues with `NULL` values generated by members that should be filtered
out?
I think I initially wrote the comment to clarify to myself how Django is
handling FILTER, even though I was observing that running ANY_VALUE()
FILTER() directly in the db would raise a syntax error.
With CASE, it does return NULL values. I don't know if that is an issue,
because ANY_VALUE can return NULL for MySQL. For example:
{{{
CREATE TABLE person(id int, first_name varchar(255), dob date);
INSERT INTO person(id, first_name, dob)
VALUES(1, null, '2025-01-01'),
(2, 'alice', '2024-01-01'),
(3, 'alice', '2025-02-02'),
(4, 'bob', '2025-04-04'),
(5, 'charlie', '2025-05-05');
SELECT ANY_VALUE(first_name), EXTRACT(YEAR FROM dob) FROM person GROUP BY
EXTRACT(YEAR FROM dob);
}}}
For me, this returns `(NULL, 2025), ('alice', 2024)`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34262#comment:19>
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/01070195aa2381a6-0a05f38a-ae85-46ca-9f2c-f223fc9503bf-000000%40eu-central-1.amazonses.com.
