Why is GROUP_CONCAT in this SQLite query not working in recent versions?-CodePudding

I wrote a Python bottle app some years ago that used the following query:

SELECT * FROM (
    SELECT a.*,
    GROUP_CONCAT(st.ID) AS Sentences
    FROM (
        SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, GROUP_CONCAT(sc2.ID) AS Children
        FROM Section AS sc
        LEFT JOIN Section AS sc2 ON sc2.Parent_ID = sc.ID
        GROUP BY sc2.Parent_ID
    ) AS a
    LEFT JOIN Sentence AS st ON st.Section_ID = a.ID
    GROUP BY a.ID
    UNION
    SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, NULL AS Children, GROUP_CONCAT(st.ID) AS Sentences
    FROM Section AS sc
    LEFT JOIN Sentence AS st ON st.Section_ID = sc.ID
    GROUP BY sc.ID
)
GROUP BY ID
ORDER BY Parent_ID;

It used to properly return something like:

ID  |Title_PT   |Title_DE   |Parent_ID  |Children   |Sentences
1   |Idioma geral 1 - Expressões comuns em contextos específicos    |Gebräuchliche Ausdrücke in spezifischen Kontexten      |2,7,8,12,20,25,26,30,38,39,42,43,44,45,50,54,58,59,60,71,72,73,76,77,78,89,92,93,123,127,142   |2530,2571

In which each row mentions a section of the book (Section.ID), with the ID for its subsections (column Children) and the ID for all individual Sentences that belong in that section (column Sentences). I've tested this on an old version (3.5, from 2013) of SQLite Expert Professional and it runs fine.

However, when I run the same query in newer versions of sqlite3.exe (3.35.5, from 2021), the field "Children" returns NULL for all rows (so, I don't know, the GROUP_CONCAT is not working anymore):

ID  |Title_PT   |Title_DE   |Parent_ID  |Children   |Sentences
1   |Idioma geral 1 - Expressões comuns em contextos específicos    |Gebräuchliche Ausdrücke in spezifischen Kontexten      |NULL   |2530,2571

I also tried to reproduce it with Python 3.10 and the sqlite3 library and I get the same problem. Does anyone know what might have changed in recent SQLite that broke my code?

Schema from the database (a multilingual book of sentences, organized by sections/topics, with its equivalents in other languages):

CREATE TABLE [Sentence] (
        ID INTEGER PRIMARY KEY,
        Section_ID INTEGER,
        Sentence_PT CHAR(1024) NOT NULL,
        Sentence_DE CHAR(1024),
        Sentence_EN CHAR(1024),
        Sentence_ES CHAR(1024)
);
CREATE TABLE [Section] (
        ID INTEGER PRIMARY KEY,
        Parent_ID INTEGER REFERENCES Section(ID),
        Title_PT CHAR(1024),
        Title_DE CHAR(1024),
        Title_EN CHAR(1024),
        Title_ES CHAR(1024)
);

Edit: Here is a fiddle: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=c31242bb38d85afcd1acfc8f57d1dd99

CodePudding user response：

Actually the correct results that you got with the older version of SQLite that you used were coincidentally correct.
In your code you group by only 1 column and select many columns that are not present in the GROUP BY clause.
This is not allowed in most databases, but SQLite allows it and this leads to unexpected results as SQLite picks an (almost) arbitrary row to select the values for all the unaggregated columns.

As it is your code, yo can use aggregation with the function MAX() for the column Children to get a non-null value (if it exists):

SELECT ID, Title_PT, Title_DE, Parent_ID ,
       MAX(Children) AS Children,
       Sentences
FROM (
    SELECT a.*,
           GROUP_CONCAT(st.ID) AS Sentences
    FROM (
        SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, GROUP_CONCAT(sc2.ID) AS Children
        FROM Section AS sc
        LEFT JOIN Section AS sc2 ON sc2.Parent_ID = sc.ID
        GROUP BY sc2.Parent_ID
    ) AS a
    LEFT JOIN Sentence AS st ON st.Section_ID = a.ID
    GROUP BY a.ID
    UNION
    SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, NULL AS Children, GROUP_CONCAT(st.ID) AS Sentences
    FROM Section AS sc
    LEFT JOIN Sentence AS st ON st.Section_ID = sc.ID
    GROUP BY sc.ID
)
GROUP BY ID
ORDER BY Parent_ID;

See the demo.