Repository navigation
fix(postgres): keep not null on any column beside a table read twice - #59
Merged
Merged
Conversation
An outer join nulls a table by its name, so every reading of it: a column of a table read twice is nullable where the join nulls the table and as the catalog says where it does not, whichever reading it comes from. A bare column or a whole row beside such a table was nullable.
|
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
I reviewed the Postgres settle change, which stops a table read twice from hiding provenance, along with the two oracle cases it moves and the rebuilt artifact.
settleno longer degrades a repeated table:hiddennow comes from grouping sets alone. A column with provenance is nullable when the catalog says so or when an outer join nulls its table by name. Where a top-levelalias.columnnames its reading, that reading's own flag is used instead.- New oracle case
large_queries/repeated_table_outer_join_short.sql: here a scalar subquery readsordersagain beside aleft join, andnamenow comes outtext(it wastext?). inference_precision/whole_row_self_join.sql: injsonb_agg(b)overusers a left join users b, the fields are non-null inside the element, and the element stays nullable.packages/postgres/engine.wasmrebuilt:tests/fingerprint.test.tspasses for all three artifacts at this head.
I checked the soundness argument against analyze.rs:
nullable_tablesis collected by relation name over the whole tree (relnames_inon every outerJoinExpr's nulled side). That covers derived tables, LATERAL subqueries and joins inside WITH queries.unreduced→nulled_tablesexpands a nulled WITH query to every relation it reads, including a DML target.- The strict-WHERE and inner-ON reductions only apply to a relation with
occurrences == 1, so a repeated name is never taken off. own_provenanceis false only for a set operation or a statement with no parse.aliasedis empty in both cases, so folding the old degradedreadingbranch into the main branch loses nothing.- The self-join rule in
site/content/docs/postgres.mdx(e.namestays non-null inemployees e left join employees m) still holds.
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.
A statement that reads a table twice, with an outer join anywhere in it, typed every column but a top-level
alias.columnas nullable: the describe names a column's table, never which reading of it, so provenance was taken to say nothing. A large statement meets this easily, since a scalar subquery reads a table its WITH queries already read, and aleft joinsits beside it.mainlarge_queries/repeated_table_outer_join_short.sql:select o.id, name, email, r.rating, exists (select 1 from orders x …) from orders o join users u … left join reviews r …name: text?name: textinference_precision/whole_row_self_join.sql:jsonb_agg(b)ofusers a left join users b[users{ id: int4?, name: text?, email: text? }?][users{ id: int4, name: text, email: text? }?]An outer join nulls a table by its name: every reading of it, and every relation a WITH query it nulls reads. So a column of a table some outer join nulls is nullable whichever reading it comes from, and a column of a table no outer join nulls is what the catalog says, whichever reading it comes from. Neither needs the reading, and a repeated table no longer hides provenance; a top-level
alias.columnstill takes the reading its alias names, which tells a table joined to itself apart (employees e left join employees m, wheree.namestays non-null). Grouping sets still do.Checked
--strict-coverage(the two lines above change, and every other case is as it was); the whole workspace builds with no warning.packages/postgres/engine.wasmrebuilt (with the other two in one run; only this one changes);nub run typecheckandnub run testagainst Postgres 18.