Repository navigation
feat(postgres): infer the result key of a statement - #160
Conversation
The analysis carries `key`: the output columns that identify each row of the result, no two rows agreeing on all of them. Empty for one row at most, absent where nothing proves one; MySQL, SQLite and DuckDB report none yet. The oracle checks every run's rows against it.
A built module carries the analysis's result key, and its Standard Query object lists it as `key`: the output columns that identify each row, which a driver comparing two results reads to tell a row changed in place. Absent where no key is proven.
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
ℹ️ No critical issues. Minor suggestions only.
Reviewed changes
Reviewed the whole PR: Postgres now infers a result key, which reaches the oracle, the loader and the Standard Query object.
- Key inference —
cardinality::result_keycollects candidate keys from several sources: set operations withoutall, the left side ofintersect/except,group by,distinct/distinct on, the FROM's unique keys (non-null only), and every FROM item's key together. It reports the shortest and drops any key whose column name is repeated in the output. I checked these against the unique-key catalog query, which already excludes deferrable constraints, inheritance parents and operator classes or collations whose equality differs. That covers what the key needs to be sound. - Gating —
postgres.rscomputes the key only for aselectwhose cardinality ismany. MySQL, SQLite and DuckDB reportkey: None. - Oracle —
seed.rs::key_repeatsfails a case run where two rows match on every key column. About 40expected.txtfiles are re-blessed, and a newresult_keycase covers each rule. I checked the less obvious re-blessed keys (alias_column_list_shifted,using_merged,right_join_nulled,with_shadow,star_joined) against their SQL, and each is correct. - Run time —
src/loader.tswriteskeyintodefineQuery, andstandardQueryexposes it asquery["~standard"].query.key. A loader test covers it on pg and on node:sqlite. - Docs — a "Result key" paragraph with a captured example under Cardinality on the Postgres page, plus a bullet in the skill.
The failed Vercel check isn't caused by this PR: the Vercel deploy also fails on main (d3940d3) and on #154–#159.
ℹ️ --replay postgres never checks the key
oracle/src/replay/mod.rs checks each replayed statement for non-null columns and cardinality, but not the new key. It keeps only row counts and NULL flags, not row values. The Postgres regression suite is the oracle's broadest source of statements: inheritance trees, nondeterministic collations, odd types. As things stand, the key rules are checked only on the hand-written cases. CI doesn't run replay, so this doesn't block the PR, but the PR body's "checks every run of every statement" holds only for oracle/cases.
Technical details
# Replay does not check the result key
## Affected sites
- oracle/src/replay/mod.rs:~81 — the replayed result's check covers `nullable` and `cardinality`, not `analysis["key"]`
## Required outcome
- When an analysis carries `key`, a replayed Postgres statement fails as a contradiction if two of its rows match on every key column, a NULL equal to another NULL. This is the rule `seed.rs::key_repeats` applies.
## Suggested approach (optional)
- Have the replay runner keep each row's text values (or a hash of the key columns), and reuse `key_repeats`.ℹ️ StatementDescriptor in the spec has no key
spec/type-descriptor.ts's StatementDescriptor carries cardinality, which is the closest relative of the new fact, but it has no key. AGENTS.md says the engines' output follows the spec and that a change to the spec is a design decision. So it is worth deciding explicitly whether the result key belongs in the descriptor or stays specific to Reylo's Analysis.
claude-opus-5-5 ｜ 𝕏
There was a problem hiding this comment.
🟡 Changes recommended
Critical key-inference flaws report unique keys even when lossy set-operation conversions or result decoding collapse distinct values, making decoded-row matching unreliable.
2 open issues
What changed in this PR
This PR adds Postgres result-key inference to Reylo and exposes the inferred column names through Standard Query, allowing consumers to identify rows across query results.
Changes:
- Infer result keys from unique constraints, joins, grouping, distinct queries, and set operations.
- Carry optional key metadata from engine analysis into generated query modules.
- Add oracle validation, regression cases, runtime assertions, and documentation.
| File | Description |
|---|---|
tests/standard-query.test.ts |
Test key exposure and omission. |
src/standard-query.ts |
Expose optional result-key metadata. |
src/loader.ts |
Emit inferred keys in generated modules. |
src/index.ts |
Pass query-definition keys to runtime modules. |
src/execute.ts |
Add keys to runtime module types. |
src/engine-core.ts |
Add keys to the analysis interface. |
skills/using-reylo/SKILL.md |
Document result-key usage. |
site/lib/sql-modules.json |
Capture the new documentation example. |
site/content/docs/postgres.mdx |
Explain result keys with an example. |
oracle/src/seed.rs |
Check returned rows for repeated keys. |
oracle/src/main.rs |
Print keys in oracle expectations. |
oracle/cases/unique_keys/expected.txt |
Record keys for unique-index cases. |
oracle/cases/trust_key_operator_class/expected.txt |
Record keys for operator-class cases. |
oracle/cases/trust_catalog_exact_operands/expected.txt |
Record the selected primary key. |
oracle/cases/subquery_keys/expected.txt |
Record keys propagated through subqueries. |
oracle/cases/sequences/expected.txt |
Record the shadowing query’s key. |
oracle/cases/row_varying/expected.txt |
Record grouping and distinct keys. |
oracle/cases/row_values/expected.txt |
Record the correlated query’s key. |
oracle/cases/result_key/view.sql |
Cover keys propagated through views. |
oracle/cases/result_key/values.sql |
Cover duplicate VALUES rows. |
oracle/cases/result_key/union.sql |
Cover UNION’s whole-row key. |
oracle/cases/result_key/union_all.sql |
Cover key omission for UNION ALL. |
oracle/cases/result_key/to_one_join.sql |
Cover key preservation across to-one joins. |
oracle/cases/result_key/to_many_join.sql |
Cover repeated keys across to-many joins. |
oracle/cases/result_key/to_many_both_keys.sql |
Cover combined join keys. |
oracle/cases/result_key/subquery.sql |
Cover grouped subquery keys. |
oracle/cases/result_key/star.sql |
Cover keys selected through a star. |
oracle/cases/result_key/star_joined.sql |
Cover duplicate output names in joins. |
oracle/cases/result_key/setup.sql |
Define result-key test tables and view. |
oracle/cases/result_key/set_returning.sql |
Cover key omission for row-expanding calls. |
oracle/cases/result_key/partial_unique.sql |
Cover selection among available unique keys. |
oracle/cases/result_key/partial_unique_unfiltered.sql |
Cover unrestricted partial-index rejection. |
oracle/cases/result_key/partial_unique_live.sql |
Cover filtered, non-null partial keys. |
oracle/cases/result_key/nullable_unique.sql |
Cover nullable unique-key rejection. |
oracle/cases/result_key/nullable_unique_filtered.sql |
Cover null-filtered unique keys. |
oracle/cases/result_key/limited.sql |
Cover key preservation through LIMIT. |
oracle/cases/result_key/limited_union.sql |
Cover limited UNION ALL without a key. |
oracle/cases/result_key/left_to_one.sql |
Cover preserved-side outer-join keys. |
oracle/cases/result_key/left_nullable_side.sql |
Cover nullable-side key rejection. |
oracle/cases/result_key/lateral.sql |
Cover combined lateral-join keys. |
oracle/cases/result_key/intersect_all.sql |
Cover left-key preservation through INTERSECT ALL. |
oracle/cases/result_key/group_by.sql |
Cover grouping-column keys. |
oracle/cases/result_key/group_by_position.sql |
Cover positional grouping keys. |
oracle/cases/result_key/group_by_nullable.sql |
Cover nullable grouping keys. |
oracle/cases/result_key/group_by_hidden.sql |
Cover unselected grouping columns. |
oracle/cases/result_key/expected.txt |
Record result-key regression expectations. |
oracle/cases/result_key/except_all.sql |
Cover left-key preservation through EXCEPT ALL. |
oracle/cases/result_key/distinct.sql |
Cover DISTINCT’s whole-row key. |
oracle/cases/result_key/distinct_on.sql |
Cover DISTINCT ON keys. |
oracle/cases/result_key/distinct_on_output_name.sql |
Cover DISTINCT ON output aliases. |
oracle/cases/result_key/cte.sql |
Cover keys propagated through WITH queries. |
oracle/cases/result_key/cross_join_one_side.sql |
Cover insufficient cross-join key selection. |
oracle/cases/result_key/composite_unique.sql |
Cover complete composite keys. |
oracle/cases/result_key/composite_pinned.sql |
Cover composite keys with fixed components. |
oracle/cases/result_key/composite_part.sql |
Cover incomplete composite-key rejection. |
oracle/cases/result_key/by_unique.sql |
Cover non-null unique keys. |
oracle/cases/result_key/by_primary_key.sql |
Cover selected primary keys. |
oracle/cases/result_key/by_key_lookup.sql |
Cover key omission for single-row lookups. |
oracle/cases/result_key/aggregate.sql |
Cover key omission for scalar aggregates. |
oracle/cases/qualified_types/expected.txt |
Record keys alongside qualified types. |
oracle/cases/precision_rules/expected.txt |
Record view and self-join keys. |
oracle/cases/postgis_types/expected.txt |
Record keys for spatial queries. |
oracle/cases/pgvector_types/expected.txt |
Record keys for vector queries. |
oracle/cases/pg18_syntax/expected.txt |
Record keys for virtual-column queries. |
oracle/cases/params_once/expected.txt |
Record keys for parameterized queries. |
oracle/cases/param_keys/expected.txt |
Record keys alongside parameter diagnostics. |
oracle/cases/one_row_joins/expected.txt |
Record the many-side join key. |
oracle/cases/nullability/expected.txt |
Record grouped aggregate keys. |
oracle/cases/network_types/expected.txt |
Record keys for network-type queries. |
oracle/cases/narrowing_reach/expected.txt |
Record keys after nullability narrowing. |
oracle/cases/multirange_types/expected.txt |
Record keys for multirange queries. |
oracle/cases/limits/expected.txt |
Record keys preserved by limits. |
oracle/cases/large_queries/expected.txt |
Record keys for complex queries. |
oracle/cases/key_equality/expected.txt |
Record keys for equality-sensitive cases. |
oracle/cases/json_whole_rows/expected.txt |
Record the grouped whole-row key. |
oracle/cases/json_subqueries/expected.txt |
Record keys alongside nested JSON results. |
oracle/cases/json_schema_keys/expected.txt |
Record the outer-join key. |
oracle/cases/json_schema_checks/expected.txt |
Record the selected document-table key. |
oracle/cases/json_lateral_case/expected.txt |
Record keys for lateral JSON queries. |
oracle/cases/json_item_shapes/expected.txt |
Record the aggregated WITH-query key. |
oracle/cases/json_grouped_values/expected.txt |
Record the positional grouping key. |
oracle/cases/json_correlated/expected.txt |
Record keys for correlated JSON queries. |
oracle/cases/json_builders/expected.txt |
Record aggregate and subquery keys. |
oracle/cases/joins/expected.txt |
Record distinct, grouping, and set-operation keys. |
oracle/cases/inference_precision/expected.txt |
Record keys alongside existing inference cases. |
oracle/cases/index_keys/expected.txt |
Record primary keys in index-filter cases. |
oracle/cases/from_set_returning/expected.txt |
Record the lateral aggregate key. |
oracle/cases/from_functions/expected.txt |
Record keys for function-join queries. |
oracle/cases/edge_arithmetic/expected.txt |
Record keys alongside arithmetic results. |
oracle/cases/derived_tables/expected.txt |
Record keys through derived-table queries. |
oracle/cases/deep_statements/expected.txt |
Record keys for supported deep statements. |
oracle/cases/contrib_types/expected.txt |
Record keys for extension-type queries. |
oracle/cases/check_unions/expected.txt |
Record the preserved outer-join key. |
oracle/cases/builtin_functions/expected.txt |
Record the grouped function-query key. |
oracle/cases/arrays/expected.txt |
Record keys alongside array results. |
oracle/cases/aggregate_names/expected.txt |
Record grouped and subquery keys. |
oracle/cases/aggregate_levels/expected.txt |
Record keys for nested aggregate queries. |
engine/sqlite/src/sqlite.rs |
Leave SQLite result keys absent. |
engine/postgres/src/postgres.rs |
Attach inferred keys to SELECT analysis. |
engine/postgres/src/extension.rs |
Accept keys from extension analysis. |
engine/postgres/src/cardinality.rs |
Implement key inference and unit coverage. |
engine/mysql/src/mysql.rs |
Leave MySQL result keys absent. |
engine/duckdb/src/duckdb.rs |
Leave DuckDB result keys absent. |
engine/core/src/api.rs |
Add optional serialized result keys. |
engine/core/src/analysis.rs |
Initialize fallback analysis without a key. |
Review effort: Balanced
| } | ||
| // A set operation's columns are its left side's, by position and by name. | ||
| if s.op == Op::SetopIntersect as i32 || s.op == Op::SetopExcept as i32 { | ||
| keys.extend(select_keys(left, trust, &catalog.scoped(&with_names(s), s.with_clause.as_ref(), None), columns)); |
There was a problem hiding this comment.
Right, and the cardinality proof's own set-operation keys had the same gap. Fixed in 47b1090: an INTERSECT or EXCEPT keeps its l
8000
eft side's keys only through columns where each side passes one table column through and both are of one type, so neither side is converted. That applies to the result key and to the keys the cardinality proof reads from a subquery. A non-ALL operation still has its whole-row key, since its rows are distinct under the result's own types. The regression is oracle/cases/result_key/except_all_converted.sql, now without a key, plus unit tests.
| let columns: Vec<(String, bool)> = columns.iter().map(|c| (c.name.clone(), c.nullable)).collect(); | ||
| cardinality::result_key(sql, trust, catalog, &columns).filter(|key| !key.is_empty()) |
There was a problem hiding this comment.
Agreed. Fixed in 47b1090: only a column whose type is a scalar or an enum can be part of a result key. An array, a JSON document or a composite can read two values alike once decoded (array bounds, large JSON numbers), so a key that needs one is dropped, and a whole-row key over one goes too. The other scalars decode to exact text, numbers or bigints. The regression is oracle/cases/result_key/array_unique.sql: a NOT NULL UNIQUE int[] gives no key. The docs page says so too.
A set operation's left side keeps its keys only through columns no side converts to a common type, where two keys could become equal, in the result key and in the cardinality proof alike. An array, JSON or composite column is never part of a result key, since two of its values can read alike once decoded.
|
On the replay note from the review: that's right. |
There was a problem hiding this comment.
ℹ️ No critical issues. One minor note inline.
Reviewed changes
This run covers 47b1090, which keeps a result key only where its columns' values read apart.
identifiesfilter.postgres.rs::result_keynow marks each output column as able to identify a row only if it is aType::Scalar(other thanrecord) or aType::Enum.cardinality::result_keydrops any key that uses another column, so arrays, JSON and composites are never key columns.- Set-operation conversions. The new
unconvertedkeeps a left-sideintersect/exceptkey only where, at every key position, both sides pass through a table column of the same type. This applies toselect_keysand also toset_keys, the cardinality proof's keys for a set-operation subquery. That also closes a soundness gap in the cardinality proof: abigintkey turned intofloat8could match twice in a join. Wherever the check cannot tell (*, nested set operations, literals), it answers false, which is sound. - Oracle cases. Added
array_unique.sqlandexcept_all_converted.sql, plus unit tests for a literal and a column converted tofloat8. - Docs wording. The Postgres page and the skill now say Reylo "infers" a key and that array, JSON and composite columns are never part of one. This fixes the earlier wording thread, which is now resolved.
claude-opus-5-5 ｜ 𝕏
| // A value read whole as a number, a string or an enum's label stays as distinct as the database | ||
| // keeps it; an array (`{1}` and `[0:0]={1}` read alike), a JSON document (two large numbers | ||
| // read alike) or a composite may not, so no key holds one. | ||
| let identifies: Vec<bool> = columns.iter().map(|c| matches!(c.ty, Type::Scalar { .. } | Type::Enum { .. }) && !matches!(&c.ty, Type::Scalar { name } if name == "record")).collect(); |
There was a problem hiding this comment.
On Bun.sql, a timestamp or timestamptz column does not stay as distinct as the database keeps it. Bun.sql hands both back as a Date, and instantOfDate/utcText (src/values.ts) can only rebuild them to the millisecond. So select distinct created_at from events (key (created_at)) can return two rows with the same created_at once two events fall within one millisecond. The oracle's key_repeats compares the server's text, so it won't catch this. One fix is to leave timestamp/timestamptz out of identifies. The cost is every timestamp key on every driver, so documenting the Bun.sql exception may be the better trade.
Technical details
# Timestamp keys can repeat on Bun.sql
## Affected sites
- engine/postgres/src/postgres.rs:3338 — `identifies` admits every `Type::Scalar`, `timestamp`/`timestamptz` included
- src/values.ts:297 (`instantOfDate`), src/values.ts:617 (`utcText`) — Bun.sql's `Date` values are rebuilt to millisecond precision
## Required outcome
- The comment's claim ("a value read whole … stays as distinct as the database keeps it") holds for every supported driver, or the docs and skill state the exception.
## Open questions for the human
- Drop timestamp keys everywhere, or document that on Bun.sql a key of a `timestamp`/`timestamptz` column with sub-millisecond values may repeat?There was a problem hiding this comment.
The key belongs to the Standard Query, whose decode takes each Postgres value as the text the server sends (decoder in src/standard-query.ts refuses anything else). A driver running it reads rows raw, Bun.sql included, so a timestamptz keeps its microseconds there and the key holds. The millisecond loss is in Bun.sql's own Date path under .run(), which is that driver's limit and applies to any comparison of its timestamps. I'm leaving timestamp keys in, since dropping them would cost every driver for one driver's .run() rows.
Reylo now infers a statement's result key on Postgres: the output columns that identify each row of the result, so that no two rows of a result agree on all of them (a NULL counting as equal to another NULL). Anything that compares two results of the same query can match each row to its earlier version by them, telling a row that changed in place from one row going and another coming.
The analysis carries it as
key, and every built module exposes it on its Standard Query object asquery["~standard"].query.key, for example["id"]. It is present only for a result that can hold more than one row, and absent where the cardinality isone,maybeOneornone, and where nothing proves a key, which is always sound. MySQL, SQLite and DuckDB report none yet.What is a key
withqueries, subqueries and partial unique indexes thewhererestricts to included). Where each FROM item's key is selected, the keys together.group bycolumns that are output columns (not under grouping sets, rollup or cube), and thedistinct oncolumns; a group ordistinct onvalue may be NULL.distinct, or of aunion,intersectorexceptwithoutall;intersectandexceptkeep their left side's keys.limitoroffsetkeeps any key. Avalueslist, a set-returning call in the select list,union all, or a key column whose name another output column shares leaves none.A column of an array, JSON or composite type is never part of a key, since two of its values can read alike once decoded, and an
intersectorexceptkeeps its left side's keys only through columns neither side converts. Of the keys proven, the one of fewest columns is reported, the earliest columns breaking a tie.The Vercel preview fails on every
maincommit since #152, not because of this change.Checks
oracle/casesagainst the key: no two rows of a result may agree on all of its columns. The replay of each database's own test suite (--replay) records only row counts and NULLs, so it does not check keys yet. A new case,oracle/cases/result_key, covers each rule, and every existing case'sexpected.txtprints the key where one is proven (key (id)).engine/postgres/src/cardinality.rs; a loader test that a built module's Standard Query carries the key, on pg and on node:sqlite.