castBinary() falls through to Postgres md5() on SQL Server — source query aborts and the table is silently skipped

[RodolfoMeloDev]

Summary

DataTypeCastingUtils.castBinary() has no mssql branch, so SQL Server binary columns fall through
to the default case, which emits Postgres' md5(). That function does not exist in SQL Server, so
the generated source query aborts. The comparison then reports source_cnt = 0 and the table is
effectively never verified — without any error surfaced to the user.

A related problem in the same area: SQL Server's rowversion is reported by the JDBC driver with
TYPE_NAME = 'timestamp', which matches TIMESTAMP_TYPES and routes it to castTimestamp(). That
applies AT TIME ZONE to what is actually an 8-byte binary token, and the source query aborts the
same way.

Affected versions

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

Impact

Any SQL Server source table containing a binary, varbinary or rowversion column is skipped.
The failure is silent in the sense that matters: the run completes, no comparison error is raised
for the user, and the table simply shows no meaningful result. In our deployment this went unnoticed
until we asserted on source_cnt ourselves.

Root cause

src/main/java/com/crunchydata/util/DataTypeCastingUtils.java, lines 78–92 (v0.6.0):

public static String castBinary(String dataType, String columnName, String platform) {
    return switch (platform) {
        // Snowflake TODO: Add support for binary types.
        // MSSQL does not have a binary type.
        // Postgres and MySQL use the same function for binary types.
        case "db2" -> ...
        case "mariadb" -> ...
        case "oracle" -> ...
        default -> String.format("coalesce(md5(%1$s), '%2$s')", columnName, EMPTY_STRING);
    };
}

The comment on line 81 — "MSSQL does not have a binary type" — is the origin of the bug. SQL Server
does have binary types: binary, varbinary, image and rowversion. And ColumnMetadataUtils
already routes two of them here:

public static final Set<String> BINARY_TYPES = Set.of("bytea", "binary", "blob", "raw", "varbinary");

So binary and varbinary from a SQL Server source reach castBinary() and receive md5(...),
which SQL Server rejects with:

'md5' is not a recognized built-in function name.

The rowversion half

UNSUPPORTED_TYPES already lists rowversion, so the intent to exclude it is there:

public static final Set<String> UNSUPPORTED_TYPES = Set.of("bfile", "bit", "cursor", "hierarchyid",
        "image", "rowid", "rowversion", "set", "sql_variant", "uniqueidentifier", "long", "long raw");

But that intent never takes effect, because the JDBC driver does not report the type as
rowversion — it reports timestamp, which is in TIMESTAMP_TYPES. The column is therefore
treated as temporal and castTimestamp() emits AT TIME ZONE against binary data.

Reproduction

1. SQL Server source table with a varbinary(max) column (and/or a rowversion column).
2. Postgres target with the corresponding bytea column.
3. discover + compare for that table.
4. The source-side query fails; dc_result shows source_cnt = 0 while target_cnt > 0.

Proposed fix

Two changes in DataTypeCastingUtils.java.

1. Add the mssql branch to castBinary(), using HASHBYTES('MD5', ...) rendered as lowercase
hex so it matches Postgres' md5() on the target side:

         case "oracle" ->
                 String.format("case when dbms_lob.getlength(%1$s) = 0 or %1$s is null then '%2$s' else lower(dbms_crypto.hash(%1$s,2)) end",
                              columnName, EMPTY_STRING);
+        case "mssql" ->
+                String.format("coalesce(lower(convert(varchar(max), hashbytes('MD5', cast(%1$s as varbinary(max))), 2)),'%2$s')",
+                             columnName, EMPTY_STRING);
         default -> String.format("coalesce(md5(%1$s), '%2$s')", columnName, EMPTY_STRING);

CONVERT(..., 2) produces hex without the 0x prefix, and LOWER matches the casing of Postgres'
md5(), so the two sides hash identically.

2. Route SQL Server timestamp (rowversion) to castBinary() before the temporal check, in
cast():

 public static String cast(String dataType, String columnName, String platform, JSONObject column) {
+    // SQL Server reports rowversion as data_type 'timestamp', but it is an 8-byte binary
+    // row-version token, not a temporal value. Without this, TIMESTAMP_TYPES matches and
+    // castTimestamp() applies AT TIME ZONE to binary data, aborting the source query.
+    if ("mssql".equals(platform) && "timestamp".equals(dataType)) {
+        return castBinary(dataType, columnName, platform);
+    }
     if (BOOLEAN_TYPES.contains(dataType)) {
         return castBoolean(dataType, columnName, platform);
     }

With the first change in place, this routes rowversion to HASHBYTES, which matches a target-side
bytea compared with md5().

Notes

This is the same class of defect as #77 ('mod' is not a recognized built-in function name when using MSSQL as the source): a Postgres function reaching a SQL Server session. We are happy to open
a PR with the change plus the tests above if that is preferred over an issue.

We have been running both changes in production against SQL Server 2019/2022 sources since June 2026.

[cbrianpace]

Binaries are always tricky. They have typically been marked as not supported as there was no way to consistently cast them across all platforms. If you have seen success on SQL Server, please open PR. If you could test on other database platforms to see if the support could be expanded that would be great.