driver-sql: the Postgres and MySQL introspectPrimaryKeys arms return the key in UNSPECIFIED row order — neither orders by key position

[os-zhuang]

Split out of #10997 (SQLite composite-key truncation). #10997 repaired the SQLite arm — completeness and key ordering. The PG and MySQL arms of the same method do not carry that completeness defect, but neither orders its result by key position, so the composite key they return is in unspecified row order.

packages/drivers/driver-sql/src/sql-driver.ts, SqlDriver.introspectPrimaryKeys.

What

Postgres arm:

SELECT a.attname as column_name
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
 AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = ?::regclass
 AND i.indisprimary

i.indkey is an int2vector holding the key's attnums in key order. a.attnum = ANY(i.indkey) is a membership test — it reads the vector as a set and discards the position. With no ORDER BY, the row order is whatever the plan yields (in practice pg_attribute scan order, i.e. column order). So the set is right and the order is not the declared key order whenever the key is declared out of column sequence.

MySQL arm:

SELECT COLUMN_NAME as column_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
 AND TABLE_NAME = ?
 AND CONSTRAINT_NAME = 'PRIMARY'

KEY_COLUMN_USAGE.ORDINAL_POSITION is exactly the key ordinal and is selected by neither the projection nor an ORDER BY. InnoDB tends to return ordinal order in practice, but nothing in the query requires it.

Why it matters

Same reason the ordering half of #10997 mattered: primaryKeys is consumed as an addressing / upsert-conflict-target key (federated-object codegen, the persisted external_catalog under ADR-0015, and schema-drift comparison against a declared key). For those consumers a key in the wrong order is a different key. After #10997 the SQLite arm reports declared key order and these two do not, so the same table introspected through different dialects can disagree.

Likely shape of the fix

• MySQL: add ORDER BY ORDINAL_POSITION.
• Postgres: join against the ordinality of indkey rather than testing membership — e.g. unnest(i.indkey) WITH ORDINALITY (or generate_subscripts) joined to pg_attribute, with ORDER BY on the ordinal.

Why this was NOT fixed in #10997's PR

Two reasons, both about verification rather than difficulty:

1. Neither arm is executable in the agent container — no listener on 5432/3306, no DSN in the environment, no mysql client, and the Docker CLI is present but has no daemon socket (/var/run/docker.sock absent). The original filer of driver-sql: SQLite introspection reports only the FIRST column of a composite primary key (pk === 1 vs SQLite's 1,2,3… numbering) #10997 hit the same limit. Landing a rewritten pg_index query that has never been parsed by a real server is a guess, not a fix.
2. The whole method body is wrapped in catch { /* silently ignore */ } and returns [] on error. A rewritten query that is syntactically invalid on some PG version does not fail loudly — it degrades to no primary key at all, with no diagnostic. That turns a cosmetic ordering gap into total, invisible loss of the key. Unexecuted SQL behind a silent catch is the wrong risk to take for an ordering improvement.

So this needs a seat with a reachable Postgres and MySQL. Worth pairing with a look at whether that silent catch should stay swallowing, which is arguably the more serious finding here and is deliberately not folded in.

Not measured here

Both arms were read, not run. No claim is made about what a live server actually returns for either query — only about what the SQL as written does and does not guarantee.

[os-zhuang]

First-touch: promoted finding → pm:queue, Bug, domain:engine (packages/drivers/driver-sql, same method #10997 repaired for SQLite). Rationale: after #10997 the SQLite arm reports declared key order while PG/MySQL arms return unspecified order — the same table introspected through different dialects can disagree, and the consumers (upsert conflict targets, external_catalog under ADR-0015, drift comparison) treat a differently-ordered composite key as a different key.

Binding dispatch constraints, from the card's own reasons for not folding this into #10997: ① hard serial after #10997 — same method body, #10997 is pm:dispatched with an in-flight claim; do not claim this until that lands, then re-verify line anchors on the merged ref. ② The fix must not land unexecuted SQL behind the method's silent catch { return [] } — a syntactically bad query degrades to no key at all with no diagnostic. Acceptable execution evidence, in order of preference: the repo's PG/MySQL integration rig if one exists in CI; otherwise a seat with a reachable Postgres+MySQL (agent containers have neither daemon, as the card measured). If neither is reachable, deliver the MySQL arm (ORDER BY ORDINAL_POSITION — plain information_schema, low syntax risk) only with the PG arm reported as needs-execution, rather than landing an unparsed unnest … WITH ORDINALITY rewrite. ③ The silent-catch posture itself is deliberately out of scope — file it as its own finding if not already recorded. Size S code, M verification.

---

Generated by Claude Code

[os-zhuang]

Constraint update, same round: #10997 CLOSED at 17:38Z (its PR merged) — the hard-serial condition in the grading comment above is already satisfied. The claiming seat's remaining obligation from that clause is only the second half: re-verify this card's line anchors and the SQLite arm's landed shape on the merged ref before editing.

---

Generated by Claude Code