SQLite corrects early filtering during index construction

A condition attached to a later LEFT JOIN can refer entirely to an earlier table. That does not make it a condition that the earlier table's rows must satisfy. SQLite 3.38.3, released on April 27, corrects an optimization that overlooked this distinction. Inappropriate ON-clause terms could restrict construction of an automatic index or Bloom filter, causing valid rows to disappear from the result.

The problem concerns the scope of a predicate during query planning. Evaluating a condition earlier can reduce the amount of work needed to execute a query, but that transformation requires a semantic justification. I value SQLite as an embedded relational engine precisely because application code can express a query and leave those implementation decisions to the library. This repair addresses a case where an internal decision changed the relation returned to the application.

An inner join report exposed an outer join condition

Daniel Lockyer's report came through node-sqlite3, which had moved its bundled SQLite from 3.34.0 to 3.38.2. The reported query used views. Richard Hipp supplied a simplified example involving two ordinary joined tables followed by a LEFT JOIN. The explanation identified an automatic index whose contents had been restricted using a condition belonging to that later join.

The reduced SQL below follows the example in the investigation. Its important property is that t2.d equals 2, while the LEFT JOIN condition asks whether it equals 1.

CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(c INT, d INT);
CREATE TABLE t3(e TEXT, f TEXT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 2);

SELECT *
FROM t1
JOIN t2 ON t2.c = t1.a
LEFT JOIN t3 ON t2.d = 1;

The first join has a match. Failure of the later condition means the result retains that matched pair and supplies NULL values for t3. It does not mean that the t2 row should disappear from the first join. An early index that contains only rows satisfying t2.d=1 removes the matching row before the outer join can preserve it.

This example also explains why the syntax of a top-level query can conceal the relevant operation. A view can contribute joins and predicates to the expanded query. Investigating only the visible INNER JOIN would miss the condition responsible for the unsafe filtering. The reduced case makes the scope error legible without requiring the original database's schema.

Building an automatic index includes choosing its contents

The released planner source constructs an automatic index for a query and supplies the columns needed to make it covering. The code comments define a covering index as one containing all columns required by the query, allowing execution to avoid accessing the original table for those values. They also explain why automatic indexes need that property when the table and transient index might otherwise become inconsistent.

Construction proceeds through separate decisions about equality columns, collations, and extra columns used by the query. It then opens the automatic index and fills it with content. When Bloom filtering is enabled, the same area of code allocates a register holding a zero-filled filter blob. Those operations make the choice of input rows relevant to both structures.

An index containing too few rows cannot supply a correct answer merely because each retained key has the correct shape. The data excluded while constructing it is unavailable to later lookups. Similarly, a Bloom filter built from an incomplete set can reject a lookup that the full relation requires. This is why the release description names both automatic-index and Bloom-filter construction.

The repair checks a predicate against its data source

The expression code in 3.38.3 contains sqlite3ExprIsTableConstraint. For a source on the right of a LEFT JOIN, it requires the expression to come from a join and identify that same source as its associated join table. For the other branch, it rejects expressions marked as coming from a join. It then checks whether the expression is constant for an individual row of the selected table.

This adds context beyond asking which columns occur in an expression. The planner has information about where a predicate originated and which join introduced it. That information determines whether an apparently local expression is safe to use as a constraint on the source being scanned. In the reduced example, a predicate mentioning only t2 still belongs to the operation that joins t3.

The Bloom-filter construction routine calls this constraint check before generating code that excludes an input row. A false predicate can jump past adding that row's key to the filter. Moving the semantic check to the point where that jump is authorized connects the restriction directly to its consequence.

I regard the explicit association between a predicate and its join as the useful lesson in the implementation. Column dependencies alone do not describe the meaning of an outer join. The repaired planner retains the additional context while deciding which rows may be omitted from its temporary structures. In this example, that preserves the earlier match and leaves the later LEFT JOIN responsible for determining whether its own right-hand values are present.