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
