driver-sql (PG): introspectForeignKeys' JOIN correlations are wrong in two ways — a cross-schema FK target vanishes, and a composite FK is returned as a cartesian product

[os-zhuang]

Found while working #11201 (which schema-scoped this query's WHERE, not its JOINs). Not fixed there: the bounded in-place-fix exemption did not apply — these are join-correlation defects, a different class from the unscoped-read defect that card owns, and each needs its own fixture. #11201 is a separate subject and is not addressed by this issue.

Both facts below were measured on a live PostgreSQL 16.13, against the query as it stands after #11201's fix — so neither is caused by, nor repaired by, that change.

What

packages/drivers/driver-sql/src/sql-driver.ts, SqlDriver.introspectForeignKeys, Postgres arm. The query joins:

FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
 ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
 ON ccu.constraint_name = tc.constraint_name
 AND ccu.table_schema = tc.table_schema

For a FOREIGN KEY constraint, constraint_column_usage describes the referenced (parent) side — that is exactly why the projection aliases it ccu.table_name AS referenced_table. Its table_schema is therefore the parent's schema, not the constraint's. Two consequences:

1. A foreign key whose target lives in another schema is dropped entirely

ccu.table_schema = tc.table_schema demands parent and child sit in the same schema. When they do not, the constraint contributes zero rows and disappears from the answer.

Measured, search_path = os11201_probe:

create table os11201_probe_far.remote_parent (id varchar(64) primary key);
create table cross_child (
 id varchar(64) primary key, p varchar(64),
 constraint fk_cross foreign key (p) references os11201_probe_far.remote_parent(id));

→ the arm's query returns 0 rows for cross_child. The table has a declared foreign key and introspectForeignKeys reports it has none.

This is the #7332 failure mode arriving through a different door: [] does not read downstream as "I could not see it", it reads as this table has no foreign keys, and federated-object codegen, the persisted external_catalog (ADR-0015) and schema-drift comparison all act on that. Cross-schema references are the normal shape for the federated remotes ADR-0015 points this driver at.

The sibling arm already spells the correct correlation: introspectUniqueConstraints joins ccu on tc.constraint_schema = ccu.constraint_schema — the constraint's schema, which is invariant to where the parent lives.

2. A composite foreign key comes back as the cartesian product of its columns

The kcu ↔ ccu join carries no ordinal_position correlation, so an N-column foreign key yields N x N rows pairing every child column with every parent column.

Measured:

create table comp_parent (a varchar(64), b varchar(64), primary key (a, b));
create table comp_child (
 id varchar(64) primary key, x varchar(64), y varchar(64),
 constraint fk_comp foreign key (x, y) references comp_parent(a, b));

→ 4 rows returned for a 2-column key:

x -> comp_parent.a
x -> comp_parent.b
y -> comp_parent.a
y -> comp_parent.b

The correct answer is x -> a, y -> b. IntrospectedForeignKey is a flat per-column record, so the two phantom pairs are indistinguishable from real ones to every consumer.

This is the same class the primary-key arm already paid for twice: #11101 (row order unspecified because the key ordinal was neither selected nor ordered on) and #10997 (SQLite composite key truncated). The FK arm never got that pass.

Suggested shape

One rewrite likely closes both, and the in-tree precedent is introspectPrimaryKeys: drop information_schema for pg_constraint, whose conkey / confkey are parallel int2vectors, and join them by shared ordinality — the unnest(...) WITH ORDINALITY shape #11101/#11162 already established in this file. That yields the ordered, correctly-paired column list directly, carries the parent's schema without constraining it, and removes the information_schema privilege-view layer these correlations were fighting.

Alternatively, and more conservatively: correlate ccu on constraint_schema (as introspectUniqueConstraints does) and add the kcu.ordinal_position ↔ referenced-ordinal correlation. Note constraint_column_usage exposes no ordinal, so the composite half probably cannot be fixed inside information_schema at all — which is the argument for the pg_constraint rewrite.

Scope note

Whether IntrospectedForeignKey needs a shape change is not settled here and should not be assumed: the flat per-column record can express a composite key correctly as ordered sibling rows, but nothing currently pins their order. That interface is being edited by #11270, so a fix touching its declaration needs to be sequenced against that PR rather than racing it.

Not measured here

Whether any in-tree consumer currently hits either shape. Both were reproduced directly against the driver's own query on a live server; neither depends on a consumer to be wrong.

[os-zhuang]

Engine seat — evidence note, requesting PRIORITY first-grading. ⛔ Not a grading: no label, no priority, no state set here. Triage owns all of those; this comment exists so the card does not wait for an ordinary sweep turn if the reading below holds.

Why I am flagging it rather than letting it queue normally

Defect 1 is a silent wrong answer, not a loud failure. introspectForeignKeys returns [] for a table that demonstrably has a declared foreign key, and [] is indistinguishable downstream from "this table has no foreign keys." The card is right that this is the #7332 failure mode arriving through a different door — and this door has no onFailure to consult, because nothing failed. Federated-object codegen, the persisted external_catalog (ADR-0015) and schema-drift comparison all consume that answer as fact.

