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.