Hi,
I'm hitting an issue with ordering and GROUP_CONCAT that seems to
depend on whether the reverse_unordered_selects pragma is enabled,
and/or non-aggregate terms are also selected.
reverse_unordered_select with non-agg result
------------------------ ------------ ------
ON No Bad
OFF No Good
ON Yes Good
OFF Yes Good
The attached file should show some details. SQLite version is 3.7.12.1
on Debian. Is this a known or expected behaviour?
Mark.
--
Mark Lawrence
CREATE TABLE t(
id integer
);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);
PRAGMA reverse_unordered_selects = ON;
SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
;
PRAGMA reverse_unordered_selects = OFF;
SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
;
PRAGMA reverse_unordered_selects = ON;
SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
GROUP BY
x
;
PRAGMA reverse_unordered_selects = OFF;
SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
GROUP BY
x
;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
