generate_surrogate_key treats Redshift super column types as NULL

[jeffkeller-einc]

Describe the bug

The generate_surrogate_key macro silently treats Redshift super column types as NULL.

Steps to reproduce

1. Use generate_surrogate_key on a SUPER column
2. Use generate_surrogate_key on a column containing all NULL values

Expected results

The generated keys should be different.

Actual results

The generated keys are the same.

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.3
  - package: metaplane/dbt_expectations
    version: 0.10.10

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

1.11.7

Additional context

The macro generates code like:

md5(cast(coalesce(cast(col_name as TEXT), '_dbt_utils_surrogate_key_null_') || '-' || ... AS TEXT))

But the inner cast(col_name as TEXT) step returns NULL if col_name is a SUPER type.

Consider:

select
	JSON_PARSE('{"count": 1}') as super_col,
	cast(super_col as TEXT) as super_col_text,
	md5(cast(coalesce(cast(super_col as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash,
	md5(cast('_dbt_utils_surrogate_key_null_' as TEXT)) as surrogate_key_hash

Similar to #1016, the workaround is to cast SUPER columns prior to calling generate_surrogate_key:

select
	JSON_PARSE('{"count": 1}') as super_col,
	cast(super_col as TEXT) as super_col_text,
	md5(cast(coalesce(cast(super_col as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash,
	md5(cast('_dbt_utils_surrogate_key_null_' as TEXT)) as surrogate_key_hash,
	JSON_SERIALIZE(super_col) as super_col_serialized,
	md5(cast(coalesce(cast(super_col_serialized as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash_fixed

Are you interested in contributing the fix?

Fix seems non-trivial and I'm too dumb.