On Sunday, 1 March, 2020 14:58, mailing lists <[email protected]> wrote:
>Assume I create the following table:
>CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
>INSERT INTO Test (Value) VALUES('Alpha');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Alpha');
>According to the documentation of group_concat the order is undefined,
>indeed:
>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
>Both queries result in Alpha,Beta.
>Changing the queries to
>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC)
>SELECT group_concat(x) FROM Result;
>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC)
>SELECT group_concat(x) FROM Result;
>leads to the results Alpha,Beta, respectively Beta,Alpha.
>Is this a coincidence or is this guaranteed to work?
>Are there any other solutions / possibilities?
group_concat builds a "group" by concatenating the values sent to it in the
order they are sent. If you do not know this order then for all intents and
purposes the order is "undefined" because it is defined as the order in which
the query planner decides to visit the rows forming the group. SQLite3
believes that all aggregate functions are commutative and that the order in
which rows are fed into them is immaterial to the result and there (presently)
is no way to specify that this is not the case.
So in the rare case where the aggregate is not commutative and you depend on
the presentation order, then you must specify it. The only built-in aggregate
that is not commutative is the group_concat function. If you were to write
another non-commutative aggregate function, lets say SHA1(...), that computed
the SHA1 hash of the values fed into it, you would also have to control the
presentation order or the result would be "undefined".
select group_concat(value) from (select distinct value from test order by value
desc); will do that. (rephrasing as a CTE makes no difference)
This works because the query as phrased cannot be flattened since the outer
query contains an aggregate and the inner query contains an order by.
Moving the distinct does not alter the fact that the query cannot be flattened.
select group_concat(distinct value) from (select value from test order by value
desc);
Whether the query planner will always not flatten a query where the outer query
contains an aggregate and the inner query contains an order by is something on
which I cannot comment other than to say that is does not flatten such a query
up to now.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
