SQLite many useful new features, SQLite 3.35.0 is released

Do you know SQLite many useful new features, SQLite 3.35.0 is released? The most popular embedded database SQLite development team released version 3.35.0 of SQLite on March 12, 2021. This version adds multiple SQL statements and some new features related to the query optimizer. This article will analyze them one by one.

SQLite many useful new features

Support built-in SQL mathematical functions

SQLite 3.35.0 adds built-in SQL math function support to the core source code. We only need to use the -DSQLITE_ENABLE_MATH_FUNCTIONSoption when compiling, and it has been enabled in the default compilation configuration. In other words, we no longer need to compile the extension-functions. c file to obtain these mathematical functions.

The currently supported mathematical functions include:

acos(X)
acosh(X)
asin(X)
asinh (X)
atan (X)
atan2 (X, Y)
atanh(X)
ceil (X)
ceiling(X)
cos(X)
cosh(X)
degrees(X)
exp(X)
floor(X)
ln (X)
log(B,X)
log(X)
log10(X)
log2(X)
mod (X, Y)
pi()
pow (X, Y)
power (X, Y)
radians(X)
without (X)
birth (X)
sqrt(X)
so (X)
fishy (X)
trunc(X)

The parameters of these functions can be integers, floating-point numbers, or strings or binary strings that can be converted to integers or real numbers. If any parameter is NULL, or a string or binary string that cannot be converted to a number, the function will return NULL. In addition, if there is a numeric field error, such as calculating the square root of a negative number or calculating the arc cosine value greater than 1.0 or less than -1.0, the function will also return NULL.

sqlite> select sqlite_version();
3.35.0
sqlite> .nullvalue [NULL]
sqlite> select sqrt(null), sqrt(-1);
[NULL]|[NULL]

SQLite many useful new features

In addition, the return values ​​of these functions are usually approximate results. For example, the return result of the function pi() function is 3.141592653589793115997963468544185161590576171875, which is about 1.22465e-16 smaller than the actual value, but this is the closest value to Pi in the IEEE754 double-precision floating-point number.

For the specific introduction of these mathematical functions, please refer to the official documents .

Support ALTER TABLE DROP COLUMN statement

The new version of SQLite adds the ALTER TABLE DROP COLUMN statement, which can be used to delete existing fields in the table. This command will delete the field with the specified name in the table and rewrite the contents of the table to clear the data in the field. E.g:

sqlite> create table t(id int, col1 int);
sqlite> insert into t values(1,1);
sqlite> alter table t drop column col1;
sqlite> select * from t;
1

SQLite many useful new features

This command can only delete fields that are not referenced by other objects, SQLite many useful new features, but also cannot delete PRIMARY KEY fields or UNIQUE constraint fields. The following situations may cause the execution of this command to fail:

1. The deleted field is a part of the PRIMARY KEY or primary key.
2. The deleted field is used for the UNIQUE constraint
3. An index exists for the deleted field.
4. The deleted field appears in the WHERE clause of a partial index.
5. The deleted column appears in the table-level CHECK constraint or the CHECK constraint of other columns.
6. The deleted field is referenced by a foreign key constraint.
7. The deleted field is used in the expression that generates the column.
8. The deleted field is used in a trigger or view.

For the implementation principle of the ALTER TABLE DROP COLUMN statement, you can refer to the official document.

Extend the functionality of UPSERT

SQLite many useful new features, The new version of SQLite has enhanced the UPSERT statement in the following two aspects:

• Multiple ON CONFLICT clauses are allowed, and they are judged in order;
• The last ON CONFLICT clause can ignore the conflicting target, and still use the DO UPDATE command for update operations.

E.g:

sqlite> create table t(id int primary key, col1 int unique, col2 text);
sqlite> insert into t values (1, 1, 'sqlite');
sqlite> insert into t values (2, 1, 'sqlite3')
   ...> on conflict(id) do nothing
   ...> on conflict(col1) do update set col2 = excluded.col2;
sqlite> select * from t;
1|1|sqlite3

SQLite many useful new features

For detailed introduction of UPSERT function, please refer to official documents .

DML statement supports RETURNING clause

The new version of SQLite provides RETURNING clauses for INSERT, UPDATE, and DELETE statements, which can return inserted, updated, or deleted data. This extended function comes from PostgreSQL. E.g:

CREATE TABLE t0(
  a INTEGER PRIMARY KEY,
  b DATE DEFAULT CURRENT_TIMESTAMP,
  c INTEGER
);
sqlite> INSERT INTO t0(c)
   ...> VALUES(random()),(random()),(random())
   ...> RETURNING *;
1|2021-03-15 19:50:08|-2232026377363516485
2|2021-03-15 19:50:08|-6973867001075546289
3|2021-03-15 19:50:08|-2021925416820049180

SQLite many useful new features

