snowflake treating null different in select compared to where

Question

I encountered something strange with regard to snowflake's treatment of null values.

I have an intermediary query that filters out ineligible client names. I've narrowed it down to the where clause. I have some clients' legal names that are null, and are subsequently filtered out by the where clause. When I put this logic into the select, it still comes up as true.

select
v.legal_name
,not(iff(upper(v.legal_name) like any ('%PTY%'
                                            ,'%LTD%'
                                            ,'%BKU%'
                                            ,'%TRUST%'
                                            ,'%CORP%'
                                            ,'%PERPETUAL%'
                                            ,'%LIMITED%'
                                            ,'%MORTGAGE%'
                                            ,'%POSSESSION%'
                                            ,'%EXERCISING%'
                                            ,'%EXECUTOR%'
                                            ,'%ESTATE%'
                                            ,'%LEGAL%'
                                            ,'%PERSONAL%'
                                            ,'%REPRESENT%'
                                            ,'%DECEASED%'
                                            ,'%PRINCIPAL%'), true, false)) as test_legal_name

 from clients


 where not(upper(v.legal_name) like any ('%PTY%'
                                            ,'%/%'
                                            ,'%LTD%'
                                            ,'%BKU%'
                                            ,'%TRUST%'
                                            ,'%CORP%'
                                            ,'%PERPETUAL%'
                                            ,'%LIMITED%'
                                            ,'%MORTGAGE%'
                                            ,'%POSSESSION%'
                                            ,'%EXERCISING%'
                                            ,'%EXECUTOR%'
                                            ,'%ESTATE%'
                                            ,'%LEGAL%'
                                            ,'%PERSONAL%'
                                            ,'%REPRESENT%'
                                            ,'%DECEASED%'
                                            ,'%PRINCIPAL
                                            ))

Am I missing something? do other also find this? Is there a different way that I could state my where clause? I am thinking of using

 where iff(legal_name is null, true, <the rest of the where clause above>)

I am finding more and more instances where snowflake's handling of nulls are quirky and different to MS SQL.

Answer 1

Please check this article: https://community.snowflake.com/s/article/NULL-handling-in-Snowflake

Your use-case seems to fall into the rule 'An equality or inequality comparison like 'a'=NULL, 'a'>NULL or NULL=NULL will always return NULL'. In fact:

SELECT True AS Is_Not_Like_Any
WHERE NOT (NULL LIKE ANY ('%A%', '%B%'));

doesn't return True.

SELECT True AS Is_Not_Like_Any 
WHERE NOT (IFNULL(NULL, '') LIKE ANY ('%A%', '%B%'));

does return True. Therefore, you should use in your query:

WHERE NOT(UPPER(IFNULL(v.legal_name, '') ) LIKE ANY ..)