Snowflake returns 'invalid query block' error when using `=ANY()` subquery operator-CodePudding

I'm trying to filter a table with a list of strings as a parameter, but as I want to make the parameter optional (in Python sql user case) I can't use IN operator.

With postgresql I was able to build the query like this:

        SELECT *
        FROM table1
        WHERE (id = ANY(ARRAY[%(param_id)s]::INT[]) OR %(param_id)s IS NULL)
        ;

Then in Python one could choose to pass a list of param_id or just None, which will return all results from table1. E.g. pandas.read_sql(query, con=con, params={param_id: [id_list or None]})

However I couldn't do the same with snowflake because even the following query fails:

        SELECT *
        FROM table1
        WHERE id = ANY(param_id)
        ;

Does Snowflake not have ANY operator? Because it is in their doc.

CodePudding user response：

If the parameter is a single string literal 1,2,3 then it first needs to be parsed to multiple rows SPLIT_TO_TABLE

SELECT *
FROM table1
WHERE id IN (SELECT s.value
             FROM TABLE (SPLIT_TO_TABLE(%(param_id)s, ',')) AS s);

CodePudding user response：

Agree with @Yuya. This is not very clear in documentation. As per doc - "IN is shorthand for = ANY, and is subject to the same restrictions as ANY subqueries."

However, it does not work this way - IN works with a IN list where as ANY only works with subquery.

Example -

select * from values (1,2),(2,3),(4,5);
 --------- --------- 
| COLUMN1 | COLUMN2 |
|--------- ---------|
|       1 |       2 |
|       2 |       3 |
|       4 |       5 |
 --------- ---------

IN works fine with list of literals -

select * from values (1,2),(2,3),(4,5) where column1 in (1,2);
 --------- --------- 
| COLUMN1 | COLUMN2 |
|--------- ---------|
|       1 |       2 |
|       2 |       3 |
 --------- ---------

Below gives error (though as per doc IN and = ANY are same) -

select * from values (1,2),(2,3),(4,5) where column1 = ANY (1,2);
002076 (42601): SQL compilation error:
Invalid query block: (.

Using subquery ANY runs fine -

select * from values (1,2),(2,3),(4,5) where column1 = ANY (select column1 from values (1),(2));
 --------- --------- 
| COLUMN1 | COLUMN2 |
|--------- ---------|
|       1 |       2 |
|       2 |       3 |
 --------- ---------

CodePudding user response：

Would it not make more sense for both snowflake and postgresql to have two functions/store procedures that have one/two parameters.

Then the one with the “default” just dose not asked this fake question (is in/any some none) and is simpler. Albeit it you question is interesting.