SQLite Integer vs Float Division Behavior and Documentation

Understanding Integer and Floating-Point Division Discrepancies in SQLite

The core issue revolves around the differing results produced by division operations in SQLite when operands are integers versus floating-point numbers. For example, 7 / 3 returns 2 (an integer), while 7.0 / 3 returns approximately 2.33333333333333 (a floating-point value). This discrepancy arises from SQLite’s handling of operand types during arithmetic operations, influenced by its type affinity system and adherence to C-language-inspired rules for integer division. The confusion is compounded by the initial lack of explicit documentation clarifying how operand types determine the result’s type and value.

Key Observations:

1. Operand Type Sensitivity: SQLite’s arithmetic operators are sensitive to the storage classes (integer or real) of their operands. If either operand is a floating-point number (real), the operation is performed in floating-point arithmetic. If both operands are integers, the operation uses integer arithmetic.
2. Truncation Toward Zero: Integer division in SQLite truncates fractional results toward zero, mirroring C/C++ behavior. For example, -7 / 3 yields -2, not -3.
3. Implicit Type Conversion: SQLite dynamically promotes operands to a common type before performing operations. A mix of integer and real operands triggers promotion to real.
4. Modulo Operator Nuances: The % operator behaves differently depending on operand types. For integers, it computes the remainder using truncated division. For real numbers, it converts operands to integers first, leading to potentially unexpected results (e.g., 9.2 % 2.9 returns 1.0).

Root Causes of Division Result Discrepancies

1. SQLite’s Type Affinity and Storage Classes

SQLite uses dynamic typing, where values have a "storage class" (integer, real, text, blob, or null) rather than rigid column types. When performing arithmetic:

• Integer Division: If both operands are integers, SQLite performs integer division, truncating toward zero. This matches C/C++ semantics but differs from databases like MySQL, where / always performs floating-point division.
• Floating-Point Division: If any operand is a real (floating-point), SQLite promotes both operands to real and returns a real result.

Example:

SELECT typeof(7), typeof(7/3), 7/3;   -- integer|integer|2
SELECT typeof(7.0), typeof(7.0/3), 7.0/3; -- real|real|2.33333333333333

2. Operator Precedence and Implicit Casting

SQLite implicitly casts operands based on their storage class:

• Literals without a decimal point are treated as integers.
• Literals with a decimal point or scientific notation (e.g., 7.0, 3e0) are treated as reals.
• Columns with numeric affinity (e.g., INTEGER, REAL, NUMERIC) inherit the storage class of their values.

Example:

CREATE TABLE test (a INTEGER, b REAL);
INSERT INTO test VALUES (7, 3);
SELECT a/b FROM test; -- 2.33333333333333 (a is integer, b is real → real division)

3. Cross-Database Inconsistencies

Different SQL databases handle division inconsistently:

• MySQL: / performs floating-point division; integer division requires DIV.
• PostgreSQL/MSSQL: / behaves like SQLite but returns more precise real values.
• SQLite: Follows C-like rules, which may surprise users expecting mathematical flooring or precise remainders.

Resolving Division Issues: Strategies and Best Practices

1. Diagnosing Type-Related Issues

Step 1: Inspect Operand Storage Classes

Use typeof() to determine the storage class of operands:

SELECT typeof(7), typeof(3);       -- integer, integer
SELECT typeof(7.0), typeof(3);    -- real, integer

Step 2: Analyze Implicit Promotions

Identify where implicit type conversions occur:

SELECT 7/3,        -- 2 (integer division)
       7.0/3,      -- 2.33333333333333 (real division)
       -7/3,       -- -2 (truncated toward zero)
       -7.0/3;     -- -2.33333333333333

Step 3: Verify Modulo Operations

Check % operator behavior with mixed types:

SELECT 9%2,         -- 1 (integer modulo)
       9.2%2.9;     -- 1.0 (operands converted to 9 and 2)

2. Explicit Type Casting

Force operands to the desired type using CAST:

SELECT CAST(7 AS REAL)/3;   -- 2.33333333333333
SELECT 7/CAST(3 AS REAL);   -- 2.33333333333333

3. Schema Design Adjustments

Define columns as REAL if fractional results are required:

CREATE TABLE measurements (
    numerator REAL,
    denominator REAL
);
INSERT INTO measurements VALUES (7, 3);
SELECT numerator/denominator FROM measurements; -- 2.33333333333333

4. Understanding Truncation and Rounding

Use ROUND() or string formatting for controlled rounding:

SELECT ROUND(7.0/3, 2); -- 2.33

5. Leveraging Documentation Updates

Refer to SQLite’s clarified documentation:

• Integer Division: "Yields an integer result, truncated toward zero."
• Modulo Operation: "Computes the remainder after dividing the left integer by the right integer."

6. Cross-Database Compatibility

Write portable queries by avoiding implicit integer division:

-- Use explicit casting for consistent results across databases
SELECT CAST(7 AS REAL)/3; -- Works in SQLite, PostgreSQL, MSSQL

7. Handling Negative Operands

Account for truncation toward zero in applications:

SELECT -7/3;   -- -2 (not -3)
SELECT -7%3;   -- -1 (since (-7) = (-2)*3 + (-1))

8. Edge Cases and Error Handling

Guard against division by zero using CASE:

SELECT
  CASE
    WHEN denominator = 0 THEN NULL
    ELSE numerator/denominator
  END
FROM calculations;

By understanding SQLite’s type affinity system, leveraging explicit casting, and adhering to updated documentation, users can avoid pitfalls related to integer and floating-point division. This approach ensures predictable results across diverse use cases and database environments.