Detecting Non-Deterministic Expressions in SQLite Generated Columns and Runtime Contexts

Issue Overview: Discrepancies in Determinism Checks for Generated Columns and Runtime Errors

SQLite enforces strict rules regarding deterministic expressions when defining generated columns. A function or expression is considered deterministic if it always produces the same output given the same inputs and database state. This property is critical for generated columns because their values are computed during write operations and stored persistently. The original discussion highlights two key observations:

1. The random() function is immediately rejected during table creation when used in a generated column due to its non-deterministic nature.
2. The julianday('now') expression is accepted during table creation but triggers a runtime error during data insertion.

This discrepancy raises fundamental questions about SQLite’s determinism validation logic. Why does SQLite allow julianday('now') in generated column definitions if it ultimately causes insertion failures? The answer lies in SQLite’s layered validation process. When creating a table with a generated column:

• Parse-Time Checks: SQLite performs basic syntactic validation and rejects functions explicitly marked as non-deterministic in their registration (e.g., random()).
• Runtime Checks: For functions that are conditionally non-deterministic (like julianday('now')), validation occurs during DML operations when actual parameter binding and expression evaluation happen.

The julianday() function itself is deterministic when given constant arguments, but becomes non-deterministic when using time modifiers like 'now'. SQLite’s static analysis during table creation cannot always detect these contextual non-determinisms because the evaluation context (current time) isn’t known until runtime. This dual-phase validation creates a scenario where schema modifications succeed but data operations fail, requiring developers to implement additional safeguards when working with generated columns.

Possible Causes: Function Registration Flags and Deferred Expression Evaluation

The observed behavior stems from SQLite’s internal handling of function determinism and the limitations of static analysis in certain expression contexts.

1. Function Registration Characteristics

SQLite functions are registered with flags that declare their determinism. The random() function is explicitly registered with SQLITE_DIRECTONLY | SQLITE_FUNC_UNSAFE, marking it as non-deterministic. In contrast, julianday() is registered as deterministic (SQLITE_DETERMINISTIC) because its core calculation is mathematically precise when given fixed inputs. However, when used with dynamic modifiers like 'now', the expression inherits non-determinism from external state (system clock). SQLite cannot statically detect this contextual non-determinism during table creation.

2. SQLite’s Two-Phase Validation Approach

Generated column expressions undergo two validation stages:

• Schema Creation Phase: Syntax checking and basic determinism verification using function registration flags. Expressions using explicitly non-deterministic functions (e.g., random()) are rejected.
• Row Insertion/Update Phase: Full expression evaluation with concrete values and runtime context. This phase catches dependencies on volatile state like CURRENT_TIMESTAMP or 'now' modifiers.

This separation allows SQLite to optimize schema parsing but defers more expensive validation to data modification operations. The trade-off is that some non-deterministic expressions pass initial schema checks but fail later during actual usage.

3. Limitations of the Proposed Testing Function

The custom isDeterministic function attempts to detect non-determinism by:

1. Creating a temporary table with a generated column using the test expression
2. Inserting a row to trigger runtime validation
3. Checking for execution errors

While this approach works for many cases, it has critical flaws:

• False Negatives: Expressions causing errors unrelated to determinism (e.g., syntax errors, type mismatches) would incorrectly return 0 (non-deterministic)
• False Positives: The function returns 1 only if the entire SQL batch (create table + insert + drop) succeeds, potentially missing deterministic expressions that fail for other reasons
• Resource Contention: Temporary table creation in concurrent environments could lead to race conditions
• Version Sensitivity: Behavior may change across SQLite versions as error codes and determinism checks evolve

Troubleshooting Steps, Solutions & Fixes: Comprehensive Determinism Analysis and Workarounds

Step 1: Understand SQLite’s Determinism Hierarchy

SQLite categorizes functions and expressions into three determinism levels:

Generated columns prohibit both contextually and explicitly non-deterministic expressions, but SQLite only enforces this for explicitly marked functions during schema creation.

Step 2: Implement Robust Determinism Testing

Modify the isDeterministic function to isolate determinism-related errors:

int isDeterministic(sqlite3 *db, const char *expr) {
    sqlite3_stmt *stmt = NULL;
    char *create_sql = sqlite3_mprintf(
        "CREATE TEMP TABLE __tst__(c, g AS (%s));",
        expr
    );
    int rc = sqlite3_prepare_v2(db, create_sql, -1, &stmt, 0);
    sqlite3_free(create_sql);
    
    if (rc != SQLITE_OK) {
        // Check if error message indicates non-determinism
        const char *err = sqlite3_errmsg(db);
        if (strstr(err, "non-deterministic")) {
            sqlite3_finalize(stmt);
            return 0;
        }
        // Other errors are treated as indeterminable
        sqlite3_finalize(stmt);
        return -1; 
    }
    sqlite3_finalize(stmt);
    
    // Test runtime determinism with actual insertion
    char *insert_sql = "INSERT INTO __tst__(c) VALUES (0);";
    rc = sqlite3_exec(db, insert_sql, 0, 0, 0);
    sqlite3_exec(db, "DROP TABLE IF EXISTS temp.__tst__;", 0, 0, 0);
    
    return (rc == SQLITE_OK) ? 1 : 0;
}

