Hash Algorithm

[anitsche-bt]

Hi guys, following question:
i like to link a value to the unknown default value (0*), but this seems to be impossible, because of the IFNULLS within the hash logic you use. Wanted to use the unknown value as starting point (lvl 0) in a hierarchical link.
I don't believe that this was intentionally, because the outer IFNULL has the 0* as a replacing value.
Could you check please.

BR

[tkirschke]

Hi Alexander!

Thanks for your input! Could you please give me more details about:

• Where you modified the value of the global variable for the unknown default value
• How your model looks like
• How the compiled SQL looks like
• How you want the compiled SQL to look like

In the macros "hash", "hash_standardization" and "hash_default_values" I can't find any hardcoded "000..0000" as a replacement value, instead, the parameter is passed down from the global variable. Let's try to figure out what causes the problem here!

[anitsche-bt]

Hi Tim,
i left some things out, sorry for that, first of all, i am talking about dv4dbt on bigquery. I was building a staging view which is a source for a hierarchical link. i hoped to link an object to the default value (0000..) when the input column for the hash is null, but it materializes to following code (replaced the column variable with null here):
SELECT IFNULL(TO_HEX(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(IFNULL( (CONCAT('\"', REPLACE(REGEXP_REPLACE( REGEXP_REPLACE(TRIM(CAST(NULL AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^')), r'\n', ''), r'\t', ''), r'\v', ''), r'\r', '') AS STRING), '')))), '00000000000000000000000000000000') AS hk_abcd_h

Result Hash: ea01662a5bcbe96a3236d575bbcb3da3

Assumption (had not enough time yet to check in detail): Due to the CONCAT, the inner statement is never null, so the outer IFNULL could not be reached and therefore, i am not able to link the default value.
And i don't want to add a second dummy value manually.

Possible Solution: From my point of view, there is a possible bug in the NULLIF clause, which should have NULLIF(expr, '^^') but currently is NULLIF(expr, '')

BR

[tkirschke]

Thanks for the details!

I tested everything again with NULL inputs for hash calculation and you are absolutely right, it does not work as it should. The reason is, that the variable "all_null" is created and appended, but not passed in correctly into the replacement of the NULLIF. I fixed it and it now works on my side.
The two files I have changed are the hash.sql and the hash_standardization.sql, for all three adapters. The working solution is located in this branch. I am currently waiting for my colleagues to approve this change, but you could install the branch manually inside your dbt project and already test if it works for you now as well!

[anitsche-bt]

Thanks Tim, I'll have a look.

BR