Array binding support for filtering parameter

[nearsyh]

My code

row, err = db.QueryRow("SELECT ARRAY_CONTAINS('a'::variant, ?)", gosnowflake.Array([]string{"a", "b"}))

What is the current behavior?

We have a non-nil err. Error message is

Fail to get the query result: 002049 (42601): SQL compilation error: error line 1 at position 36
    Bind variable ? not set.

What is the desired behavior?

This query would complete successfully.

How would this improve gosnowflake?

It would make the SQL simpler. Currently, we need to change the code like below:

row, err = db.QueryRow("SELECT ARRAY_CONTAINS('a'::variant, SPLIT(?, ','))", strings.Join(arr, ","))

[sfc-gh-dszmolka]

hi and thank you for submitting this enhancement request, also a possible workaround ! we'll take a look and see how it can be prioritized - it might take a while, so thank you in advance for bearing with us !

in the meantime, additional workaround if it makes sense for your environment, using ARRAY_CONSTRUCT to build the array, which can then conveniently take bind variables:

        selectQuery := "SELECT ARRAY_CONTAINS('a'::VARIANT, ARRAY_CONSTRUCT(?, ?));"
        rows, err := conn.QueryContext(ctx, selectQuery, "a", "b")

[sfc-gh-dszmolka]

as an initial estimation per current situation, feature can be expected earliest H2 2025 or later (for the Go driver). thank you everyone for bearing with us !

[sfc-gh-dszmolka]

hey folks - team just got to this issue. Upon reviewing it, it turns out it is a limitation on the backend. INSERT is the only one statement supported natively for array binding (on the backend) . The driver prepares the statement the exactly same way for all the other types of queries. For SELECT, UPDATE, etc. first of all, the backend (==Snowflake itself) needs to support this functionality, and this is driver-independent.

As a workaround, the already mentioned ways can be used.

To move forward with this issue, please reach out to your Snowflake account team and let them know how important it is for you to have Snowflake backend support array binding for non-INSERT statements. They can work with the relevant teams responsible for the Snowflake engine and implement this functionality to be available for all drivers (besides gosnowflake)