Issue Overview: Custom Function Argument Type Mismatch Between BLOB and TEXT in Multi-Row Queries

A common challenge when working with SQLite custom functions involves unexpected data type conversions between BLOB and TEXT arguments. This issue typically manifests in queries where a user-defined function expects a BLOB argument but receives TEXT instead, leading to runtime errors or incorrect behavior. The problem often surfaces under specific query conditions, such as when the number of rows in a table changes, or when deterministic functions are involved in query optimization.

In the observed case, two custom functions are defined:

1. vector_from_json(input_json_text) parses JSON text and returns a BLOB.
2. vector_distance(vector_blob1, vector_blob2, distance_type_text) calculates distances between BLOB vectors.

The system works correctly in simple queries:

SELECT vector_distance(vector_from_json('[1,2,3]'), vector_from_json('[1,2,3]'), 'cosine');

But fails in table-based queries with multiple rows:

SELECT id, vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine') AS distance
FROM vectors;

The error occurs because vector_from_json('[2,3,4]') is unexpectedly interpreted as TEXT in multi-row contexts, despite being defined as BLOB. The paradox emerges when:

• Queries work with single-row tables
• Same queries fail with multi-row tables
• Argument sizes (12 bytes for 3 float32 values) remain consistent across both scenarios

This behavior indicates hidden type conversion mechanics within SQLite’s query execution engine that interact with custom function implementation details. The core conflict arises from three interconnected layers:

1. Function Argument Handling: How SQLite interprets and converts data types when passing values between functions
2. Query Optimizer Behavior: How query plans change based on result set size and function determinism
3. Type Affinity Rules: How column definitions influence value storage and retrieval

Possible Causes: Deterministic Function Caching and Type Conversion Mechanics

1. Incorrect Value Accessors in Custom Function Implementation

SQLite provides type-specific value accessors (sqlite3_value_text(), sqlite3_value_blob(), etc.) that perform implicit type conversions when the actual storage type differs from the requested type. Using sqlite3_value_text() to access BLOB values converts them to UTF-8 strings, corrupting binary data. This conversion is destructive for BLOBs containing non-textual binary data (like float arrays).

Critical Implementation Detail:

// Incorrect implementation using text accessor
const char *vec1 = (const char*)sqlite3_value_text(argv[0]);

// Correct implementation using blob accessor
const void *vec1 = sqlite3_value_blob(argv[0]);
int blob_size = sqlite3_value_bytes(argv[0]);

2. SQLITE_DETERMINISTIC Flag and Function Result Caching

When functions are marked as deterministic (SQLITE_DETERMINISTIC flag), SQLite may cache and reuse their results during query execution. This optimization becomes particularly impactful in multi-row queries:

• Single-row queries: No need for result reuse
• Multi-row queries: Cached results reused across rows

The caching mechanism stores values using SQLite’s internal storage format, which respects type affinity rules. If a function returns BLOB but is accessed as TEXT during caching, subsequent uses will receive the TEXT-converted value. This creates an inconsistent type experience between single-row and multi-row executions.

3. Type Affinity Conflicts in Expression Handling

SQLite employs dynamic typing with type affinity rules for columns. When storing values in tables:

• BLOB affinity columns store values exactly as provided
• TEXT affinity columns apply UTF-8 encoding conversions

However, for temporary values in query execution (like function results):

• Type handling depends on function registration flags
• Expression parsing may infer types based on usage context
• Query optimizer may insert implicit type conversions

In multi-row queries, the optimizer might:

1. Precompute vector_from_json('[2,3,4]') once
2. Store it in a temporary space with type derived from first access
3. Reuse this cached value for subsequent rows

If the first access uses sqlite3_value_text(), the cached value becomes TEXT, poisoning all subsequent accesses even if the original function returns BLOB.

Troubleshooting Steps, Solutions & Fixes: Ensuring Consistent BLOB Handling

Step 1: Audit Custom Function Value Accessors

Implementation Review:

1. Locate all sqlite3_value_*() calls in function implementations
2. Verify BLOB parameters use sqlite3_value_blob()
3. Confirm TEXT parameters use sqlite3_value_text()

Example Correction:

// Before (incorrect text access)
static void VectorDistance(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    const char *vec1 = (const char*)sqlite3_value_text(argv[0]);
    const char *vec2 = (const char*)sqlite3_value_text(argv[1]);
}

// After (correct blob access)
static void VectorDistance(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    const void *vec1 = sqlite3_value_blob(argv[0]);
    const void *vec2 = sqlite3_value_blob(argv[1]);
    int size1 = sqlite3_value_bytes(argv[0]);
    int size2 = sqlite3_value_bytes(argv[1]);
}

Validation Technique:
Add debug statements to log actual value types received:

printf("Arg 0 type: %d\n", sqlite3_value_type(argv[0]));
printf("Arg 1 type: %d\n", sqlite3_value_type(argv[1]));

SQLite type codes:

• 1 (SQLITE_INTEGER)
• 2 (SQLITE_FLOAT)
• 3 (SQLITE_TEXT)
• 4 (SQLITE_BLOB)
• 5 (SQLITE_NULL)

Step 2: Configure Function Determinism Appropriately

Function Registration:

