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

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.

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

Claim: PM loop round 2
Session: session_01VK8rFDtg8eREaxBGX99Csn
Branch: claude/issue-11379-fk-introspection-ordinal-order
Worktree: objectstack-issue-11379
Domain: domain:engine
File surface: packages/drivers/driver-sql/src/sql-driver.ts + its test files (stop on breach; explain in the report)
Container & model: S, mode:subagent, model: opus (tier: dispatch-gates.mjs --tier returns no path-derived mandate. The edit is one line; the pin is the judgement call — see below — which is why this is not at the sonnet floor.)
Clause-②: no — determinism of a row order the contract already declares. No accept set moves, no public surface widens.
Serial constraints cleared, measured on origin/main @ cad8b42, not inherited:

Target region: introspectForeignKeys begins at 13757; its MySQL arm is 13868-13884.
PR fix(driver-sql): introspectUniqueConstraints reports single-column uniqueness on all three dialects (#11202) #11657 (domain:spec seat, card driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints #11202, draft) is open on this same file — its hunks are at 3686 and 14059-14232, i.e. introspectUniqueConstraints (which begins at 14067) and the IntrospectedColumn interface. Disjoint, different methods roughly 200 lines apart. Read from its diff, ⛔ not from its prose — reading a region off PR prose is a mistake on this seat's record.
Sibling dispatched this round, driver-sql: single→multi-value field change never migrates the existing column type (varchar/text kept), arrays silently stored as stringified literals — data corruption on Postgres #11535, lands in schema-drift.ts — a different file.
H17 on-hold trigger index: the two holds naming this file, drivers(sql): 共享 canonical 修复表达式把「裸数字文本」当儒略日读,backfill 会把这个误读写进盘里(观察项) #6009 (restart: sqliteCanonicalDatetimeSql / backfillCanonicalDatetimes) and drivers(sql): upsert's empty-merge-set fallback is merge-ALL, which re-admits every insert-only column — currently unreachable, but it is the wrong shape for "nothing to merge" #8740 (restart: insertOnlyUpsertColumns / a 4th dialect / conflict-target gains a non-null DEFAULT), declare their restart conditions on symbols. introspectForeignKeys is none of them, so neither fires and no rider is owed. Triage's standing precedent applies (⛔ quoted, not translated): 「read the patch rather than the file list, because restart condition 2 is about one function, not the file」.

Premise re-verified, and a near-miss worth recording

introspectForeignKeys' MySQL arm still queries information_schema.KEY_COLUMN_USAGE with no ORDER BY — confirmed by reading the arm verbatim at 13868-13884. The card is live.

⚠️ The trap I nearly fell into, so you do not: there is an ORDER BY ORDINAL_POSITION a few lines below, at ~14011. It belongs to a different method's MySQL arm — the one filtering CONSTRAINT_NAME = 'PRIMARY' (that is #11101's fix for introspectPrimaryKeys). Grepping this file for the literal and concluding "already fixed" would close a live defect as absorbed. Match on the enclosing method, not on the literal.

The real work here is the pin, not the line

⛔ Read the card honestly: it did not reproduce. The reporter measured live MySQL 8.0.46 with a deliberately out-of-sequence composite key and the server returned key order — the correct answer. The card is filed as an observation precisely because there is no wrong answer to point at.

That makes the naive pin vacuous: a behavioural test asserting "the columns come back in ordinal order" passes today, passes after your one-line fix, and would pass with the fix reverted. A green that cannot go red is not evidence, and this repo has paid for that shape before.

So the deliverable is an honest answer to: what can actually be pinned here? Options, and I am not choosing for you:

A structural pin — assert the emitted SQL for this arm carries ORDER BY ORDINAL_POSITION. Weak-looking, but it is the thing that is actually true and it does go red when the line is removed.
A behavioural pin with a declared limitation — keep the out-of-sequence fixture, and say in the test's own text that it is a characterisation which the server currently satisfies either way.
Something better you find by measuring.

⚠️ Whichever you choose, reverse-verify it: remove the ORDER BY and show what your pin does. If it stays green, say so plainly and pick a different pin — ⛔ do not ship a pin you have not seen fail, and ⛔ do not describe a vacuous pin as though it guards the behaviour. The card explicitly rules the "prove a plan shape returns it out of order" investigation out of scope, so ⛔ do not spend the round building a fixture large enough to change the query plan.

