Issue Overview: RTree Node Data Corruption in ESP32 SQLite Implementation

The core issue revolves around an "undersize RTree blobs" error when querying an RTree virtual table in SQLite on ESP32. This manifests specifically as the error message logSQLError undersize RTree blobs in "AIRSPACERTREE_node" during SELECT operations, despite successful table creation and INSERT statements. The problem indicates structural corruption in how RTree nodes are stored as BLOB data within SQLite’s underlying storage engine. Key observations from the discussion include:

1. Schema Definition Compliance: The RTree virtual table was initially created with a non-integer primary key column (ID), violating RTree’s requirement for the first column to be a 64-bit integer. While SQLite attempts implicit conversions, mismatched data types destabilize node initialization.

2. Zero-Length Node Data: The root node (nodeno=1) contained a BLOB of length 0 after table creation, whereas a healthy RTree initializes this node with a zeroblob of size proportional to the database page size and dimensionality. For a 2D RTree with 4096-byte pages, the expected root node size is 1228 bytes.

3. ESP32-Specific Constraints: Memory limitations and configuration flags in the ESP32 SQLite port (siara-cc/esp32-idf-sqlite3) may interfere with RTree’s dependency on incremental BLOB I/O, zeroblob functionality, and page size alignment.

Possible Causes: RTree Node Initialization Failures

1. Schema Design Violations

RTree virtual tables enforce rigid column requirements:

• First Column: Must be a 64-bit integer primary key. Inserting strings (e.g., 'test') forces silent type conversions that destabilize node structures.
• Dimension Columns: Subsequent columns must be 32-bit floats or integers. Type mismatches corrupt coordinate storage.

Example of Invalid Schema Usage:

CREATE VIRTUAL TABLE AIRSPACERTREE USING RTREE (ID, MINX, MAXX, MINY, MAXY);
INSERT INTO AIRSPACERTREE VALUES ('test', 1, 1, 3, 3); -- ID is string!

This forces SQLite to convert 'test' to integer 0, creating ambiguous primary keys and misaligning node data.

2. Incorrect SQLite Compilation Flags

While SQLITE_ENABLE_RTREE=1 is required, other flags indirectly break RTree:

• SQLITE_OMIT_INCRBLOB: Disables incremental BLOB I/O, preventing RTree from reading/writing node BLOBs in chunks. Though the user claimed this wasn’t set, dependencies on ENABLE_LOAD_EXTENSION or ENABLE_DBSTAT_VTAB might be missing.
• SQLITE_DEFAULT_LOOKASIDE: Aggressive lookaside buffer reductions (e.g., 512,64) on memory-constrained ESP32 may starve RTree of working memory during node allocations.
• SQLITE_SYSTEM_MALLOC: Using system malloc instead of SQLite’s custom allocator introduces fragmentation, critical for large BLOB operations.

3. Page Size Misconfiguration

RTree computes node sizes using pragma page_size. If the ESP32 port hardcodes an incompatible page size (e.g., 512 bytes instead of 4096), the formula nodesize = (pagesize - 64)/4 - 4 for 2D RTrees yields invalid values. A zero or negative nodesize causes zeroblob(0) initialization, rendering nodes unusable.

4. Zeroblob Functionality Breakdown

The zeroblob(N) function preallocates N bytes of zeroed space. If the ESP32 port lacks or breaks this function, node initialization writes empty BLOBs instead of zero-filled buffers of correct size. This directly triggers "undersize blob" errors during queries that traverse nodes.

5. ESP32 Memory Limitations

• PSRAM Configuration: Without enabling PSRAM (pseudo-static RAM), SQLite may exhaust internal heap during RTree node operations.
• 32-bit Address Space: ESP32’s 32-bit architecture limits single allocations to <16MB. Large zeroblob requests could fail silently.
• Memory Fragmentation: Repeated node updates fragment the heap, causing subsequent queries to fail with inconsistent node sizes.

Troubleshooting Steps, Solutions & Fixes

Step 1: Validate RTree Schema Compliance

1.1 Enforce Integer Primary Key
Redefine the RTree table with an explicit integer primary key:

CREATE VIRTUAL TABLE AIRSPACERTREE USING RTREE (
  id INTEGER PRIMARY KEY,  -- Force 64-bit integer
  minx, maxx, miny, maxy
);

Insert only integers or NULL (for auto-generation):

INSERT INTO AIRSPACERTREE VALUES (NULL, 1.0, 1.0, 3.0, 3.0); -- Auto-generate ID

1.2 Verify Column Count and Dimensionality
A 2D RTree requires 5 columns (id + 2 dimensions × 2). Mismatched columns corrupt node structure:

-- Valid for 2D:
CREATE VIRTUAL TABLE AIRSPACERTREE USING RTREE(id, minx, maxx, miny, maxy);
-- Invalid (4 columns):
CREATE VIRTUAL TABLE AIRSPACERTREE USING RTREE(id, minx, maxx, miny);

