id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34992	MariaDB does not support functional group by dependencies	Matthew Somerville	Nathaniel Conroy	"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."""	Bug	closed	Database layer (models, ORM)	4.2	Release blocker	fixed			Ready for checkin	1	0	0	0	0	0