On Tue, Jan 8, 2019 at 10:50 AM Eric Grange <[email protected]> wrote:
> 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 ?
My guess is that it works as "designed", even if this is surprising...
I believe that JSON1 leverages value "sub-types" [1], which allow chained
JSON
"documents" to be processed in an optimized fashion (to avoid
internal-representation
to text, and back conversions across JSON1 calls).
But when you add sorting to the mix, SQLite probably decides to "lose" the
subtype
and convert to string for some reasons.
Look at the plans. SQLite may "flatten" the 1st query, so that the JSON1
functions "chain"
correctly, preserving the subtype, while in the 2nd query's plan, the two
functions do not
"chain" anymore, "losing" the subtype.
This is a side-effect of subtypes being a bit of "wart" and not really part
of the type-system proper.
So they are easily lost along the way, in ways which depend on how the
planner "rewrites" the
query, as in your case. Subtypes are still very useful, but more of a
"pragmatic" solution, than
an elegant design, for once in SQLite. IMHO :).
This is just a guess though. DRH will likely shed more light on this.
Thanks, --DD
[1] https://www.sqlite.org/c3ref/value_subtype.html
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
