quote from https://sqlite.org/lang_aggfunc.html
The group_concat() function returns a string which is the concatenation
of all non-NULL values of X. If parameter Y is present then it is used as
the separator between instances of X. A comma (",") is used as the
separator if Y is omitted. The order of the concatenated elements is
arbitrary.
------
"Arbitrary" is crazy considering the actual current implementation below.
Looking at the linear loop and the call to sqlite3StrAccumAppend(), how is
it arbitrary? "Arbitrary" is a documentation bug. The row order of the
enclosing query may be arbitrary but group_concat is not otherwise changing
that row order in any way. The documentation should either say nothing or
say that the concatenation order depends on the row order of the enclosing
query. Saying group_concat is arbitrary is simply wrong.
static void groupConcatStep(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
const char *zVal;
StrAccum *pAccum;
const char *zSep;
int nVal, nSep;
assert( argc==1 || argc==2 );
if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
if( pAccum ){
sqlite3 *db = sqlite3_context_db_handle(context);
int firstTerm = pAccum->mxAlloc==0;
pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
if( !firstTerm ){
if( argc==2 ){
zSep = (char*)sqlite3_value_text(argv[1]);
nSep = sqlite3_value_bytes(argv[1]);
}else{
zSep = ",";
nSep = 1;
}
if( zSep ) sqlite3StrAccumAppend(pAccum, zSep, nSep);
}
zVal = (char*)sqlite3_value_text(argv[0]);
nVal = sqlite3_value_bytes(argv[0]);
if( zVal ) sqlite3StrAccumAppend(pAccum, zVal, nVal);
}
}
On Tue, Aug 15, 2017 at 1:50 PM, Donald Griggs <dfgri...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
