SQLite 3.15.2 repairs the new row-value logic

A comparison between two composite keys has more cases than its compact SQL spelling suggests. Equality must account for unknown values, ordering must stop at the right component, and a join must preserve unmatched rows. SQLite added row values in 3.15.0 on 14 October. The 3.15.2 release on 28 November fixes several errors in that new logic. The short interval between introduction and repair makes this patch interesting to anyone adopting the syntax.

I like SQLite because an application can use a SQL engine without operating a separate database service. That makes correctness inside the library especially important. An application author sees a function call and a result set, while the engine still has to implement the full meaning of the query. A compact deployment does not make SQL semantics simpler.

Composite comparisons retain SQL null semantics

The row-value regression tests shipped with 3.15.2 give a concrete account of the behaviour. They compare lists of values with equality and ordering operators, and separately exercise their use in predicates. These tests are useful primary material because they specify expected answers for the released implementation, including cases where the answer is unknown.

Consider these expressions, which follow cases in that test file.

SELECT (1, 2, NULL) = (1, 2, 3);
SELECT (1, NULL, 1) = (1, 1, 2);
SELECT (1, 2, NULL) IS (1, 2, NULL);

The first result is NULL. Its last component might match if the unknown value were 3, but the comparison cannot establish that. The second result is false, because the final components already disagree. The unknown middle component cannot make the entire row equal. Using IS in the last expression returns true because IS has different null semantics from ordinary equality. Regression tests explicitly distinguish these outcomes.

This matters when translating an application concept such as a composite identifier into SQL. Treating every occurrence of NULL as an instruction to return NULL would get the second comparison wrong. Treating an unknown component as an ordinary unequal value would get the first comparison wrong. The implementation must retain enough information about the rest of the row to decide whether uncertainty can still affect the result.

Ordering introduces a different dependency between components. The same test file checks that (1, 1, 3) < (1, 2, 3) is true and that equal rows compare neither less nor greater. It also checks unknown results where NULL occupies the component needed to establish order. These are ordered comparisons of composite values. They do not mean that every component independently satisfies the comparison operator.

Join behaviour is part of the feature

The release notes distinguish fixes in the current patch from earlier changes in the series. In particular, 3.15.1 had already fixed an incorrect result involving row values in the ON clause of a LEFT JOIN. That distinction prevents attributing the whole accumulated release page to 3.15.2. The November patch adds further row-value repairs after that earlier correction.

The versioned test file includes the outer-join regression with a comment dated 27 October. Its example creates one row on each side, with keys that do not match, and verifies that the left row remains in the output. The equivalent small example is easy to inspect.

CREATE TABLE left_items(a, b);
INSERT INTO left_items VALUES(1, 2);
CREATE TABLE right_items(x, y);
INSERT INTO right_items VALUES(3, 4);

SELECT a, b, x, y
FROM left_items
LEFT JOIN right_items ON (a, b) = (x, y);

The expected result contains 1 and 2 in the left columns and NULL in both right columns. A failed join comparison does not remove the preserved left row. This example shows why a syntax addition needs tests in its surrounding query context. Getting a standalone comparison right does not establish that an outer join will handle it correctly.

My preference for this syntax comes from the relationship it expresses. The two columns belong to one comparison, and grouping them makes that relationship explicit. The repair history also makes the relevant compatibility boundary clear. Applications using the new notation depend on the correctness of the new row-value implementation even when their schema and stored data have not changed.

Errors must remain errors

The patch also repairs a NULL pointer dereference in ATTACH and DETACH after a maliciously constructed syntax error. The release describes that trigger specifically. It does not establish arbitrary code execution or a general database corruption problem, so those would be unjustified descriptions of this fix. The practical issue is that malformed input could reach an invalid memory access instead of remaining an ordinary SQL error.

Another fix concerns a crash after an out-of-memory condition inside instr(). Allocation failure is already an error condition, but subsequent error handling must still remain valid. This patch addresses a failure after that initial resource shortage. It therefore affects the reliability of failure handling, even though a successful call to the function might show no visible difference.

The JSON extension receives a validator correction as well. It now rejects invalid backslash escapes within strings. A validator that accepts malformed text gives its caller an incorrect statement about the input. The release note does not enumerate every affected escape sequence, so the supported claim is the correction to validation, without an invented catalogue of payloads.

These fixes cover distinct contracts. Row-value repairs concern the meaning of valid SQL. The attachment and string-function fixes concern the safety of error handling. JSON validation concerns whether input satisfies the advertised format. SQLite 3.15.2 changes those behaviours without introducing another SQL feature in this patch. For applications adopting the October row-value addition, the November release supplies the repaired implementation and its associated regression coverage.