Skip to content

fix: a column the source added comes with its values - #306

Merged
jasdeepkhalsa merged 4 commits into
fix/escape-identifiersfrom
fix/new-column-values
Nov 16, 2026
Merged

jasdeepkhalsa merged 4 commits into
fix/escape-identifiersfrom
fix/new-column-values

Conversation

@jasdeepkhalsa

Copy link
Copy Markdown
Member

Stacked on #305, which changes the same lines. GitHub retargets this to master once #305 merges.

The data diff compared only the columns both sides had. When the source had added a column and filled it in, the target got the column with its default in every row it already held. So a value the source had set, or set back to NULL, never arrived. The same-server MySQL diff already carried these values. The streaming merge (all PostgreSQL diffs, and MySQL across two servers) and SQLite did not.

  • StreamingMergeDiff::getDiff sets a column only the source has to the source's value on every row both sides hold. It's set by the UP only (DiffOpAdd), since the DOWN drops the column. Without such a column, the hash comparison is unchanged.
  • SQLite's local diff does the same.
  • A column the schema diff renames (AlterTableRenameColumn) keeps its data and is left out. DiffCalculator passes the renames down to the data diff.
  • StreamingMergeDiffTest::testDifferentColumnSets pinned the old behaviour and now asserts the new one.

Tests:

  • NewColumnValuesRoundTripPostgresTest: a column added with a default, with rows changed from it, rows set to NULL and a new row; UP and DOWN round trip. Fails on master. A rename alone writes no UPDATE; that test fails with the rename map switched off.
  • NewColumnValuesSQLiteTest: the same on SQLite. Fails on master.

Locally on PostgreSQL 18, MySQL 8.4 and SQLite: the Postgres, DBDiff+Unit and SQLite suites pass.

🤖 Generated with Claude Code

The data diff compared only the columns both sides had. A column the
source added and filled in was added to the target with its default in
every row the target already held, so a value the source had set (or set
back to NULL) never arrived. The same-server MySQL diff already set it;
the streaming merge (PostgreSQL, and MySQL across servers) and SQLite did
not.

Every row both sides hold is now set to the source's value for such a
column, by the UP only: the DOWN drops the column. A column the schema
diff renames keeps its data and is left out.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
@github-actions github-actions Bot added bug mysql Related to Mysql php Pull requests that update php code postgres Related to Postgres sqlite Related to Sqlite labels Nov 15, 2026
@sonarqubecloud

Copy link
Copy Markdown

@jasdeepkhalsa
jasdeepkhalsa merged commit 095c490 into fix/escape-identifiers Nov 16, 2026
2 checks passed
jasdeepkhalsa added a commit that referenced this pull request Nov 16, 2026
…307)

Stacked on #306 (itself on #305), since all three change the same SQLite
diff. GitHub retargets each one as the PR below it merges.

**MySQL `BIT`.** Values were read as their integer and written quoted,
`'5'`. MySQL takes that as the text's bytes, so strict mode fails with
"Data too long" and non-strict mode stores a different value. `BIT` is
now one of the columns read as hex, like `BINARY`/`BLOB`, and written as
`UNHEX('…')`. That includes the rows of a table only one side has, which
`TableIterator` now also reads as hex.

**SQLite.** Every value was written quoted:
- an integer in a column with no declared type came back as the text
`'12'`, which no longer equals `12`;
- a `STRICT` table's `REAL` column refused `'Infinity'`;
- blobs, and text holding a NUL, were written as raw bytes inside a
string literal.

Now:
- integers and reals are written bare. Infinities are `±9e999`. NaN
becomes `NULL`, which is what SQLite stores for it;
- columns declared `BLOB` (`SQLiteAdapter::getBinaryColumns`, previously
always empty) are read with `hex()`, NULL kept, and written as `X'…'`;
- any other bytes that aren't UTF-8 are written as `X'…'`;
- UTF-8 text holding a NUL is written as `CAST(X'…' AS TEXT)`, so it
stays text.

The SQLite golden files change only in numbers losing their quotes. A
check that undoes that normalisation leaves the old and new baselines
identical.

Tests:
- `RoundTripMySQLTest::testBitValuesRoundTrip`: `BIT(3)` and `BIT(64)`
at their extremes, in changed rows, new rows and a new table, UP and
DOWN. Fails on `master`.
- `SQLiteValueTypesTest`: the storage class and exact value (`typeof`,
`quote`) of each value after UP and after DOWN, for an untyped column
and a `STRICT` table. Fails on `master`.

Locally on MySQL 8.4, PostgreSQL 18 and SQLite: the `DBDiff`+`Unit`,
`Postgres` and `SQLite` suites pass.

🤖 Generated with [Claude Code](https://claude.com/claude-code)
jasdeepkhalsa added a commit that referenced this pull request Nov 19, 2026
…307) (#310)

Lands #306 and #307 on master. Both were stacked PRs that merged into
their base branches after those bases had merged, so their changes never
reached master.

**New column values (#306)** — PostgreSQL, SQLite, and MySQL across two
servers
- A column only the source has now arrives with its values: every row
both sides have gets an UPDATE setting it (UP only; the DOWN drops the
column).
- Renamed columns are left out, so a rename alone writes no UPDATE.

**Value types (#307)** — MySQL and SQLite
- MySQL `BIT` columns are read as hex and written as `UNHEX('…')`
instead of a quoted decimal, which strict mode rejected.
- SQLite values keep their type: integers and reals unquoted, BLOBs as
`X'…'`, text holding NUL as `CAST(X'…' AS TEXT)`.
- SQLite golden files re-recorded; they differ only in number quoting.

Tests: `NewColumnValuesRoundTripPostgresTest`,
`NewColumnValuesSQLiteTest`, `SQLiteValueTypesTest`,
`RoundTripMySQLTest::testBitValuesRoundTrip`.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

https://claude.ai/code/session_015SLu3N7SbCP8UAv4sbSrjR

---------

Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

bug mysql Related to Mysql php Pull requests that update php code postgres Related to Postgres sqlite Related to Sqlite

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant