generate_surrogate_key treats Redshift super column types as NULL
- Dominant language
- Makefile
- Stars
- 1.8k
- Forks
- 632
- PR merge metrics
- No merged PRs in 30d
Description
### 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:**
```yaml
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
- [x] redshift
- [ ] bigquery
- [ ] snowflake
- [ ] other (specify: ____________)
**The output of `dbt --version`:**
```
1.11.7
```
### Additional context
The macro generates code like:
```sql
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:
```sql
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`:
```sql
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.
Contributor guide
Assessment
This issue has not been assessed yet.