Repository navigation
fix(postgres): keep a table's not null in array_agg of its whole row - #97
Merged
Merged
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
Contributor
There was a problem hiding this comment.
✅ No new issues found.
Reviewed changes
This review covers the Postgres engine's new rule that a top-level array_agg of a table's whole row holds stored rows of that table, along with its oracle cases and the rebuilt artifact.
sublink_rowsreadsarray_agg(e)/array_agg(e.*): a top-levelFuncCalltarget over a whole row named by an item of the SELECT's own FROM is now recorded as an array of that table's rows. It is tagged as an aggregate through a new fourth tuple field.agg_star,WITHIN GROUP, multi-argument calls, named arguments and schemas other thanpg_catalogare refused.stored_sublinkschecks trust: it now takes&Trustand drops the aggregate targets whenarray_aggis shadowed. Becausearray_aggis already inPOSTGRES_GROUPED_NEVER_NULL,postgres_function_names()asks the catalog about it. The existing guards still apply on the top-level path and the probe path: plain-table FROM items only, no table named like a WITH query,unambiguous(name), and the column type must be an array of the table's row type.- Oracle
inference_precision: therows:elements inwhole_row_agg_groupedandwhole_row_outer_joinednow carry the catalog's nullability, CHECK enums and JSON Schema. Three new statements cover a built row that stays all-nullable, plus the inner-join and outer-join versions insidejsonb_build_object, where the oracle checks each field's values. packages/postgres/engine.wasm: rebuilt.
I checked these cases and found no problem with them:
- Outer joins: a null-extended whole-row Var is
NULL, so the element stays?. - Grouping sets: functional dependency only uses Vars common to every set, so a grouped
ecan't be partly nulled. - Shadowing: a user-defined
array_agg(events)that would win resolution is caught by the trust check.
The existing "Whole rows" rule in postgres.mdx already describes this behavior, so the docs don't need to change. I didn't run the engine tests locally because this environment had no prepared workspace. CI's engine and oracle jobs are the check.
claude-opus-5-5 ｜ 𝕏
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
array_agg(e)over a table's whole row typed every field of its element row as nullable, even though the table'sNOT NULLholds for each of those rows.jsonb_agg(e)beside it already kept those constraints. Each element is a row the scan read, so it meets the table's constraints, the same as anarray(select c from child c …)already does.rows: (events{ id: int4?, kind: text?, payload: json?, note: text? }!)[]rows: (events{ id: int4, kind: 'click' | 'key', payload: json {…}, note: text? }!)[], the same element asevents, with the CHECK-derived enum and JSON SchemaThe rule
A top-level
array_agg(e)orarray_agg(e.*)holds stored rows ofe's table when all of these hold:eis an item of the SELECT's own FROM;e;array_aggis the built-in one.It may be ordered, filtered or over a window. The column is marked
whole_row, the same mark anARRAYsubquery of stored rows gets.Outside JSON, the run time decodes a composite or an array of them as the text Postgres prints, typed
string. So the difference shows in the type and, inside JSON (jsonb_build_object('rows', array_agg(e))), in each field's type, where the oracle checks the values.An outer join nulls a whole row: its element stays nullable, and a row that is there keeps its fields' own nullability, as
jsonb_agg(p)beside it shows. A row value the statement builds, such asarray_agg(row(…)::events), keeps every field nullable.Oracle (
inference_precision)whole_row_agg_grouped:rows:element goes fromevents{ id: int4?, kind: text?, payload: json?, note: text? }!to theevents{ id: int4, kind: 'click' | 'key', payload: json {…}, note: text? }!beside it.whole_row_outer_joined:rows:element goes fromposts{ id: int4?, user_id: int4?, title: text?, published_at: timestamptz? }?toposts{ id: int4, user_id: int4?, title: text, published_at: timestamptz? }?.whole_row_array_agg_in_jsonandwhole_row_array_agg_outer_in_json:array_agg(e)insidejsonb_build_object, where the oracle checks each field.whole_row_array_agg_built: a built row stays all-nullable.