CTE Behavior and last_insert_rowid() in SQLite

Issue Overview: CTE Evaluation and last_insert_rowid() Behavior

When working with SQLite, a common task involves inserting rows into a table and then using the last_insert_rowid() function to retrieve the auto-incremented primary key of the most recently inserted row. This key is often needed for subsequent operations, such as inserting related data into another table. However, when attempting to use a Common Table Expression (CTE) to capture and reuse the value returned by last_insert_rowid(), unexpected behavior can occur.

In the provided scenario, the user attempts to insert multiple rows into a table bar, where each row should reference the same foo_id obtained from the most recent insert into the foo table. The expectation is that the CTE will capture the last_insert_rowid() value once and reuse it for all subsequent inserts into bar. However, the observed behavior is that the last_insert_rowid() value changes between inserts, leading to inconsistent results in the bar table.

The core issue lies in how SQLite evaluates CTEs and the last_insert_rowid() function. By default, SQLite may re-evaluate the CTE for each reference, causing last_insert_rowid() to return different values if the underlying data changes between evaluations. This behavior is particularly problematic when the CTE is used in a multi-row insert statement, as each row insertion can trigger a re-evaluation of the CTE, leading to inconsistent results.

Possible Causes: CTE Re-evaluation and Query Planner Behavior

The unexpected behavior observed in the scenario can be attributed to several factors related to how SQLite handles CTEs and the last_insert_rowid() function.

First, SQLite’s query planner may choose to re-evaluate the CTE for each reference within the query. This re-evaluation can occur even if the CTE is referenced multiple times within the same statement. When the CTE includes a call to last_insert_rowid(), each re-evaluation can result in a different value if the underlying table (foo in this case) has been modified between evaluations. This is why the lir column in the bar table ends up with different values for each row.

Second, the last_insert_rowid() function itself is context-sensitive. It returns the rowid of the most recent successful insert into the database from the current database connection. If the CTE is re-evaluated after each insert into bar, the last_insert_rowid() function may return the rowid of the most recent insert into bar rather than the original insert into foo. This can lead to further inconsistencies in the data.

Third, the use of the MATERIALIZED hint in the CTE can influence the behavior of the query planner. The MATERIALIZED hint instructs SQLite to evaluate the CTE once and store the result in a temporary table, which can then be reused for subsequent references. However, as noted in the discussion, the MATERIALIZED hint is not guaranteed to be honored by the query planner in all versions of SQLite. This introduces an element of uncertainty, as the behavior of the query may change in future releases of SQLite.

Troubleshooting Steps, Solutions & Fixes: Ensuring Consistent CTE Evaluation

To address the issue of inconsistent CTE evaluation and ensure that the last_insert_rowid() value is captured and reused correctly, several approaches can be considered. Each approach has its own trade-offs, and the choice of solution will depend on the specific requirements and constraints of the application.

1. Using a Temporary Variable to Store last_insert_rowid()

One approach to ensure that the last_insert_rowid() value is captured and reused consistently is to store the value in a temporary variable before performing the multi-row insert into the bar table. This approach avoids the need for a CTE altogether and ensures that the last_insert_rowid() value is evaluated only once.

begin transaction;

create table foo (foo_id integer primary key);
create table bar (bar_id integer primary key, lir integer);

-- Insert rows into foo and capture the last_insert_rowid()
insert into foo default values;
insert into foo default values;
insert into foo default values;

-- Store the last_insert_rowid() in a temporary variable
with last_id as (
  select last_insert_rowid() as last_id
)
insert into bar (lir)
select last_id from last_id
union all
select last_id from last_id;

select * from foo;
select * from bar;

rollback;

In this approach, the last_insert_rowid() value is captured in a temporary CTE named last_id. The last_id CTE is then used to insert multiple rows into the bar table. Since the last_id CTE is evaluated only once, the last_insert_rowid() value remains consistent across all inserts into bar.

2. Using a Materialized CTE with the MATERIALIZED Hint

