System.Data.SQLite.Core SUM Operation Returns Int64 and Truncates Decimal Values

Understanding the Discrepancy Between System.Data.SQLite.Core and DB Browser in SUM Operation Results

SQLite Type Affinity, SUM Function Behavior, and C# Data Type Mapping

The core issue revolves around the mismatch in data types returned by the SUM function when executing the same query in System.Data.SQLite.Core (C#) versus DB Browser for SQLite. Specifically:

• In DB Browser, the SUM(Cost * Quantity) operation returns a numeric type (e.g., REAL or FLOAT).
• In System.Data.SQLite.Core, the same operation returns an Int64 (long in C#), truncating decimal values.

This discrepancy arises from the interaction between SQLite’s dynamic type system, the SUM function’s return type logic, and how the System.Data.SQLite.Core library maps SQLite data types to C# types. To resolve this, we must dissect three interconnected components:

1. SQLite’s type affinity rules and arithmetic operations.
2. The behavior of the SUM aggregate function.
3. System.Data.SQLite.Core’s type conversion logic.

Root Causes: Type Inference in SQLite and C# Wrapper Limitations

1. SQLite’s Dynamic Typing and Implicit Conversions

SQLite uses dynamic typing, where the data type of a value is associated with the value itself, not the column. Columns have type affinity (e.g., NUMERIC, INTEGER), which influences how values are stored but does not enforce strict typing. For arithmetic operations:

• If all operands in an expression (e.g., Cost * Quantity) are integers, SQLite performs integer arithmetic, returning an INTEGER result.
• If at least one operand is a REAL (floating-point) or TEXT-represented number (e.g., '5.5'), SQLite converts the operands to REAL and returns a REAL result.

The SUM function inherits this behavior:

• If all summed values are integers, SUM returns an INTEGER.
• If any summed value is a REAL, SUM returns a REAL.

2. System.Data.SQLite.Core’s Type Mapping

The C# library maps SQLite data types to .NET types as follows:

• INTEGER → Int64 (long).
• REAL → Double.
• TEXT → String.
• BLOB → Byte[].

When SUM returns an INTEGER, the C# wrapper always maps it to Int64, even if the result is mathematically a whole number (e.g., 10.0). This differs from DB Browser, which may display INTEGER results as floating-point numbers without decimal places (e.g., 10 vs. 10.0), creating the illusion of a "numeric" type.

3. Implicit Truncation in C#

When the SUM result is an INTEGER, the C# library returns an Int64, discarding any fractional part that might exist in a REAL result. For example:

• If SUM returns 15.99 as a REAL, it maps to 15.99 (a Double in C#).
• If SUM returns 15 as an INTEGER, it maps to 15 (an Int64), even if the arithmetic operation logically implies a decimal component (e.g., 7.5 * 2 = 15).

Resolving Type Mismatches and Ensuring Decimal Precision in C#

Step 1: Verify Data Types and Table Schema

Confirm the declared type affinity of the Cost and Quantity columns using:

PRAGMA table_info(Parts);

• If Cost is declared as INTEGER, SQLite will prioritize integer arithmetic even if decimal values are stored as TEXT or REAL. Redefine the column with NUMERIC affinity if necessary:

  CREATE TABLE Parts_new (Cost NUMERIC, Quantity INTEGER);
  INSERT INTO Parts_new SELECT * FROM Parts;
  DROP TABLE Parts;
  ALTER TABLE Parts_new RENAME TO Parts;
  

Step 2: Force Floating-Point Arithmetic in SQL

Modify the query to ensure SQLite treats the result as REAL, even if all inputs are integers:

var sqlCmd = new SQLiteCommand(
  "SELECT SUM(Cost * Quantity * 1.0) FROM Parts", // Multiply by 1.0 to promote to REAL
  connection
);

Alternatively, use explicit casting:

var sqlCmd = new SQLiteCommand(
  "SELECT CAST(SUM(Cost * Quantity) AS REAL) FROM Parts", 
  connection
);

Step 3: Validate the Data Type in C#

After executing the query, check the runtime type of the result:

var value = await sqlCmd.ExecuteScalarAsync();
Console.WriteLine(value?.GetType().FullName); // Should output "System.Double"

If the result is still Int64, inspect individual rows to identify integer-only results:

SELECT Cost, Quantity, (Cost * Quantity) AS Subtotal FROM Parts;

Ensure at least one Subtotal has a fractional component (e.g., 9.99).

Step 4: Handle Decimal Values in C# Code

If the SUM result must retain decimal precision, use Double in C#:

var value = await sqlCmd.ExecuteScalarAsync();
double sum = Convert.ToDouble(value ?? 0.0);

Step 5: Adjust Column Affinity and Data Storage

If the Cost column stores decimal values as TEXT (e.g., '10.5'), SQLite may fail to infer numeric context. Convert all values to REAL:

UPDATE Parts SET Cost = CAST(Cost AS REAL);

By addressing SQLite’s type affinity rules, forcing floating-point arithmetic, and validating type mapping in C#, developers can ensure that SUM operations return precise decimal values without truncation.