GROUP_CONCAT returns NULL
(1) By Bill W (bill-w-pcor) on 2024-05-23 21:07:30 [source]
I've run into a possible defect where GROUP_CONCAT is returning NULL values when it should be returning an empty string.
Here is a minimal example:
CREATE TABLE foo (
id INTEGER,
key TEXT,
val TEXT
);
INSERT INTO foo
(id, key, val)
VALUES
(1, 'status', ''),
(1, 'color', 'black'),
(2, 'status', 'installed'),
(2, 'color', 'blue');
SELECT DISTINCT id,
GROUP_CONCAT(val) FILTER (WHERE key = 'status')
OVER (PARTITION BY id) AS status,
GROUP_CONCAT(val) FILTER (WHERE key = 'color')
OVER (PARTITION BY id) AS color
FROM foo;
What I actually get back from the SELECT query:
id status color
-- --------- -----
1 <NULL> black
2 installed blue
What I expected to get back from the SELECT query:
id status color
-- --------- -----
1 black
2 installed blue
(2) By Richard Hipp (drh) on 2024-05-23 22:31:17 in reply to 1 [link] [source]
Simplified test case:
SELECT group_concat(x) OVER () FROM (SELECT '' AS x);
The problem only arises when group_concat() is used as a window function.
(3) By Richard Hipp (drh) on 2024-05-23 23:38:48 in reply to 2 [link] [source]
This problem goes back more than five years to 2019-03-26. It first appeared in release 3.28.0. The problem has nothing to do with the 3.46.0 release that was published earlier today. Apparently it was just a coincidence that the problem was reported just a few hours after the 3.46.0 release was cut.
The problem has now been fixed on trunk and on branch-3.46. The two-line patch will appear in the next release.
(4) By Bill W (bill-w-pcor) on 2024-05-24 00:18:31 in reply to 3 [link] [source]
Awesome, thanks so much for such a quick response!
(5.1) By Aask (AAsk1902) on 2024-05-24 08:48:11 edited from 5.0 in reply to 2 [link] [source]
I'm a little puzzled by this- I get the "What I expected to get back from the SELECT query" answer using SQLite version 3.45.3 on Windows 11 with locale en-GB.
SQLite version 3.45.3 2024-04-15 13:34:05 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> CREATE TABLE foo (
(x1...> id INTEGER,
(x1...> key TEXT,
(x1...> val TEXT
(x1...> );
sqlite>
sqlite> INSERT INTO foo
...> (id, key, val)
...> VALUES
...> (1, 'status', ''),
...> (1, 'color', 'black'),
...> (2, 'status', 'installed'),
...> (2, 'color', 'blue');
sqlite>
sqlite> SELECT DISTINCT id,
...> GROUP_CONCAT(val) FILTER (WHERE key = 'status')
...> OVER (PARTITION BY id) AS status,
...> GROUP_CONCAT(val) FILTER (WHERE key = 'color')
...> OVER (PARTITION BY id) AS color
...> FROM foo;
id status color
-- --------- -----
1 black
2 installed blue
sqlite>
(6) By Phil G (phil_g) on 2024-05-24 09:39:06 in reply to 5.1 [link] [source]
What do you get from SELECT NULL; ? It could be the CLI setting of .nullvalue that's disguising the results (check it with .show).
Might be irrelevant, but there's no harm in checking the simple answer first.
(7) By Aask (AAsk1902) on 2024-05-24 12:22:54 in reply to 6 [link] [source]
CLI setting of .nullvalue that's disguising the results
Spot on!