// Current registration (potential issue if SQLITE_DETERMINISTIC misused)
sqlite3_create_function_v2(db, "vector_from_json", 1, SQLITE_UTF8, 0,
                           VectorFromJson, 0, 0, 0);

// Recommended registration with explicit determinism
sqlite3_create_function_v2(db, "vector_from_json", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,
                           VectorFromJson, 0, 0, 0);

Determinism Guidelines:

1. Mark as deterministic only if identical inputs always produce identical outputs
2. Non-deterministic functions prevent certain optimizations but avoid caching issues
3. Test both deterministic and non-deterministic versions with multi-row queries

Testing Procedure:

1. Run failing query with deterministic flag enabled
2. Run same query with flag removed
3. Compare EXPLAIN QUERY PLAN outputs for differences

Step 3: Enforce BLOB Type Throughout Value Lifecycle

Storage Layer Verification:

-- Verify table schema enforces BLOB storage
CREATE TABLE vectors (
  id INTEGER PRIMARY KEY,
  vector BLOB NOT NULL
);

-- Confirm inserted values maintain BLOB type
SELECT typeof(vector) FROM vectors;

Temporary Value Handling:
Use explicit type casting in queries:

SELECT vector_distance(vector, CAST(vector_from_json('[2,3,4]') AS BLOB), 'cosine')
FROM vectors;

Query Plan Analysis:
Use EXPLAIN to detect implicit type conversions:

EXPLAIN SELECT id, vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine') FROM vectors;

Look for:

• Affinity operations
• Function opcodes with type hints
• ResultRow with type specifications

Step 4: Implement Type Assertions in Custom Functions

Add runtime type checks in function implementations:

static void VectorDistance(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    if(sqlite3_value_type(argv[0]) != SQLITE_BLOB){
        sqlite3_result_error(ctx, "First argument must be BLOB", -1);
        return;
    }
    if(sqlite3_value_type(argv[1]) != SQLITE_BLOB){
        sqlite3_result_error(ctx, "Second argument must be BLOB", -1);
        return;
    }
    // ... rest of implementation ...
}

Step 5: Analyze Query Optimizer Behavior Across Row Counts

The differing behavior between single-row and multi-row queries stems from SQLite’s cost-based optimizer:

• Single-row tables: Full table scan preferred, no result caching
• Multi-row tables: Index lookups or cached intermediate results

Investigation Tools:

1. Query Plan Visualization:

   EXPLAIN QUERY PLAN
   SELECT id, vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine')
   FROM vectors;
   

2. SQLITE_STMT Virtual Table:

   SELECT * FROM sqlite_stmt;
   

3. Compiler Directives:

   sqlite3 test.db ".eqp on" "SELECT ..."
   

Optimizer Thresholds:
Modify where needed:

PRAGMA optimizer_limit=1000;  -- Default 1000
PRAGMA optimizer_cost='100 0 0'; -- Adjust cost model

Step 6: Utilize Strict Typing with SQLite Version 3.37+ Features

For SQLite 3.37 (2022-01-27) and newer:

CREATE TABLE vectors (
  id INTEGER PRIMARY KEY,
  vector BLOB NOT NULL STRICT
);

The STRICT mode enforces column types rigorously, preventing automatic type conversions.

Compatibility Considerations:

1. Verify SQLite version with SELECT sqlite_version();
2. Use fallback constraints for older versions:

   CREATE TABLE vectors (
     id INTEGER PRIMARY KEY,
     vector BLOB NOT NULL CHECK(typeof(vector) = 'blob')
   );
   

Step 7: Benchmark and Compare Type Conversion Performance

Measure the impact of type conversions:

-- Test BLOB access
SELECT vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine')
FROM vectors;

-- Test TEXT access (for comparison)
SELECT vector_distance(CAST(vector AS TEXT), vector_from_json('[2,3,4]'), 'cosine')
FROM vectors;

Use .timer on in SQLite CLI to compare execution times.

Step 8: Implement Cross-Validation Test Harness

Create comprehensive test cases:

-- Single-row validation
INSERT INTO vectors VALUES (1, vector_from_json('[1,2,3]'));
SELECT vector_distance(vector, vector_from_json('[1,2,3]'), 'cosine') FROM vectors;

-- Multi-row validation
INSERT INTO vectors VALUES (2, vector_from_json('[4,5,6]'));
SELECT id, vector_distance(vector, vector_from_json('[1,2,3]'), 'cosine') FROM vectors;

-- Type assertion tests
SELECT 
  typeof(vector_from_json('[1,2,3]')),
  typeof(CAST(vector_from_json('[1,2,3]') AS BLOB)),
  typeof(CAST(vector_from_json('[1,2,3]') AS TEXT));

Final Resolution Strategy

1. Mandate Blob Accessors: Convert all sqlite3_value_text() calls to sqlite3_value_blob() in BLOB-handling functions
2. Control Function Determinism: Register functions as non-deterministic unless absolutely certain
3. Enforce Storage Types: Use STRICT tables or CHECK constraints
4. Add Runtime Type Checks: Validate argument types in function implementations
5. Monitor Query Plans: Analyze optimizer behavior across different data volumes

By systematically addressing each layer—function implementation, type affinity rules, and query optimization behaviors—developers can eliminate BLOB/TEXT type mismatches and ensure consistent operation across all query scenarios.