Repository navigation
fix(postgres): count an aggregate holding a subquery as its query's own - #69
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
✅ No new issues found.
Reviewed changes
I reviewed the change to how Postgres's Level::owns handles an aggregate call that holds a subquery, along with its new oracle statements and unit tests.
referencesreads through subqueries: it no longer gives up at aSubLink. It collects the column references outside any subquery, plus theSubLink'stestexpr, and returns whether it saw a subquery.ownsis decided by the call's own reads: one read of this FROM outside a subquery makes the call this query's own. A call with no reads that holds a subquery is still treated as possibly the outer query's.- Oracle and unit tests: there are four new
aggregate_levelsstatements: the nestedjsonb_aggtree, anINsubquery, an own read beside an outer-level subquery, and an outer read beside a subquery. There are also five new unit cases for owned and not-owned calls. - Rebuilt
packages/postgres/engine.wasm.
This rule matches how Postgres places an aggregate (check_agg_arguments). The aggregate's level is the lowest level among the variables it reads, and a variable inside a subquery has its level shifted by the subquery's depth. So a read of this FROM outside any subquery always puts the aggregate here. Also:
- Postgres parses the
testexprofx IN (…)/x op ANY (…)at the call's own level, so reading it is right. - The JSON comes from a plain
serde::Serializederive, so the key istestexpr.cardinality.rsalready reads that key. - Both
|=and thefoldwith|evaluate every branch, so no read is skipped after the first subquery.
In subquery_own_beside_outer_read, the inner count(name) belongs to the users level, because comments has no name column. Postgres accepts that, and the statement still yields 0, not NULL, so n: int8 holds.
I didn't run the engine's tests or the oracle in this run, because the workspace's prepare step wasn't done. CI covers both.
claude-opus-5-5 ｜ 𝕏
On Postgres, an ungrouped scalar subquery over an aggregate returns exactly one row, but only when the aggregate is the subquery's own: one over columns of a query around it belongs to that query.
Level::ownsdecides that, and it gave up whenever the aggregate's argument held a subquery of its own, so the scalar subquery around it was typed nullable. That is the shape of a tree built to a fixed depth, where each level'sjsonb_aggholds the next level's subquery:was typed
children: { id: number; name: string; children: { id: number; name: string }[] }[] | null, though it is never null.Now
ownsreads the columns the call reads outside any subquery in it, and the expression anINorANYsubquery is compared with. An aggregate belongs to the innermost query whose column it reads, so one read of this FROM there settles it, whatever the subquery reads. A call that reads no column of this FROM and holds a subquery is still taken as possibly the outer query's, since the subquery may read one.children: …[] | nullchildren: …[]select (select count(p.id + (select count(name) from comments)) from posts p where false) as n from usersn: int8?n: int8select (select count(p.id in (select post_id from comments)) from posts p where false) as n from usersn: int8?n: int8select (select count(name || (select body from comments limit 1)) from posts where false) as n from usersn: int8?n: int8?Tests:
oracle/cases/aggregate_levels: the four statements above (the tree with posts and comments), sound over the seeded edge values; no other case'sexpected.txtchanges.engine/postgres/src/levels.rs, for calls that hold a subquery, owned and not.--strict-coverage, the engine's tests, the workspace build with no warning, the rebuilt artifact's fingerprint, and the loader's typecheck and tests.