On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf <kmedc...@dessus.com> wrote: > 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)
OK. I was more trying to find out whether such nested "ordered" projections were a standard-SQL thing or not. > 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" That's assuming the sort is "stable" :) Stable-sort is typically slower than non-stable-sort, that's why the STL has std::sort and std::stable_sort. > 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. I would actually prefer these nested order-by to be ignored, and the "set of rows" being assumed, forcing the outer query to do its own ordering. The very notion of "ordered projection" for nested query sounds more like an implementation detail, to word-around the lack of window functions, than something "official" from the SQL standard or relational theory. I'm not disputing how SQLite implements things, for historical or practical reasons, I just want to understand whether such "ordered projection" is an official concept from SQL or just an SQLite thing. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
