SQLite INSERT RETURNING and changes() Behavior

The Behavior of INSERT … RETURNING and changes() in SQLite

The core issue revolves around the behavior of the INSERT ... RETURNING clause in SQLite, particularly when used in conjunction with the changes() function. The discussion highlights three key observations: the number of rows returned by RETURNING, the misuse of aggregate functions like sum() within RETURNING, and the discrepancy in the results of changes() between the SQLite CLI and applications using the SQLite library. These observations raise questions about the internal mechanics of SQLite’s execution model, the timing of when changes() is evaluated, and how the RETURNING clause interacts with the underlying database operations.

Why Does INSERT … RETURNING Return Multiple Rows for changes()?

The first observation is that when using INSERT ... RETURNING changes(), the number of rows returned matches the number of rows inserted, even though changes() is a scalar value. This behavior is counterintuitive because changes() typically returns a single value representing the number of rows affected by the most recent INSERT, UPDATE, or DELETE operation. However, in the context of RETURNING, it appears that changes() is evaluated for each row being inserted, resulting in multiple rows of output.

This behavior can be explained by the processing order of the RETURNING clause. According to SQLite’s documentation, the RETURNING clause causes the statement to return one result row for each database row that is inserted, updated, or deleted. This means that for each row inserted, the RETURNING clause is evaluated independently, and changes() is called for each row. Since changes() returns the number of rows affected by the most recently completed statement, and the INSERT statement is not yet complete when RETURNING is evaluated, the result is that changes() returns the number of changes from the previous statement, not the current one.

For example, consider the following sequence of statements:

CREATE TABLE tblXYZ(Field1);
INSERT INTO tblXYZ VALUES('A'),('B'),('C') RETURNING changes();

In this case, the INSERT statement inserts three rows, and the RETURNING clause is evaluated for each row. However, since the INSERT statement is not yet complete when changes() is called, the function returns the number of changes from the previous statement. If no previous statement has been executed, changes() returns 0 for each row.

Why Does Using Aggregate Functions Like sum() in RETURNING Cause a Parse Error?

The second observation is that attempting to use an aggregate function like sum() within the RETURNING clause results in a parse error. This behavior is expected and is explicitly documented in SQLite’s documentation. The RETURNING clause may not contain top-level aggregate functions or window functions. This restriction exists because the RETURNING clause is designed to return one result row for each row affected by the statement, and aggregate functions operate on sets of rows, not individual rows.

For example, the following statement will result in a parse error:

INSERT INTO tblXYZ VALUES('A'),('B'),('C') RETURNING sum(changes());

The error occurs because sum() is an aggregate function that operates on a set of rows, but the RETURNING clause is evaluated for each individual row. SQLite’s parser detects this mismatch and raises a parse error to prevent incorrect usage.

However, it is worth noting that subqueries within the RETURNING clause may contain aggregate functions, as long as the aggregates are not at the top level. For example, the following statement is valid:

INSERT INTO tblXYZ VALUES('A'),('B'),('C') RETURNING (SELECT sum(changes()) FROM tblXYZ);

In this case, the aggregate function is contained within a subquery, which is allowed by SQLite’s rules.

Why Does changes() Return Different Results in Applications Compared to the CLI?

The third observation is that when executing INSERT ... RETURNING changes() from an application using the SQLite library, the result is three rows of 0, whereas the SQLite CLI returns three rows of 3. This discrepancy arises from differences in how the CLI and the SQLite library handle the evaluation of changes() within the context of RETURNING.

In the SQLite CLI, changes() appears to be evaluated before the INSERT statement is fully committed, resulting in the function returning the number of changes from the current statement. However, in applications using the SQLite library, changes() is evaluated after the INSERT statement is committed, resulting in the function returning the number of changes from the previous statement.

This behavior can be attributed to the way the SQLite library processes statements. When an application executes an INSERT ... RETURNING statement, the library first processes the INSERT operation and then evaluates the RETURNING clause. By the time the RETURNING clause is evaluated, the INSERT statement is considered complete, and changes() returns the number of changes from the previous statement.

