Unexpected Row Count Discrepancy in FTS4 Virtual Table Joins with WHERE Clause

Issue Overview: LEFT JOINs with Virtual Tables Producing Contradictory Row Counts Based on WHERE Presence

A critical issue arises when executing SQL queries involving LEFT OUTER JOINs between FTS4 virtual tables and views, where the inclusion of a WHERE clause unexpectedly increases the number of returned rows instead of filtering them. This behavior directly contradicts standard SQL expectations, where adding a WHERE clause should reduce or maintain the result set size. The problem manifests in scenarios where:

1. Virtual Tables with FTS4: Two FTS4 virtual tables (vt0, vt1) are created, storing text or numeric-like data. These tables leverage SQLite’s full-text search capabilities but behave differently in joins compared to regular tables due to their internal tokenization and indexing mechanisms.

2. View with Cross-Join Logic: A view (v0) is defined using a CROSS JOIN between the virtual tables, with an ON clause that evaluates a boolean condition derived from a text column in one of the tables. This creates implicit type conversions between text and numeric values during join evaluation.

3. Chained LEFT OUTER JOINs: The main query performs two consecutive LEFT OUTER JOINs between the virtual tables and the view. The first JOIN links vt1 to v0, while the second JOIN links the intermediate result to vt0 again. The absence of explicit column comparisons in the JOIN conditions (relying instead on truthiness evaluations) amplifies ambiguity in row matching.

4. WHERE Clause Triggering Optimization Anomalies: Adding a WHERE clause that references a column from the final JOINed table (vt0.c0) alters the query optimizer’s behavior, causing it to incorrectly materialize rows that should have been excluded or not generated in the first place.

This discrepancy indicates a deeper conflict between SQLite’s query planner logic, virtual table implementations, and boolean expression handling in JOIN/WHERE clauses. The problem is exacerbated by the implicit type conversions required to evaluate the ON and WHERE conditions involving text and numeric columns.

Possible Causes: Virtual Table Join Semantics and Query Planner Misoptimization

1. Implicit Type Conversion in Boolean Contexts with FTS4 Columns

FTS4 virtual tables store text data but are often involved in comparisons with numeric literals or columns. When evaluating conditions like ON vt1.c0 (where vt1.c0 contains text values), SQLite attempts to convert the text to a numeric value. Non-numeric strings like ‘x’ convert to 0, making the condition evaluate as false. However, inconsistencies arise when:

• The same column is referenced in JOIN conditions vs. WHERE clauses.
• Multiple JOIN layers exist, causing the type conversion rules to be applied inconsistently across query stages.
• The query planner caches or reuses converted values incorrectly between JOIN evaluation and WHERE filtering.

2. Incorrect NULL Handling in Multi-Layer LEFT JOINs

LEFT OUTER JOINs preserve rows from the left table even if no matching rows exist in the right table, filling unmatched columns with NULLs. However, when chaining multiple LEFT JOINs (as in vt1 → v0 → vt0), the propagation of NULLs through the JOIN hierarchy becomes unstable if:

• The view v0 itself contains a JOIN with ambiguous NULL generation due to virtual table limitations.
• The second LEFT JOIN (v0 → vt0) uses an ON condition (v0.c0) that references a column from a preceding LEFT JOIN, which may be NULL.
• The query planner incorrectly eliminates NULL rows prematurely when a WHERE clause is present, violating the expected LEFT JOIN semantics.

3. Query Planner Over-Optimization with Virtual Tables

SQLite’s query planner may generate different execution plans for queries with and without WHERE clauses, especially when virtual tables are involved. Specific failure modes include:

• Join Order Reversal: The planner might reorder JOINs to prioritize tables referenced in the WHERE clause, inadvertently converting LEFT JOINs to INNER JOINs if it assumes non-NULL constraints from the WHERE condition.
• Predicate Pushdown Conflicts: Conditions from the WHERE clause might be pushed into the JOIN ON clauses during optimization, altering how virtual tables are queried. FTS4 tables may return different results when filters are applied at the full-text search layer vs. the SQL layer.
• Materialization of Views: The view v0 may be materialized differently depending on whether the WHERE clause exists, causing it to resolve column types or contents inconsistently.

4. Version-Specific Regression in JOIN Evaluation Logic

The problem was reported in SQLite 3.39.0 and confirmed fixed in a subsequent trunk check-in, indicating a regression introduced in query processing components. Potential regression sources include:

• Changes to the virtual table xBestIndex method, which governs how FTS4 tables participate in JOINs and handle constraints.
• Modifications to the truthiness evaluation logic for columns in JOIN conditions, particularly for mixed text/numeric comparisons.
• Adjustments to NULL propagation rules in compound JOIN scenarios.

Troubleshooting Steps, Solutions & Fixes: Diagnosing and Resolving Virtual Table Join Inconsistencies

Step 1: Isolate the Impact of Implicit Type Conversions

Action: Explicitly cast columns in JOIN conditions to eliminate ambiguity in boolean evaluations.

-- Original JOIN condition
SELECT * FROM vt1 LEFT JOIN v0 ON vt1.c0;

-- Modified to enforce text-to-integer conversion
SELECT * FROM vt1 LEFT JOIN v0 ON CAST(vt1.c0 AS INTEGER);

Analysis: If the modified query returns the same anomalous results, the issue is not solely caused by type conversion. If results change, adjust schema definitions to use strict typing or avoid boolean evaluations on text columns.

Schema Adjustment Example:

