We expect to release SQLite 3.45.0 soon, perhaps as early as next week. See draft documentation here:

• https://sqlite.org/draft
• https://sqlite.org/draft/releaselog/3_45_0.html

Please visit the download page and grab the latest pre-release snapshot and give it a try. Report any issues or concerns as follow-ups to this forum post, or in private email to drh at sqlite dot org.

There are two big changes in this release:

1. The JSON SQL functions have been rewritten for improved performance. The internal parse-tree for JSON can now be stored as a BLOB in a database. Accessing that parse-tree BLOB (that we call "JSONB) instead of text JSON can result in about 3-times faster performance for large JSON objects, according to our internal testing. Please try this and share your own experiences.

2. If you are using the sqlite3_result_subtype() API to build application-defined SQL functions, then there is a new requirement that you add the SQLITE_RESULT_SUBTYPE property when registering that SQL function with SQLite. Failure to do so might result in incorrect answers. This is technically a backwards compatibility break. However, we believe that very few people use sqlite3_result_subtype() (which is good as very few people actually need that interface), and the fix is relatively simple. This change is necessary because it was discovered that some query optimizations performed by SQLite are incompatible with subtypes, and so SQLite needs to know which functions might set subtypes so that it can disable those query optimizations.

   Consider adding -DSQLITE_STRICT_SUBTYPE=1 to your builds. If you add that compile-time option and later misuse sqlite3_result_subtype(), SQLite will let you know.

Please offer feedback as follow-ups to this post, or if you must, via direct email to me. Thanks!

No jsonb_each?

The docs say:

Any SQL function parameter that accepts text JSON as an input will also accept a BLOB in the JSONB format. The function will operate the same in either case, except that it will run faster when the input is JSONB, since it does not need to run the JSON parser.

and

Most SQL functions that return JSON text have a corresponding function that returns the equivalent JSONB. The functions that return JSON in the text format begin with "json_" and functions that return the binary JSONB format begin with "jsonb_".

json_each() is a table valued function, returning a table, not a json string. So, there is no need for jsonb_each().

If you are using the sqlite3_result_subtype() API to build application-defined SQL functions, then there is a new requirement that you add the SQLITE_RESULT_SUBTYPE property ...

The only place I've used the subtype interface is with ancillary SQL functions for virtual tables that operate on the result values returned by the xColumn method. The dummy sqlite3_context object passed to xColumn implicitly has the SQLITE_RESULT_SUBTYPE property, which prevents complaints when that method sets the result subtype. Does that mean virtual tables suffer an optimization penalty even when they don't use subtypes?

The doc for sqlite3_result_subtype says

Only the lower 8 bits of the subtype T are preserved in current versions of SQLite; higher order bits are discarded

Is there a reason why an error (MISUSE or RANGE?) aren't returned instead of silently ignoring 24 bits?

Yes - my lack of foresight has sqlite3_result_subtype() returning void instead if int. So no return code is available.

We could do sqlite3_result_subtype_v2() that returns an integer result code, but I'd rather not.

Stephan suggests having out-of-range calls to sqlite3_result_subtype() work as if they were calls to sqlite3_result_error(), in order to get the developers attention that way. But how much legacy code will that break?

So my original design error of having sqlite3_result_subtype() return void instead of int has lead to a lot of problems. But none of them are severe, so I propose to ignore the problem for the time being. If somebody puts an oversized value into sqlite3_result_subtype(), then they get whatever it is that they get.

Probably people shouldn't be using sqlite3_result_subtype() anyhow. Maybe my biggest design error was exposing that interface in the first place.

I downloaded the prerelease source code and I note that the bug, as discussed in this conversation regarding the use of _fstat in WIN32 code in shell.c does not appear to be fixed.

https://sqlite.org/forum/forumpost/bc14e24e234abc3d4a39dc5fde86c7f9e04ce10039a69d9b304efb21dc23945f

It appears that the said source, at least the generated shell.c, does not come from the repo trunk branch. The fix is on trunk, as can be seen here. This was due to a stale shell.c, now replaced in the latest prerelease tarball.

Another mechanism is to call sqlite3_log(SQLITE_WARNING, "Out of range value in sqlite3_result_subtype").

In my documentation I strongly encourage receiving log messages, and even have an entire base practice module.

I was considering exposing subtypes, but won't now.

Fixed, thanks!

Typo in json1.html:

https://sqlite.org/draft/json1.html#the_json_valid_function

6 → X is JSON5 text or JSONB ← This is probably the you value you want

has one "you" too many; the comment should probably read "This is probably the value you want"