Repository navigation
fix(engine)!: infer tighter types across joins, functions and writes - #145
Merged
Merged
Conversation
extract and date_part of a field that grows with time, at time zone, make_interval, substring by bounds, position, format, the range tests, the advisory locks, the session functions, current_setting of one argument, in, between, = any (array[...]), is json, grouping(), first_value and last_value over a frame holding the current row, and any_value and the ordered-set aggregates under a plain group by now follow their inputs; system columns are the scanned row's own. Two set-returning calls in one select list run in step and pad the shorter with NULL, so neither is non-null there; two row constructors compare field by field, NULL where a pair of fields is; and current_schema is NULL where no schema of the search path exists.
A strict set-returning function returns no row for a NULL argument, so generate_series is non-null whatever its arguments, and the columns of json(b)_each, json(b)_array_elements, json(b)_object_keys and regexp_split_to_table are settled from the SQL, lateral ones included. A with ordinality number and a JSON_TABLE ordinal are never NULL, a bare name among several FROM items is the function's where the tables lack it, a function that reads the items before it is probed over them, and a LATERAL subquery that reads them is read by the rules alone.
# Conflicts: # engine/postgres/src/postgres.rs
json_build_array and jsonb_build_array are typed as a tuple of their items, JSON_ARRAY as one too, or as an array of its items where a NULL one may be left out, and JSON_OBJECT of literal keys as an object. array_to_json of ARRAY(SELECT ...) is an array of the subquery's values, and of array_agg as json_agg is. Each was unknown before.
# Conflicts: # engine/postgres/src/infer.rs # engine/postgres/src/postgres.rs
# Conflicts: # packages/duckdb/engine/engine.core.wasm # packages/mysql/engine/engine.core.wasm # packages/postgres/engine/engine.core.wasm # packages/sqlite/engine/engine.core.wasm
|
The latest updates on your projects. Learn more about Vercel for GitHub. 1 Skipped Deployment
|
Contributor
There was a problem hiding this comment.
ℹ️ One soundness gap in the new rule for set-returning calls that run in step, plus a note on the lexer change.
Reviewed changes
All 152 files at 2ffa02e: Postgres inference, the core lexer and its TypeScript port, catalog queries, oracle cases, docs and the rebuilt components.
- Lexer: on Postgres, a colon right after a subscript's
[now starts a slice (tags[:n]), inlex.rsandsrc/lex.tsalike. - Narrowing:
*andt.*expand to qualified references that the WHERE narrows. An inner join's ON narrows the sides no outer join nulls. The WHERE of an UPDATE or DELETE narrows its RETURNING, guarded against rules, views, row triggers, assigned columns and generated columns. - Built-ins: new lists for date parts, the ordered-set aggregates, frame values, the series functions and calls with no argument. Also
IN,BETWEEN,= ANY(ARRAY[…]), row comparisons field by field,IS JSONandGROUPING.CURRENT_SCHEMAis now nullable. - Set-returning calls in step:
targets_lockstepandset_callsmark a built-in set-returning call nullable when the select list holds more than one. The JSON-shape builder does the same. - FROM items: columns are known without a probe for
FROM_FUNCTIONS,JSON_TABLEandWITH ORDINALITY. Also new: LATERAL subqueries read by the rules, bare names among several items, the extra columns of SEARCH and CYCLE, and a WITH query nulled at each place it is read. - Parameters and cardinality: write targets keep their schema, and the catalog queries take a count and resolve names as written. A bare parameter written to a column whose CHECK lists strings is typed as that union. A query with no FROM but a WHERE is
maybeOne,GROUP BY ()is dropped, andlower(:x)and integer arithmetic match keys. - JSON shapes:
Shape::Tupleforjson[b]_build_arrayandJSON_ARRAY, plusJSON_OBJECTandarray_to_json(ARRAY(…))/array_to_json(array_agg(…)).
ℹ️ The subscript lexer change is visible to existing queries
On Postgres, a query written as select tags[:i] from t used to bind :i as a parameter. Now it reads i as a column and the result as a slice, so :i drops out of Params and the query fails its describe or changes type. This matches how Postgres reads the SQL, and postgres.mdx documents it. AGENTS.md asks for ! after the scope for a breaking change, and for the skill to be updated with any change users notice, so a maintainer should decide whether this needs either.
Technical details
# The subscript lexer change is visible to existing queries
## Affected sites
- engine/core/src/lex.rs:396 — `_ if last == Last::Subscript && dialect == Dialect::Postgres` now skips the colon
- src/lex.ts:51 — the same rule in the run-time lexer
- skills/using-reylo/SKILL.md — never mentions the `tags[(:i)]` form
## Required outcome
- A maintainer decides whether the PR title takes `!` (`fix(engine)!: …`) and whether the skill should note that a parameter used as a subscript is written `tags[(:i)]`.claude-opus-5-5 ｜ 𝕏
A set-returning call in a SELECT's ORDER BY, DISTINCT ON or GROUP BY is an entry of the same target list as the select list's, so Postgres runs it in step with them and pads the shorter with NULL: SELECT generate_series(1, 2) ORDER BY generate_series(1, 4) is NULL, NULL, 2, 1. Those clauses, and a window's definition, now count toward it.
Contributor
There was a problem hiding this comment.
✅ No new issues found. The gap in the rule for set-returning calls that run in step is closed.
Reviewed changes
The commits since 2ffa02e: the fix for set-returning calls outside the select list, and the rebuilt Postgres component.
- Set-returning calls in step:
targets_lockstepnow takes the statement and also counts calls in theSelectSt 8BD0 mt'ssort_clause,distinct_clause,group_clauseandwindow_clause. Becauseset_callswalks the JSON generically,SortByandWindowDefnodes need no special handling. Over-counting (a repeated ORDER BY call, or a window definition) costs only precision. - JSON shapes:
jsonshape::plan_withuses the same function, so a JSON field over a set-returning call is nullable under the same rule. - Tests: unit assertions in
infer.rsandjsonshape.rs, plus four oracle statements inbuiltin_functions(set_returning_order_by,_distinct_on,_group_by,_order_by_json). Each one typesgasint4?. - Components: rebuilt the Postgres component.
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.
Precision fixes for Postgres inference, found by running real-world queries (sqlc's Postgres test suite and examples, pgtyped's examples, Supabase's postgres-meta catalog queries) through the engine. Every change was checked by the oracle. Three of them correct types that could come back NULL where non-null was claimed.
Could be NULL, was typed non-null (now nullable)
row(a, b) = row(…)is non-null only when every field is.current_schema()is NULL when no schema on the search path exists.Narrowing
*andt.*are narrowed by the WHERE's null tests, as written-out columns are.t.*, SEARCH or CYCLE keeps its columns' types.Functions
extract/date_part, but only for fields that grow with time;monthof an infinite timestamp is still NULL;at time zone,make_interval,substring,position,format;in,between,= any(array[…]),is json,grouping();version();first_valueover a frame that holds the current row, and ordered-set aggregates under GROUP BY.generate_series(with parameter bounds, and on the preserved side of a left join),jsonb_each,WITH ORDINALITY,json_table'sfor ordinality.count(*)in a lateral subquery is non-null.json_build_array,jsonb_build_array,JSON_ARRAY,JSON_OBJECTandarray_to_json(array(…))are typed. Core gainsShape::Tuplefor them.Parameters, lexer and cardinality
tags[:n]on Postgres as a slice, not a parameter (engine/core/src/lex.rsandsrc/lex.ts). Breaking: a query that subscripted by a parameter astags[:i]now readsias a column and fails its describe; writetags[(:i)]. The skill says so.public.kv, or a table outside the search path) keeps its parameters' nullability, CHECKs and domains.lower(:x)or:id::int + 1, or read through a view, bounds the rows tomaybeOne.group by ()with an aggregate isone.Also in this PR
narrowing_reach,builtin_functions,from_set_returning,json_builders,computed_keys. New statements inarrays,params_writtenandcheck_unions. Other cases'expected.txtchanges are the narrower types above.site/content/docs/postgres.mdxstates the new rules, and the docs' captured types are updated.