Inconsistent Query Results from Constant Propagation and Affinity Conflicts in SQLite

Understanding Query Result Inconsistencies in SQLite

This guide examines a critical behavior discrepancy observed when comparing WHERE clause evaluations against SELECT expression results in SQLite 3.31.1 and development builds. The core conflict arises from three fundamental SQLite mechanisms: type affinity resolution, constant propagation optimizations, and implicit string conversion rules. A table containing REAL and dynamically typed columns demonstrates how identical logical conditions produce divergent truth values based on their execution context – an issue patched in later SQLite versions but requiring deep understanding for legacy system maintenance.

The problem manifests through two seemingly equivalent queries against a table with REAL and affinity-less columns. Though both attempt to filter rows where a REAL-stored value matches integer 10 through equality and LIKE operators, one returns matches while the other rejects them. This occurs because:

1. WHERE clauses undergo constant propagation that SELECT expressions don’t receive
2. REAL-to-TEXT conversions differ from INTEGER-to-TEXT handling
3. Optimization phases rewrite conditions before execution

CREATE TABLE v0 (v2 REAL, v1);  -- v2 has REAL affinity; v1 is typeless
INSERT INTO v0 VALUES (10, 11);
UPDATE v0 SET v1 = v2;          -- Stores 10.0 in v1 due to REAL affinity propagation

When querying SELECT * FROM v0 WHERE v1 = 10 AND v1 LIKE 10, SQLite’s query planner transforms the condition to WHERE 10 LIKE 10 AND v1 = 10 through constant propagation. This evaluates to TRUE because:

• 10 LIKE 10 becomes '10' LIKE '10' (TRUE)
• v1 = 10 compares REAL 10.0 to INTEGER 10 using numeric equality (TRUE)

However, SELECT v1 = 10 AND v1 LIKE 10 FROM v0 calculates the expression without optimizations:

• v1 = 10 → TRUE (numeric comparison)
• v1 LIKE 10 → Converts REAL 10.0 to TEXT ‘10.0’ and INTEGER 10 to TEXT ’10’ → ‘10.0’ LIKE ’10’ → FALSE
  Resulting in FALSE (TRUE AND FALSE).

Root Causes of Affinity-Related Result Discrepancies

1. Differential Constant Propagation Between Clauses
SQLite’s query optimizer applies constant propagation exclusively to WHERE/HAVING clauses to enable predicate simplification and index optimizations. SELECT expressions remain unoptimized to preserve literal evaluation semantics. This creates evaluation asymmetry:

WHERE Clause (Optimized):
v1 = 10 AND v1 LIKE 10
↓ Constant Folding
10 = 10 AND v1 = 10
↓ Boolean Reduction
v1 = 10

SELECT Clause (Unoptimized):
v1 = 10 AND v1 LIKE 10
↓ No Optimization
Full expression evaluation using runtime values

2. Affinity-Driven Storage and Comparison Rules
Column affinity determines value storage format and comparison precedents:

3. Implicit String Conversion Inconsistencies
The LIKE operator forces TEXT conversions using SQLite’s internal sqlite3_snprintf():

• REAL 10.0 → "10.0"
• INTEGER 10 → "10"

These conversions differ from numeric equality checks, which use memcmp() semantics after type promotion. The string mismatch explains the FALSE result in SELECT expressions but not WHERE clauses (where constant folding removes the LIKE operation).

4. Query Planner Phase Ordering
Optimizations occur before execution plan generation, creating an irreversible transformation sequence:

Original SQL  
↓ Parse/AST  
↓ Constant Propagation (WHERE only)  
↓ Type Affinity Application  
↓ Bytecode Generation  
↓ Runtime Execution

This pipeline explains why SELECT expressions can’t retroactively benefit from WHERE clause optimizations.

Step-by-Step Diagnosis and Resolution Strategies

1. Version-Specific Behavior Verification
First confirm whether the SQLite build exhibits pre-patch behavior:

SELECT sqlite_version(), sqlite_source_id();
/* 3.31.1 | 2020-01-27 19:55:54 */

