Does Snowflake have issues with MD5 on insert?

Question

At the risk that I've missed something obvious......

I'm inserting a record into a table that has a STRING used to hold a checksum value.

I'm attempting to insert this record:

INSERT INTO control_table (field1, field2, field3, field4, CHECKSUM_FIELD) VALUES ('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4', MD5('VALUE1VALUE2VALUE3VALUE4'));

Snowflake doesn't like the insert, telling me:

SQL compilation error: Invalid expression [MD5('VALUE1VALUE2VALUE3VALUE4')] in VALUES clause.

I'm not sure why it doesn't work, because this statement works fine:

UPDATE control_table SET CHECKSUM_FIELD = MD5('VALUE1VALUE2VALUE3VALUE4') WHERE FIELD1 = 'VALUE1';

Answer 1

Your issue is not related to the use of the MD5 function within an INSERT statement, but rather the fact that each expression in a VALUES clause must be

• a constant, OR
• an expression that can be evaluated as a constant during compilation of the SQL statement

This behavior is documented in this Snowflake docs page

Your INSERT can be rewritten as follows:

INSERT INTO control_table (field1, field2, field3, field4, CHECKSUM_FIELD)
SELECT $1, $2, $3, $4, MD5($5) FROM VALUES ('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4', 'VALUE1VALUE2VALUE3VALUE4');

And since it seems as though you want the value passed into the MD5() to be the concatenation of the other 4 expressions, you could use this instead:

INSERT INTO control_table (field1, field2, field3, field4, CHECKSUM_FIELD)
SELECT $1, $2, $3, $4, MD5(CONCAT($1, $2, $3, $4)) FROM VALUES ('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4');

But be careful about simply concatenating expressions like this since CONCAT(1,2,3,45) will yield the same results as CONCAT(12,3,4,5). Generally, you might want to inject delimiters between the expressions to avoid such issues. In that case, this might be a more appropriate approach:

INSERT INTO control_table (field1, field2, field3, field4, CHECKSUM_FIELD)
SELECT $1, $2, $3, $4, MD5(ARRAY_TO_STRING(ARRAY_CONSTRUCT($1, $2, $3, $4),':')) FROM VALUES ('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4');