[Django] #34992: MariaDB does not support functional group by dependencies

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
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 |
-------------------------------------+-------------------------------------
Requested to be opened from comment 11 on #34978. If you're using MariaDB
with ONLY_FULL_GROUP_BY turned on, all queries that end up using a `GROUP
BY` fail. Given a model such as:

{{{#!python
class Author(models.Model):
pass

class Play(models.Model):
title = models.CharField(max_length=100)
authors = models.ManyToManyField(Author)
}}}

Then `list(Play.objects.annotate(Count('authors')))` generates the SQL:

{{{#!sql
SELECT app_play.id, app_play.title, COUNT(app_play_authors.author_id) AS
authors__count
FROM app_play
LEFT OUTER JOIN app_play_authors ON (app_play.id =
app_play_authors.play_id)
GROUP BY app_play.id
ORDER BY NULL
}}}

Which errors with `django.db.utils.OperationalError: (1055,
"'mariadbtest.app_play.title' isn't in GROUP BY")`

Unlike MySQL since 5.7.5 (release notes: "MySQL now recognizes when a
nonaggregated selected column is functionally dependent on (uniquely
determined by) GROUP BY columns."), MariaDB does not support this feature.
But Django 4.2 assumes both MySQL and MariaDB support it.

Simon's comment on #34978 said: "The solution will likely be to turn off
the allows_group_by_selected_pks feature on MariaDB when
ONLY_FULL_GROUP_BY mode is turned on. Note that the
allows_group_by_selected_pks feature is different from the
allows_group_by_pk feature removed in
041551d716b69ee7c81199eee86a2d10a72e15ab."

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

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------

Reporter: Matthew Somerville | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* stage: Unreviewed => Accepted

Comment:

Thanks for creating this Matthew 🏆

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:1>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------

Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nathaniel Conroy):

* owner: nobody => Nathaniel Conroy
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:2>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | 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 Nathaniel Conroy):

I think I understand the problem here, and I was able to test an simple
solution locally.

However, this is my first ticket, so it may be a few days before I have a
patch ready. I need to get more acquainted with the tests and the general
workflow.

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:3>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nathaniel Conroy):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:4>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* severity: Normal => Release blocker

Comment:

[https://github.com/django/django/pull/17529 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:5>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:6>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"0257426fe1fe9d146fd5813f09d909917ff59360" 0257426f]:
{{{
#!CommitTicketReference repository=""
revision="0257426fe1fe9d146fd5813f09d909917ff59360"
Fixed #34992 -- Fixed DatabaseFeatures.allows_group_by_selected_pks on
MariaDB with ONLY_FULL_GROUP_BY sql mode.

Regression in 041551d716b69ee7c81199eee86a2d10a72e15ab.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:7>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"d9ba8fa3972dd78c38cc94c08ad6514e8a2e237d" d9ba8fa]:
{{{
#!CommitTicketReference repository=""
revision="d9ba8fa3972dd78c38cc94c08ad6514e8a2e237d"
[5.0.x] Fixed #34992 -- Fixed

DatabaseFeatures.allows_group_by_selected_pks on MariaDB with
ONLY_FULL_GROUP_BY sql mode.

Regression in 041551d716b69ee7c81199eee86a2d10a72e15ab.

Backport of 0257426fe1fe9d146fd5813f09d909917ff59360 from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:8>

[Django]

#34992: MariaDB does not support functional group by dependencies
-------------------------------------+-------------------------------------
Reporter: Matthew Somerville | Owner: Nathaniel
| Conroy
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"450d518d2fe28b33a12805fcaaad53f8244bd5df" 450d518d]:
{{{
#!CommitTicketReference repository=""
revision="450d518d2fe28b33a12805fcaaad53f8244bd5df"
[4.2.x] Fixed #34992 -- Fixed

DatabaseFeatures.allows_group_by_selected_pks on MariaDB with
ONLY_FULL_GROUP_BY sql mode.

Regression in 041551d716b69ee7c81199eee86a2d10a72e15ab.

Backport of 0257426fe1fe9d146fd5813f09d909917ff59360 from main.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34992#comment:9>