Thanks.

I think I may have encountered a "real" bug while ordering in a subquery.
I have simplified it in the following exemples:

select json_group_array(o) from (
   select json_object(
      'id', sb.id
   ) o
   from (
      select 1 id, 2 field
      ) sb
)


the json_group_array returns an array of JSON object, ie. [{"id":1}] while
in

select json_group_array(o) from (
   select json_object(
      'id', sb.id
   ) o
   from (
      select 1 id, 2 field
      ) sb
   order by sb.field desc
)

so with an added order by in the subquery, it returns and array of JSON
strings, ie. ["{\"id\":1}"]

In my particular case, I can work around the issue by using group_concat()
rather than json_group_array()

Can someone confirm whether this is a bug ?

Thanks!


Le mar. 8 janv. 2019 à 10:18, Hick Gunter <h...@scigames.at> a écrit :

> I don't recall that any (aggregate) function is concerned at all about the
> order in which rows are visited. The effect is only visible in
> non-commutative aggregates (e.g. concatenation).
>
> If you want the arguments presented to an aggregate function in a specific
> order, then you need to enforce that order, with an order by clause in a
> subselect if necessary.
>
> If you have an order by clause which is already fulfilled by the
> visitation order, SQLite will not sort again.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Eric Grange
> Gesendet: Dienstag, 08. Jänner 2019 09:17
> An: General Discussion of SQLite Database <
> sqlite-users@mailinglists.sqlite.org>
> Betreff: [EXTERNAL] [sqlite] json_group_array() and sorting
>
> Hi,
>
> Is json_group_array() supposed to honor a sorting clause or not ? (and
> concatenation aggregates in general)
>
> I have a query like
>
> select json_group_array(json_object(
>    'id', st.id,
>    'num', st.numeric_field,
>    ...bunch of fields here...
> ))
> from some_table st
> ...bunch of joins here...
> where ...bunch of conditions...
> order by st.numeric_field desc
> limit 50
>
>
> but the resulting JSON array is not ordered according to the "order by",
> but AFAICT by the st.id field (a primary key) When not aggregating, the
> records are in the correct order.
>
> Is it a bug or something expected ?
>
> I can get the proper order when I use a subquery for the joins & filters,
> and aggregate in a top level query, but that is rather more verbose, and I
> am not sure the ordering being preserved in that case is not just
> 'circumstancial' and could be affected by future SQLite query optimizations.
>
> Thanks!
>
> Eric
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> 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

Reply via email to