[sqlite] PRAGMA reverse_unordered_selects and GROUP_CONCAT

[Mark Lawrence]

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