driver-sql (MySQL): introspectSchema returns a table's columns in ALPHABETICAL order, not declared column order — knex columnInfo() is an object keyed by column name

[os-zhuang]

Found while working #11101 (key order in introspectPrimaryKeys). This is the same unordered-result defect class one method over, in introspectColumns, and it is out of that card's scope. Measured on a live MySQL 8.0.46.

What

packages/drivers/driver-sql/src/sql-driver.ts, SqlDriver.introspectColumns:

const columnInfo = await this.knex(tableName).columnInfo();
const columns: IntrospectedColumn[] = [];
for (const [colName, info] of Object.entries<any>(columnInfo)) {
 /* … */
 columns.push({ name: colName, /* … */ });
}

The array's order is the key-insertion order of knex's columnInfo() object, which is the row order of knex's own information_schema.columns query. That query carries no ORDER BY, and MySQL does not return declared column order for it.

Measured (MySQL 8.0.46)

Fixture (from #11101's test file):

create table os11101_shipment_legs (
 carrier_code varchar(64) not null,
 shipment_id varchar(64) not null,
 leg_seq integer,
 primary key (shipment_id, carrier_code)
)

introspectSchema().tables[t].columns.map(c => c.name) returns:

dialect	result
SQLite	carrier_code, shipment_id, leg_seq — declared column order
Postgres 16.13	carrier_code, shipment_id, leg_seq — declared column order
MySQL 8.0.46	carrier_code, leg_seq, shipment_id — alphabetical

This surfaced as a real red: #11101's first draft read column order back from introspectSchema() to prove its fixture was out-of-sequence, and that leg failed on the live MySQL cell only. The test now holds column order as a constant pinned against its own DDL text, with a note pointing here.

Why it matters

Same argument as #11101, one notch weaker because a column list is not an addressing key:

• Cross-dialect disagreement. The same table introspected through different dialects returns different columns arrays. Any consumer comparing two introspection snapshots across dialects, or comparing one against a declared object, sees a difference that is not a difference.
• Generated artifacts. ExternalDatasourceService.generateObjectDraft and the persisted external_catalog (ADR-0015) carry this order into their output, so a federated object drafted from a MySQL remote gets its fields alphabetized rather than in the order the remote declares them — cosmetic in the DB, but it is the order a human then reads and edits.
• It is invisible until someone declares a table whose alphabetical order differs from its declared order, which is most tables.

Likely shape of the fix

Replace the columnInfo() call with the driver's own per-dialect query ordered by the catalog's ordinal — information_schema.COLUMNS.ORDINAL_POSITION on MySQL, pg_attribute.attnum (or information_schema.columns.ordinal_position) on Postgres, PRAGMA table_info row order on SQLite — rather than sorting after the fact, since the ordinal is the fact and the row order is not.

⚠️ Note the cost: columnInfo() also supplies type, nullable, defaultValue and maxLength, and each dialect spells those differently. This is a larger change than #11101's two ORDER BYs, which is the other reason it is filed rather than folded in.

Not measured here

Whether MySQL's order is stably alphabetical or merely happened to be here — like the key-order defect this replaces, the query specifies no order, so the answer is "unspecified" regardless of what this server did. MariaDB was not measured.

[os-zhuang]

Triage: pm:queue, domain:engine, Bug — see the family-anchor comment on #11161 (driver-sql introspection trio; prefer one claim covering all three, region-declared against in-flight #11067/#11176).

---

Generated by Claude Code