Repository navigation
fix(postgres): keep a table's not null in a subquery's array_agg - #99
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
ℹ️ No correctness issues found. One stale doc comment is noted below.
Reviewed changes
I reviewed the whole PR: the engine rule, its unit tests, the oracle case and the rebuilt artifact.
aggregatedandemptyhelpers insublink_rows: these find a built-inarray_agg(e)orarray_agg(e.*)of a FROM item's whole row. The call can stand alone or sit inside a two-argumentcoalesce(…, <empty array literal>). Nestedcoalesce, non-empty defaults,'{}'as the first argument andarray_agg(…) || '{}'are all rejected.- Scalar subquery's own
array_agg: anExprSublinkwhose one target isaggregated 8000over its own FROM now gets the same stored-rows mark as a top-levelarray_agg.ARRAY(…)stays excluded, because its result would also be typedposts[], as a 2-D array. The existing guards inEngine::stored_sublinksstill apply: the type must be an array of the table's row type, no table in the statement may have a column named like the alias, andarray_aggmust be the built-in one. The_outer_columncase shows these guards keep a barepread asusers.p. - Top-level
coalesce(array_agg(p), '{}'): this is now marked too. The JSON case_in_jsonuses this path, because jsonshape's probe hands the subquery tostored_sublinks. - Oracle
json_subqueries: the_coalescefields become non-null. The new statements cover the uncorrelated, bare, outer-joined and in-JSON cases, plus guards that stay nullable. packages/postgres/engine.wasm: rebuilt.tests/fingerprint.test.tspasses against the PR's sources.
ℹ️ The doc comments on SublinkRows and sublink_rows still describe the old rule
These comments sit outside the diff hunks, so they can't take an inline comment. They still say an array_agg counts only over "the SELECT's FROM" (SublinkRows.targets) or "the SELECT's own" (sublink_rows). Neither mentions a scalar subquery's own array_agg or the coalesce(…, '{}') form, which the code now accepts.
Technical details
# Stale doc comments after widening the array_agg rule
## Affected sites
- engine/postgres/src/postgres.rs:1904-1907 — `SublinkRows.targets` describes an `array_agg` only "of a table of the SELECT's FROM"
- engine/postgres/src/postgres.rs:1935-1937 — `sublink_rows` names only `array_agg(e)` / `array_agg(e.*)` "of one of the SELECT's own"
## Required outcome
- Both comments also cover a scalar subquery whose one target is an `array_agg` of a whole row of its own FROM, and the `coalesce(…, <empty array literal>)` wrapping at either level.claude-opus-5-5 ｜ 𝕏
|
Updated both doc comments, on |
65a2bbf to
cd1c797
Compare
cd1c797 to
85f4acf
Compare
An
array_aggof a table's whole row inside a scalar subquery typed every field of its element row as nullable. The top-levelarray_agg(e)already keeps the table'sNOT NULL, and so doesjsonb_agg(q)in the same position:rows: posts{ id: int8?, user_id: int8?, title: text? }[]rows: posts{ id: int8, user_id: int8, title: text }[]The rule
The column carries the same stored-rows mark that a top-level
array_agg(e)gets. A scalar subquery's own target may now be:array_agg(q)orarray_agg(q.*), whereqis an item of the subquery's FROM;coalesce(…, <empty array literal>), at the top level too.The empty array literal is
'{}'orarray[], cast or not. All the existing conditions of the mark still hold:q, so the aggregate is the subquery's andqis its row;array_aggis the built-in one.These are left as they were:
array[row(…)::posts]);ARRAY(select array_agg(q) …);array_agg(row(…)::posts)).Oracle (
json_subqueries)scalar_row_array_agg_coalesce:rows: posts{ id: int8?, user_id: int8?, title: text? }[]→rows: posts{ id: int8, user_id: int8, title: text }[]_uncorrelated_bare(stays nullable, fields their own)_outer_joined(an outer join inside the subquery)_in_json(insidejsonb_build_object, so the oracle checks each field)_builtand_default_rowsstay all-nullable;_outer_column, where a barepisusers.p, staystext[]?.