SNOW-1325123: Params qmarks does not handle empty list properly

[duongleh]

Python version

3.11

Operating system and processor architecture

Snowflake Python Worksheet

Installed packages

snowflake-snowpark-python

What did you do?

import snowflake.snowpark as snowpark

def main(session: snowpark.Session): 
    whitelist = []
    dataframe = session.sql("""
    select ARRAY_SIZE(PARSE_JSON(:1))
    """, params=[whitelist])

    # Print a sample of the dataframe to standard output.
    dataframe.show()

    # Return value will appear in the Results tab.
    return dataframe

What did you expect to see?

Expect the query to return 0 as the param is an empty list But here is the error log

Traceback (most recent call last):
  Worksheet, line 11, in main
  File "snowflake/snowpark/_internal/telemetry.py", line 139, in wrap
    result = func(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/dataframe.py", line 2891, in show
    self._show_string(
  File "snowflake/snowpark/dataframe.py", line 3009, in _show_string
    result, meta = self._session._conn.get_result_and_metadata(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/server_connection.py", line 649, in get_result_and_metadata
    result_set, result_meta = self.get_result_set(plan, **kwargs)
                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/analyzer/snowflake_plan.py", line 116, in wrap
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/server_connection.py", line 609, in get_result_set
    result = self.run_query(
             ^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/server_connection.py", line 123, in wrap
    raise ex
  File "snowflake/snowpark/_internal/server_connection.py", line 117, in wrap
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/server_connection.py", line 415, in run_query
    raise ex
  File "snowflake/snowpark/_internal/server_connection.py", line 400, in run_query
    results_cursor = self.execute_and_notify_query_listener(
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/snowpark/_internal/server_connection.py", line 352, in execute_and_notify_query_listener
    results_cursor = self._cursor.execute(query, **kwargs)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/connector/cursor.py", line 991, in execute
    kwargs["binding_params"] = self._connection._process_params_qmarks(
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "snowflake/connector/connection.py", line 736, in _process_params_qmarks
    first_type = all_param_data[0].type
                 ~~~~~~~~~~~~~~^^^
IndexError: list index out of range

Can you set logging to DEBUG and collect the logs?

import logging
import os

for logger_name in ('snowflake.connector',):
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
    logger.addHandler(ch)

[sfc-gh-sghosh]

Hello @duongleh ,

Thanks for raising the issue, we are having a look, will update.

Regards, Sujan

[sfc-gh-sghosh]

Hello @duongleh ,

Could you try this way as follows by casting the array to Strng, you will get the expected output.

`whitelist = [] whitelist_json = str(whitelist) sql_query = """ SELECT ARRAY_SIZE(PARSE_JSON(?)) """

dataframe = session.sql(sql_query, params=[whitelist_json]) dataframe.show()

`

Regards, Sujan

[sfc-gh-sghosh]

Hello @duongleh ,

The PARSE_JSON function accepts string as parameter, so you have to either cast it to string the recommendation which we provided before or or use as string whitelist = '["a", "b"]'

whitelist = ['']

whitelist_json = str(whitelist)

sql_query = """ SELECT ARRAY_SIZE(PARSE_JSON(?)) """ dataframe = session.sql(sql_query, params=[whitelist]) dataframe.show()

or

whitelist = [] whitelist_json = str(whitelist) sql_query = """ SELECT ARRAY_SIZE(PARSE_JSON(?)) """

dataframe = session.sql(sql_query, params=[whitelist_json]) dataframe.show()

Regards, Sujan

[duongleh]

ah got it, we will proceed to to use a string whitelist