nullif(x,x) == null?

[RobbertDM]

Hi there! Sometimes nullif is used with twice the same value, for example in https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/include/global_project/macros/materializations/snapshots/helpers.sql#L62

Wouldn't that always evaluate to null? Why not put null instead?

[dbeatty10]

Those are some very sharp eagle eyes @RobbertDM ! 🦅

Caveat: all the commentary that follows is my best guess from reading the relevant code -- I didn't actually test any of this out to be certain.

TL;DR

In the initial snapshot, dbt_valid_to will be null for all rows. This nullif trick forces the cast to the same data type as dbt_valid_from rather than the database choosing some other unintended/incompatible data type.

null sounds about right

I think the answer is "yes!" it will always evaluate to null. But I suspect that it isn't just any null, it's a null with the same data type as the strategy.updated_at column/expression.

So if strategy.updated_at has a datatype of timestamp, then I'm thinking it would be equivalent to this:

cast(null as timestamp) as dbt_valid_to

More detail

Looking at the initial commit for this logic more closely, it looks to me like nullif({{ strategy.updated_at }}, {{ strategy.updated_at }}) as dbt_valid_to replaced {{ timestamp_column.literal('null') }} as dbt_valid_to.

Expanding out literal('null'), we'd get something like null::TIMESTAMP or cast(null as TIMESTAMP) depending on the database adapter.

Why not just do something like cast(null as timestamp) directly? Because strategy.updated_at can be an expression, dbt might not know the actual data type it has. So the nullif() approach is a clever way of handing it to the database and saying "you figure it out!".

[RobbertDM]

Oh wow, I didn't even know nulls had a type 😲
Thanks for explaining!