How does SQLite’s dynamic typing and three-valued NULL logic bite: affinity, STRICT tables, IN and NOT IN?

13.9.2 · L3 · Interview

Predict first

What do mixed types, NULL comparisons and a STRICT table do?

import Foundation
import SQLite3

final class DB {
    var h: OpaquePointer?
    init(_ path: String = ":memory:", busy: Int32 = 0) { sqlite3_open(path, &h); sqlite3_busy_timeout(h, busy) }
    deinit { sqlite3_close(h) }
    @discardableResult func exec(_ sql: String) -> String? {
        var err: UnsafeMutablePointer<CChar>?
        if sqlite3_exec(h, sql, nil, nil, &err) != SQLITE_OK { defer { sqlite3_free(err) }; return String(cString: err!) }
        return nil
    }
    func rows(_ sql: String) -> [[String]] {
        var st: OpaquePointer?; var out: [[String]] = []
        guard sqlite3_prepare_v2(h, sql, -1, &st, nil) == SQLITE_OK else { return [["error: " + String(cString: sqlite3_errmsg(h))]] }
        defer { sqlite3_finalize(st) }
        while sqlite3_step(st) == SQLITE_ROW {
            out.append((0..<sqlite3_column_count(st)).map { i in sqlite3_column_text(st, i).map { String(cString: $0) } ?? "NULL" })
        }
        return out
    }
}

let db = DB()
db.exec("CREATE TABLE t(a INTEGER, b TEXT)")
db.exec("INSERT INTO t VALUES('42', 7), ('abc', 'x'), (NULL, NULL), (3.7, 'y')")
print(db.rows("SELECT a, typeof(a), b, typeof(b) FROM t").map { $0.joined(separator: ",") })
print("NULL = NULL:", db.rows("SELECT NULL = NULL, NULL IS NULL, 1 IN (NULL, 1), 2 NOT IN (NULL, 1)")[0])
db.exec("CREATE TABLE s(n INTEGER) STRICT")
print("STRICT rejects text in an INTEGER column:", db.exec("INSERT INTO s VALUES('abc')") ?? "accepted")
  • mixed storage types / NULL = NULL gives NULL, NOT IN with a NULL gives NULL / STRICT rejects text in an INTEGER column
  • Mixed types are rejected in a normal table
  • NULL = NULL is true

Mental model. SQLite ships with the safety rails folded down: they exist and work, but you have to raise them yourself on every connection.

Typing is dynamic. A column’s declared type is an affinity, not a constraint: in the check an INTEGER column accepts the string 'abc' (stored as text), the string '42' is converted to integer 42, 3.7 stays a real, and a TEXT column stores 7 as the text '7'. Use STRICT tables (SQLite 3.37+) or CHECK(typeof(x) = 'integer') to enforce types, and validate in your model layer. NULL logic is three-valued: NULL = NULL is NULL (not true), so use IS NULL; 1 IN (NULL, 1) is true but 2 NOT IN (NULL, 1) is NULL, which a WHERE treats as false, so a stray NULL in a NOT IN list makes the filter match nothing. Also remember NOT NULL and UNIQUE allow multiple NULLs in a unique column. Write a migration test that opens the database with your real connection settings, not a bare one.

Declared types are affinities, NULL comparisons are three-valued, and STRICT tables enforce the declared type.

Common traps

  • Trusting the declared column type to reject bad data. In a normal table it is only an affinity; use STRICT or a CHECK.
  • Using NOT IN against a subquery that can return NULL. The whole predicate becomes unknown and matches nothing; use NOT EXISTS.

They might follow up with

  • What does STRICT change? It rejects values whose type does not match the declared column type (the check shows the error).
  • Is NULL allowed twice in a UNIQUE column? Yes; NULLs are considered distinct from each other.

Related questions

  • 13.9.1 How do you read EXPLAIN QUERY PLAN, and which common query shapes stop SQLite using an index?
  • 13.9.4 How do savepoints and constraint failures behave inside a transaction, and when do you use FTS5 instead of…
  • 13.2.1 How do SQLite transactions, journal modes (WAL) and constraints work, and which defaults surprise people?
  • 13.2.2 How do you use SQLite safely and quickly: bound parameters, indexes, and schema migrations?

← How do you read EXPLAIN QUERY PLAN, and which common query shapes…   How does write-ahead logging change locking, what does SQLITE_BUSY… →