SQLite Improves Nulls Support
Written by Kay Ewbank   
Thursday, 10 October 2019

The latest release of SQLite is available with support for Nulls in Order By clauses and the ability to use Filter on aggregate functions.

SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. It supports full text search and has extensions offering JSON support. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file. The developers say SQLite is the most used database engine in the world, as it is built into all mobile phones, major web browsers and most operating systems. 


The new filter clause lets you add an expression of the form Filter (Where expr) to the aggregate function. This will ensure that only only rows for which the expr is true are included in the aggregate.

The improved handling of Nulls in Order By overcomes the fact that SQLite treats Null values as being smaller than any other values for sorting purposes. Until now this has meant that rows with a Null value in the Order By column would appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can now be changed using either "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.

Index Info and xinfo pragmas have both been improved so they return information about the way tables created using Without RowIS are stored. Pragmas are used to return information about various aspects of data; for example, information about the columns in an index can be read using the index_info pragma like this:

PRAGMA index_info('idx52');

This returns one row for each key column in the named index. If there is no index named index-name but there is a Without RowID table with that name, the pragma now returns the Primary Key columns of the Without RowID table as they are used in the records of the underlying b-tree; in other words, with duplicate columns removed. Other Pragma handling features are now enabled by default.

Elsewhere, the schema parser has been changed so that it throws an error if various elements of the sqlite_master table have been corrupted. 


More Information

SQLite Site

Related Articles

SQLite Introduces Vacuum Into

SQLite Adds Zipfile Support

SQLite 3.20 Improves Query Planner

SQLite 3.17 Adds SHA1 Extensions 

SQLite 3.16 Adds Pragma Functions  

SQLite 3.15 Adds Row Value Support

Portable Version Of DB Browser For SQLite

SQLite 3.9 

SQLite Updated

LiteCLI - SQLite Client with Autocomplete 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.


Celebrating Alan Turing

Today, June 7th 2024 is the 70th anniversary of the untimely death of Alan Turing. While we now commemorate him for his contributions to code-breaking computer science and artificial intelligence, sev [ ... ]

Meet Ambidex and Rookie From NAVER

Robots may not be ready yet for the great takeover of jobs that some people fear, but in South Korea, where there are 1,000 robots per 10,000 workers, service robots are finding a role. 

More News

C book



or email your comment to: comments@i-programmer.info

Last Updated ( Thursday, 10 October 2019 )