[Django] #36890: StringAgg doesn't allow DISTINCT in sqlite3 while native GROUP_CONCAT does

[Django]

#36890: StringAgg doesn't allow DISTINCT in sqlite3 while native GROUP_CONCAT does
-------------------------------------+-------------------------------------
Reporter: AJ Slater | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: Aggregate, sqlite3 | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Attempting to use the new generic StringAgg with distinct=True raises
exceptions because any Aggregate with multiple parameters cannot be
distinct with an sqlite3 connection. Running StringAgg without distinct
produces many duplicate entries aggregated together.

However this Aggregate runs fine under sqlite3, allows distinct and does
not produce duplicates.

{{{
class GroupConcat(Aggregate):
"""Sqlite3 GROUP_CONCAT."""

# Defaults to " " separator which is all I need for now.

allow_distinct = True
allow_order_by = True
function = "GROUP_CONCAT"
name = "GroupConcat"

def __init__(self, *args, **kwargs):
"""output_field is set in the constructor."""
super().__init__(*args, output_field=CharField(), **kwargs)
}}}

It occurs to me that this may happen because `delimiter` can be an
expression. If delimiter is submitted as some invariant like Value() it
would be nice to escape this limitation.
--
Ticket URL: <https://code.djangoproject.com/ticket/36890>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36890: StringAgg doesn't allow DISTINCT in sqlite3 while native GROUP_CONCAT does
-------------------------------------+-------------------------------------

Reporter: AJ Slater | Owner: (none)
Type: Uncategorized | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: Aggregate, sqlite3 | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Kundan Yadav):

can i it assigned to me if it get's reviewed
--
Ticket URL: <https://code.djangoproject.com/ticket/36890#comment:1>

[Django]

#36890: StringAgg doesn't allow DISTINCT in sqlite3 while native GROUP_CONCAT does
-------------------------------------+-------------------------------------
Reporter: AJ Slater | Owner: (none)
Type: Uncategorized | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Aggregate, sqlite3 | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

I'm not sure I understand your report here.

The code you provided states that `" "` is the default separator for
`GROUP_CONCAT` but [https://sqlite.org/lang_aggfunc.html#group_concat the
documentation says otherwise].

> The `group_concat()` function returns a string which is the
concatenation of all non-`NULL` values of X. If parameter Y is present
then it is used as the separator between instances of X. **A comma (",")
is used as the separator if Y is omitted**.

which I was able to confirm with

{{{
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE ticket_36890 (value text);
sqlite> INSERT INTO ticket_36890 (value) VALUES ('foo'), ('bar'), ('foo');
sqlite> SELECT GROUP_CONCAT(value), GROUP_CONCAT(DISTINCT value) FROM
ticket_36890;
foo,bar,foo|foo,bar
}}}

which that we could support `StringAgg("field", Value(","),
distinct=True)` on SQLite but not `StringAgg("field", Value(" "),
distinct=True)`
--
Ticket URL: <https://code.djangoproject.com/ticket/36890#comment:2>