For example, consider the following sequence of statements executed in an application:

CREATE TABLE tblXYZ(Field1);
INSERT INTO tblXYZ VALUES('A'),('B'),('C') RETURNING changes();

In this case, the INSERT statement inserts three rows, but by the time the RETURNING clause is evaluated, the INSERT statement is considered complete. As a result, changes() returns 0 for each row, indicating that no changes were made by the previous statement.

To obtain the correct number of changes in an application, you can use a separate SELECT changes() statement after the INSERT statement. For example:

CREATE TABLE tblXYZ(Field1);
INSERT INTO tblXYZ VALUES('A'),('B'),('C');
SELECT changes();

In this case, the SELECT changes() statement is executed after the INSERT statement is complete, and it correctly returns the number of changes made by the INSERT statement.

Troubleshooting Steps, Solutions & Fixes

Understanding the Processing Order of RETURNING

To address the issues raised in the discussion, it is essential to understand the processing order of the RETURNING clause in SQLite. The RETURNING clause is evaluated after the INSERT, UPDATE, or DELETE operation is performed but before the statement is considered complete. This means that any function calls within the RETURNING clause, such as changes(), are evaluated in the context of the ongoing statement, not the completed one.

To avoid confusion, it is important to recognize that changes() returns the number of changes made by the most recently completed statement, not the current one. Therefore, when using changes() within a RETURNING clause, the function will return the number of changes from the previous statement, not the current one.

Avoiding Aggregate Functions in RETURNING

As discussed earlier, the RETURNING clause may not contain top-level aggregate functions or window functions. This restriction exists because the RETURNING clause is designed to return one result row for each row affected by the statement, and aggregate functions operate on sets of rows, not individual rows.

To avoid parse errors, ensure that aggregate functions are not used at the top level of the RETURNING clause. If you need to perform aggregate calculations, consider using a subquery within the RETURNING clause. For example:

INSERT INTO tblXYZ VALUES('A'),('B'),('C') RETURNING (SELECT sum(changes()) FROM tblXYZ);

In this case, the aggregate function is contained within a subquery, which is allowed by SQLite’s rules.

Handling changes() in Applications

When using the SQLite library in an application, it is important to recognize that changes() may return different results compared to the SQLite CLI. Specifically, changes() may return 0 when used within a RETURNING clause because the function is evaluated after the INSERT statement is considered complete.

To obtain the correct number of changes in an application, use a separate SELECT changes() statement after the INSERT statement. For example:

CREATE TABLE tblXYZ(Field1);
INSERT INTO tblXYZ VALUES('A'),('B'),('C');
SELECT changes();

In this case, the SELECT changes() statement is executed after the INSERT statement is complete, and it correctly returns the number of changes made by the INSERT statement.

Best Practices for Using RETURNING and changes()

To ensure consistent and predictable behavior when using the RETURNING clause and the changes() function in SQLite, follow these best practices:

1. Understand the Processing Order: Recognize that the RETURNING clause is evaluated after the INSERT, UPDATE, or DELETE operation but before the statement is considered complete. This means that changes() will return the number of changes from the previous statement, not the current one.

2. Avoid Top-Level Aggregate Functions: Do not use top-level aggregate functions or window functions in the RETURNING clause. If you need to perform aggregate calculations, use a subquery within the RETURNING clause.

3. Use Separate SELECT changes() Statements in Applications: When using the SQLite library in an application, use a separate SELECT changes() statement after the INSERT, UPDATE, or DELETE statement to obtain the correct number of changes.

4. Test and Validate: Always test and validate your SQL statements in both the SQLite CLI and your application to ensure consistent behavior. If you encounter discrepancies, refer to SQLite’s documentation and adjust your code accordingly.

By following these best practices, you can avoid common pitfalls and ensure that your SQLite queries behave as expected in both the CLI and application environments.