[observation] driver-sql (MySQL): introspectForeignKeys returns a composite key's columns in UNSPECIFIED row order — no `ORDER BY ORDINAL_POSITION`

[os-zhuang]

Found while implementing #11324 (the Postgres arm of the same method). Filed rather than fixed because it is a different dialect arm with a different mechanism, and — stated up front — it did not reproduce. #11324 is a separate subject and is not addressed by this issue.

What

packages/drivers/driver-sql/src/sql-driver.ts, SqlDriver.introspectForeignKeys, MySQL arm:

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
 AND TABLE_NAME = ?
 AND REFERENCED_TABLE_NAME IS NOT NULL

There is no ORDER BY. KEY_COLUMN_USAGE.ORDINAL_POSITION is the key ordinal and is selected by neither the projection nor an order clause, so the row order of a composite foreign key's columns is whatever the plan yields.

This arm has no correlation defect — COLUMN_NAME and REFERENCED_COLUMN_NAME sit on one row, so child and parent columns are paired by construction and there is no cartesian product to produce. What is unpinned is only the order of those correctly-paired rows.

Why the order is load-bearing at all

#11324 establishes the contract that a composite foreign key is expressed as ordered sibling rows in a flat per-column record — (x, y) references p (a, b) is x -> p.a then y -> p.b, in that order, with no ordinal field to recover the position from. The Postgres arm now pins it with ORDER BY … k.ord. The MySQL arm relies on the server.

What was measured, and what it showed

Live MySQL 8.0.46, a key declared out of column sequence so key order and column order are different answers:

create table ooo_parent (pa varchar(64), pb varchar(64), primary key (pa, pb));
create table ooo_child (
 id varchar(64) primary key, first_col varchar(64), second_col varchar(64),
 constraint fk_ooo foreign key (second_col, first_col) references ooo_parent (pa, pb));

The arm's query, verbatim, returned key order — second_col -> pa then first_col -> pb. So on this server, this version and this shape, the answer was correct.

That is why this is filed as an observation rather than a bug: there is no wrong answer to point at.

Why it is still worth recording

The sibling introspectPrimaryKeys MySQL arm carries a measured comment saying the same view, queried the same way without ORDER BY, returned column order rather than ordinal order on an out-of-sequence key — which is what #11101 fixed there, and it explicitly notes that the InnoDB folklore about "tending to" return ordinal order does not hold on that shape. So the identical gap exists here, one arm over, and the only thing standing between it and a wrong answer is a plan choice nobody declared.

The fix, if triage wants it, is one line — ORDER BY ORDINAL_POSITION — matching what introspectPrimaryKeys already spells. The cost of leaving it is that the contract above is pinned on one dialect and left to the optimizer on another.

Not measured here

Whether any plan shape on any supported MySQL version actually returns this view out of ordinal order for a foreign key. Establishing that would need a fixture large enough to change the plan, which is beyond what an observation should spend.

[claude]

Finding concentration round (triage seat, session session_01Kktexqp6uVuFMztvvTMf3V, 2026-08-24, maintainer-invoked): promoted → pm:queue (domain:engine already present), Task (S, one line + pin). Add ORDER BY ORDINAL_POSITION to the MySQL introspectForeignKeys arm — did not reproduce, but determinism of composite-key column order is the declared expectation and the fix is unambiguous. Serial note: same file as #11324's PG arm work — check in-flight claims on sql-driver.ts first.

---

Generated by Claude Code