SQLite runs infinetly in 1.8.1 and not in 1.8.0

[guilhermebodin]

I had a problem of SQLite running forever in a migration inside a private codebase. Asked Claude to figure out what was happening and it pointed out that the version 1.8.1 was the problem.

Here was Claude answer:

I traced it. SQLite is not the culprit — the C library behaves identically in 3.48.0 and 3.53.2. The regression is in SQLite.jl, which the same commit bumped 1.6.1 →
 1.8.1.

 What actually happens

 Migration 2 doesn't error — it hangs (infinite loop) on this statement:

 ALTER TABLE ProcessInPlant ADD COLUMN refurbishment_lifetime INTEGER

 Minimal reproducer (test/… not needed):

 db = SQLite.DB()
 DBInterface.execute(db, "CREATE TABLE t (a INTEGER) STRICT")
 DBInterface.execute(db, "ALTER TABLE t ADD COLUMN b INTEGER") # spins forever

 Drop STRICT, or use SQLite.execute instead of DBInterface.execute, and it completes.

 Root cause

 ALTER TABLE … ADD COLUMN on a STRICT table is not a zero-column statement. SQLite compiles a hidden result column to validate existing rows against the new column's
 type:

 ncol=1 name="CASE WHEN quick_check GLOB 'CHECK*' THEN raise(ABORT,…) … END"
 step1=SQLITE_DONE coltype=SQLITE_NULL
 step2=SQLITE_ERROR "duplicate column name: b" ← re-stepping auto-resets and re-runs the DDL

 I verified this via ctypes against all three DLLs — 3.48.0, 3.51.2, and 3.53.2 produce byte-identical output. This behavior is old, not new.
 ALTER TABLE … ADD COLUMN on a STRICT table is not a zero-column statement. SQLite compiles a hidden result column to validate existing rows against the new column's type:

 ncol=1 name="CASE WHEN quick_check GLOB 'CHECK*' THEN raise(ABORT,…) … END"
 step1=SQLITE_DONE coltype=SQLITE_NULL
 step2=SQLITE_ERROR "duplicate column name: b" ← re-stepping auto-resets and re-runs the DDL

 I verified this via ctypes against all three DLLs — 3.48.0, 3.51.2, and 3.53.2 produce byte-identical output. This behavior is old, not new.

 What is new is juliatype in SQLite.jl 1.8.1 (SQLite.jl:481-495), called unconditionally for every result column in DBInterface.execute (tables.jl:178):

 while stored_typeid == C.SQLITE_NULL
 st = C.sqlite3_step(handle)
 if st == C.SQLITE_DONE; break; end # only DONE exits
 stored_typeid = C.sqlite3_column_type(handle, col - 1)
 if stored_typeid != C.SQLITE_NULL; break; end
 end

 The column is NULL, so it scans forward. sqlite3_step after SQLITE_DONE auto-resets and re-executes the DDL, which now fails with SQLITE_ERROR — never SQLITE_DONE, column still NULL → the loop
 never terminates. That forward-scan loop is absent in 1.6.1 and in 1.8.0; it was introduced in 1.8.1 only.

 Fix

 Pin SQLite.jl to 1.8.0 — it's compatible with SQLite_jll 3.53 and PSRDatabase's SQLite = "1.6.1" bound, so you keep the rest of the dependency update:

 julia --project -e 'using Pkg; Pkg.add(name="SQLite", version="1.8.0")'

 (adds an explicit SQLite dep + compat so the resolver can't drift back to 1.8.1)

I can confirm that going to SQLite version 1.8.0 solved the problem.

Analysing the changes it seems that if the column has every value null it does run forever.