Problem

The siara-cc/esp32-idf-sqlite3 component ships with SQLITE_OMIT_COMPOUND_SELECT enabled in config_ext.h. This silently breaks recursive CTEs — they compile but crash at runtime.

Symptoms

  • sqlite3_prepare_v2() succeeds (no error)
  • sqlite3_step() crashes with LoadProhibited in sqlite3VdbeCursorMoveto
  • EXCVADDR: 0x00000003 (null pointer dereference)

Root Cause

SQLITE_OMIT_COMPOUND_SELECT disables UNION and UNION ALL at the SQL engine level. Recursive CTEs require UNION ALL for the recursion step. Without it, the VDBE bytecode program has an incomplete cursor setup — the parser accepts the syntax, but execution crashes.

Notably, SQLITE_OMIT_CTE is correctly #undef'd in the same file, so CTEs are intentionally enabled. The two flags have an undocumented dependency: enabling CTEs without compound selects is a silent time bomb.

Fix

One line in config_ext.h:

- #define SQLITE_OMIT_COMPOUND_SELECT          1
+ #undef SQLITE_OMIT_COMPOUND_SELECT

Verification query:

WITH RECURSIVE cnt(x) AS (
  SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x < 5
) SELECT x FROM cnt;
-- Returns: 1, 2, 3, 4, 5

Optional: Upgrade the SQLite Amalgamation

The stock component ships SQLite 3.25.2 (September 2018). You can upgrade to the latest amalgamation for bug fixes and performance improvements:

  1. Download the amalgamation from sqlite.org
  2. Replace sqlite3.c and sqlite3.h
  3. Prepend #include "config_ext.h" to the new sqlite3.c
  4. Add #define SQLITE_OMIT_MMAP 1 to config_ext.h (no mmap on ESP32)

Pull Request

We submitted this fix upstream:

View PR #39 on GitHub →

Environment

  • Board: ESP32-S3-DevKitC (8 MB PSRAM)
  • Framework: ESP-IDF v5.2.3
  • SQLite: 3.49.1 amalgamation