Another approach is to use the MATERIALIZED hint to force SQLite to evaluate the CTE once and store the result in a temporary table. This approach can be effective in ensuring that the last_insert_rowid() value is captured and reused consistently, but it comes with the caveat that the MATERIALIZED hint is not guaranteed to be honored by the query planner in all versions of SQLite.

begin transaction;

create table foo (foo_id integer primary key);
create table bar (bar_id integer primary key, lir integer);

-- Insert rows into foo
insert into foo default values;
insert into foo default values;
insert into foo default values;

-- Use a materialized CTE to capture last_insert_rowid()
with f as materialized (
  select foo_id from foo where foo_id = last_insert_rowid()
)
insert into bar (lir)
values (
  (select foo_id from f)
),
(
  (select foo_id from f)
);

select * from foo;
select * from bar;

rollback;

In this approach, the MATERIALIZED hint is used to ensure that the CTE f is evaluated only once, and the result is stored in a temporary table. This prevents the CTE from being re-evaluated for each reference, ensuring that the last_insert_rowid() value remains consistent across all inserts into bar.

3. Using a Temporary Table to Store the last_insert_rowid() Value

A more robust approach, especially in environments where transactions are not supported (such as Cloudflare’s D1 SQLite database), is to use a temporary table to store the last_insert_rowid() value. This approach avoids the uncertainty associated with the MATERIALIZED hint and ensures that the last_insert_rowid() value is captured and reused consistently.

-- Create the main tables
create table foo (foo_id integer primary key);
create table bar (bar_id integer primary key, lir integer);

-- Create a temporary table to store the last_insert_rowid() value
create temp table temp_last_id (last_id integer);

-- Insert rows into foo and capture the last_insert_rowid()
insert into foo default values;
insert into foo default values;
insert into foo default values;

-- Store the last_insert_rowid() in the temporary table
insert into temp_last_id (last_id) values (last_insert_rowid());

-- Insert multiple rows into bar using the stored last_insert_rowid() value
insert into bar (lir)
select last_id from temp_last_id
union all
select last_id from temp_last_id;

-- Clean up the temporary table
drop table temp_last_id;

select * from foo;
select * from bar;

In this approach, a temporary table named temp_last_id is created to store the last_insert_rowid() value. After inserting rows into the foo table, the last_insert_rowid() value is stored in the temp_last_id table. This value is then used to insert multiple rows into the bar table. Since the last_insert_rowid() value is stored in a temporary table, it remains consistent across all inserts into bar.

4. Using the RETURNING Clause (If Supported)

In environments where transactions are supported, the RETURNING clause can be used to capture the last_insert_rowid() value directly from the insert statement. This approach is both efficient and reliable, as it avoids the need for a CTE or temporary table.

begin transaction;

create table foo (foo_id integer primary key);
create table bar (bar_id integer primary key, lir integer);

-- Insert rows into foo and capture the last_insert_rowid() using RETURNING
insert into foo default values returning foo_id into @last_id;
insert into foo default values;
insert into foo default values;

-- Insert multiple rows into bar using the captured last_insert_rowid() value
insert into bar (lir)
values (@last_id), (@last_id);

select * from foo;
select * from bar;

rollback;

In this approach, the RETURNING clause is used to capture the foo_id of the most recently inserted row into the foo table and store it in a variable @last_id. This value is then used to insert multiple rows into the bar table. The RETURNING clause ensures that the last_insert_rowid() value is captured and reused consistently.

Conclusion

The behavior of CTEs and the last_insert_rowid() function in SQLite can lead to unexpected results when attempting to capture and reuse the most recently inserted rowid. By understanding the underlying causes of this behavior and employing one of the solutions outlined above, developers can ensure that the last_insert_rowid() value is captured and reused consistently across multiple inserts. Each approach has its own trade-offs, and the choice of solution will depend on the specific requirements and constraints of the application. Whether using a temporary variable, a materialized CTE, a temporary table, or the RETURNING clause, developers can achieve the desired behavior while maintaining the integrity and consistency of their data.