Development builds after commit 9be208a6d70582c6 disable constant propagation in LIKE operations to prevent this inconsistency.

2. Query Plan Analysis
Use EXPLAIN to reveal optimization differences:

EXPLAIN SELECT * FROM v0 WHERE v1 = 10 AND v1 LIKE 10;
/* Output shows constant folding: */
addr  opcode         p1    p2    p3    p4             p5
----  -------------  ----  ----  ----  -------------  ----
0     Init           0     12    0                    00
...
5     String8        0     2     0     10             00
6     Affinity       2     0     0     D              00
7     Ne             3     11    2                    16

Compare with the SELECT expression plan:

EXPLAIN SELECT v1 = 10 AND v1 LIKE 10 FROM v0;
/* No constant folding evident: */
addr  opcode         p1    p2    p3    p4             p5
----  -------------  ----  ----  ----  -------------  ----
0     Init           0     16    0                    00
...
7     Integer        10    3     0                    00
8     Affinity       3     0     0     D              00
9     Ne             2     14    3                    16

3. Affinity and Type Enforcement
Explicitly cast values to harmonize types:

-- Force TEXT comparison in WHERE
SELECT * FROM v0 
WHERE CAST(v1 AS TEXT) LIKE CAST(10 AS TEXT) 
AND v1 = 10;

-- BINARY comparison (no type conversion)
SELECT * FROM v0 
WHERE v1 LIKE 10 
COLLATE BINARY;

4. Schema Design Adjustments
Prevent affinity conflicts through column typing:

CREATE TABLE v0_fixed (
  v2 REAL CHECK(TYPEOF(v2) = 'real'), 
  v1 TEXT  -- Enforce TEXT affinity
);

INSERT INTO v0_fixed VALUES (10, CAST(10 AS TEXT));

5. Query Pattern Refactoring
Isolate comparison contexts to avoid optimization discrepancies:

-- Move LIKE to separate filtered column
SELECT 
  v1, 
  v1 = 10 AS equality_check,
  v1 LIKE 10 AS like_check 
FROM v0;

6. Migration to Patched Versions
For builds affected by pre-patch behavior, upgrade to SQLite 3.35+ where the constant propagation rules for LIKE were adjusted to prevent this class of inconsistency.

7. Comprehensive Test Suite
Implement regression tests covering:

-- Affinity propagation checks
SELECT 
  TYPEOF(v1), 
  TYPEOF(10),
  v1 == 10,
  v1 LIKE 10 
FROM v0;

-- EXPLAIN verification
EXPLAIN QUERY PLAN 
SELECT * FROM v0 WHERE v1 = 10 AND v1 LIKE 10;

8. Runtime Overrides
Use PRAGMA statements to alter affinity handling (caution advised):

PRAGMA encoding = 'UTF-16le';  -- Affects string conversions
PRAGMA short_column_names = OFF;  -- Disambiguate references

Final Resolution Guidance
For legacy systems unable to upgrade SQLite, apply these corrective measures:

1. Explicit Casting: Uniformly cast values in comparisons

   SELECT * FROM v0 
   WHERE CAST(v1 AS INTEGER) = 10 
     AND CAST(v1 AS TEXT) LIKE '10';
   

2. Column Affinity Standardization: Use consistent affinities

   ALTER TABLE v0 ADD COLUMN v1_text TEXT GENERATED ALWAYS AS (CAST(v1 AS TEXT));
   

3. Operator Replacement: Avoid LIKE with numeric values

   SELECT * FROM v0 
   WHERE v1 BETWEEN 9.999999 AND 10.000001 
     AND INSTR(CAST(v1 AS TEXT), '10') > 0;
   

Developers must recognize that SQLite’s flexible typing and optimization pipeline create context-dependent evaluation rules. By rigorously controlling value storage formats and anticipating optimization phase effects, these inconsistencies become avoidable. Post-patch versions resolve the specific issue through more conservative constant propagation in LIKE contexts, but the broader lesson remains: explicit type management prevents implicit assumption failures.