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
31228	DISTINCT with  GROUP_CONCAT() and multiple expressions raises NotSupportedError on SQLite.	Andy Terra	Hongtao Ma	"Contrary to what is suggested in [https://github.com/django/django/blob/6f9ecc23f676e7a6f25d1a6cf830fe638a1eb589/django/db/backends/sqlite3/operations.py#L60 lines 60-64] of `django.db.backends.sqlite3.operations.py`, SQLite does support DISTINCT on aggregate functions.

One such example is GROUP_CONCAT, which is quite similar to PostgreSQL's STRING_AGG.

I can't find any canonical links which provide a useful explanation of GROUP_CONCAT, but this should be good enough: https://www.w3resource.com/sqlite/aggregate-functions-and-grouping-group_concat.php

I came across this issue when trying to create my own `GroupConcat` function subclassing `Aggregate` (similar to the [https://github.com/django/django/blob/6f9ecc23f676e7a6f25d1a6cf830fe638a1eb589/django/contrib/postgres/aggregates/general.py#L53 StringAgg implementation] from postgres) and noticed that skipping the check in `django.db.backends.sqlite3.operations.py` would allow my queries to run as advertised.

My code for `GroupConcat` is:

{{{
from django.db.models import Value
from django.db.models.aggregates import Aggregate

class GroupConcat(Aggregate):
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(distinct)s %(expressions)s)'
    allow_distinct = True

    def __init__(self, expression, delimiter=None, **extra):
        if delimiter is not None:
            self.allow_distinct = False
            delimiter_expr = Value(str(delimiter))
            super().__init__(expression, delimiter_expr, **extra)        
        else:
            super().__init__(expression, **extra)        

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            function=self.function,
            template=self.template,
            **extra_context
        )
}}}


For the record, as the code above suggests, a separate issue is that GROUP_CONCAT only allows DISTINCT when a delimiter isn't specified.

After some discussion on #django, an argument was raised in favor of changing the message to say ""Django doesn't support..."", but I would imagine that skipping the check entirely would simply result in an `OperationalError` for malformed queries while still allowing users to extend the ORM as needed."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	sqlite	Simon Charette	Ready for checkin	1	0	0	0	0	0