The sibling arm is your model for spelling: introspectPrimaryKeys already carries ORDER BY ORDINAL_POSITION plus a measured comment explaining that the InnoDB folklore about "tending to" return ordinal order does not hold. Match that idiom.

Generated by Claude Code

fix(driver-sql): order the MySQL introspectForeignKeys read by the ke…

{
  "issue": 11379,
  "status": "done",
  "branch": "claude/issue-11379-fk-introspection-ordinal-order",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/11715",
  "premise_still_valid": true,
  "premise_evidence": "Re-verified on origin/main @ 4ceae8ab0b (main had moved past the brief's cad8b42). introspectForeignKeys now begins at 13774, not 13757; its MySQL arm queried information_schema.KEY_COLUMN_USAGE with no ORDER BY. Confirmed by reading the arm verbatim AND by matching the enclosing method, never the literal: the file carries ORDER BY ORDINAL_POSITION three times — introspectColumnOrder (13696), introspectForeignKeys (now 13924, the fix), introspectPrimaryKeys (14056). The brief's trap was real and is what a literal grep would have hit.",
  "summary": "Added ORDER BY ORDINAL_POSITION to the MySQL arm of SqlDriver.introspectForeignKeys with a measured comment in the sibling introspectPrimaryKeys idiom, plus a pin on the SQL the arm emits and a changeset. The real work was the pin, and the answer is that a behavioural pin is genuinely not achievable here — so the pin is structural, and it was seen to fail. What makes the fix more than cosmetic is a measurement the card did not have: on this container's MySQL 8.0.46 the FK predicate returned key order unpinned (reproducing the card's non-reproduction), but the sibling introspectPrimaryKeys predicate over the SAME view, in the SAME session, returned an out-of-sequence primary key in COLUMN order (carrier_code at ordinal 2 ahead of shipment_id at ordinal 1) on a small fixture. KEY_COLUMN_USAGE therefore does not preserve the ordinal for free on this server: which order you get is decided by the WHERE clause. The FK predicate was on the lucky side of a choice nobody made. No plan-shape investigation was attempted — the card rules it out.",
  "pin_reverse_verification": {
    "pin_kind": "structural — asserts the statement THIS method hands to knex carries ORDER BY ORDINAL_POSITION after the predicate that identifies it; captured at the knex seam, never a source-file grep",
    "why_not_behavioural": "vacuous by measurement, not by assumption. The un-ordered FK query returns key order on this server, so 'columns come back in ordinal order' passes with the fix, without it, and reverted. Not shipped, and not described as a guard.",
    "predicted_direction": "test 1 (emission) red; test 2 (row-order preservation) stays green — it does not depend on the SQL",
    "observed": "exactly as predicted: Test Files 1 failed (1) / Tests 1 failed | 1 passed (2). FAIL > emits ORDER BY ORDINAL_POSITION on the KEY_COLUMN_USAGE read",
    "mutation_confirmed_on_disk": "anchored counts scoped to the enclosing method, not a bare diffstat: introspectForeignKeys ORDER-BY count BEFORE=1 AFTER=0, while introspectPrimaryKeys still=1 (proving the mutation was method-scoped). Python replace aborted unless its anchor matched exactly once.",
    "rebuild": "no rebuild leg was required, and that is proven rather than assumed: the pin imports ../src/index.js from inside src/, which vitest resolves to SOURCE, not dist. The ablation went red with no rebuild at all — only possible if the test reads source. The false-green ablation failure mode (mutating source, reading stale dist) is therefore not reachable for this pair. The workspace closure was nevertheless built later for check:type-check-debt.",
    "restore_leg": "trap '<restore from HEAD>' EXIT INT TERM on the mutation script; restore proven byte-identical to HEAD via git diff --exit-code = 0 and git status --porcelain = 0 lines. Fix was committed BEFORE the ablation, so the restore point really existed."
  },
  "gates": [
    { "gate": "pnpm --filter @objectstack/driver-sql test", "exit": 0, "verdict_line": "Test Files 122 passed | 8 skipped (130) · Tests 1913 passed | 104 skipped (2017)" },
    { "gate": "pnpm --filter @objectstack/driver-sql typecheck", "exit": 0, "verdict_line": "os-verify-lock: VERDICT command-exit 0; script name echoed as '> @objectstack/driver-sql@17.2.0 typecheck', so this was not a zero-match silent pass" },
    { "gate": "pnpm lint (repo-wide eslint . --no-inline-config)", "exit": 0, "verdict_line": "os-verify-lock: VERDICT command-exit 0 · held the lock 147s (2m27s) — FULL repo scan, no narrowing claimed or needed" },
    { "gate": "pnpm check:driver-conformance", "exit": 0, "verdict_line": "check-driver-conformance: OK — 45 covered cell(s), 0 in the DEBT ledger, 0 exempt" },
    { "gate": "pnpm check:type-check-debt (--re-measure, built closure)", "exit": 0, "verdict_line": "check-type-check-coverage --re-measure: OK — 32 ledger entr(ies) re-measured in 395.8s, 1898 raw tsc error(s) total, none above its recorded number" },
    { "gate": "pnpm check:type-check-coverage", "exit": 0, "verdict_line": "check-type-check-coverage: OK — 65/78 workspace packages type-checked (plus the root), 13 in the DEBT ledger, 1 exempt" },
    { "gate": "pnpm check:changeset-gate-self-tests", "exit": 0 },
    { "gate": "pnpm check:objectui-changeset", "exit": 0 },
    { "gate": "pnpm check:published-files", "exit": 0 },
    { "gate": "pnpm check:slot-lookup", "exit": 0 },
    { "gate": "pnpm check:test-source-alias", "exit": 0 },
    { "gate": "pnpm check:type-source-resolution", "exit": 0 },
    { "gate": "pnpm check:nul-bytes", "exit": 0 },
    { "gate": "pnpm check:query-options-erasure", "exit": 0 },
    { "gate": "pnpm check:engine-double-contract", "exit": 0 },
    { "gate": "pnpm check:cross-package-test-inputs", "exit": 0 },
    { "gate": "pnpm check:where-matcher", "exit": 0 },
    { "gate": "node scripts/check-adr-0087-registration.mjs", "exit": 0 },
    { "gate": "node scripts/check-changeset-no-major.mjs", "exit": 0 },
    { "gate": "node scripts/check-empty-changeset.mjs", "exit": 0 },
    { "gate": "node scripts/check-plugin-teardown-shape.mjs", "exit": 0 },
    { "gate": "node scripts/docs-audit/check-affected-docs.mjs", "exit": 0 },
    { "gate": "node scripts/pm/release-rehearsal-clone.mjs --self-test", "exit": 0 }
  ],
  "gates_note": "Derived with `node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack` from the script's own merge-base change set (3 paths), never from a hand-built git diff. 13 path-matched + 7 convention-triggered families; all run. Every exit code captured before any pipe (per-step redirect then EXIT=$?), never read off a `| tail`. Union run at 8a43d5d633 = final commit = pushed HEAD; nothing landed after it.",
  "driver_conformance_census": {
    "promise": "lane standing promise on driver cards: covered/DEBT before and after",
    "before": "45 covered cell(s), 0 in the DEBT ledger, 0 exempt (measured at merge base 4ceae8ab0b in a throwaway comparison worktree, removed afterwards)",
    "after": "45 covered cell(s), 0 in the DEBT ledger, 0 exempt",
    "delta": "none — the new test imports no shared conformance case-set"
  },
  "files_changed": [
    "packages/drivers/driver-sql/src/sql-driver.ts (+28: the ORDER BY line and its measured comment, inside introspectForeignKeys)",
    "packages/drivers/driver-sql/src/sql-driver-11379-introspect-fk-mysql-ordinal-order.test.ts (new, 193 lines)",
    ".changeset/driver-sql-mysql-fk-introspection-ordinal-order.md (new, patch)"
  ],
  "file_surface_respected": true,
  "deviations": [
    "TOOLCHAIN TRAP I HIT: ran `pnpm --filter @objectstack/driver-sql test -- --maxWorkers=2`. The bare `--` silently discarded `--maxWorkers=2`, so the suite ran at default concurrency instead of 2 — a resource-discipline miss under the shared verify lock. NOT a correctness miss: no file pattern was passed, and all 130 test files are accounted for in the output (122 passed + 8 skipped), so nothing was silently filtered. Declaring it because it is exactly the trap the brief names.",
    "The workspace-closure build required by check:type-check-debt exceeded the container's ~10-minute foreground cap and was auto-backgrounded. I did NOT end the round on it: I waited in-turn with a bounded polling loop and collected its real result (70/70 tasks successful, VERDICT command-exit 0, 10m38s), then ran the ratchet.",
    "Live MySQL was DOWN at task start — shut down by another actor at 13:01:34 UTC per /var/log/mysql/error.log, 30s before I first reached for it. Started it (starting is not killing) and restored CI's parameter @@global.time_zone='+08:00', which the restart had reset to SYSTEM. All measurements were taken after that.",
    "BRIEF CORRECTION: PR #11657 is not open. It merged as 992161b728 and is in my merge base, so the in-flight overlap on sql-driver.ts no longer exists. Its regions were disjoint from mine either way.",
    "Created and then removed a detached comparison worktree at the merge base to measure the driver-conformance before number (the AGENTS.md-sanctioned compare mechanism). No pnpm install was needed — that gate uses node builtins only.",
    "Worktree cleanup succeeded without --force. Scratch MySQL database os11379_probe dropped. While cleaning up I found another agent had reconfigured MySQL root to root:root over TCP and created the `conformance` database — noted, not touched."
  ],
  "not_applicable": [
    "skip-changeset label: this PR carries a changeset (source change, lane convention — the sibling introspection fix 992161b728 carried one), so the label mechanism does not apply.",
    "on-hold riders: #6009 and #8740 both declare restart conditions on SYMBOLS (sqliteCanonicalDatetimeSql / backfillCanonicalDatetimes; insertOnlyUpsertColumns / a 4th dialect / conflict target gaining a non-null DEFAULT). introspectForeignKeys is none of them and this diff touches none of them. Neither fires.",
    "skills/** budget reporting: no skills/** path in this diff."
  ],
  "tests": "Dependency closure built first (pnpm --filter '@objectstack/driver-sql^...' build, VERDICT command-exit 0, 4m06s) — a new worktree, so this preceded every judgement. New pin green: Test Files 1 passed (1), Tests 2 passed (2). Full package suite green: Test Files 122 passed | 8 skipped (130), Tests 1913 passed | 104 skipped (2017). Typecheck green. Repo-wide eslint green in 2m27s (full scan, not a narrowing). Ablation: fix committed FIRST, then ORDER BY deleted under a trap-restore; mutation confirmed on disk by method-scoped anchored counts (introspectForeignKeys 1 -> 0, introspectPrimaryKeys still 1), NOT by a bare diffstat; pin went red (1 failed | 1 passed) in the predicted direction; restore proven byte-identical to HEAD (git diff --exit-code = 0). No rebuild leg needed and that is proven, not assumed — the pin resolves ../src/index.js to SOURCE, so the ablation could not have been reading stale dist, which is the direction that produces a silent false green. Live MySQL 8.0.46 measurements (FK predicate returns key order; PK predicate returns column order) were taken with the mysql CLI against a scratch database, since they are evidence for the PR body rather than a shipped test.",
  "open_questions": [],
  "out_of_scope_findings": []
}