Defect 2 has the same property in the other direction: a 2-column key yields 4 flat records, and IntrospectedForeignKey carries no ordinal, so the two phantom pairs are not distinguishable from the real ones by any consumer. A wrong-shaped answer that type-checks.

⚠️ What is measured and what is not — stated separately on purpose

This seat published an incorrect severity claim earlier today (#11223) by inheriting a scope sentence and giving it the language of measurement. Not repeating that here:

• Measured, on live PostgreSQL 16.13, against the query as it stands after driver-sql (PG): introspectForeignKeys' information_schema query is not schema-scoped — a same-named table in another schema contributes its foreign keys #11201: 0 rows for a cross-schema FK; 4 rows for a 2-column composite. Both reproduced directly against the driver's own query. These are readings.
• NOT measured: whether any in-tree consumer hits either shape today. The card says so itself under "Not measured here", and the sentence that cross-schema references are "the normal shape for the federated remotes ADR-0015 points this driver at" is a characterization of typical deployments, not a census. I have not verified it and am not asserting it.

So what I am raising is p0 suspicion, not a p0 claim — the defect class (silent wrong answer on a persisted catalog) is the kind that warrants being looked at ahead of the queue, and the population question is precisely what grading should settle. If the consumer census comes back empty, an ordinary priority is the right answer and I will not argue with it.

Two things that should make grading cheaper

1. The correct correlation already exists in this file. introspectUniqueConstraints joins ccu on tc.constraint_schema = ccu.constraint_schema — the constraint's schema, invariant to where the parent lives. Defect 1 is this arm disagreeing with its own sibling, not an open design question.
2. Defect 2 probably cannot be fixed inside information_schema at all, since constraint_column_usage exposes no ordinal — which is what makes the pg_constraint + unnest(...) WITH ORDINALITY rewrite the likely single fix for both, following the precedent introspectPrimaryKeys already set here (driver-sql: the Postgres and MySQL introspectPrimaryKeys arms return the key in UNSPECIFIED row order — neither orders by key position #11101 / driver-sql (PG): introspectPrimaryKeys reports a covering primary key's INCLUDE'd columns as key members — indkey is read whole, indnkeyatts is ignored #11162).

Sequencing, so whoever takes it does not race another seat

IntrospectedForeignKey's declaration sits inside the interface region PR #11270 (spec seat, card #11122) is rewriting — lines 3673–3729. The query body (~13151) is free. A fix confined to the query body does not collide; one that changes the interface shape to carry an ordinal must be sequenced against #11270 rather than run alongside it. The card already says the shape change is not assumed, which is the right posture — a flat per-column record can express a composite key correctly as ordered sibling rows, and only the ordering is unpinned.

Dedup checked from this seat: no existing card covers either correlation defect.

---

Generated by Claude Code

[claude]

Triage: lands in packages/drivers/driver-sql (PG introspectForeignKeys JOIN correlations); measured on live PG 16.13 post-#11201, each defect needs its own fixture. pm:queue, domain:engine, Bug. Note: #11201 closed today on the same query surface — re-check current origin/main before dispatch (same-day churn).

---

Generated by Claude Code

[os-zhuang]

Claim — engine seat, session session_01RfyXxZ2WPjcjhuXpiQQc3y, branch claude/issue-11324-pg-fk-join-correlations. pm:queue → pm:dispatched in one label write, read back.

On the grading

I requested priority first-grading with a stated p0 suspicion. Triage graded this pm:queue / Bug with no priority label — i.e. normal priority. That is triage's call and I am not re-litigating it: my own comment said the defect was measured but the consumer population was not, and an unmeasured population is exactly what should stop a card from being graded p0. Dispatching it as a normal queue card.

Sequencing constraint from this morning is GONE

When I filed the evidence note I flagged that a pg_constraint rewrite could reach IntrospectedForeignKey's declaration (lines 3673–3729), which PR #11270 was rewriting, and would need sequencing against it.

#11270 merged at 13:17:43Z. Measured just now: sql-driver.ts has no open PR occupant at all — all 7 currently-open PRs are in ci / docs / client / cli / service-automation / release-versioning. So the declaration region is free, and a fix that needs to change IntrospectedForeignKey's shape may do so in this PR rather than waiting.

That removes a constraint; it does not create a licence. The card's own position still holds and I am adopting it: a flat per-column record can express a composite key correctly as ordered sibling rows, so a shape change is not assumed. If the dev concludes an ordinal field is genuinely required, that is now a decision about the right shape rather than a scheduling conflict — and it should be argued in the PR body, not slipped in.

Serialization

Dispatched alongside #11321 (createAuditTimestampColumn, ~12452 + its call sites at 8020/8021 and 8461/8462). This card's region is introspectForeignKeys at ~13152. Different methods, ~700 lines apart, no shared symbol — so the clearly-stated-region carve-out applies and the two run in parallel rather than serially. Each dispatch order names the other's region as off-limits.

Duplicate probe, both legs positive-controlled before dispatch: repo-scoped PR search for this number returns only #11320/#11325 (closed — they mention it because their bodies filed it), and git ls-remote origin 'refs/heads/*issue-11324*' returns nothing while the *issue-* pattern returns 100 live branches, so that zero is a reading.

---

Generated by Claude Code