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.
-
Fix for ticket 7be932dfa60a8a6b3b26bcf76
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