Repository navigation
fix(mysql): null a merged view's column on an outer join's null side - #46
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
b9f0f7c to
b14a44b
Compare
There was a problem hiding this comment.
ℹ️ No critical issues — two rough edges in how the copy's rewrite interacts with the rest of
prove.
Reviewed changes
Reviewed the full diff: the engine change that reads each view through a materialized derived table in the hinted copy, the new oracle case, and the unit tests.
- View rewrite in the materialized copy —
materializednow wraps eachTableFactor::Tablewhose name is a session view as(SELECT * FROM view) AS name, skippingSELECT * FROM <one table>blocks, and a statement that reads a view always gets the copy. - Views asked for every SELECT —
analyzenow asksVIEWS_QUERY(session-scoped) for every query and threads the set throughproveandthrough_derived. - Oracle case
mysql_merged_views— 12 statements covering MERGE, default and TEMPTABLE views on both sides of outer joins, nested joins, derived tables and a WITH query that shadows a view's name.
tests/fingerprint.test.ts passes at b14a44b, so the committed engine.wasm matches its sources.
claude-opus-5-5 ｜ 𝕏
A view created with ALGORITHM = MERGE is merged whatever the derived_merge hint says, and its expression keeps its NOT NULL flag on the null-extended side of an outer join. The materialized copy now reads each view through a derived table of its own.
A view read through a derived table no longer counts as a derived table to prove, and a copy the view rewrite breaks falls back to the copy without it.
b14a44b to
f7f70ae
Compare
There was a problem hiding this comment.
✅ No new issues found. Both points from the previous review are fixed.
Reviewed changes
Reviewed f7f70ae, which keeps the derived-table materialization when the view rewrite breaks the copy and stops the views' synthetic derived tables from counting as through_derived targets.
- Fallback copy in
prove: when the copy with views rewritten fails to prepare,provenow describes the copy without the rewrite, and only if that copy prints differently. The successful copy then becomesq_readfor everything after it. A statement that refers toapp.v.xkeeps its derived tables' nullability. reads_one_tabletargets skipped:through_derivedno longer describes a target that is exactlySELECT * FROM <one table>, and such a target does not count towardMAX_DERIVED. A user-written(select * from cte) dis skipped as well, which loses nothing because the WITH query is still a target of its own.- Unit test:
a_view_read_through_a_derived_table_neither_breaks_the_copy_nor_takes_a_targetcovers both changes.
tests/fingerprint.test.ts passes at f7f70ae, so the rebuilt packages/mysql/engine.wasm matches its sources.
claude-opus-5-5 ｜ 𝕏
On MySQL, a view's computed column read on the null-extended side of an outer join was typed non-null, and is NULL wherever the join finds no row:
typed
nextasint8; it is nowint8?.Why
The server merges a view into the query that reads it, and a merged expression keeps its own NOT NULL flag under the outer join (measured on 8.4). #33 reads the flags from a copy of the statement prepared with
SET_VAR(optimizer_switch = 'derived_merge=off'), which materializes derived tables, WITH queries and views of the default algorithm. A view created withALGORITHM = MERGEis merged whatever that hint says, and aNO_MERGEhint does not move it either.What changed
(SELECT * FROM view) AS name, under the name the statement reads it by. The hint materializes that derived table, so the server flags its columns as a temporary table's on the null-extended side. A block that only selects every column of one table is left alone: nothing in it can null-extend the view, and it is the shape a view is read through.information_schema.views, which it already asked for some statements) for every SELECT; the answer is per session. A statement that reads a view is read materialized.Tests
oracle/cases/mysql_merged_views(new, 12 statements, none unchecked): a view withALGORITHM = MERGE, one of the default algorithm and one withTEMPTABLE, on the null-extended side of LEFT and RIGHT joins, unaliased, inside a derived table, in a nested join, under an outer expression, with a WHERE that rejects the null row, on the preserved side, read alone, and a WITH query that shadows the view's name. Every statement runs on MySQL 8.4 over the seeded values.mysql_*cases.mysql_derivedcover the rewrite of a view, with and without an alias, in a schema, inside a nested join and inside a derived table.cargo testfor the four engines, the oracle with--strict-coverageoveroracle/cases/mysql_*,nub run typecheck, andnub run testagainst Postgres 18 and MySQL 8.4 (203 pass, none skipped) all pass.packages/mysql/engine.wasmis rebuilt.