Re: How to enforce a specific order of group_concat?

On Monday, 2 March, 2020 09:20, Dominique Devienne <[email protected]> wrote:

>On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf <[email protected]> wrote:

>> select group_concat(value) from (select distinct value from test order by value);

>But is that guaranteed to be ordered correctly "forever" instead of by
>"happenstance" from current implementation details? 

>My point was that the Window Function version is ordered "by design", 
>and not an implementation detail (as I think
>the simpler version is).

>Your subquery returns rows in a given order too, but "who" says
>they'll be processed in that order?

>Tables are just "sets of rows" after all, and the relational model is
>about set-theory, no? order by in subquery therefore make little to no 
>sense in nested SQL (in theory...). --DD

Well, in theory an order by in a nested select means that the result of the operation is an ordered projection and not merely a set of rows.  For this particular case (a nested select with an order by and the outer query with an aggregate) the query will not be flattened (#16)

https://sqlite.org/optoverview.html#flattening

Consider that

create table t (x,y);
insert into t values (1,1),(2,2),(3,3),(4,1),(4,2),(4,3),(4,4);
select x,y from (select x, y from t order by y) order by x;

will do two order-by sorts to obtain the result even though the query could be (in this particular case) re-written as "select x, y from t order by x, y" because a query with an order-by in both the outer and nested query cannot be flattened (#11).

This is why putting an "order by" in a view will usually preclude query flattening because the view is not merely producing a "set of rows" it is producing an "ordered projection" and the ordering must be significant else it would not be there.

Of course in the case of the original statement:

select group_concat(distinct value) from test order by value;

The "order by value" might in fact be used to select the use of an index on value to scan, rather than the table test, if that results in less I/O that scanning the table.  However, the Query Planner doesn't believe aggregate functions (including group_concat) results are non-commutative so determines that the "order by value" clause is superfluous (perhaps this is an error, since other functions such as sum(), total(), avg() can also be non-commutative in certain pathological cases and have varying results depending on the ordering of the data sent to them, especially avg() since it merely returns sum()/count() rather than a successive approximation to the mean, though successive approximation still has pathological cases for non-sorted input, they are fewer than the sum()/count() method).

-- 
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