1.3 Check Data Types
Use typeof() to verify inserted values:

SELECT typeof(id), typeof(minx), typeof(maxy) FROM AIRSPACERTREE;

Ensure id is integer, and coordinates are real or integer.

Step 2: Diagnose SQLite Configuration

2.1 Confirm Active Compile Options
Execute pragma compile_options; and verify:

• ENABLE_RTREE is present.
• OMIT_INCRBLOB, OMIT_ZEROBLOB, OMIT_FLOATING_POINT are absent.

2.2 Recompile SQLite with Critical Flags
In config_ext.h, enforce:

#define SQLITE_ENABLE_RTREE 1
#define SQLITE_OMIT_INCRBLOB 0  // Explicitly disable if needed
#define SQLITE_ENABLE_ZEROBLOB 1
#define SQLITE_DEFAULT_PAGE_SIZE 4096  // Match RTree expectations
#undef SQLITE_SYSTEM_MALLOC
#define SQLITE_ENABLE_MEMSYS5 1  // Use memory allocator suited for embedded

2.3 Validate Page Size and Zeroblob

pragma page_size;  -- Must return ≥1024 (4096 recommended)
SELECT zeroblob(1228);  -- Check if returns 1228-byte blob

If zeroblob fails, rebuild SQLite with SQLITE_ENABLE_ZEROBLOB.

Step 3: Inspect RTree Node Integrity

3.1 Query Node Data Size
After table creation and inserts:

SELECT nodeno, length(data) FROM AIRSPACERTREE_node;

Expected output for 2D RTree with 4096-byte pages:

┌────────┬──────────────┐
│ nodeno │ length(data) │
├────────┼──────────────┤
│ 1      │ 1228         │
└────────┴──────────────┘

A length(data) = 0 indicates failed node initialization.

3.2 Manually Initialize Node
If nodes are undersized, attempt manual repair:

DELETE FROM AIRSPACERTREE_node WHERE nodeno = 1;
INSERT INTO AIRSPACERTREE_node (nodeno, data)
VALUES (1, zeroblob(1228));

Caution: This may corrupt the RTree if done incorrectly. Backup first.

Step 4: Address ESP32-Specific Issues

4.1 Enable PSRAM Support
Modify sdkconfig.h or ESP-IDF configuration:

CONFIG_SPIRAM_SUPPORT=y
CONFIG_SPIRAM_MALLOC_ALWAYSINTERNAL=16384  // Reserve internal RAM for SQLite

Recompile SQLite library with PSRAM flags:

#define SQLITE_USE_ESP32_PSRAM 1  // If supported by the port

4.2 Optimize Memory Settings
Adjust sqlite3_config(SQLITE_CONFIG_HEAP, ...) to allocate from PSRAM:

extern void *ps_malloc(size_t size);
sqlite3_config(SQLITE_CONFIG_HEAP, ps_malloc, sqlite3_free, 1048576, 16);

4.3 Reduce Memory Footprint

• Lower SQLITE_DEFAULT_CACHE_SIZE (-1 → 2000).
• Disable unused features: SQLITE_OMIT_FTS4, SQLITE_OMIT_JSON.

Step 5: Advanced Debugging

5.1 Trace RTree Operations
Use sqlite3_trace_v2() to log RTree queries:

sqlite3_trace_v2(db, SQLITE_TRACE_STMT, 
  [](unsigned, void*, void *pStmt, void*) {
    printf("SQL: %s\n", sqlite3_sql((sqlite3_stmt*)pStmt));
    return 0;
  }, NULL);

5.2 Dump Node Content
Hexdump node data to verify structure:

SELECT hex(substr(data,1,16)) FROM AIRSPACERTREE_node WHERE nodeno=1;

Expected: 16 bytes of zeros for a new RTree. Non-zero data implies corruption.

5.3 Cross-Validate with Desktop SQLite
Export ESP32 database and inspect on desktop:

sqlite3 esp32.db "SELECT * FROM AIRSPACERTREE_node"

Compare node sizes and content.

Final Solution Summary

1. Correct Schema Definition:

   • Use INTEGER PRIMARY KEY for the first column.
   • Ensure 5 columns for 2D RTrees.

2. Recompile SQLite:

   • Enable ENABLE_RTREE, ENABLE_ZEROBLOB.
   • Set DEFAULT_PAGE_SIZE=4096.

3. ESP32 Memory Configuration:

   • Activate PSRAM in SDK and SQLite.
   • Use SQLite’s memsys5 allocator.

4. Runtime Validation:

   • Check length(data) after table creation.
   • Ensure zeroblob() returns correct sizes.

By methodically addressing schema design, compilation settings, node initialization, and ESP32 memory constraints, the "undersize RTree blobs" error can be systematically resolved.