Opened 3 months ago

Closed 3 months ago

Last modified 3 months ago

#34992 closed Bug (fixed)

MariaDB does not support functional group by dependencies

Reported by: Matthew Somerville Owned by: Nathaniel Conroy
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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:

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:

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."

Change History (9)

comment:1 by David Sanders, 3 months ago

Triage Stage: UnreviewedAccepted

Thanks for creating this Matthew 🏆

comment:2 by Nathaniel Conroy, 3 months ago

Owner: changed from nobody to Nathaniel Conroy
Status: newassigned

in reply to:  description comment:3 by Nathaniel Conroy, 3 months ago

I think I understand the problem here, and I was able to test a 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.

Last edited 3 months ago by Nathaniel Conroy (previous) (diff)

comment:4 by Nathaniel Conroy, 3 months ago

Has patch: set

comment:5 by Mariusz Felisiak, 3 months ago

Severity: NormalRelease blocker

comment:6 by Mariusz Felisiak, 3 months ago

Triage Stage: AcceptedReady for checkin

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

Resolution: fixed
Status: assignedclosed

In 0257426f:

Fixed #34992 -- Fixed DatabaseFeatures.allows_group_by_selected_pks on MariaDB with ONLY_FULL_GROUP_BY sql mode.

Regression in 041551d716b69ee7c81199eee86a2d10a72e15ab.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

In d9ba8fa:

[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

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

In 450d518d:

[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.

Note: See TracTickets for help on using tickets.
Back to Top