Problem

You are working with a view that includes several columns containing hexadecimal-formatted data. These values are typically derived through transformations such as xxhash64 or similar functions. When attempting to convert these results to the BINARY data type using CAST(... AS BINARY) or TRY_CAST(... AS BINARY), the query fails with the following error.

[DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION] Cannot resolve "TRY_CAST(<operation> AS BINARY)" due to data type mismatch: cannot cast "<column-type>" to "BINARY". SQLSTATE: 42K09

This error occurs consistently, whether ANSI SQL mode (ANSI_MODE) is enabled or disabled. 

Cause

Standard SQL CAST(... AS BINARY) operations are designed to operate primarily on character strings — most commonly, hexadecimal strings — which can be parsed and converted into binary byte arrays. 

When the input is a numeric type such as BIGINT (for example, the result of xxhash64), the CAST function does not have a defined behavior for transforming that numeric value into a binary representation. It does not reinterpret the internal memory layout of the numeric value, nor does it serialize the number into a byte array. 

As a result, providing a non-string input to CAST(... AS BINARY) violates expected type semantics and triggers a data type mismatch error.

Solution

Instead of relying on CAST or TRY_CAST, which adhere to strict type conversion rules, use TO_BINARY(...) or TRY_TO_BINARY(...) functions. 

Both TO_BINARY(...) and TRY_TO_BINARY(...) are explicitly designed to handle a wider range of input types, including numerics, and convert them into valid binary representations. They provide a more flexible and robust mechanism for converting transformed or hashed values (for example, outputs of xxhash64) into the BINARY data type.