-
[OPIK-7448] [BE] test: MySQL query plan-shape gate (EXPLAIN regression guard) (#7583)
frostbyte_neo 发布于
2026-08-03 08:34:06 +00:00 - [OPIK-7448] [BE] test: MySQL query plan-shape gate (EXPLAIN regression guard)
Adds a plan-shape regression gate for the MySQL read paths. A JDBI
SqlLogger captures every rendered SELECT exercised by the read
endpoints and runs EXPLAIN FORMAT=JSON on the same live connection
(re-applying bindings via JDBI's own Argument machinery). The plan is
inspected for the OPIK-7198 failure class — subqueries materialized
into internal temporary tables — and for full scans on tenant-growing
tables. Enforcement is net-new only vs. a checked-in baseline allowlist
that ratchets down as legacy offenders are fixed.The gate immediately caught a pre-existing PromptDAO list/count derived
table that MySQL materializes; baselined and tracked for ratchet-down
under OPIK-7450.Slice 1 of OPIK-7448 (MySQL). Full-suite passive capture, the
ClickHouse gate, and the dedicated CI required-check are follow-ups.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- test(backend): address review — v2 EXPLAIN format, surface failures, builders
- Switch the asserter to explain_json_format_version=2: v1 reports the
table ALIAS in table_name, so the full-scan guard never matched an
aliased table (FROM prompts AS p). v2 reports the base table name and
a uniform operation/access_type vocabulary, fixing the full-scan check
while keeping materialize/temp-table detection. Verified against
mysql:8.4.2. - CapturingSqlLogger records EXPLAIN failures in a failedSql set instead
of dropping the null via computeIfAbsent; the gate now asserts no
un-vetted queries so a failed EXPLAIN can't pass by omission. - Add @Builder(toBuilder = true) to PlanShapeViolation and
PlanShapeBaseline.Entry and build violations via the builder, per the
backend record convention. - Re-derive the baseline for v2 (materialize + temp-table collapse into
one MATERIALIZED_SUBQUERY finding per query).
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- test(backend): capture CTE reads (WITH ...) in the plan gate
isSelect() only matched SQL starting with
select, so CTE-based reads
(WITH ... SELECT) were skipped and never EXPLAINed — the gate silently
missed the exact multi-reference-CTE shape (OPIK-7198) it exists to
catch. Broaden the predicate to accept a leadingwithtoo (renamed
isReadQuery). Verified no WITH-prefixed @SqlUpdate writes exist and
MySQL EXPLAINs WITH ... SELECT the same as a plain SELECT; the newly
captured CTE reads EXPLAIN cleanly and add no net-new violations.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- docs(backend): fix awkward wording in isReadQuery comment
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- test(backend): address review — lombok, logging, table list, style
- Drop
experimentsfrom FULL_SCAN_SENSITIVE_TABLES — it's a ClickHouse
table, not MySQL, so it never belonged in the MySQL gate's set. - Lombok for boilerplate: @RequiredArgsConstructor on MySqlPlanShapeAsserter
and PlanShapeBaseline; @Builder on CapturedQueries. Drop @NonNull from
these test-scope types. - CapturingSqlLogger: fold the null guard into isReadQuery; bump the failed
EXPLAIN log to warn and pass the exception object (full stacktrace);
convert line comments to javadoc. - PlanShapeBaseline: hoist the TypeReference to a static final field.
- Use String.formatted(...) over
+concatenation; import IOException
instead of FQN; rename stream varv->violation.
Kept the recursive plan walk (EXPLAIN JSON is shallow, bounded depth) and
the shared TestContainersSetup bootstrap.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- test(backend): address review — JsonUtils.readValue, evaluator tables, per-path coverage
Use the JsonUtils.readValue counterpart instead of reaching for the inner
mapper, which also removes the wide catch. Add the automation_rule* tables
backing /automations/evaluators to the full-scan sensitive set. Assert each
read path contributes a new captured SELECT so a route that stops hitting
MySQL can no longer pass the gate on the other routes' captures.Co-Authored-By: Claude Opus 5 (1M context) noreply@anthropic.com
- test(backend): make the per-path coverage assertion order-independent
The previous check asserted the deduped plan set grew per read path, which
would false-fail on a route whose SELECT an earlier route had already
captured — the route ran and was vetted, but contributed no new fingerprint.Count read queries in CapturingSqlLogger before deduplication and assert on
that instead, so the per-path check means "this route executed a MySQL read"
rather than "this route produced unseen SQL". The final net-new plan-shape
check is unchanged.Co-Authored-By: Claude Opus 5 (1M context) noreply@anthropic.com
Co-authored-by: Claude Opus 4.8 (1M context) noreply@anthropic.com
下载附件