COALESCE validation change for custom transformations (2024-04-16)

We've changed the way the SQL analyzer validates SQL statements that include the COALESCE() function. To ensure consistent behavior, all the operands for COALESCE should be of the same data type. If they aren't, COALESCE might return the result as a data type that you didn’t expect, causing downstream errors that are harder to troubleshoot.

Previously, our SQL analyzer allowed mixed data types for COALESCE. From this week, it’ll return an error if you try to publish your object-centric data model with a statement like that in a custom transformation. The error message looks like this:

Cannot infer return type for COALESCE; operand types: y....]

Transformations that you already published won’t stop working, but you’ll see the errors when you next attempt to publish. In order to publish the transformations, you'll need to resolve these errors by changing each affected SQL statement to use the same data type for all the operands for COALESCE.

For example, this statement mixes a column with the TIMESTAMP data type, and a timestamp represented using the STRING data type:

COALESCE(timestamp_column, ‘2024-04-16’)

You can fix this by giving the literal the correct data type:

COALESCE(timestamp_column, timestamp‘2024-04-16’)

For the instructions to work with custom transformations, see Creating custom transformations.