SQLite changes() Returns 0 After CREATE TABLE AS SELECT (CTAS)

Understanding the Discrepancy Between CTAS and changes() Behavior

Core Problem: changes() Does Not Reflect Rows Inserted via CTAS

The SQLite changes() function is designed to report the number of rows affected by the most recent INSERT, UPDATE, or DELETE operation. However, when using the CREATE TABLE AS SELECT (CTAS) statement, changes() returns 0 instead of the number of rows inserted into the newly created table. This behavior conflicts with expectations derived from other relational database systems (e.g., PostgreSQL), where CTAS is treated as a hybrid operation that combines table creation and row insertion, with the latter triggering row-count reporting mechanisms.

For example:

CREATE TABLE new_table AS SELECT * FROM source_table;
SELECT changes(); -- Returns 0 instead of the row count from source_table

This creates confusion for developers relying on changes() for auditing or logging purposes after bulk operations. The root cause lies in SQLite’s classification of CTAS as a DDL (Data Definition Language) operation rather than a DML (Data Manipulation Language) operation. While CTAS does insert rows internally, the SQLite engine treats it as a single CREATE statement, which does not update the changes() counter.

Technical Basis for changes() Behavior with CTAS

1. SQLite’s Operational Classification of CTAS

The changes() function explicitly excludes DDL operations. This is a deliberate design choice documented in SQLite’s official specifications:

"The changes() function returns the number of rows modified by the most recent INSERT, UPDATE, or DELETE statement… Only changes made directly by the INSERT, UPDATE, or DELETE statement are considered."

CTAS is parsed as a CREATE TABLE command followed by an implicit INSERT…SELECT operation. However, SQLite’s internal implementation treats CTAS as an atomic CREATE operation. The implicit insertion of rows during table creation is not exposed to the changes() function. This behavior is consistent across all SQLite versions and is not considered a bug but a documented limitation.

2. Historical and Compatibility Considerations

Altering changes() to include CTAS insertions would violate SQLite’s strict backward-compatibility guarantees. The current behavior has persisted for over two decades, and countless applications depend on changes() returning 0 after DDL operations. For instance, tools that use changes() to detect unintended data modifications during schema migrations would malfunction if CTAS suddenly incremented the counter.

3. Internal Execution Flow of CTAS

When SQLite processes a CTAS statement:

1. The parser identifies it as a CREATE TABLE command.
2. The table structure is defined based on the SELECT clause’s result set.
3. The SELECT query is executed, and rows are inserted into the new table.
4. The operation is finalized as a CREATE statement, not an INSERT.

Since changes() only reflects the row count from the last completed DML operation, the implicit insertion during CTAS is not registered. The function’s internal counter is reset to 0 after the CREATE operation completes.

Workarounds and Solutions for Tracking CTAS Row Counts

1. Explicitly Separate Table Creation and Data Insertion

Instead of using CTAS, break the operation into discrete steps:

-- Step 1: Create the table structure
CREATE TABLE new_table (column1 INT, column2 TEXT);

-- Step 2: Insert data from the source table
INSERT INTO new_table SELECT * FROM source_table;

-- Step 3: Retrieve the row count using changes()
SELECT changes(); -- Returns the number of inserted rows

This approach ensures changes() accurately reflects the row count. However, it requires prior knowledge of the source table’s schema, which may not always be feasible.

2. Use total_changes() for Session-Level Tracking

The total_changes() function provides a cumulative count of all rows inserted, updated, or deleted during the current database connection. To isolate the impact of a CTAS-like operation:

-- Record the initial total_changes() value
SELECT total_changes() AS before_count;

-- Execute CTAS
CREATE TABLE new_table AS SELECT * FROM source_table;

-- Calculate the difference
SELECT total_changes() - before_count AS rows_inserted;

Limitations: This method includes all changes made during the session, not just those from the CTAS. It is unsuitable for concurrent environments.

3. Trigger-Based Row Counting

Create a temporary trigger to log row insertions during CTAS:

-- Step 1: Create a temporary table to store the row count
CREATE TEMP TABLE audit_log (rows_inserted INT);

-- Step 2: Create a trigger that increments a counter on INSERT
CREATE TEMP TRIGGER ctas_audit AFTER INSERT ON new_table
BEGIN
  UPDATE audit_log SET rows_inserted = rows_inserted + 1;
END;

-- Step 3: Initialize the counter
INSERT INTO audit_log VALUES (0);

-- Step 4: Execute CTAS
CREATE TABLE new_table AS SELECT * FROM source_table;

-- Step 5: Retrieve the row count
SELECT rows_inserted FROM audit_log;

-- Cleanup
DROP TRIGGER ctas_audit;
DROP TABLE audit_log;

This method provides an accurate row count but adds complexity and overhead.

4. Post-Hoc Row Count Query

After executing CTAS, run a separate SELECT COUNT(*) query:

CREATE TABLE new_table AS SELECT * FROM source_table;
SELECT COUNT(*) FROM new_table;

Drawbacks: This requires an additional table scan, which is inefficient for large datasets.

5. Python-Specific Workarounds

When using Python’s sqlite3 module, leverage Connection.total_changes:

import sqlite3

conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

# Get initial total changes
before = conn.total_changes

# Execute CTAS
cursor.execute("CREATE TABLE new_table AS SELECT * FROM source_table")

# Calculate inserted rows
inserted_rows = conn.total_changes - before
print(f"Rows inserted: {inserted_rows}")

Note: Like total_changes(), this includes all changes in the session.

6. Modify Application Logic to Avoid CTAS

Redesign workflows to use temporary tables or pre-defined schemas, enabling explicit INSERT operations that work with changes().

Key Takeaways

1. changes() is DML-Specific: It only reflects row counts from INSERT, UPDATE, or DELETE operations.
2. CTAS is DDL: Despite its implicit insertion of rows, CTAS is classified as a schema modification, not a data modification.
3. Workarounds Exist: Use explicit INSERTs, triggers, total_changes(), or post-hoc counts to track CTAS row counts.
4. Backward Compatibility Prevails: SQLite prioritizes stability over altering longstanding behavior, even if unintuitive.

Developers must adjust their auditing strategies to account for SQLite’s operational distinctions. While CTAS offers syntactic convenience, its classification as DDL necessitates alternative approaches for row-count tracking.