group_concat() reverses order given where clause?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Jens Alfke-2
In reply to this post by Bob Friesenhahn

> On Aug 15, 2017, at 8:12 AM, Bob Friesenhahn <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Jay Kreibich
In reply to this post by Bob Friesenhahn

On Aug 15, 2017, at 10:39 AM, Bob Friesenhahn <[hidden email]> 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
> [hidden email], http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Keith Medcalf
In reply to this post by Bob Friesenhahn

"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 <whatever conditions you want>
      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-
>[hidden email]] 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
>[hidden email],
>http://www.simplesystems.org/users/bfriesen/
>GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Keith Medcalf
In reply to this post by Jens Alfke-2

On Tuesday, 15 August, 2017 09:52, Jens said:
>> On Aug 15, 2017, at 8:12 AM, Bob Friesenhahn <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Jens Alfke-2

> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Keith Medcalf

On Tuesday, 15 August, 2017 13:27, Jens Alfke <[hidden email]> said:
>> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

>
> On Tuesday, 15 August, 2017 13:27, Jens Alfke <[hidden email]> said:
> >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]>
> wrote:
>
> >
> > On Tuesday, 15 August, 2017 13:27, Jens Alfke <[hidden email]> said:
> > >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <[hidden email]>
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Simon Slavin-3


On 16 Aug 2017, at 2:25am, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Jean-Luc Hainaut
In reply to this post by Bob Friesenhahn
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: group_concat() reverses order given where clause?

Petite Abeille-2

> On Aug 16, 2017, at 11:11 AM, Jean-Luc Hainaut <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users