This correlated subquery example currently shown in the docs returns an incorrect result rather the result shown or an error message.

$ super -version
Version: 9db7691f8

$ echo '                                                                                      
{"x":1}
{"x":2}
{"x":3}' > f1.json &&
echo '
{"y":4}
{"y":5}' > f2.json &&
super -c "SELECT (SELECT sum(f1.x+f2.y) FROM f1.json) AS s FROM f2.json"

{s:null}
{s:null}

Details

Repro is with super commit 9db7691.

The #6100 notes set expectations that this query may not work:

Correlated subqueries using the outer scope for SQL queries remains unsupported.

That said, when most SQL correlated subqueries are attempted, super returns a helpful error message "correlated subqueries not currently supported". Meanwhile, the one above doesn't show the message but instead just returns the wrong result.

Here's DuckDB returning the correct result that's shown in the docs:

$ duckdb --version
v1.4.2 (Andium) 68d7555f68

$ duckdb -c "SELECT (SELECT sum(f1.x+f2.y) FROM f1.json) AS s FROM f2.json"
┌────────┐
│   s    │
│ int128 │
├────────┤
│     18 │
│     21 │
└────────┘

We've talked about perhaps not trying to support correlated subqueries in SQL before the first GA release, which is fine. However, if that remains true, as long as this example remains in the docs, then for this kind of query we'd either want to:

1. Fix it so it does return the correct result for this particular kind of SQL correlated subquery while others can continue to show the error message, or,
2. Enhance our error detection logic so the can surface the same "correlated subqueries not currently supported" error for this kind of query as well, or,
3. Add some text in the doc disclosing that the incorrect result from super is a known issue (not ideal since running a query successfully and returning a bad result is one of the worst things we can do, but disclosure is better than nothing).