alter col leaves sqlitesequence table behind note that i'm unsure of alter column rules,…
- User
alter col leaves sqlite_sequence table behind note that i'm unsure of alter column rules, i was not able to find any docs or rules around this, but my expectation is when we change a autoincrement col to plain text there shouldn't be a stale sqlite_sequence.
turso> CREATE TABLE t(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); INSERT INTO t(b) VALUES('x'),('y'); ALTER TABLE t ALTER COLUMN a TO a2 TEXT; SELECT * FROM sqlite_sequence; SELECT sql FROM sqlite_schema WHERE name='t'; ┌──────┬─────┐ │ name │ seq │ ├──────┼─────┤ │ t │ 2 │ └──────┴─────┘ ┌──────────────────────────────────┐ │ sql │ ├──────────────────────────────────┤ │ CREATE TABLE t (a2 TEXT, b TEXT) │ └──────────────────────────────────┘ turso> select * from sqlite_schema; ┌───────┬─────────────────┬─────────────────┬──────────┬────────────────────────────────────────┐ │ type │ name │ tbl_name │ rootpage │ sql │ ├───────┼─────────────────┼─────────────────┼──────────┼────────────────────────────────────────┤ │ table │ sqlite_sequence │ sqlite_sequence │ 2 │ CREATE TABLE sqlite_sequence(name,seq) │ ├───────┼─────────────────┼─────────────────┼──────────┼────────────────────────────────────────┤ │ table │ t │ t │ 3 │ CREATE TABLE t (a2 TEXT, b TEXT) │ └───────┴─────────────────┴─────────────────┴──────────┴────────────────────────────────────────┘Interface
No new interfaces are introduced.
- Assistant
Now let me look at how DROP TABLE cleans up sqlite_sequence, for reference:
- Assistant
Let me look at
emit_rename_sqlite_sequence_entryin alter.rs and check how insert useshas_autoincrement: - Assistant
Now let me reproduce the issue and check current in-session behavior:
- Assistant
Now I'll make the edits. First, the helper in
alter.rs: - Assistant
Now compute the condition and emit the deletion in the AlterColumn branch:
- Assistant
Now the schema SQL rewrite in execute.rs. Check the AST definition first:
- Assistant
Now the in-memory schema update in
op_alter_column: - Assistant
Found it — in
op_alter_column, the column is replaced (line 13056) before my check readsis_rowid_alias(), so it sees the new column. I need to capture the flag before replacement: - Assistant
Now add tests to
alter_column.sqltest: - Assistant
That ran suspiciously fast — the filter may not have matched anything. Let me verify tests actually run:
- Assistant
Let me verify the
@skip-if mvccdirective actually works as expected when the runner is in MVCC mode: - Assistant
Everything checks out. Let me run the full sqltest suite as a final sweep since the schema-rewrite change touches a shared code path:
- Assistant
Final check — run the TCL conformance alter tests and review the complete diff:
- Assistant
TCL isn't available in this environment, but the preferred sqltest runner covers this fully. Let me review the final diff:
- Assistant
Done. The fix is complete and verified.
Summary
Your expectation was right: when ALTER COLUMN targets the AUTOINCREMENT column, the replacement definition can never carry AUTOINCREMENT (a
PRIMARY KEYconstraint in the new definition is explicitly rejected), so the table always loses the autoincrement property — and thesqlite_sequencerow must go with it. Investigating also surfaced two adjacent bugs in the same path.Fixes
1. Stale
sqlite_sequencerow (reported bug) —core/translate/alter.rsWhen ALTER COLUMN targets the autoincrement rowid-alias column, the program now deletes the table'ssqlite_sequenceentry (newemit_delete_sqlite_sequence_entry, mirroring the existing rename-table handling and DROP TABLE cleanup).2. Schema corruption with table-level PK (found while fixing #1) —
core/vdbe/execute.rsschema rewriteCREATE TABLE t(a INTEGER, b TEXT, PRIMARY KEY(a AUTOINCREMENT))+ALTER TABLE t ALTER COLUMN a TO a2 TEXTpreviously produced... PRIMARY KEY (a2 AUTOINCREMENT)on a TEXT column — invalid SQL that made the database unopenable (Parse error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY). The rewrite now strips theAUTOINCREMENTkeyword from a table-level PK when its column is altered (rename keeps it).3. Same-connection staleness —
core/vdbe/execute.rsop_alter_columnThe in-memory schema kepthas_autoincrement = true, so inserts after the ALTER on the same connection kept maintaining (even recreating) the sequence row. It's now cleared when the altered column was the rowid alias.Verification
- Your exact repro:
sqlite_sequenceis empty after the ALTER, stays empty after further inserts, on both the same and fresh connections;PRAGMA integrity_checkok - The table-level PK database now reopens correctly
- Rename of the autoincrement column and ALTER of unrelated columns correctly keep the sequence entry; only the altered table's row is removed
- 4 new tests in
testing/sqltests/turso-tests/alter_column.sqltest(skipped under MVCC, where AUTOINCREMENT is unsupported); full sqltest suite: 10529 passed, 0 failed with the prescribed--snapshot-filter __never__ cargo fmt+cargo clippy -p turso_core --all-features --all-targets --deny=warningsclean
Note: the 8 snapshot mismatches seen when running snapshot tests are pre-existing build-profile artifacts — the committed snapshots embed the release hash-join budget (
67108864) while debug builds use32768(core/vdbe/hash_table.rs:27), which is why the project's standard test command excludes them. - Your exact repro:
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.
Identical first prompt
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- fable-5Compare side by side →
- fable-5Compare side by side →