Correlated Scalar Subquery Returns NULL After First Row

[LeMikaelF]

Description

Correlated scalar subqueries return correct value for first row, NULL for subsequent rows.

Reproducer

CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE t2(id INTEGER PRIMARY KEY, val INT);
INSERT INTO t1 VALUES(1), (2), (3);
INSERT INTO t2 VALUES(1, 100), (2, 200), (3, 300);

SELECT id, (SELECT val FROM t2 WHERE t2.id = t1.id) AS subq FROM t1;
-- Turso:  1|100, 2|NULL, 3|NULL
-- SQLite: 1|100, 2|200,  3|300

---

This issue brought to you by Mikaël and Claude Code.

[karthikreddy-02]

I'd like to work on this! Could you please assign it to me?

[LeMikaelF]

done! I think you know this already, our preferred way of testing things like this is using the sqltest suites.

[karthikreddy-02]

Thanks for letting me know! I was about to ask you the same.

[karthikreddy-02]

Likely Fixed — Investigation Notes

Tested on: e1c991b against current upstream/main (personal branch on top of upstream/main; only difference is an unrelated CLI readline fix)

Reproduction attempts (all matched SQLite):

INTEGER PRIMARY KEY correlation → correct results
Plain INTEGER (no PK) → correct results
TEXT correlation column → correct results
Deeper nesting -> correct results (two levels of correlated nesting)
Larger dataset -> correct results ( 20 rows to stress-test cursor state across multiple iterations)

Root cause analysis:

Traced recent commits to

core/translate/subquery.rs
.

14aca5c — removed a duplicate wrapper function (

emit_non_from_clause_subquery
wrapping emit_non_from_clause_subquery_with_resolver), consolidating into a single function. This was a precursor cleanup.

f073afe — the direct fix. The consolidated function still manually called incr_nesting() / decr_nesting(). On any early-return error path (via ?), decr_nesting() was never called, leaving nested_level stuck at 1. This broke the guard at ~line 1356 of builder.rs, which emits a subprogram-style Halt instead of the main program Halt + transaction finalization when nested_level != 0 — corrupting subsequent subquery execution. program.nested() fixes this by wrapping in a closure that guarantees the decrement runs even on early returns.

Can a maintainer confirm this is resolved and close if appropriate?

[karthikreddy-02]

@LeMikaelF can you take a look and lmk if this is appropriate?

[LeMikaelF]

I can't reproduce it either. Closing...