Issue Overview: Zeroblob Cast Collation Assertion with STAT4 Enabled

The core issue arises when executing a query that involves comparing a CHAR(0) column to a CAST(zeroblob(0) AS TEXT) value under specific indexing and optimization conditions. The failure manifests as an assertion error in binCollFunc due to null pointer dereferences during collation sequence processing. This occurs exclusively when the SQLITE_Stat4 optimization is active, which enables advanced query planner statistics. The problem is rooted in how SQLite’s query optimizer handles degenerate column types (e.g., zero-length strings) and expression-based indexes involving REGEXP operators when generating statistical data. The assertion pKey1 && pKey2 fails because one or both pointers are unexpectedly null during comparison operations triggered by the ANALYZE command or subsequent SELECT queries.

The assertion failure is not merely a cosmetic crash but indicates a deeper inconsistency in how collation keys are derived for index entries under STAT4’s statistical sampling mechanism. The zeroblob(0) cast to TEXT produces an empty string, which interacts pathologically with the CHAR(0) column’s collation rules (binary collation in this case). The index i on v0(c0, '' REGEXP 0) complicates this interaction because the REGEXP operator’s result type (integer) creates a hybrid index key structure that conflicts with the empty-string comparisons required by the WHERE clause.

Possible Causes: Collation Key Generation and STAT4 Sampling Conflicts

1. Degenerate Column Types and Index Key Encoding
The CHAR(0) column definition creates a scenario where all stored values are zero-length strings. However, SQLite’s type affinity rules allow inserting integer 0 into this column due to flexible typing. This results in implicit type conversions during storage and retrieval. The index i includes two components: the c0 column (with binary collation) and the expression '' REGEXP 0. The latter evaluates to an integer (0 or 1), creating a composite index key that mixes string and integer data types. When STAT4 collects statistical samples, it attempts to serialize and compare index keys using collation functions, but the mixed-type keys may bypass proper collation setup.

2. STAT4 Optimization and Collation Function Initialization
The SQLITE_Stat4 optimization enhances the query planner’s ability to estimate row counts by storing detailed index key samples in the sqlite_stat4 table. During ANALYZE, SQLite executes internal queries to sample index keys, which involves comparing index entries using their defined collations. The binCollFunc function, responsible for binary collation comparisons, expects valid pKey1 and pKey2 pointers to the serialized keys. However, when the index key contains a REGEXP expression result (integer) alongside a zero-length string, the serialization process may omit collation metadata for degenerate keys, leading to null pointers during comparison.

3. Zeroblob Casting and Empty String Handling
The CAST(zeroblob(0) AS TEXT) expression generates an empty BLOB converted to a TEXT value. In SQLite, zeroblobs are treated as distinct from empty strings in certain contexts, particularly in collation and comparison operations. When the query planner attempts to use the index i to satisfy the v0.c0 < ... predicate, it must compare the c0 column’s values (empty strings after type conversion) against the zeroblob-derived empty string. The binary collation forces a byte-wise comparison, but the absence of valid collation keys (due to STAT4 sampling artifacts) triggers the assertion failure.

Troubleshooting Steps, Solutions & Fixes: Resolving Collation Assertions in STAT4 Contexts

1. Diagnosing the Assertion Context
Begin by isolating the conditions that trigger the assertion. Reproduce the issue using the exact schema and queries provided, ensuring SQLITE_Stat4 is enabled. Enable SQLite’s debugging features using the -DSQLITE_DEBUG and -DSQLITE_ENABLE_WHERETRACE flags to capture the query planner’s decision-making process. Examine the internal sqlite_stat4 table after ANALYZE to inspect the sampled index keys. Look for anomalies in key serialization, such as truncated entries or missing collation metadata. Use EXPLAIN on the SELECT query to verify whether the index i is being used and how the comparison operation is being implemented.

2. Mitigation Strategies and Workarounds
If updating to a fixed SQLite version (post-commit 5c8dd8dfcaab9c36) is not immediately feasible, apply these workarounds:

• Disable STAT4 Optimization Temporarily: Use .testctrl optimizations 0x00000800 to disable STAT4 while retaining other optimizations. This avoids the statistical sampling that triggers the assertion but may degrade query planner efficiency for complex queries.
• Reconfigure Index Definitions: Remove the REGEXP expression from the index or replace it with a deterministic literal. For example, CREATE INDEX i ON v0(c0, 0) eliminates the hybrid string/integer key component.
• Avoid Zero-Length Column Types: Redesign the schema to use TEXT instead of CHAR(0), enforcing non-empty strings via CHECK constraints if necessary. This prevents type conversion ambiguities.

3. Code-Level Fixes and Long-Term Prevention
The root fix involves ensuring collation functions receive valid pointers during STAT4 sampling. In SQLite’s wherecode.c and analyze.c, modify the key serialization logic to handle degenerate keys (empty strings, zeroblobs) with explicit collation metadata. Specifically, in the sqlite3Stat4ProbeSetValue function, verify that both pRec and pVal pointers are non-null before invoking sqlite3VdbeRecordCompareWithSkip. Add guard clauses to binCollFunc to gracefully handle null pointers with appropriate error codes instead of assertions.

To prevent recurrence, enhance the test suite with cases that combine zero-length columns, expression-based indexes, and STAT4 sampling. Introduce static analysis checks in the SQLite build process to validate collation context initialization for all index key components, including expressions. Developers should audit collation usage in composite indexes, ensuring that mixed-type keys explicitly define collation for each component or avoid non-deterministic expressions like REGEXP in indexes.

By addressing the interplay between STAT4’s sampling mechanism, collation function assumptions, and degenerate data types, this assertion failure can be systematically resolved while hardening the database engine against similar edge cases.