# Scenario-aware Slow Query ordering and projection

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "slow-query-scenario-ordering-2026-08-11",
  "problems": [
    {
      "problemId": "slow-query-implicit-order-and-lossy-projection",
      "summary": "Slow Query plans could rely on a provider's implicit timestamp ordering or omit memory and identity fields needed to compare the selected execution across diagnostic scenarios.",
      "status": "optimized_verified",
      "issueKeys": ["APID-29296"],
      "sourceRefs": ["https://tidb.atlassian.net/browse/APID-29296#problem:slow-query-implicit-order-and-lossy-projection"],
      "signalTextIncludesAny": ["slow query orderBy omitted", "memory_max omitted from fields", "timestamp ascending slow query rows"],
      "observedThrough": "2026-08-11",
      "effectiveAt": "2026-08-11T14:07:29Z",
      "verifiedAt": "2026-08-11T14:26:12Z",
      "attemptedAction": "Make Provider Executors apply explicit scenario-aware Slow Query order defaults, retain explicit refinements, add core identity, latency, resource, scan, and order fields to the projection, and preserve sorting during Wetech bad-request repair.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/tree/main/auto-oncall-services/docs/opts/2026-08-11-slow-query-scenario-ordering.md"
    }
  ]
}
-->

- Optimization ID: `slow-query-scenario-ordering-2026-08-11`
- Owner: auto-oncall-services
- Source: APID-29296 staging replay
- Implementation status: deployed to staging and case criterion verified

## Bounded problem

The query-latency plan selected the relevant long-running `ANALYZE TABLE` row
by `query_time DESC`, but its explicit projection omitted `memory_max`. The
memory-oom Slow Query plan omitted sorting, so Wetech sent an empty `orderBy`
and `desc=false`; Dashboard then returned the earliest timestamp rows rather
than the high-memory execution. The row existed and was returned first by an
exact `memory_max DESC` probe.

## Attempted change

- Resolve order as explicit plan value, then scenario default, then provider
  baseline. `memory-oom` defaults to `memory_max DESC`; other scenarios default
  to `query_time DESC`.
- Make Wetech explicit projections additive to core identity, latency,
  resource, scan, and active-order fields.
- Preserve the effective order and direction in the Wetech
  `common.bad_request` minimal-projection repair.
- Expose the same decision contract to the Evidence Agent. Clinic keeps its
  fixed safe projection, which already includes `query_time` and `memory_max`.

## Verification status

Local tests passed 751/751 for TypeScript and 64/64 for the Wetech helper.
Source PR `tidbcloud/aisupport#789` merged as
`558a6399e1967abe2d2acb56402c9c720f169bc1`; app-delivery PR `#76885`
merged as `f33cd16e2c813c563099c89ecc23bd5dcd6ce968`. The staging Pod reached
1/1 Ready with zero restarts, and `/healthz` plus `/readyz` returned HTTP 200.

Same-cutoff `mode=write` replay
`47332d45-275e-462a-916a-48852d7bfa62` / DB run `120080` made the
query-latency Slow Query request explicit as `query_time DESC`. Its first row
was the 294.251770322-second `ANALYZE TABLE` execution on
`10.164.19.243:10080`; the same row retained digest, connection, timestamp,
query text, `memory_max=46295458134`, phase timings, and scan/resource fields.
The Scenario Diagnostic Agent classified the ANALYZE identity as proven but
causality as only correlated, then used narrower rounds to establish that the
largest latency excursion preceded ANALYZE onset. This passes the bounded
ordering-and-projection criterion.

Existing Jira internal comment `23399342` was updated in place. Direct readback
returned comment property `sd.public.comment.internal=true`.

## Unresolved gaps

This selection contract makes the relevant rows observable; it does not prove
that a high-memory Slow Query caused process memory growth. Attribution still
requires same-record evidence and the memory consumer or allocation path.

The replay's final safety correction replaced the better synthesized Chinese
draft with an English evidence list plus a generic metric-semantics checklist.
That is a separate typed workload-identity/publication problem; this record
does not mark the whole APID-29296 answer optimized.

## Next validation action

Track the final typed workload-identity/publication replacement as a separate
problem. Continue monitoring Slow Query requests for an omitted effective
order or a projection that drops the active order and scenario-core fields.