Resolving NOT NULL Constraint Failures When Using unixepoch(‘subsec’) in SQLite 3.42 Upserts

Understanding NOT NULL Constraint Violations During Upsert Operations with unixepoch(‘subsec’) Defaults

Issue Overview: Conflicting SQLite Engine Versions and Default Expression Evaluation
The core problem arises when attempting to perform an INSERT ... ON CONFLICT (upsert) operation on a strict table containing a NOT NULL column with a default value using unixepoch('subsec'). The schema definition includes:

CREATE TABLE foo_bars
(
  foo_id INTEGER NOT NULL,
  bar_id INTEGER NOT NULL,
  updated_at INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT (unixepoch() * 1000),
  UNIQUE (foo_id, bar_id)
) STRICT;

When modified to use unixepoch('subsec') instead of the standard unixepoch(), the INSERT operation fails with SQLITE_CONSTRAINT_NOTNULL when executed through certain drivers (e.g., JDBC/ODBC), despite working in the SQLite CLI. This discrepancy stems from four interrelated factors:

1. Version-Specific Function Behavior:
   The unixepoch('subsec') modifier was introduced in SQLite 3.42.0 (2023-05-16). Older engine versions treat the 'subsec' argument as invalid, causing the function to return NULL. Since updated_at is declared NOT NULL in a strict table, this triggers a constraint violation.

2. Driver/Engine Version Mismatch:
   Applications often bundle their own SQLite engine versions. A JDBC/ODBC driver might link against an older SQLite library (e.g., 3.41.2) while the CLI uses 3.42.0+. This creates an environment where default expressions behave differently across execution contexts.

3. Default Expression Evaluation During Upserts:
   When using ON CONFLICT DO NOTHING, SQLite still evaluates default expressions for all columns not explicitly provided in the INSERT statement before checking for conflicts. If unixepoch('subsec') returns NULL due to version incompatibility, the NOT NULL constraint fails before the conflict resolution logic activates.

4. Type Strictness and Floating-Point Conversion:
   unixepoch('subsec') returns a floating-point value representing seconds with millisecond precision. Multiplying by 1000 (e.g., unixepoch('subsec') * 1000) may produce non-integer values (e.g., 1686423195.123 * 1000 = 1686423195123.0). Strict tables require integer values for INTEGER columns, but SQLite allows implicit conversion of float-to-integer only if the float represents an exact integer. Edge cases with fractional parts would fail in strict mode.

Diagnosing Version Incompatibilities and Expression Evaluation Failures
The error manifests under these specific conditions:

• Outdated SQLite Engine in Data Access Layer:
  Application-layer drivers (JDBC/ODBC) using pre-3.42.0 libraries cannot parse the 'subsec' modifier. This converts unixepoch('subsec') into an invalid function call, returning NULL and violating the NOT NULL constraint.

• Implicit NULL Assignment in Strict Tables:
  Strict tables prohibit type affinity conversions. If the default expression evaluates to NULL or an incompatible type (e.g., floating-point when integer is required), the insertion fails immediately, bypassing conflict resolution.

• Transaction Scope and Error Reporting:
  Auto-commit modes in CLIs often mask errors that manifest in application-level transactions. Bulk operations may fail at the first constraint violation, making it appear environment-specific when the root cause is version-related.

Comprehensive Solutions: Version Alignment, Backward Compatibility, and Type Safety

Step 1: Validate SQLite Versions Across All Execution Environments
Execute version checks in both CLI and application contexts:

-- CLI or Application Connection
SELECT sqlite_version(), sqlite_source_id();

For JDBC (Java example):

try (Statement stmt = connection.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT sqlite_version(), sqlite_source_id()")) {
    if (rs.next()) {
        System.out.println("Version: " + rs.getString(1));
        System.out.println("Source ID: " + rs.getString(2));
    }
}

Expected Output for Compatibility:

3.42.0 | 2023-05-16 12:36:15 831d0fb2836b71c9bc51067c49fee4b8f18047814f2ff22d817d25195cf350b0

If the application reports an older version (e.g., 3.41.2), upgrade the embedded SQLite library or driver.

Step 2: Implement Backward-Compatible Default Expressions
If immediate engine upgrades are impractical, rewrite the default expression to function across versions:

-- Use strftime('%s','now','subsec') for broader compatibility
updated_at INTEGER NOT NULL ... DEFAULT (CAST(strftime('%s','now','subsec') AS REAL) * 1000)

Rationale:

• strftime('%s', ...) with 'subsec' is recognized in older versions (pre-3.42.0) as a valid modifier, though documentation may not explicitly state this.
• Explicit CAST ensures the result is treated as a REAL (float), avoiding integer truncation during multiplication.

Step 3: Enforce Integer Type Safety in Strict Tables
Modify the default expression to guarantee integer values:

updated_at INTEGER NOT NULL ... DEFAULT (CAST(unixepoch('subsec') * 1000 AS INTEGER))

Key Consideration:
Explicit casting avoids strict type errors when the floating-point multiplication yields non-integer values (unlikely with unixepoch('subsec') * 1000, but possible with other expressions).

Step 4: Conditional Default Expressions Using SQL Comments
For schemas requiring cross-version compatibility, use a comment-based dispatch pattern:

updated_at INTEGER NOT NULL ... DEFAULT (
  CAST(
    /* Use subsec if available */
    CASE WHEN sqlite_version() >= '3.42.0' 
         THEN unixepoch('subsec') 
         ELSE strftime('%s','now','subsec') 
    END * 1000 
    AS INTEGER)
)

Note: SQLite ignores comments in expressions, so this pattern requires app-side schema management or migration scripts.

Step 5: Verify Driver-Level Type Handling
Some JDBC/ODBC drivers mishandle SQLite’s dynamic typing. Explicitly bind NULL values as java.sql.Types.INTEGER (Java) or equivalent in other languages to avoid type inference errors:

PreparedStatement ps = connection.prepareStatement(
    "INSERT INTO foo_bars (foo_id, bar_id, updated_at) VALUES (?, ?, ?) " +
    "ON CONFLICT DO NOTHING");
ps.setInt(1, 1);
ps.setInt(2, 2);
ps.setNull(3, java.sql.Types.INTEGER); // Force NULL as INTEGER type
ps.executeUpdate();

Step 6: Audit Transaction Isolation Levels
While not directly related to the constraint error, transaction isolation levels can affect error reporting. Ensure auto-commit modes and transaction boundaries in the application match those in the CLI during testing.

Final Schema Recommendation for Cross-Version Reliability

CREATE TABLE foo_bars
(
  foo_id INTEGER NOT NULL,
  bar_id INTEGER NOT NULL,
  updated_at INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT (
    CAST(
      (CASE 
        WHEN (SELECT sqlite_version() >= '3.42.0') 
        THEN unixepoch('subsec') 
        ELSE strftime('%s','now','subsec') 
       END) * 1000 
      AS INTEGER)
  ),
  UNIQUE (foo_id, bar_id)
) STRICT;

This schema guarantees that updated_at always receives an integer timestamp with millisecond precision, regardless of the underlying SQLite version, while adhering to strict type constraints.