Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds

SQLite 3.25.0 is out with better query optimizer and support for windows functions

Save for later
  • 180 min read
  • 2018-09-17 08:02:58

article-image

After the SQLite community, last month, released a draft of what users can expect in the SQLite’s next release, the community finally announced the release of SQLite 3.25.0 yesterday.

The primary update, however, is that they have added a support for windows functions and have provided improvements in the query optimizer.

Let’s have a closer look at what the new features in this version are.

Features in SQLite 3.25.0

  • A support for windows functions has been added in this SQLite release
  • The ALTER TABLE command has been enhanced with
    • A support for renaming columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname.
    • Fix table rename feature so that it also updates references to the renamed table in triggers and views.

  • Improvements to the Query Optimizer include:
    • Avoid unnecessary loads of columns in an aggregate query that are not within an aggregate function and that are not part of the GROUP BY clause.
    • The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value.
    • Use the transitive property to try to propagate constant values within the WHERE clause. For example, convert "a=99 AND b=a" into "a=99 AND b=99".

  • Unlock access to the largest independent learning library in Tech for FREE!
    Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
    Renews at $19.99/month. Cancel anytime
  • Users now have a separate mutex on every inode in the unix VFS, rather than a single mutex shared among them all, for slightly better concurrency in multi-threaded environments.
  • The PRAGMA integrity_check command has been enhanced for improved  detection of problems on the page freelist.
  • This version showcases the infinity output as 1e999 in the “.dump” command of the command-line shell.
  • A SQLITE_FCNTL_DATA_VERSION file-control has been added.
  • A Geopoly module has been added.

Bug fixes in SQLite 3.25.0


The August draft release had showcased fixes for two tickets. However, the final release has four ticket fixes including,

Fix for ORDER BY LIMIT optimization


The ORDER BY LIMIT optimization might have caused an infinite loop in the byte code of the prepared statement under very obscure circumstances, due to a confluence of minor defects in the query optimizer.

Fix for rearrangement of the order of constraint checks


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 ".stats on" command


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

Fix for incorrect byte-code generation by  LIKE optimization


The LIKE optimization was generating incorrect byte-code and hence getting the wrong answer if the left-hand operand has numeric affinity and the right-hand-side pattern is '/%' or if the pattern begins with the ESCAPE character.

For more details, visit SQLite 3.25.0 release log.

How to use SQLite with Ionic to store data?

Introduction to SQL and SQLite

Introducing Watermelon DB: A new relational database to make your React and React Native apps highly scalable