Re: [sqlite] group_concat() reverses order given where clause?

[Petite Abeille]

> On Aug 16, 2017, at 11:11 AM, Jean-Luc Hainaut  
> wrote:
> 
> The implementation of SQLite "group_concat" (a very powerful but often 
> overlooked function) provides some but not all the features found in other 
> DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):

For diversity's sake, Oracle’s LISTAGG:

https://docs.oracle.com/cloud/latest/db112/SQLRF/functions089.htm#SQLRF30030


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Jean-Luc Hainaut]

On 15/08/2017 17:12, Bob Friesenhahn wrote:

I am surprised by this behavior of group_concat():


The implementation of SQLite "group_concat" (a very powerful but often 
overlooked function) provides some but not all the features found in 
other DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):

- the values to concatenate (SQL string expression),
- the separator, default (comma) or user-defined (SQL string expression),
- a uniqueness contraint on the values of each group,
- the order of the values.

As far as I understand the specs, SQLlite provides the first three, but 
with a frustrating constraint: you must choose between the uniqueness 
and the user-defined separator but you cannot have both.
The "order by" is badly needed, so, programmers tend to use the 
workaround suggested in this thread: sorting the values in a "from" 
subquery. This is intuitive and works fine in the current version but, 
as said in the documentation, this order is not guaranteed to propagate 
to the concatenated list .


The uniqueness constraint can be enforced in a "from" subquery and the 
user-defined separator can be merged with the values to concatenate, 
followed by some cleaning. As I saw in various forums, it seems possible 
to force the ordering with a CTE (I have not checked). However this 
makes the final expression horribly complicated.


I personally have implemented (in Python) a UDF aggregate function that 
simulates the full group_concat version.  But it would be nice to 
include a full-fledged function (whatever the syntax) in a future SQLite 
version.

Why not in the Christmast version for example?

Best regards

Jean-Luc Hainaut

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Simon Slavin]

On 16 Aug 2017, at 2:25am, petern  wrote:

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

Saying that something is arbitrary is the same as saying nothing about it.

> The row order of the
> enclosing query may be arbitrary but group_concat is not otherwise changing
> that row order in any way.

But it might do in future versions of SQLite.  Or it might do if different 
indexes become available.  What the documentation is saying is "Do not depend 
on this.  It might change.".

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[petern]

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  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 
> wrote:
>
> >
> > On Tuesday, 15 August, 2017 13:27, Jens Alfke  said:
> > >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf 
> > 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
> >
> 

Re: [sqlite] group_concat() reverses order given where clause?

[Donald Griggs]

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

>
> On Tuesday, 15 August, 2017 13:27, Jens Alfke  said:
> >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf 
> 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

Re: [sqlite] group_concat() reverses order given where clause?

[Keith Medcalf]

On Tuesday, 15 August, 2017 13:27, Jens Alfke  said:
>> On Aug 15, 2017, at 12:22 PM, Keith Medcalf  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

Re: [sqlite] group_concat() reverses order given where clause?

[Jens Alfke]

> On Aug 15, 2017, at 12:22 PM, Keith Medcalf  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.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Keith Medcalf]

On Tuesday, 15 August, 2017 09:52, Jens said:
>> On Aug 15, 2017, at 8:12 AM, Bob Friesenhahn  
>> wrote:

>> Notice that adding a 'where' clause has caused the order to be
>>reversed from what was requested in the query.  Why is this and what
>>can I do to correct it?

>It’s actually not reversed; the ordering looks random. So it appears
>the ORDER BY wasn’t applied. Huh.

>… Actually, the docs for group_concat do say "The order of the
>concatenated elements is arbitrary.” :-/

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.

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

Re: [sqlite] group_concat() reverses order given where clause?

[Keith Medcalf]

"order by" is applied to the OUTPUT of the select and does absolutely nothing 
with respect of the traversal order of the underlying table when you are 
selecting an aggregate.

So, the reason that you are seeing a different ordering is more likely 
connected to the use of the "where enable == 1" than anything else.  

Why do you not ask SQLite to "explain" to you what it is doing?

If you want the results of a group_concat to be in a specific order, then you 
must control the in which the underlying data is fed to the aggregate -- 
sorting the aggregate result after the fact is sort of like washing the 
pesticides off an apple after it has already been eaten -- completely 
non-productive.

Something like:

select group_concat(name, ' ')
  from (select name
  from moca_config
 where 
  order by name) as T1;