This improved version:

1. Separates schema creation and data insertion phases
2. Checks error messages for determinism-specific indicators
3. Returns -1 for inconclusive results due to unrelated errors
4. Uses prepared statements to avoid SQL injection vulnerabilities

Step 3: Leverage SQLite Metadata for Function Determinism

Query registered functions’ determinism flags using PRAGMA function_list:

SELECT name, deterministic 
FROM pragma_function_list 
WHERE name IN ('random','julianday','abs');

Sample output:

name      | deterministic  
----------|-------------  
random    | 0  
julianday | 1  
abs       | 1  

This reveals that julianday is considered deterministic at registration, explaining why it passes initial schema checks. To detect contextual non-determinism, analyze expression structure for volatile modifiers:

int containsVolatileModifier(const char *expr) {
    const char *modifiers[] = {"'now'", "current_time", "current_date", 
                               "current_timestamp", "localtime", "utc"};
    for (int i = 0; i < sizeof(modifiers)/sizeof(modifiers[0]); i++) {
        if (strcasestr(expr, modifiers[i])) {
            return 1;
        }
    }
    return 0;
}

Combine this with the registration flag check to create a hybrid determinism detector.

Step 4: Use SQLite’s Expression Affinity System

Generated columns require deterministic expressions because their values affect storage affinity. Exploit this by attempting to create indexes on the generated column:

CREATE TABLE tst(c INT, g AS (julianday('now')));  
CREATE INDEX idx_tst_g ON tst(g);  -- Fails with SQLITE_ERROR  

The index creation fails because SQLite requires index expressions to be strictly deterministic. This provides another validation layer but requires careful error handling.

Step 5: Employ SQLite Authorizer Callbacks

For advanced use cases, use the sqlite3_set_authorizer API to intercept function usage during expression parsing:

int authorizer(void *userData, int actionCode, const char *arg1, 
               const char *arg2, const char *dbName, const char *triggerName) {
    if (actionCode == SQLITE_FUNCTION) {
        if (strcmp(arg1, "random") == 0) {
            return SQLITE_DENY;
        }
        if (strcmp(arg1, "julianday") == 0) {
            // Inspect expression context here
            return SQLITE_IGNORE;
        }
    }
    return SQLITE_OK;
}

// Usage:
sqlite3_set_authorizer(db, authorizer, NULL);

This allows real-time function usage monitoring but requires deep integration with the SQL parsing process.

Step 6: Utilize EXPLAIN Statements for Expression Analysis

SQLite’s bytecode engine provides insights into function determinism through EXPLAIN output:

EXPLAIN SELECT julianday('now');  

Inspect the Function opcode flags in the output. Deterministic functions include the SQLITE_FUNC_DETERMINISTIC flag. While this requires parsing bytecode, it offers definitive determinism status.

Step 7: Develop a Hybrid Validation Workflow

Combine static analysis, runtime checks, and metadata inspection:

1. Static Analysis Phase:

   • Check function registration flags via PRAGMA function_list
   • Scan for volatile modifiers like 'now'
   • Verify expression syntax validity

2. Runtime Validation Phase:

   • Attempt temporary generated column creation
   • Perform test insertion with multiple transactions
   • Check for consistency across repeated evaluations

3. Fallback Mechanisms:

   • Use authorizer callbacks to track function usage
   • Analyze EXPLAIN output for determinism flags
   • Implement whitelists/blacklists for known volatile patterns

Step 8: Address SQLite Version-Specific Behaviors

Determinism checks have evolved across SQLite versions:

• Prior to 3.38.0: Less rigorous checking of contextual non-determinism
• 3.38.0+: Enhanced detection of time-related functions in generated columns
• 3.45.0+: Stricter enforcement of determinism in virtual tables

Always verify behavior against the target SQLite version using:

SELECT sqlite_version();  

Final Recommendations

1. Prefer Literal Values Over Dynamic Modifiers: Replace julianday('now') with explicit timestamp literals when possible.
2. Use Triggers Instead of Generated Columns: For complex logic involving contextual state, employ AFTER INSERT/UPDATE triggers.
3. Implement Application-Level Validation: Check expressions for volatile patterns before passing to SQLite.
4. Adopt Defensive Schema Design: Wrap generated columns in deterministic wrapper functions that validate inputs.

By combining SQLite’s built-in mechanisms with careful expression analysis and runtime checks, developers can reliably detect both explicit and contextual non-determinism in generated columns and other expression contexts.