fix(mysql): probe a scalar subquery's items with its own with by colinhacks · Pull Request #29 · colinhacks/reylo

colinhacks · 2026-10-22T20:55:51Z

On MySQL, a scalar subquery with a WITH of its own was typed more loosely than the same statement with the WITH outside:

select (with r as (select id from posts) select count(id) from r) as n

n was int8?; it is now int8, since an ungrouped aggregate is exactly one row.

The engine types each item of a scalar subquery by preparing a probe: the item selected over the subquery's FROM. That probe was printed with the outer statement's WITH only, so SELECT count(id) FROM r failed to prepare and the column fell back to the server's flag. A JSON object's fields over such a WITH came out unknown? for the same reason.

The same gap was also unsound when the subquery's WITH query shadows a table. The probe read the table in its place, so here title was typed non-null from posts.title while the query yields NULL (the oracle finds it on seeded runs):

select (with posts as (select id, nullif(title, title) as title from posts)
        select coalesce(json_arrayagg(json_object('id', id, 'title', title)), json_array()) from posts) as titles

Now each level the builder walks keeps its query's WITH, and a probe starts with the WITH queries of every level from the statement down, as one list:

One list, not nested parentheses: MySQL 8.4 hides an outer WITH inside a parenthesized query that has a WITH of its own.
One list reads each name as the levels do. The server refuses a name listed twice, and a WITH query never reads one listed after it, with RECURSIVE or without.
RECURSIVE, taken by the list when any level has it, could make a query of a level without it that names itself read itself. No probe is made then, and the server's flag stands.
The wider form, which moves the enclosing FROMs into the probe for a correlated item, is left out when an enclosing FROM names a WITH query of an inner level, which it would then read in place of its table. A subquery with no FROM of its own gets no probe in that case.

Tests:

New mysql_shape unit test for the probe text: the subquery's own WITH, the statement's before it, nested levels, sibling subqueries, a derived table reading the WITH, RECURSIVE, a WITH that shadows a table, and each refusal.
New oracle case oracle/cases/mysql_subquery_with, 15 statements, all run on MySQL 8.4 over seeded values with none unchecked. Before → after:
- own_count, own_count_correlated, outer_and_own, derived_reads_with: int8? → int8
- own_json, nested: fields unknown? → typed, and non-null where the columns are
- recursive: count int8? → int8; sum stays numeric?
- shadows_table: title: text → text? (the old type was contradicted by the seeded runs)
- own_max, own_json_unguarded, recursive_beside_self_named, wider_shadowed, with_inside_derived, shadows_table_count: unchanged
Every mysql_* oracle case passes with --strict-coverage, and no existing expected.txt changes.
packages/mysql/engine.wasm is rebuilt.

vercel · 2026-10-22T20:55:55Z

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated
reylo	Ready	Preview	Oct 22, 2026 8:56pm UTC

Copilot

Copilot was unable to review this pull request because the user who requested the review has reached their quota limit.

pullfrog

✅ No new issues found.

Reviewed changes

I reviewed the whole PR. It changes how the MySQL JSON-shape planner probes a scalar subquery's items, so that a subquery's own WITH reaches its probe.

Per-level WITH. Builder.stack now holds Level { ctx, from, with }, so each scalar subquery level keeps its query's WITH, and each Draft carries the WITH text its probe starts with.
carried flattens the levels. It joins every level's WITH, outermost first, into one list. If any level is RECURSIVE, the list is too, and it makes no probe when a query at a non-RECURSIVE level names itself. Top-level probes print the same as before.
Wider form guarded. draft() drops the wider form when an enclosing FROM names a WITH query from a level inside it. A subquery with no FROM of its own gets no probe in that case.
Tests. There is a new mysql_shape unit test for the probe text, a new oracle case oracle/cases/mysql_subquery_with with 15 statements, and a rebuilt packages/mysql/engine.wasm.

I traced how names resolve in the flattened list against the nested statement:

Unique names read the same in both.
A name listed twice is refused by the server, so the probe fails and the server's flag stands. Before this PR, the same case read the outer query's definition, which was unsound.
Correlated inner WITH queries fail to prepare in both the narrow and the wider form, so they stay untyped rather than being typed wrongly. nested_correlated.sql is an example.

probe_flow doesn't depend on where a ? sits, so the extra placeholders from the carried WITH queries don't misalign anything. cargo test -p reylo-engine-mysql --lib mysql_shape passes locally (10/10).

^{｜ View workflow run ｜ Using claude-opus-5-5 ｜ 𝕏}

fix(mysql): probe a scalar subquery's items with its own with

6390e1b

Copilot AI balanced review requested due to automatic review settings October 22, 2026 20:55

Copilot AI reviewed Oct 22, 2026

pullfrog Bot reviewed Oct 22, 2026

View reviewed changes

colinhacks merged commit 738f83d into main Oct 22, 2026
4 checks passed

colinhacks deleted the mysql-subquery-with branch October 22, 2026 20:59

This was referenced Oct 22, 2026

fix(mapper): keep diagnostics inside the file #20

Merged

fix(mysql): type a derived table's columns as its query alone does #33

Merged

Navigation Menu

Repository navigation

fix(mysql): probe a scalar subquery's items with its own with - #29

fix(mysql): probe a scalar subquery's items with its own with#29
colinhacks merged 1 commit into
mainfrom
mysql-subquery-with

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants