On Monday, 2 March, 2020 09:20, Dominique Devienne <ddevie...@gmail.com> wrote:
>On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf <kmedc...@dessus.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
