[Django] #34978: Annotating through an aggregate with MySQL/MariaDB raises 1056 "Can't group on" error

[Django]

#34978: Annotating through an aggregate with MySQL/MariaDB raises 1056 "Can't group
on" error
-------------------------------------+-------------------------------------
Reporter: Matthew | Owner: nobody
Somerville |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have some code I am trying to update from Django 3.2 to 4.2, using
MariaDB, that has worked in previous Django versions, and works fine in
4.1, but fails in 4.2. You can see an example GitHub Action output at
https://github.com/dracos/Theatricalia/actions/runs/6922955832 showing 3
and 4.1 passing, but 4.2 failing.

One problem query is the following code:
{{{
seen =
user.visit_set.annotate(min_press_date=Min('production__place__press_date')).annotate(best_date=RawSQL('MIN(IFNULL(productions_place.press_date,
IF(productions_place.end_date!="", productions_place.end_date,
productions_place.start_date)))', ())).order_by('-best_date')
}}}

In Django 4.1, this produces the following SQL, which works fine:
{{{
SELECT `productions_visit`.`id`, `productions_visit`.`production_id`,
`productions_visit`.`user_id`, `productions_visit`.`recommend`,
`productions_visit`.`date`, MIN(`productions_place`.`press_date`) AS
`min_press_date`, (MIN(IFNULL(productions_place.press_date,
IF(productions_place.end_date!="", productions_place.end_date,
productions_place.start_date)))) AS `best_date` FROM `productions_visit`
INNER JOIN `productions_production` ON
(`productions_visit`.`production_id` = `productions_production`.`id`) LEFT
OUTER JOIN `productions_place` ON (`productions_production`.`id` =
`productions_place`.`production_id`) WHERE `productions_visit`.`user_id` =
1 GROUP BY `productions_visit`.`id` ORDER BY `best_date` DESC
}}}

Whilst the SQL produced by Django 4.2 is:
{{{
SELECT `productions_visit`.`id`, `productions_visit`.`production_id`,
`productions_visit`.`user_id`, `productions_visit`.`recommend`,
`productions_visit`.`date`, MIN(`productions_place`.`press_date`) AS
`min_press_date`, (MIN(IFNULL(productions_place.press_date,
IF(productions_place.end_date!="", productions_place.end_date,
productions_place.start_date)))) AS `best_date` FROM `productions_visit`
INNER JOIN `productions_production` ON
(`productions_visit`.`production_id` = `productions_production`.`id`) LEFT
OUTER JOIN `productions_place` ON (`productions_production`.`id` =
`productions_place`.`production_id`) WHERE `productions_visit`.`user_id` =
1 GROUP BY `productions_visit`.`id`, 7 ORDER BY 7 DESC LIMIT 21
}}}

It has added a group by on column 7 (which is best_date) and this then
gives a "1056 Can't group by best_date" error from MySQL/MariaDB.

I have bisected Django between 4.1 and 4.2, and the problem was introduced
by the fix for #31331 in 041551d716b69ee7c81199eee86a2d10a72e15ab. Somehow
that fix means my annotation is now being included in the group by when it
shouldn't be, as it's an aggregate per visit ID, as far as I understand.
Let me know if you need any other details.

--
Ticket URL: <https://code.djangoproject.com/ticket/34978>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.