SQLite 3.45 released with enhanced JSON functions, FTS5 tokendata option, and more

SQLite 3.45 has been unveiled with a series of new features and enhancements. Among these, the SQLITE_RESULT_SUBTYPE property has been added for application-defined SQL functions. If a function invoking sqlite3_result_subtype() is not registered with this new property, it can cause the call to sqlite3_result_subtype() to behave as a no-op. To prevent an SQL error when an unregistered function tries to invoke sqlite3_result_subtype(), it is recommended to compile with -DSQLITE_STRICT_SUBTYPE=1.

The JSON SQL functions have undergone significant enhancements. They have been rewritten to use a new internal parse tree format named JSONB, which can be stored in the database to avoid reparsing. The updated JSON-generating functions now produce binary JSONB instead of JSON text. The json_valid() function has a new optional second argument to define the “well-formed” criteria for the first argument.

SQLite 3.45 also introduces the FTS5 tokendata option to the FTS5 virtual table. The SQLITE_DIRECT_OVERFLOW_READ optimization is now enabled by default but can be disabled at compile-time using -DSQLITE_DIRECT_OVERFLOW_READ=0.

Several improvements have been made to the query planner. The transitive constraint optimization has been adjusted to prevent the query planner from using a range constraint when an equality constraint is more suitable. The query planner also better disregards indexes that ANALYZE identifies as low-quality. The default value for SQLITE_MAX_PAGE_COUNT has been increased from 1073741824 to 4294967294.

Finally, enhancements to the CLI include improved display of UTF-8 content on Windows and automatic detection and setting adjustments for “.dump” script playback.