Shawn's json_group_array(json(o)) works indeed, but it's also 30% slower in my case than using
'[' || ifnull(group_concat(o, ','), '') || ']' which is however more case specific and less obvious. Would be nice to see the subtype passing be improved, as otherwise query planner improvements could end up wrecking existing queries where the subtype currently survives. Eric Le mar. 8 janv. 2019 à 11:41, Dominique Devienne <ddevie...@gmail.com> a écrit : > On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne <ddevie...@gmail.com> > wrote: > > > > On Tue, Jan 8, 2019 at 10:50 AM Eric Grange <egra...@glscene.org> wrote: > >> > >> 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 > > See also [2], which states "values [...] are transient and ephemeral. > [...]. The pointers will not survive sorting". > > The pointer-passing APIs are different from the subtype one, but as [3] > states, they > both addressed the same issue, and likely obey similar rules, linked to > sqlite3_value in general. > > This is IMHO what's going on. And I think Shawn's work-around will work :). > --DD > > [2] > > https://www.sqlite.org/bindptr.html#restrictions_on_the_use_of_pointer_values > [3] https://www.sqlite.org/bindptr.html#preventing_forged_pointers > _______________________________________________ > 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
