What’s in the upcoming SQLite 3.25.0 release: windows functions, better query optimizer and more

The SQLite community has released a sneak peek to what users can expect in the upcoming version, SQLite 3.25.0, which could be released next month.

The SQLite 3.25.0 draft which the community published on its official website yesterday includes a list of some upcoming features and bug fixes. The primary update being support for windows functions and improvements in the query optimizer.

Expectations from SQLite 3.25.0

Support for windows functions will be added

This release will bring in an added window function support. Prior to this, SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave.

The community has carried out several test cases against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL.

Improvements in the Query Optimizer

Unnecessary loads of columns in an aggregate query are avoided. These columns are neither within an aggregate function nor a part of the GROUP BY clause.

The IN-early-out optimization: When doing a look-up on a multi-column index, an IN operator is used on a column other than the left-most column. If no rows match against the first IN value, one should check the existence of rows that match the columns to the right before continuing with the next IN value.

Transitive property can be used to propagate constant values within the WHERE clause. For example, convert "a=99 AND b=a" into "a=99 AND b=99".

Separate mutex on every inode

In the SQLite 3.25.0, one can use a separate mutex on every inode in the Unix VFS, rather than a single mutex shared among them all. This results in better concurrency in multi-threaded environments.

Improvised PRAGMA integrity_check command

The PRAGMA integrity_check command will be enhanced for improved detection of problems on the page freelist.

The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE, CHECK, and NOT NULL constraint errors.

.dump command infinity output

This version will showcase the infinity output as 1e999 in the ".dump" command of the command-line shell.

Bug fixes in the upcoming version SQLite 3.25.0

A fix for ticket 79cad5e4b2e219dd197242e9e

On an UPSERT when the order of constraint checks is rearranged, ensure that the affinity transformations on the inserted content occur before any of the constraint checks.