# Source analysis: JSON_SCHEMA_VALID shared-reference regression

## Scope and official measurements

The affected function is `JSON_SCHEMA_VALID()`. The official comparison used unmodified MySQL Community Server **8.0.45** and **9.7.2**. No intermediate release was performance-bisected. The attachments preserve the measured SQL rather than generating new benchmark data.

The schema root is `{"type":"integer"}` and the document is the JSON scalar `1`. The root does not reference `definitions`. Within `definitions`, each shared-case level has two `allOf` branches referencing the same next-level definition; the terminal definition is an integer schema. This is an acyclic graph. In the control, the second branch references a terminal schema instead, preserving a long reference chain without doubling the continuing subtree.

`L` denotes reference-chain levels, not textual JSON nesting. The maximum container/reference-edge heights for L12, L16 and L20 are 38, 50 and 62, respectively, below the checked limit of 100. Both query forms return `1`; the JSON string `"wrong"` is rejected.

| Case | Schema bytes | 8.0.45 P50, ms | 9.7.2 P50, ms | Median round new/old |
|---|---:|---:|---:|---:|
| Shared L12 | 973 | 0.86645 | 3.94130 | 4.586819 |
| Shared L16 | 1,277 | 0.84985 | 50.60625 | 59.533623 |
| Shared L20 | 1,581 | 0.91895 | 865.38960 | 941.715653 |
| Chain+terminal L16 | 1,366 | 0.88595 | 0.94980 | 1.072070 |
| Chain+terminal L20 | 1,690 | 0.87980 | 0.97680 | 1.089037 |

Each case used three rounds with three warmups and twenty timed executions per endpoint per round, alternating endpoint order. P50 is the median of round medians; the ratio is the median of paired round ratios and need not equal the quotient of the displayed aggregate medians. Times cover client statement execution and complete result fetching over local TCP. L20 was a separate follow-up using the same protocol. The five accompanying `schema_diamond_*.json` files contain the original SQL and samples.

## What the retained source establishes

Pinned 9.7.2 source: commit `008e09c2834b98143a8c067d4d225c90953050cf`, [`sql-common/json_schema.cc`](https://github.com/mysql/mysql-server/blob/008e09c2834b98143a8c067d4d225c90953050cf/sql-common/json_schema.cc#L189-L247).

`json_schema_has_unsafe_expansion()`:

- Traverses JSON container edges and local `$ref` edges, starting with the complete schema, including definitions unused by the validation root.
- Keeps a set of nodes on the **current recursion path**, adding nodes on entry and erasing them on return. This permits cycle detection.
- Does not retain a completed-node result or maximum remaining height. A shared node reached by a different path is expanded again.

The depth/cycle guard is at lines 274-287. Dynamic schema validation and literal-schema initialization invoke the preflight at lines 353 and 377, respectively: [fixed source](https://github.com/mysql/mysql-server/blob/008e09c2834b98143a8c067d4d225c90953050cf/sql-common/json_schema.cc#L274-L377).

For the shared fixture, each level forks into two paths to the same next level. From `d0`, this creates `2^L` paths to `dL`, although the stored schema grows linearly with L. Because completed subgraphs are not memoized, the source control flow entails repeated expansion. This path count is **source-derived**, not an instrumented count of runtime visits. No claim of sampled call counts or full CPU profiling is made. The increasing measured latency and the larger-but-fast chain controls are consistent with this mechanism.

The retained 8.0.45 source lacks this safety preflight. Source history identifies its introduction in [c2f1d42d5ee698f771e16b3e9865ede2b1b609af](https://github.com/mysql/mysql-server/commit/c2f1d42d5ee698f771e16b3e9865ede2b1b609af), “Bug#39000847: Cap JSON Schema $ref expansion depth.” [ffaf6638bf552897e3a1289167492ca4365446f8](https://github.com/mysql/mysql-server/commit/ffaf6638bf552897e3a1289167492ca4365446f8), “Bug#39489024 Reject cyclic JSON schema references,” subsequently strengthened cycle protection. These are source comparisons, not a commit-by-commit performance bisection; the protections address real safety requirements.

## Separate causal diagnostic: measured, but not a fix

A dedicated 9.7.2 instance underwent an **official -> diagnostic -> official** experiment. Only one five-byte call site for the dynamic safety preflight was bypassed in the official executable; the literal initializer's preflight was unchanged. No modified binary is included in this submission.

| Separate L16 case | Official A, ms | Dynamic preflight bypass B, ms | Official restored A2, ms |
|---|---:|---:|---:|
| Dynamic shared schema, read from a dedicated table | 51.24805 | 0.86355 | 50.79400 |
| Literal shared schema; initializer preflight unchanged | 50.561 | 50.721 | 51.633 |

The same-height chain control remained near baseline (approximately 0.9 ms). The dynamic result changed reversibly, while the literal control remained slow. This localizes the dominant added cost in the dynamic case to the bypassed preflight, independently of a cross-version operating-system difference. The official executable was restored and verified after the experiment.

**The bypass removes depth/cycle protection and is not a deployable optimization.** It was used only for independently checked acyclic fixtures with heights at most 50, not for arbitrary schemas. No cycle/excessive-depth rejection tests were run on the bypass. Near-baseline timing does not establish exact statistical equivalence or prove the safety of any repair. The L20 version comparison used only official executables and was not part of the bypass experiment.

## Limits and possible optimization direction

- Both official versions had the same CPU/memory limits on the same host. The 8.0.45 image used Oracle Linux 9.7; the 9.7.2 image used Oracle Linux 9.8. That distribution difference was not eliminated. The reversible same-version intervention supports attribution to the preflight despite this limitation.
- A later source snapshot, commit `561753af986c3c006c4b6c7caa8359748fb55b22`, had an identical `json_schema.cc`; that server build was **not benchmarked**. No claim is made about all releases, worldwide novelty, or the absence of an existing bug report.
- A potential optimization is to cache completed-subgraph maximum remaining heights while retaining active-path cycle detection. Reusing a completed node must still check `current_depth + remaining_height <= 100`, using the existing edge-count rules. A simple global “visited/safe” flag is insufficient: the same subtree can later be reached at a greater depth.
- This is a source-supported direction, **not an implemented, compiled or database-validated production patch**. It must preserve depth/cycle rejection, allocation-failure behavior and appropriate cache lifetime. Removing the preflight is not suggested.