may be more what you are seeking ... if you want the output of group_concat to 
be ordered, you need to control what goes INTO the function, not what is coming 
out.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Bob Friesenhahn
>Sent: Tuesday, 15 August, 2017 09:13
>To: SQLite mailing list
>Subject: [sqlite] group_concat() reverses order given where clause?
>
>I am surprised by this behavior of group_concat():
>
>sqlite> select group_concat(name, ' ') AS 'names' from moca_config
>order by name;
>names
>bonding cof lof_update moca_core_trace_enable preferred_nc rf_band
>verbose
>sqlite> select group_concat(name, ' ') AS 'names' from moca_config
>where enable == 1 order by name;
>names
>rf_band verbose moca_core_trace_enable preferred_nc lof_update
>bonding
>
>Notice that adding a 'where' clause has caused the order to be
>reversed from what was requested in the query.  Why is this and what
>can I do to correct it?
>
>Thanks,
>
>Bob
>--
>Bob Friesenhahn
>bfrie...@simple.dallas.tx.us,
>http://www.simplesystems.org/users/bfriesen/
>GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
>___
>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

Re: [sqlite] group_concat() reverses order given where clause?

[Jay Kreibich]

On Aug 15, 2017, at 10:39 AM, Bob Friesenhahn  
wrote:

> On Tue, 15 Aug 2017, Dan Kennedy wrote:
> 
>> On 08/15/2017 10:12 PM, Bob Friesenhahn wrote:
>>> select group_concat(name, ' ') AS 'names' from moca_config where enable == 
>>> 1 order by name; 
>> 
>> Maybe this:
>> 
>> select group_concat(name, ' ') AS 'names' from (
>> SELECT name FROM moca_config where enable == 1 order by name
>> );
> 
> That does return the expected order.  Was my expectation unreasonable?


Unreasonable?  Not really; it is a simple mistake, but it is a mistake.

You need to remember that the different clauses of an SQL statement are 
processed in a specific order.  Relevant to this case, ORDER BY is applied 
*after* GROUP BY, or any other aggregation.  Because you have an aggregate 
function in the SELECT clause, but no explicit GROUP BY, you have an implied 
GROUP BY across the whole output of the FROM clause.  So the aggregation 
happens and *then* the ORDER BY is applied…  except the final output of this 
statement, as defined by the SELECT clause, has no column named “name” ...so no 
ordering is enforced.

This is also why the sub-select works.  It generates a full output, with 
ordering, and then passes it to the outer statement to do the aggregation.

  -j




> 
> Bob
> -- 
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Jens Alfke]

> On Aug 15, 2017, at 8:12 AM, Bob Friesenhahn  
> wrote:
> 
> Notice that adding a 'where' clause has caused the order to be reversed from 
> what was requested in the query.  Why is this and what can I do to correct it?

It’s actually not reversed; the ordering looks random. So it appears the ORDER 
BY wasn’t applied. Huh.

… Actually, the docs for group_concat do say "The order of the concatenated 
elements is arbitrary.” :-/

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Bob Friesenhahn]

On Tue, 15 Aug 2017, Dan Kennedy wrote:


On 08/15/2017 10:12 PM, Bob Friesenhahn wrote:
select group_concat(name, ' ') AS 'names' from moca_config where enable == 
1 order by name; 


Maybe this:

select group_concat(name, ' ') AS 'names' from (
 SELECT name FROM moca_config where enable == 1 order by name
);


That does return the expected order.  Was my expectation unreasonable?

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Dan Kennedy]

On 08/15/2017 10:12 PM, Bob Friesenhahn wrote:
select group_concat(name, ' ') AS 'names' from moca_config where 
enable == 1 order by name; 


Maybe this:

select group_concat(name, ' ') AS 'names' from (
  SELECT name FROM moca_config where enable == 1 order by name
);


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group_concat() reverses order given where clause?

[Bob Friesenhahn]

I am surprised by this behavior of group_concat():

sqlite> select group_concat(name, ' ') AS 'names' from moca_config order by 
name;
names
bonding cof lof_update moca_core_trace_enable preferred_nc rf_band verbose
sqlite> select group_concat(name, ' ') AS 'names' from moca_config where enable 
== 1 order by name;
names
rf_band verbose moca_core_trace_enable preferred_nc lof_update bonding

Notice that adding a 'where' clause has caused the order to be 
reversed from what was requested in the query.  Why is this and what 
can I do to correct it?


Thanks,

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users