Constant Propagation Causes Integer Overflow in ABS Despite Unmet WHERE Condition

Issue Overview: Overflow Triggered by Constant Propagation in WHERE Clause

The core issue arises when executing a SQL query that combines a conditional filter with a function call susceptible to integer overflow. Specifically, the query uses a WHERE clause that filters for a column value equal to the minimum 64-bit signed integer (-9223372036854775808) and applies the ABS() function to the same column. Despite the column value not matching the filter (e.g., the value is 1), the query triggers a runtime integer overflow error due to the ABS() function. This occurs because SQLite’s query optimizer propagates the constant value from the filter into the ABS() function during query compilation, bypassing the logical short-circuiting implied by the WHERE clause.

The problem is rooted in the interaction between three components:

1. Constant Propagation: SQLite’s optimizer replaces column references with literal values when possible, assuming equivalence based on filter conditions.
2. Integer Overflow in ABS(): The ABS() function cannot compute the absolute value of the minimum 64-bit integer without exceeding the maximum representable value for a signed 64-bit integer (9223372036854775807).
3. Evaluation Order: The optimizer does not guarantee that filter conditions are evaluated in left-to-right order, allowing expressions with side effects (like overflow) to execute even if preceding conditions are false.

This creates a scenario where the query engine evaluates ABS(-9223372036854775808) before verifying whether the column value actually matches the filter, leading to an error that should not occur if the WHERE clause were evaluated sequentially.

Possible Causes: Optimizer Behavior and Edge-Case Integer Handling

1. Aggressive Constant Propagation in Query Optimization

SQLite’s query optimizer employs constant propagation to simplify expressions and improve performance. When a column is compared to a literal in a filter (e.g., c0 = -9223372036854775808), the optimizer substitutes subsequent references to that column with the literal value in other parts of the query. This substitution assumes that the filter condition is true, even if the actual data in the table invalidates this assumption. In the example query, the optimizer replaces c0 in ABS(c0) with -9223372036854775808, leading to an immediate overflow during expression parsing or execution.

2. Lack of Validity Checks for Propagated Constants

The optimizer does not validate whether the substituted constant is safe to use in all contexts where the column appears. While -9223372036854775808 is a valid integer, its use in ABS() is invalid because the absolute value exceeds the maximum positive 64-bit integer. The optimizer does not detect this overflow risk during the substitution phase, allowing an error-prone expression to enter the execution pipeline.

3. Absence of Short-Circuit Evaluation Guarantees

SQL does not strictly enforce left-to-right evaluation of WHERE clause predicates. The optimizer may reorder or combine predicates for efficiency. In this case, the ABS(c0) expression is evaluated independently of the c0 = -9223372036854775808 filter, even though the latter appears first. This reordering eliminates the intended short-circuiting behavior where ABS(c0) would only execute if c0 matched the filter value.

Troubleshooting Steps, Solutions & Fixes: Mitigating Overflow and Controlling Optimization

1. Disable Constant Propagation with Subqueries or CTEs

To prevent the optimizer from propagating the constant, restructure the query to isolate the filter condition from the expression causing the overflow. Use a subquery or Common Table Expression (CTE) to force the optimizer to evaluate the filter before applying the ABS() function.

Example Fix:

SELECT c0 FROM (
  SELECT c0 FROM t0 WHERE c0 = -9223372036854775808
) WHERE ABS(c0);

This structure separates the filter logic from the ABS() call, reducing the optimizer’s ability to propagate the constant. The inner query filters rows first, and the outer query applies ABS() only to the filtered subset.

2. Use CASE Statements to Enforce Evaluation Order

Explicitly control the evaluation order with a CASE statement that checks the filter condition before executing ABS(). This mimics short-circuiting behavior.

Example Fix:

SELECT c0 FROM t0
WHERE CASE
  WHEN c0 = -9223372036854775808 THEN ABS(c0)
  ELSE NULL
END;

The CASE statement ensures ABS(c0) is only evaluated if c0 matches the filter value. If the condition fails, the expression returns NULL, which the WHERE clause treats as false.

3. Parameterize Queries to Avoid Literal Substitution

Replace the literal value with a parameter bound at runtime. This prevents the optimizer from treating the value as a constant eligible for propagation.

Example Fix (Using SQLite Parameters):

SELECT c0 FROM t0 WHERE c0 = ? AND ABS(c0);

Bind the value -9223372036854775808 to the parameter at runtime. The optimizer cannot propagate parameter values during compilation, so ABS(c0) remains a reference to the column rather than the literal.

4. Avoid ABS() for Edge-Case Integer Values

Manually handle the edge case of the minimum 64-bit integer by replacing ABS() with a conditional expression.

Example Fix:

SELECT c0 FROM t0
WHERE c0 = -9223372036854775808
  AND (CASE WHEN c0 = -9223372036854775808 THEN 1 ELSE ABS(c0) END);

This explicitly checks for the problematic value and avoids calling ABS() when it would cause an overflow.

5. Update SQLite or Use Pragma Settings

Newer versions of SQLite may include optimizations or fixes for edge-case integer handling. Check for updates and test the query with the latest version. Additionally, use pragmas like optimization_control to disable specific optimizations.

Example Fix (Disabling Optimization):

PRAGMA optimization_control = 'constant_propagation=off';
SELECT c0 FROM t0 WHERE c0 = -9223372036854775808 AND ABS(c0);

This pragma disables constant propagation, though its availability depends on the SQLite version.

6. Schema Design and Data Type Considerations

Use TEXT or BLOB storage for integers that may exceed 64-bit limits, and validate values at the application layer. While SQLite’s INT type is flexible, storing numbers as strings avoids implicit casting errors.

Example Schema Adjustment:

CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('-9223372036854775808');

This requires converting values to integers in the application layer with explicit overflow checks.

7. Error Handling with SQLite’s Error Code Capture

Use runtime error handling mechanisms (e.g., SQLite’s sqlite3_exec return codes or try-catch blocks in application code) to gracefully recover from integer overflows.

Example Application Code (Python):

import sqlite3
conn = sqlite3.connect(':memory:')
try:
    conn.execute("SELECT c0 FROM t0 WHERE c0 = -9223372036854775808 AND ABS(c0)")
except sqlite3.OperationalError as e:
    if "integer overflow" in str(e):
        print("Overflow handled gracefully")

This approach acknowledges the risk of overflow and handles it externally.

8. Leverage SQLite’s Type Affinity and Casting

Explicitly cast values to REAL to avoid integer overflow, though this sacrifices precision.

Example Fix:

SELECT c0 FROM t0
WHERE c0 = -9223372036854775808 AND ABS(CAST(c0 AS REAL));

Casting to REAL allows ABS() to compute a floating-point result, avoiding the 64-bit integer overflow.

Final Recommendation

The optimal solution depends on the use case:

• For ad-hoc queries, restructuring with subqueries or CASE statements is practical.
• For application code, parameterization and error handling provide robustness.
• Schema adjustments are advisable if edge-case integers are common.