castNumber() uses SQL Server FORMAT() without an explicit culture — every numeric column reports false drift on non-English sessions

[RodolfoMeloDev]

Summary

DataTypeCastingUtils.castNumber() builds the SQL Server expression with FORMAT() and no culture
argument. FORMAT() honours the session's language, so on any non-English SQL Server session the
decimal separator becomes a comma while Postgres' to_char() on the target side produces a period.
The two hashes never match, and every numeric column in every table reports as out of sync, with
identical data on both sides.

Affected versions

Reproduced on v0.5.0 and confirmed still present on v0.6.0 (DataTypeCastingUtils.java is
unchanged between the two tags).

Impact

This is a systemic false positive, not an edge case. On a pt-BR (or es-ES, fr-FR, de-DE, …) SQL
Server instance, a fully consistent table is reported as fully divergent. In our case the first
reaction was to doubt the replication pipeline, not the comparison tool — we spent time chasing a
data problem that did not exist.

The severity is worth stating plainly: a user in a non-English locale is likely to conclude that
pgCompare does not work against SQL Server, without ever learning why.

Root cause

src/main/java/com/crunchydata/util/DataTypeCastingUtils.java, lines 184–185 (v0.6.0), inside
castNumber():

case "mssql" -> NOTATION_CAST.equals(numberCast)
        ? String.format("lower(replace(coalesce(trim(format(%1$s,'E10')),'%2$s'),'E+0','e+'))", columnName, EMPTY_STRING)
        : String.format("coalesce(cast(format(%1$s, '%2$s') as text),'%3$s')", columnName, Props.getProperty("standard-number-format"), EMPTY_STRING);

Both expressions call FORMAT() with only a format string. Per Microsoft's documentation, FORMAT()
is culture-aware and falls back to the language of the current session when no culture is supplied.

Comparison, same value, same table:

Session language	Source expression result	Target (to_char)
us_english	1234.56	1234.56 — match
Português	1234,56	1234.56 — mismatch

Every row differs, so not_equal_cnt equals the row count while missing_source_cnt and
missing_target_cnt stay at zero.

Reproduction

1. SQL Server login whose default language is not English (sp_configure/ALTER LOGIN … WITH DEFAULT_LANGUAGE = Português), or run SET LANGUAGE Português; in the session.
2. Any table with a decimal, numeric, money or float column.
3. Postgres target with matching data.
4. compare reports all rows as not-equal.

Quick confirmation without pgCompare:

SET LANGUAGE us_english;  SELECT FORMAT(CAST(1234.56 AS decimal(10,2)), 'G');  -- 1234.56
SET LANGUAGE Português;   SELECT FORMAT(CAST(1234.56 AS decimal(10,2)), 'G');  -- 1234,56

Proposed fix

Pass the invariant culture explicitly. FORMAT() accepts a third argument for exactly this:

 case "mssql" -> NOTATION_CAST.equals(numberCast)
-        ? String.format("lower(replace(coalesce(trim(format(%1$s,'E10')),'%2$s'),'E+0','e+'))", columnName, EMPTY_STRING)
-        : String.format("coalesce(cast(format(%1$s, '%2$s') as text),'%3$s')", columnName, Props.getProperty("standard-number-format"), EMPTY_STRING);
+        ? String.format("lower(replace(coalesce(trim(format(%1$s,'E10','en-US')),'%2$s'),'E+0','e+'))", columnName, EMPTY_STRING)
+        : String.format("coalesce(cast(format(%1$s, '%2$s','en-US') as text),'%3$s')", columnName, Props.getProperty("standard-number-format"), EMPTY_STRING);

en-US is used rather than a configurable value because the target side is not configurable either:
Postgres' to_char() always emits a period. Pinning both sides to the same convention is what makes
the hashes comparable. On an English session the output is byte-for-byte identical to today's, so
this is not a behaviour change for existing users.

Notes

We have been running this change in production against SQL Server 2019/2022 with pt-BR sessions
since June 2026. Happy to open a PR with the change and the test if preferred.

[cbrianpace]

Another great find. Please proceed with a PR.