Resolving Incorrect NaN Detection and Byte Order Conflicts in SQLite on RISC OS

Understanding the Core Conflict Between sqlite3IsNaN(), HAVE_ISNAN, and Platform-Specific Double/u64 Byte Order

The primary issue revolves around the incorrect behavior of SQLite’s sqlite3IsNaN() function on RISC OS, where it fails to accurately detect Not-a-Number (NaN) values in floating-point numbers. This misdetection stems from a deeper conflict between the byte order (endianness) of double and unsigned long long int (u64) types on RISC OS. The problem is compounded by the apparent ineffectiveness of the HAVE_ISNAN compile-time option, which was expected to delegate NaN detection to the system’s native isnan() function but did not function as intended.

At its core, this issue exposes two critical layers of complexity:

1. Platform-Specific Floating-Point Representation: SQLite relies on a consistent byte order for 64-bit floating-point values (IEEE 754 doubles) to ensure cross-platform compatibility of database files. RISC OS’s unique byte ordering for double and u64 disrupts this consistency, leading to incorrect interpretation of NaN values and potential data corruption when databases are shared across platforms.
2. Compilation Configuration Nuances: The HAVE_ISNAN flag, intended to enable the use of the system’s isnan(), was either misapplied or rendered ineffective in the SQLite amalgamation build, forcing the user to modify core SQLite files to work around the NaN detection issue.

The implications of this problem extend beyond NaN detection. If the byte order of floating-point values differs from SQLite’s expectations, databases created on RISC OS may become unreadable or produce incorrect results on other systems. This violates SQLite’s design goal of platform-agnostic database files. The user’s initial workaround—replacing sqlite3IsNaN() with the system’s isnan()—resolved the immediate symptom but did not address the underlying byte order mismatch, leaving the database’s portability in question.

Diagnosing the Root Causes: Byte Order Mismatches, Compile-Time Flag Misconfigurations, and Hidden Platform Assumptions

1. Byte Order Mismatch Between double and u64 on RISC OS

SQLite stores floating-point numbers as 64-bit IEEE 754 values in databases. To manipulate these values, it often uses u64 (unsigned 64-bit integers) for bitwise operations, relying on memcpy() to transfer bytes between double and u64 variables. This approach assumes that the byte order of double and u64 is identical on the target platform. However, RISC OS exhibits a discrepancy: the double and u64 types have different word orders. When memcpy() is used to copy bytes from a double to a u64, the resulting integer’s bytes are reversed, causing sqlite3IsNaN() to incorrectly interpret the NaN flag.

For example, a NaN value represented as 0x7ff8000000000000 in a u64 might be stored as 0x0000000000f87f in a double on RISC OS due to byte order differences. The existing sqlite3IsNaN() logic, which checks specific bits in the u64, would fail to recognize this pattern.

2. Ineffective HAVE_ISNAN Compile-Time Option

The HAVE_ISNAN flag is designed to instruct SQLite to use the system’s isnan() function instead of its internal implementation. However, the user observed that defining HAVE_ISNAN had no effect in the amalgamation build. This occurs because:

• Pre-Amalgamation vs. Post-Amalgamation Configuration: Certain SQLite compile-time options require definition during the pre-amalgamation build phase (i.e., when generating the amalgamation from individual source files). Flags like HAVE_ISNAN are processed at this stage to conditionally include or exclude code blocks. If defined after amalgamation, they have no effect.
• Legacy Code Paths: The SQLite codebase had temporarily removed reliance on system isnan() due to portability concerns, rendering HAVE_ISNAN obsolete until a recent check-in (b3cfe23bec0b95ca) restored it.

3. Hidden Platform Assumptions in SQLite’s Floating-Point Handling

SQLite assumes that the byte order of double and u64 is consistent across all platforms. This assumption is critical for functions like sqlite3IsNaN() and for correctly reading/writing floating-point values to the database file. RISC OS violates this assumption, leading to two distinct problems:

• In-Memory NaN Detection Errors: The reversed byte order in u64 causes sqlite3IsNaN() to return false negatives/positives.
• Database File Portability Issues: Floating-point values stored by SQLite on RISC OS may use a byte order incompatible with other platforms, violating the SQLite file format specification.

Comprehensive Resolution Strategy: Byte Order Alignment, Compilation Fixes, and Database Sanitization

Step 1: Validate the Platform’s Floating-Point Byte Order

Before addressing NaN detection, confirm the byte order discrepancy using SQLite’s diagnostic tools:

1. Generate a Test Database:

   ./sqlite3 byteorder.db 'CREATE TABLE t1 AS SELECT 1579060583422312000000.0 x;'
   

2. Inspect the Raw Database Bytes:
   Use the dbtotxt utility (or a hex editor) to examine the last 8 bytes of the second page. On a compliant system, these bytes should appear as 44 55 66 77 88 99 aa ff. If the output shows 88 99 aa ff 44 55 66 77 (swapped words), the platform violates SQLite’s byte order expectations.

Step 2: Address NaN Detection with System isnan()

1. Apply the HAVE_ISNAN Fix:
   Ensure the SQLite build includes the b3cfe23bec0b95ca check-in, which restores support for system isnan(). Rebuild SQLite with -DHAVE_ISNAN during the pre-amalgamation phase.
2. Verify isnan() Integration:
   Confirm that sqlite3IsNaN() now delegates to the system isnan() by inspecting the generated assembly or adding debug prints to the function.

Step 3: Enforce Correct Floating-Point Byte Order

1. Compile with -DSQLITE_MIXED_ENDIAN_64BIT_FLOAT:
   This flag instructs SQLite to account for platforms where the upper and lower 32-bit halves of a 64-bit double are stored in reverse order. Rebuild SQLite with this flag and rerun the dbtotxt test. The output should now match the expected byte sequence (44 55 66 77 88 99 aa ff).
2. Enable Debug Assertions:
   Compile with -DSQLITE_DEBUG to activate runtime assertions that validate floating-point byte order during database operations. This helps catch mismatches early.

Step 4: Sanitize Existing Databases for Cross-Platform Use

If existing databases were created without SQLITE_MIXED_ENDIAN_64BIT_FLOAT, they may contain non-portable floating-point values. Mitigate this as follows:

1. Export Data to a Portable Format:
   Use the SQLite CLI to dump the database to an SQL text file:

   ./sqlite3 riscos.db .dump > riscos.sql
   

2. Reimport on a Compliant System:
   Transfer riscos.sql to a system with a correctly configured SQLite build and recreate the database:

   ./sqlite3 portable.db < riscos.sql
   

   This ensures all floating-point values are stored in a byte order compliant with the SQLite specification.

Step 5: Continuous Validation and Regression Testing

1. Integrate SQL Logic Test:
   Run SQLite’s extensive test suite regularly to detect regressions. The user confirmed that all tests pass after applying the isnan() fix, indicating robust NaN detection.
2. Cross-Platform Byte Order Checks:
   Periodically validate database files using dbtotxt on both RISC OS and a reference platform (e.g., Linux) to ensure byte order consistency.

Final Considerations and Long-Term Maintenance

The resolution of this issue hinges on aligning SQLite’s byte order assumptions with RISC OS’s peculiarities while leveraging system-specific optimizations like isnan(). Developers targeting niche platforms must:

• Audit Compile-Time Flags: Verify that flags like HAVE_ISNAN and SQLITE_MIXED_ENDIAN_64BIT_FLOAT are applied at the correct build stage and validated through byte-level inspection.
• Prioritize Database Portability: Use SQLite’s .dump/.restore commands or the VACUUM command to rebuild databases after configuration changes.
• Engage with Upstream Updates: Track SQLite’s release notes for changes to floating-point handling, such as the reintroduction of HAVE_ISNAN support, and adjust build configurations accordingly.

By addressing both the immediate NaN detection issue and the underlying byte order conflict, developers can ensure SQLite operates reliably on RISC OS while maintaining cross-platform database compatibility.