Try to understand the BigQuery behaviour of the ANY operator (see also here
The following statement returns as expected true:
SELECT 'a' LIKE ANY ('b','%b%', 'b', 'a');
--true
It basically answers the question, is there any ‘a’ in the pattern, yes it is!
Now inverting the statement also returns true:
SELECT 'a' NOT LIKE ANY ('b','%b%', 'b', 'a');
-- true
In this case the question would be is there not any ‘a’ in the pattern, which is false, there is a ‘a’ in the pattern.
Can somebody help me to understand the behaviour of BigQuery? thanks!
Was reading throw the documentation which states: ANY: Checks if the set of patterns contains at least one pattern that matches the search value
>Solution :
No, the second query asks the database if in the tupel are elements that are not a
And as there are 3 You get true.
For what you are searching is NOT IN