Generated by Claude Code

os-dev-report

(Marker note: this comment supersedes the one posted at 13:50 UTC. That one opened with the marker in HTML-comment form and GitHub's sanitizer stripped it on write, leaving the report invisible to a marker scan. The comment-edit REST endpoint is not available to this session, so the fix is a re-post rather than an edit. Same JSON, marker as literal text; nothing else changed.)

{
  "issue": 11379,
  "status": "done",
  "branch": "claude/issue-11379-fk-introspection-ordinal-order",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/11715",
  "premise_still_valid": true,
  "premise_evidence": "Re-verified on origin/main @ 4ceae8ab0b (main had moved past the brief's cad8b42). introspectForeignKeys now begins at 13774, not 13757; its MySQL arm queried information_schema.KEY_COLUMN_USAGE with no ORDER BY. Confirmed by reading the arm verbatim AND by matching the enclosing method, never the literal: the file carries ORDER BY ORDINAL_POSITION three times - introspectColumnOrder (13696), introspectForeignKeys (now 13924, the fix), introspectPrimaryKeys (14056). The brief's trap was real and is what a literal grep would have hit.",
  "summary": "Added ORDER BY ORDINAL_POSITION to the MySQL arm of SqlDriver.introspectForeignKeys with a measured comment in the sibling introspectPrimaryKeys idiom, plus a pin on the SQL the arm emits and a changeset. The real work was the pin, and the answer is that a behavioural pin is genuinely not achievable here - so the pin is structural, and it was seen to fail. What makes the fix more than cosmetic is a measurement the card did not have: on this container's MySQL 8.0.46 the FK predicate returned key order unpinned (reproducing the card's non-reproduction), but the sibling introspectPrimaryKeys predicate over the SAME view, in the SAME session, returned an out-of-sequence primary key in COLUMN order (carrier_code at ordinal 2 ahead of shipment_id at ordinal 1) on a small fixture. KEY_COLUMN_USAGE therefore does not preserve the ordinal for free on this server: which order you get is decided by the WHERE clause. The FK predicate was on the lucky side of a choice nobody made. No plan-shape investigation was attempted - the card rules it out.",
  "pin_reverse_verification": {
    "pin_kind": "structural - asserts the statement THIS method hands to knex carries ORDER BY ORDINAL_POSITION after the predicate that identifies it; captured at the knex seam, never a source-file grep",
    "why_not_behavioural": "vacuous by measurement, not by assumption. The un-ordered FK query returns key order on this server, so 'columns come back in ordinal order' passes with the fix, without it, and reverted. Not shipped, and not described as a guard.",
    "predicted_direction": "test 1 (emission) red; test 2 (row-order preservation) stays green - it does not depend on the SQL",
    "observed": "exactly as predicted: Test Files 1 failed (1) / Tests 1 failed | 1 passed (2). FAIL on 'emits ORDER BY ORDINAL_POSITION on the KEY_COLUMN_USAGE read'",
    "mutation_confirmed_on_disk": "anchored counts scoped to the enclosing method, not a bare diffstat: introspectForeignKeys ORDER-BY count BEFORE=1 AFTER=0, while introspectPrimaryKeys still=1 (proving the mutation was method-scoped). The python replace aborted unless its anchor matched exactly once.",
    "rebuild": "no rebuild leg was required, and that is proven rather than assumed: the pin imports ../src/index.js from inside src/, which vitest resolves to SOURCE, not dist. The ablation went red with no rebuild at all - only possible if the test reads source. The false-green ablation failure mode (mutating source, reading stale dist) is therefore not reachable for this pair. The workspace closure was nevertheless built later for check:type-check-debt.",
    "restore_leg": "the mutation script carried a trap on EXIT INT TERM that restored the file from HEAD; restore proven byte-identical to HEAD via git diff --exit-code = 0 and git status --porcelain = 0 lines. Fix was committed BEFORE the ablation, so the restore point really existed."
  },
  "gates": [
    { "gate": "pnpm --filter @objectstack/driver-sql test", "exit": 0, "verdict_line": "Test Files 122 passed | 8 skipped (130) / Tests 1913 passed | 104 skipped (2017)" },
    { "gate": "pnpm --filter @objectstack/driver-sql typecheck", "exit": 0, "verdict_line": "os-verify-lock: VERDICT command-exit 0; script name echoed as '@objectstack/driver-sql@17.2.0 typecheck', so this was not a zero-match silent pass" },
    { "gate": "pnpm lint (repo-wide eslint . --no-inline-config)", "exit": 0, "verdict_line": "os-verify-lock: VERDICT command-exit 0 - held the lock 147s (2m27s). FULL repo scan, no narrowing claimed or needed" },
    { "gate": "pnpm check:driver-conformance", "exit": 0, "verdict_line": "check-driver-conformance: OK - 45 covered cell(s), 0 in the DEBT ledger, 0 exempt" },
    { "gate": "pnpm check:type-check-debt (--re-measure, built closure)", "exit": 0, "verdict_line": "check-type-check-coverage --re-measure: OK - 32 ledger entr(ies) re-measured in 395.8s, 1898 raw tsc error(s) total, none above its recorded number" },
    { "gate": "pnpm check:type-check-coverage", "exit": 0, "verdict_line": "check-type-check-coverage: OK - 65/78 workspace packages type-checked (plus the root), 13 in the DEBT ledger, 1 exempt" },
    { "gate": "pnpm check:changeset-gate-self-tests", "exit": 0 },
    { "gate": "pnpm check:objectui-changeset", "exit": 0 },
    { "gate": "pnpm check:published-files", "exit": 0 },
    { "gate": "pnpm check:slot-lookup", "exit": 0 },
    { "gate": "pnpm check:test-source-alias", "exit": 0 },
    { "gate": "pnpm check:type-source-resolution", "exit": 0 },
    { "gate": "pnpm check:nul-bytes", "exit": 0 },
    { "gate": "pnpm check:query-options-erasure", "exit": 0 },
    { "gate": "pnpm check:engine-double-contract", "exit": 0 },
    { "gate": "pnpm check:cross-package-test-inputs", "exit": 0 },
    { "gate": "pnpm check:where-matcher", "exit": 0 },
    { "gate": "node scripts/check-adr-0087-registration.mjs", "exit": 0 },
    { "gate": "node scripts/check-changeset-no-major.mjs", "exit": 0 },
    { "gate": "node scripts/check-empty-changeset.mjs", "exit": 0 },
    { "gate": "node scripts/check-plugin-teardown-shape.mjs", "exit": 0 },
    { "gate": "node scripts/docs-audit/check-affected-docs.mjs", "exit": 0 },
    { "gate": "node scripts/pm/release-rehearsal-clone.mjs --self-test", "exit": 0 }
  ],
  "gates_note": "Derived with 'node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack' from the script's own merge-base change set (3 paths), never from a hand-built git diff. 13 path-matched + 7 convention-triggered families; all run. Every exit code captured before any pipe (per-step redirect then EXIT=$?), never read off a tail pipeline. Union run at 8a43d5d633 = final commit = pushed HEAD; nothing landed after it.",
  "driver_conformance_census": {
    "promise": "lane standing promise on driver cards: covered/DEBT before and after",
    "before": "45 covered cell(s), 0 in the DEBT ledger, 0 exempt (measured at merge base 4ceae8ab0b in a throwaway comparison worktree, removed afterwards)",
    "after": "45 covered cell(s), 0 in the DEBT ledger, 0 exempt",
    "delta": "none - the new test imports no shared conformance case-set"
  },
  "files_changed": [
    "packages/drivers/driver-sql/src/sql-driver.ts (+28: the ORDER BY line and its measured comment, inside introspectForeignKeys)",
    "packages/drivers/driver-sql/src/sql-driver-11379-introspect-fk-mysql-ordinal-order.test.ts (new, 193 lines)",
    ".changeset/driver-sql-mysql-fk-introspection-ordinal-order.md (new, patch)"
  ],
  "file_surface_respected": true,
  "deviations": [
    "TOOLCHAIN TRAP I HIT: ran 'pnpm --filter @objectstack/driver-sql test -- --maxWorkers=2'. The bare double-dash silently discarded --maxWorkers=2, so the suite ran at default concurrency instead of 2 - a resource-discipline miss under the shared verify lock. NOT a correctness miss: no file pattern was passed, and all 130 test files are accounted for in the output (122 passed + 8 skipped), so nothing was silently filtered. Declaring it because it is exactly the trap the brief names.",
    "The workspace-closure build required by check:type-check-debt exceeded the container's ~10-minute foreground cap and was auto-backgrounded. I did NOT end the round on it: I waited in-turn with a bounded polling loop and collected its real result (70/70 tasks successful, VERDICT command-exit 0, 10m38s), then ran the ratchet.",
    "Live MySQL was DOWN at task start - shut down by another actor at 13:01:34 UTC per the mysql error log, 30s before I first reached for it. Started it (starting is not killing) and restored CI's parameter global time_zone = '+08:00', which the restart had reset to SYSTEM. All measurements were taken after that.",
    "BRIEF CORRECTION: PR #11657 is not open. It merged as 992161b728 and is in my merge base, so the in-flight overlap on sql-driver.ts no longer exists. Its regions were disjoint from mine either way.",
    "Created and then removed a detached comparison worktree at the merge base to measure the driver-conformance before number (the AGENTS.md-sanctioned compare mechanism). No pnpm install was needed - that gate uses node builtins only.",
    "Worktree cleanup succeeded without --force. Scratch MySQL database os11379_probe dropped. While cleaning up I found another agent had reconfigured MySQL root to root:root over TCP and created the 'conformance' database - noted, not touched.",
    "REPORT DELIVERY: the first report comment lost its HTML-comment marker to GitHub's body sanitizer on write. Re-posted with the marker as literal text; the earlier comment is superseded, not additional work."
  ],
  "not_applicable": [
    "skip-changeset label: this PR carries a changeset (source change, lane convention - the sibling introspection fix 992161b728 carried one), so the label mechanism does not apply.",
    "on-hold riders: #6009 and #8740 both declare restart conditions on SYMBOLS (sqliteCanonicalDatetimeSql / backfillCanonicalDatetimes; insertOnlyUpsertColumns / a 4th dialect / conflict target gaining a non-null DEFAULT). introspectForeignKeys is none of them and this diff touches none of them. Neither fires.",
    "skills budget reporting: no published-skills path in this diff."
  ],
  "tests": "Dependency closure built first (pnpm --filter '@objectstack/driver-sql^...' build, VERDICT command-exit 0, 4m06s) - a new worktree, so this preceded every judgement. New pin green: Test Files 1 passed (1), Tests 2 passed (2). Full package suite green: Test Files 122 passed | 8 skipped (130), Tests 1913 passed | 104 skipped (2017). Typecheck green. Repo-wide eslint green in 2m27s (full scan, not a narrowing). Ablation: fix committed FIRST, then ORDER BY deleted under a trap-restore; mutation confirmed on disk by method-scoped anchored counts (introspectForeignKeys 1 to 0, introspectPrimaryKeys still 1), NOT by a bare diffstat; pin went red (1 failed | 1 passed) in the predicted direction; restore proven byte-identical to HEAD (git diff --exit-code = 0). No rebuild leg needed and that is proven, not assumed - the pin resolves ../src/index.js to SOURCE, so the ablation could not have been reading stale dist, which is the direction that produces a silent false green. Live MySQL 8.0.46 measurements (FK predicate returns key order; PK predicate returns column order) were taken with the mysql CLI against a scratch database, since they are evidence for the PR body rather than a shipped test.",
  "open_questions": [],
  "out_of_scope_findings": []
}

Generated by Claude Code

ACCEPT — PR #11715

Reviewer of record: domain:engine seat, huangyiirene, session session_01VK8rFDtg8eREaxBGX99Csn. Checked against the diff and GitHub, not the report. ⛔ Landing held pending CI — last section.

Form and scope

Check	Reading
Path surface	✅ 3 files: `sql-driver.ts`, one new test, one changeset. No governed path, no `packages/spec/src`.
Region	✅ hunks at 13886 (comment) and 13924 (the `ORDER BY`).
Right method?	✅ verified by enclosing-method boundaries, not by literal grep: `introspectForeignKeys` spans 13774–13979, and 13924 is inside it. The file's other two `ORDER BY ORDINAL_POSITION` occurrences belong to `introspectColumnOrder` (13696) and `introspectPrimaryKeys` (14056).
Changeset	✅ present, patch — correct for a source change in a shipped package.

⚠️ The dev found three occurrences of that literal in the file; my brief warned of two. The extra one (introspectColumnOrder) makes the trap strictly worse than I described, and it is now recorded in the code comment.

The measurement that turns this from cosmetic into a real fix

This is the part I want on the record, because the card was filed as an observation that did not reproduce, and the honest ceiling on such a card is usually "ship a structural pin and say so". The dev found more than that:

Through this predicate, an out-of-sequence composite FK came back in key order with no ORDER BY — reproducing the card's non-reproduction.
On the same server, in the same session, the sibling introspectPrimaryKeys predicate over the same KEY_COLUMN_USAGE view returned an out-of-sequence primary key in column order — carrier_code (ordinal 2) ahead of shipment_id (ordinal 1), reproducing driver-sql: the Postgres and MySQL introspectPrimaryKeys arms return the key in UNSPECIFIED row order — neither orders by key position #11101 exactly.

So the view does not preserve the ordinal for free on this server: which of the two orders you get is decided by the WHERE clause, and nothing declares that. The FK arm was on the lucky side of a choice nobody made. That converts "did not reproduce, fix is unambiguous anyway" into "the class demonstrably occurs on this view, one predicate over" — a strictly better answer than the card had, and it is what justifies the line rather than the card's authority.

The pin — exactly the honest answer I asked for

I asked for an honest answer to what can actually be pinned, with an explicit warning not to ship a pin that cannot go red. The delivery:

Structural pin, asserting the statement the method hands to knex carries ORDER BY ORDINAL_POSITION — captured at the knex seam, ⛔ not a source-file grep (a grep-based pin would pass on any of the three occurrences and is exactly the trap this card is about).
Behavioural pin declined, and declined by measurement rather than assumption: the un-ordered FK query returns key order on this server, so a row-order assertion passes with the fix, without it, and reverted. Not shipped, and — importantly — not described as a guard.
Reverse-verified with a predicted direction stated in advance: test 1 (emission) red, test 2 (row-order preservation) green because it does not depend on the SQL. Observed exactly that: 1 failed | 1 passed.

⭐ The ablation's mutation proof is better than the usual: method-scoped anchored counts — introspectForeignKeys 1→0 while introspectPrimaryKeys stayed 1 — rather than a bare diffstat. On a file with three copies of the literal, a diffstat could not have shown which copy was removed. And the no-rebuild claim is proven rather than asserted (the pin resolves ../src/index.js to source, so the stale-dist false-green direction is unreachable for this pair).

Lane standing promise honoured: driver-conformance census read before and after — 45 covered, 0 DEBT, 0 exempt, unchanged; before-number taken in a throwaway comparison worktree at the merge base, which is the sanctioned compare mechanism.

⛔ Correction — my dispatch brief was wrong about the in-flight overlap

My brief told this dev that PR #11657 was an open draft on sql-driver.ts at 3686 and 14059+, and instructed it to treat those regions as occupied. The dev checked and corrected me: #11657 merged at 11:31:49Z as 992161b728 and is inside this PR's own merge base. I have re-verified that independently — it is in origin/main's log.

The failure is mine and it is a named one: I measured the open-PR set once at ~11:0xZ and reused that snapshot in a brief written around 12:5xZ, without re-measuring. That is precisely "inherit a reading, then state it as a current fact" — the same failure mode this seat's own notes warn about, and I committed it while instructing others to read diffs rather than prose. No decision turned on it (the regions were disjoint either way, and a merged PR frees the file rather than contending it), but the record needs to be right. The same stale claim appears in my #11541 claim comment and is corrected there too.

Deviation — declared, and correctly judged not a correctness issue

pnpm --filter @objectstack/driver-sql test -- --maxWorkers=2: the bare -- silently discarded the flag, so the suite ran at default concurrency under the shared verify lock. The dev declared it rather than hiding it, and the reasoning that it is a resource-discipline miss and not a correctness one holds up: no file pattern was passed and all 130 test files are accounted for in the output (122 passed + 8 skipped), so nothing was silently filtered. That last check is the one that matters — a swallowed positional filter would have been a false green.

Also noted, no action: live MySQL was down at task start (shut down by another actor 30s earlier, per its error log). The dev started it and restored global.time_zone = '+08:00', which the restart had reset to SYSTEM — the exact discipline the brief asked for, and the reason the brief says "check first, never assume it is running". It also found another agent had reconfigured MySQL root and created a conformance database, and left it alone rather than tidying another agent's state.

⛔ Landing held — CI has not converged

Verdict ACCEPT. Green so far on Build Core, Temporal Conformance (live PG + MySQL), Dogfood Verify CLI, Test Core (5/6), Type Check · source gates and · consumer gates, Check Changeset, Check PR Size and both claim guards, with nothing red; Test Core 1-4 and 6, all three Dogfood Regression Gate shards, Type Check · workspace, · debt ledger and Lint & Repo Gates still running. Entry needs every check green, not the required subset.

Generated by Claude Code

Navigation Menu

Repository navigation

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

What

Why the order is load-bearing at all

What was measured, and what it showed

Why it is still worth recording

Not measured here

Premise re-verified, and a near-miss worth recording

The real work here is the pin, not the line

ACCEPT — PR #11715

Form and scope

The measurement that turns this from cosmetic into a real fix

The pin — exactly the honest answer I asked for

⛔ Correction — my dispatch brief was wrong about the in-flight overlap

Deviation — declared, and correctly judged not a correctness issue

⛔ Landing held — CI has not converged

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

What

Why the order is load-bearing at all

What was measured, and what it showed

Why it is still worth recording

Not measured here

Activity

claude commented on Aug 24, 2026

huangyiirene commented on Aug 24, 2026

Premise re-verified, and a near-miss worth recording

The real work here is the pin, not the line

huangyiirene commented on Aug 24, 2026

huangyiirene commented on Aug 24, 2026

huangyiirene commented on Aug 24, 2026

ACCEPT — PR #11715

Form and scope

The measurement that turns this from cosmetic into a real fix

The pin — exactly the honest answer I asked for

⛔ Correction — my dispatch brief was wrong about the in-flight overlap

Deviation — declared, and correctly judged not a correctness issue

⛔ Landing held — CI has not converged

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

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