Handling 64-Bit Integers in LuaSQLite3: Precision Loss and Truncation Challenges

Understanding LuaSQLite3’s Integer and Floating-Point Data Handling Limitations

Issue Overview
The core challenge revolves around accurately retrieving 64-bit integer values from SQLite databases using the LuaSQLite3 library. When querying integer values stored in SQLite’s INTEGER (64-bit) columns, developers encounter two primary issues:

1. Truncation to 32 Bits: The result_int method truncates 64-bit integers to 32-bit signed integers, discarding the upper 32 bits. For example, a value like 4294967296 (2^32) becomes 0 when retrieved via result_int.
2. Loss of Precision via Floating-Point Conversion: The result_number method converts 64-bit integers to Lua’s number type, which is typically a 64-bit double-precision floating-point value. However, doubles can only represent integers up to 2^53 exactly. Values exceeding this threshold (e.g., 9007199254740993) lose precision, leading to unexpected behavior in applications that rely on exact integer arithmetic.

This issue is exacerbated by historical limitations in Lua’s type system. Prior to Lua 5.3 (released in 2015), Lua lacked native 64-bit integer support, representing all numbers as either 32-bit integers or 64-bit doubles. LuaSQLite3’s current methods (result_int, result_number) reflect these legacy constraints, even though Lua 5.3+ introduced a dedicated integer subtype capable of storing 64-bit integers.

The root conflict lies in the interaction between three components:

• SQLite’s 64-bit INTEGER storage (exact values up to 8 bytes).
• Lua’s type system evolution (post-5.3 support for 64-bit integers).
• LuaSQLite3’s data retrieval APIs (result_int, result_number), which predate Lua 5.3 or lack updates to leverage its 64-bit integer capabilities.

Without proper handling, applications risk data corruption, incorrect calculations, or silent failures when working with large integers.

Factors Contributing to 64-Bit Integer Mismanagement

Possible Causes

1. Lua Version Compatibility:

   • Lua versions prior to 5.3 lack a native 64-bit integer type. If LuaSQLite3 is used in environments running Lua 5.2 or older, the library cannot return 64-bit integers without truncation or conversion to doubles.
   • Even in Lua 5.3+, libraries must explicitly use the lua_pushinteger C API to preserve 64-bit integers. If LuaSQLite3 was not updated to leverage this, it will default to older methods.

2. LuaSQLite3 API Design:

   • The result_int method uses sqlite3_column_int, which fetches a 32-bit integer from SQLite. This is inherently incompatible with 64-bit values.
   • The result_number method uses sqlite3_column_double, converting integers to doubles. While this avoids truncation, it introduces precision loss for values exceeding 2^53.

3. SQLite Type Affinity Behavior:

   • SQLite’s dynamic typing allows storing 64-bit integers, but applications must explicitly request them. If LuaSQLite3’s binding code does not use sqlite3_column_int64 (the correct function for 64-bit integers), the retrieved values will be truncated or converted.

4. Floating-Point Precision Limits:

   • Double-precision floating-point numbers (IEEE 754) have a 53-bit significand. Integers beyond 2^53 cannot be represented exactly, causing rounding errors. For example, 9223372036854775807 (the maximum 64-bit signed integer) becomes 9.223372036854776e+18 as a double, losing precision in the least significant digits.

5. Binding Code Assumptions:

   • LuaSQLite3 may assume all integers fit in 32 bits or that doubles are sufficient for numeric storage. This design choice ignores use cases requiring exact 64-bit integers (e.g., financial systems, UUIDs, timestamps with nanosecond precision).

Resolving 64-Bit Integer Retrieval and Storage in LuaSQLite3

Troubleshooting Steps, Solutions & Fixes

Step 1: Verify Lua Version and Configuration

• Check Lua Version:
  Run print(_VERSION) in Lua. If the output is “Lua 5.1” or “Lua 5.2”, upgrade to Lua 5.3+ to enable 64-bit integer support.
• Confirm 64-Bit Integer Compilation:
  Lua 5.3+ can be compiled with 32-bit integers (via LUA_32BITS). Ensure your Lua build uses the default 64-bit configuration. Execute print(math.type(1)); if it returns “integer”, 64-bit support is active.

Step 2: Modify LuaSQLite3 Binding Code
To retrieve 64-bit integers without truncation or precision loss, update the LuaSQLite3 C code to use SQLite’s sqlite3_column_int64 and Lua’s lua_pushinteger:

1. Locate the Result Handling Function:
   In LuaSQLite3’s source, find the function responsible for returning column values (e.g., get_value in lsqlite3.c).

2. Add 64-Bit Integer Case:
   Modify the function to check the column type and use sqlite3_column_int64 for integers:

   case SQLITE_INTEGER:  
       lua_pushinteger(L, sqlite3_column_int64(stmt->stmt, i));  
       break;  
   

3. Expose a New result_int64 Method:
   Create a Lua-visible method that explicitly returns integers:

   static int db_result_int64(lua_State *L) {  
       sqlite3_stmt *stmt = ...; // Retrieve statement pointer  
       int col = luaL_checkinteger(L, 1);  
       lua_pushinteger(L, sqlite3_column_int64(stmt, col));  
       return 1;  
   }  
   

Step 3: Handle Floating-Point Conversions Gracefully
For backward compatibility with existing code using result_number, add logic to preserve precision:

1. Check Integer Fit Before Conversion:
   If a column contains an integer that fits in 53 bits, return it as a double. Otherwise, return it as a string or userdata:

   int64_t val = sqlite3_column_int64(stmt, col);  
   if (val >= -9007199254740992LL && val <= 9007199254740992LL) {  
       lua_pushnumber(L, (double)val);  
   } else {  
       lua_pushfstring(L, "%lld", val); // Return as string  
   }  
   

2. Add Custom Serialization for Large Integers:
   Use Lua’s lightuserdata or metatables to wrap 64-bit integers, ensuring they’re handled as atomic values in arithmetic operations.

Step 4: Update Application Code to Use New Methods
Replace calls to result_int or result_number with result_int64 where exact 64-bit integers are required:

local stmt = db:prepare("SELECT id FROM big_numbers")  
stmt:step()  
local exact_value = stmt:result_int64(0)  

Step 5: Test Edge Cases and Precision Limits
Validate fixes with test cases covering:

• Minimum/Maximum 64-bit integers: -9223372036854775808 and 9223372036854775807.
• Values exceeding 53 bits: 9007199254740993 (which rounds to 9007199254740992 as a double).
• Mixed-type arithmetic in Lua to ensure integers remain precise.

Alternative Solutions

• String Serialization: Retrieve large integers as strings and convert them to Lua 5.3+ integers using tonumber().
• Custom Fork of LuaSQLite3: Maintain a patched version of LuaSQLite3 with 64-bit support if upstream updates are unavailable.
• Use FFI or External Libraries: Leverage LuaJIT’s FFI or libraries like lua-int64 to handle 64-bit integers externally.

Long-Term Considerations

• Advocate for upstream integration of 64-bit support in LuaSQLite3.
• Document integer handling practices to prevent regressions.
• Monitor SQLite and Lua updates for improved type interoperability.

By addressing these layers—Lua’s type system, SQLite’s C API, and LuaSQLite3’s binding code—developers can achieve robust 64-bit integer handling without truncation or precision loss.