2 min read

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

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.

Avoid using a prepared statement for “.stats on” command of the CLI after it has been closed by the “.eqp full” logicc..

To know more about SQLite Release 3.25.0 visit its release log draft.

Read Next

How to use SQLite with Ionic to store data?

Introduction to SQL and SQLite

NHibernate 3.0: Testing Using NHibernate Profiler and SQLite


Subscribe to the weekly Packt Hub newsletter. We'll send you the results of our AI Now Survey, featuring data and insights from across the tech landscape.

LEAVE A REPLY

Please enter your comment!
Please enter your name here