In the above INSERT statement, SQLite generated default values ​​for all fields. The RETURNING clause can return these values ​​to the application, avoiding the application of querying the database again. SQLite many useful new features.

For INSERT and UPDATE statements, the RETURNING clause returns the modified data. For the DELETE statement, the RETURNING clause returns the data before the deletion. E.g:

sqlite> UPDATE t0
   ...> SET c = 20
   ...> WHERE a = 2
   ...> RETURNING a, c;
2|20

sqlite> DELETE FROM t0
   ...> WHERE a = 1
   ...> RETURNING c;
-2232026377363516485

SQLite many useful new features

For the detailed introduction and precautions of the RETURNING clause, you can refer to the official document. SQLite many useful new features.

VACUUM optimization

For databases containing very large TEXT or BLOB text, the memory required to run the VACUUM cleanup command is smaller. SQLite no longer needs to load the entire TEXT or BLOB text into memory at once.

For the introduction of the VACUUM command, you can refer to the official document . SQLite many useful new features.

General table expression optimization

The new version supports specifying MATERIALIZED or NOT MATERIALIZED prompt options when creating common table expressions.

The AS MATERIALIZED and AS NOT MATERIALIZED keywords also come from PostgreSQL, and their function is to prompt the query planner on how to implement CTE. SQLite many useful new features.

If MATERIALIZED is specified, select-stmt is likely to generate a memory or disk temporary table, and then use the temporary table in subsequent SQL statements. Due to the immediate execution of select-stmt, optimizations such as subquery expansion or query condition pushdown cannot be obtained.

If NOT MATERIALIZED is specified, select-stmt appears as a subquery in all clauses that reference the CTE. In this way, optimizations such as subquery expansion or query condition push-down can be obtained. Nevertheless, NOT MATERIALIZED does not prevent the use of materialized temporary tables. The query planner may still use temporary tables to implement CTE based on judgment. The true meaning of NOT MATERIALIZED is closer to “process like any normal view or subquery.”

If no prompt option is specified, SQLite 3.35.0 (2021-03-12) and later versions default to MATERIALIZED for CTEs that are used multiple times, and default to NOT MATERIALIZED for CTEs that are quoted only once. In previous versions, all CTE default behaviors were consistent with NOT MATERIALIZED. It is an exploratory behavior to determine MATERIALIZED or NOT MATERIALIZED by the number of times the CTE has been cited. If the query plan strategy is improved in the future, this method will also change. In any case, these changes only have an impact on performance, and the final query results will not change.

For more information about general table expressions, you can refer to official documents .

TEMP triggers and views

The new version has modified the SQLITE_DBCONFIG_ENABLE_TRIGGER and SQLITE_DBCONFIG_ENABLE_VIEW configuration options. Now they only affect the triggers and views in the main database or additional databases, and will not affect the triggers and views in the TEMP mode. TEMP triggers and views are never disabled. SQLite many useful new features.

Query optimizer enhancements

SQLite 3.35.0 has made the following improvements to the query planner/optimizer:

1. The min/max optimization has been further enhanced, making it better to use with the IN operator than the previous version, and the OP_SeekScan optimization has been enhanced.
2. Where possible conversion and improved performance, try to convert the EXISTS operator in the WHERE clause to an IN operator.
3. Allow the expansion of UNION ALL subqueries, even if the parent query is a join query.
4. Even if STAT4 is disabled, indexes are still used to optimize IS NOT NULL expressions in the WHERE clause when appropriate.
5. If there is a NOT NULL constraint on the field x and does not appear in the outer join query, expressions of form x IS NULL or x IS NOT NULL may be converted to simple FALSE or TRUE.
6. If the UPDATE statement does not modify any fields associated with the foreign key, the foreign key constraint is no longer checked.
7. As long as the WHERE condition item is completely composed of constants and expressions in the PARTITION BY clause of all window functions, the WHERE condition item is allowed to be pushed down to the subquery that contains the window function.

CLI enhancements

SQLite 3.35.0 has made the following enhancements to the command line tools:

• .stats supports new parameters stmt and vmstep, which are used to display the statistics of the statement preparation phase and only the virtual machine step count respectively.
• Added the .filectrl data_version command to display the version number of the data file page.
• The .once and .output commands have been enhanced. If the target parameter starts with | (indicating that the output result is redirected to the pipeline), the parameter does not need to be enclosed in quotation marks.

The new version also fixes the following defects:

• Modified the potential NULL pointer indirect reference, this problem may occur when the system processes the wrong SELECT statement that contains the associated WHERE clause and HAVING 0 clause. (SQLite 3.34.1 patch version synchronously revised this defect.)
• Fixed the incorrect result that may be caused by the IN operator optimization introduced in SQLite 3.33.0.
• Fixed the wrong result when the LIKE operator ends with% and contains the ESCAPE’_’ clause.

To sum up

Knowing so many new features, which one is you most looking forward to? Hurry up and click to download the latest version and use it! SQLite many useful new features.