-- Store numeric values in a separate column
CREATE VIRTUAL TABLE vt1 USING fts4(content TEXT, numeric_value INTEGER);

Step 2: Validate View Materialization with Direct Query

Action: Bypass the view and inline its definition into the main query to check for discrepancies.

-- Original query using view
SELECT * FROM vt1 LEFT JOIN v0 ON vt1.c0 ... ;

-- Inlined view equivalent
SELECT * FROM vt1 
LEFT JOIN (
  SELECT vt0.c0 FROM vt1 CROSS JOIN vt0 ON vt1.c0
) AS v0 ON vt1.c0
... ;

Analysis: If the inlined version behaves correctly, the issue lies in how the view interacts with subsequent JOINs. Consider recreating the view with stricter column typing or using a subquery instead.

Step 3: Analyze Query Plans with EXPLAIN

Action: Compare the execution plans of the problematic queries with and without the WHERE clause.

EXPLAIN QUERY PLAN
SELECT * FROM vt1 LEFT JOIN v0 ON vt1.c0 LEFT JOIN vt0 ON v0.c0;

EXPLAIN QUERY PLAN
SELECT * FROM vt1 LEFT JOIN v0 ON vt1.c0 LEFT JOIN vt0 ON v0.c0 WHERE vt0.c0;

Key Observations:

• Check if the JOIN order changes between the two plans.
• Identify whether any LEFT JOIN is converted to an INNER JOIN in the WHERE-inclusive plan.
• Look for full-table scans on virtual tables that should leverage FTS4 indexes.

Intervention: Use CROSS JOIN syntax or index hints to force the desired JOIN order. For FTS4 tables, ensure that the query uses the built-in search indexes by phrasing conditions with MATCH.

Step 4: Test with Stable SQLite Versions

Action: Replicate the scenario in SQLite 3.38.5 (pre-3.39.0) and the latest trunk version to confirm the regression.

# Install and test with SQLite 3.38.5
wget https://www.sqlite.org/2022/sqlite-autoconf-3380500.tar.gz
tar xvfz sqlite-autoconf-3380500.tar.gz
cd sqlite-autoconf-3380500
./configure --enable-all
make
./sqlite3 test.db < your_script.sql

Outcome: If version 3.38.5 returns correct results (one row without WHERE, zero rows with WHERE), apply the official fix by upgrading SQLite. If not, escalate with a detailed bug report.

Step 5: Rewrite Query Using Explicit JOIN Conditions

Action: Replace truthiness-based ON clauses with explicit column comparisons.

-- Original ambiguous condition
LEFT JOIN vt0 ON v0.c0

-- Explicitly compare to vt0's column
LEFT JOIN vt0 ON v0.c0 = vt0.c0

Rationale: Explicit comparisons prevent unintended boolean evaluations and clarify JOIN intent to the query planner. This also sidesteps issues where v0.c0 might be NULL or non-numeric.

Step 6: Utilize COALESCE to Handle NULLs in WHERE Clauses

Action: Modify the WHERE clause to explicitly handle NULL cases, preventing optimizer overreach.

-- Original WHERE clause filtering on vt0.c0
WHERE vt0.c0

-- Explicit NULL handling
WHERE COALESCE(vt0.c0, 0) != 0

Effect: Forces the optimizer to treat NULL as a distinct value, preserving LEFT JOIN semantics even when WHERE conditions reference JOINed tables.

Step 7: Report Detailed Findings to SQLite Maintainers

Components to Include:

• Minimal reproducible script (as provided in the forum thread).
• EXPLAIN QUERY PLAN outputs from both failing and working versions.
• Observations on how JOIN types/order affect results.
• Environment details (compiler flags, platform).

Template:

**Title**: LEFT JOINs with FTS4 Virtual Tables Return Incorrect Counts When WHERE Clause References JOINed Column

**Environment**:
- SQLite Version: 3.39.0
- Commit: 7e87892c
- OS: Ubuntu 20.04
- Compiler: gcc-9

**Reproduction Steps**:
[Include CREATE TABLE, INSERT, SELECT statements]

**Expected Behavior**:
Adding `WHERE vt0.c0` should filter out NULL rows, reducing the result count.

**Actual Behavior**:
WHERE clause increases row count due to incorrect JOIN materialization.

**Query Plans**:
[Attach EXPLAIN QUERY PLAN outputs]

Step 8: Implement Workarounds Pending Official Fixes

Option 1: Use subqueries with aggregate functions to force early materialization.

SELECT * FROM vt1
LEFT JOIN (SELECT c0 FROM v0 GROUP BY c0) AS v0 ON vt1.c0
LEFT JOIN vt0 ON v0.c0 = vt0.c0;

Option 2: Disable problematic query optimizations using runtime flags.

PRAGMA query_only = 1; -- Prevents write operations, may alter planner choices
PRAGMA analysis_limit=0; -- Disables certain statistical optimizations

Option 3: Replace FTS4 virtual tables with regular tables or FTS5, which has different JOIN semantics.

CREATE VIRTUAL TABLE vt0 USING fts5(c0); -- Migrate to FTS5

Final Resolution: Upgrade to SQLite 3.39.1 or Later

The problem was confirmed resolved in a subsequent trunk check-in. Ensure your environment uses an updated SQLite build:

# Build latest trunk version
fossil clone https://www.sqlite.org/src sqlite.fossil
mkdir sqlite-trunk
cd sqlite-trunk
fossil open ../sqlite.fossil
./configure --enable-all
make