Regarding: "The ordering is entirely deterministic" When the documentation says the order is arbitrary, it doesn't mean that it's non-deterministic in some "can never be determined by humans nor Schrödingerish cats" -- it is a well-understood and very useful shorthand meaning "the order, though algorithmically determined, may change with the data and the query, and may further change, without notice, between releases of sqlite, and therefore, for the sake of one's sanity, today's empirical ordering must not be relied upon." If it did not mean that, we'd have to invent a well-understood word to mean that. I like "arbitrary."
On Tue, Aug 15, 2017 at 3:45 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > On Tuesday, 15 August, 2017 13:27, Jens Alfke <j...@mooseyard.com> said: > >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <kmedc...@dessus.com> > wrote: > > >> Well, the documentation is incorrect. The ordering is entirely > >>deterministic. The items presented to the aggregate are concatenated > >>in the order in which they are presented to the aggregate function, > >>and this ordering is determined solely by the traversal order of the > >>underlying table from which the data is drawn. > > >…which is arbitrary, from the POV of a client of SQLite. The > >documentation warns against making assumptions about unordered table > >traversal, and even offers "pragma reverse_unordered_selects” to > >deliberately perturb it, to flush out code that might be relying on > >consistent ordering. > > "To an outside observer any sufficiently advanced technology may appear to > be magic" > > I did not specify "hopefulness or luck". I was specific. The order of > the concatenated output is based on the order in which the items to be > concatenated are fed into the concatenation function. That is to say, to > use precise newfy speak, they are concatenated side after each. If you > wish the side after each output to be ordered, then you need to control the > order is which the data is presented. > > In order to be truly "arbitrary" the group_concat aggregate would need to > use a (true) randomness source to determine the insertion point of each > item in the growing list. > > Barring the use of a source of randomness, whether the concatenation was > performed side-by-each first-is-first to last-is-last; or, side-by-each > last-is-first to first-is-last; or even appended by alternate ends even to > front, odd to end (or vice versa) the concatenation order is entirely > deterministic and is based on the ordering of the data fed into the > aggregate and can be fully and completely controlled controlling the input > order. > > --- > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
