bug: chkit drift always reports primary_key_mismatch for tables whose PRIMARY KEY is derived from ORDER BY

[alvarogar4]

Bug Description

ClickHouse omits the PRIMARY KEY clause from SHOW CREATE TABLE when the primary key is derived from ORDER BY. @chkit/core mirrors that derivation when canonicalizing a schema, but the drift comparer does not apply it to the live side — so the two sides can never agree and chkit drift reports primary_key_mismatch forever.

This affects any table declared with ORDER BY and no explicit PRIMARY KEY, which is the common case. It is especially visible right after chkit pull, because pull writes the derived key into the generated schema file.

Reproduction

CREATE TABLE default.plain (sender String, receiver String)
ENGINE = MergeTree ORDER BY (sender, receiver);

1. chkit pull --database default → the generated table(...) contains primaryKey: ["sender", "receiver"] (derived; the live table has no PRIMARY KEY clause).
2. chkit generate --name init
3. chkit drift → drifted, reasonCodes=primary_key_mismatch, against the very table the schema was pulled from.

Minimal unit-level repro, no cluster needed:

const pulledSchema = table({
  database: 'app', name: 'events',
  columns: [{ name: 'a', type: 'String' }, { name: 'b', type: 'String' }],
  engine: 'MergeTree()',
  primaryKey: ['a', 'b'],   // what pull writes out
  orderBy: ['a', 'b'],
})
compareTableShape(pulledSchema, {
  engine: 'MergeTree()',
  primaryKey: undefined,    // live ClickHouse omits it when derived
  orderBy: '(a, b)',
  columns: [{ name: 'a', type: 'String' }, { name: 'b', type: 'String' }],
  settings: {}, indexes: [], projections: [],
})
// => reasonCodes: ['primary_key_mismatch'], primaryKeyMismatch: true

Impact

• Drift never reads clean. A freshly pulled, unmodified schema reports drift against its own source table.
• chkit check fails in CI whenever failOnDrift is on, for a difference that does not exist.
• Because it fires on every ORDER BY-only table, it drowns out real drift.

Investigation Notes

Verified against ClickHouse 26.3.9.1 (ObsessionDB). The live table reports no PRIMARY KEY:

CREATE TABLE default.plain (`sender` String, `receiver` String)
ENGINE = SharedMergeTree
ORDER BY (sender, receiver)

Introspection agrees — listTableDetails returns primaryKey: undefined, orderBy: "(sender, receiver)".

The asymmetry is between two places:

• packages/core/src/canonical.ts:74 — the expected side derives it: primaryKey: primaryKey.length > 0 ? primaryKey : orderBy, with a comment noting ClickHouse's behavior.
• packages/cli/src/commands/drift/compare.ts:277-279 — the actual side does not:

  const expectedPrimaryKey = normalizeClause(expected.primaryKey.join(', '))
  const actualPrimaryKey = normalizeClause(actual.primaryKey)   // '' when undefined
  const primaryKeyMismatch = expectedPrimaryKey !== actualPrimaryKey

So expected is sender, receiver and actual is ''.

Expected Behavior

A table whose primary key is derived from ORDER BY reads clean in chkit drift.

Proposed Fix

In compareTableShape, fall back to the live ORDER BY when the live PRIMARY KEY is absent, mirroring canonical.ts:74:

const actualPrimaryKey = normalizeClause(actual.primaryKey ?? actual.orderBy)

Verification

• Unit test in packages/cli/src/test/drift.test.ts: expected primaryKey: ['a','b'] vs actual primaryKey: undefined, orderBy: '(a, b)' → compareTableShape returns null.
• E2E: pull a table declared ORDER BY (a, b) with no PRIMARY KEY, then generate + drift → clean.
• Guard against over-correcting: a table with an explicit PRIMARY KEY that genuinely differs from live must still report primary_key_mismatch.

---

Found while verifying #183 / PR #193. Distinct from #190 (which is about engine parameters leaking into the parsed primaryKey/orderBy clauses).

[alvarogar4]

Fixed in #198 (merged). The drift comparer now applies ClickHouse's "PRIMARY KEY defaults to ORDER BY" derivation to the live side, so a derived primary key reads clean while a genuine primary-key difference is still reported. Auto-close didn't trigger because the PR wrote "Fixes #190 and #194" — GitHub only links the keyword to the first number.