SNOW-1437990: Server side binding of parameters to ROWS PRECEDING/FOLLOWING in the OVER Clause does not work

[suhas-dc]

Python version

Python 3.11.6 (main, Jan 8 2024, 12:38:24) [Clang 15.0.0 (clang-1500.1.0.2.5)]

Operating system and processor architecture

macOS-14.2.1-arm64-arm-64bit

Installed packages

asn1crypto==1.5.1
certifi==2024.2.2
cffi==1.16.0
charset-normalizer==3.3.2
cryptography==42.0.7
filelock==3.14.0
idna==3.7
packaging==24.0
platformdirs==4.2.2
pycparser==2.22
PyJWT==2.8.0
pyOpenSSL==24.1.0
pytz==2024.1
requests==2.32.0
snowflake-connector-python==3.10.0
sortedcontainers==2.4.0
tomlkit==0.12.5
typing_extensions==4.11.0
urllib3==2.2.1

What did you do?

On running a query that includes ROWS PRECEDING and FOLLOWING in a OVER clause, the query succeeds with paramstyle 'pyformat' and fails with 'qmark'

import snowflake.connector
CONNECTION_PARAMETERS = {  
    "account": "<account_name>",
    "user": "<user_name>",
    "database": "<database_name>",
    "schema": "<schema_name>",
    "protocol": "https",
    "host": "<host>",
    "port": "443",
}

print('Running with paramstyle pyformat')
snowflake.connector.paramstyle = 'pyformat'

c = snowflake.connector.connect(**CONNECTION_PARAMETERS)
query = 'SELECT avg("PUBLIC"."Table_1"."Numeric_Column_1") OVER (ORDER BY "PUBLIC"."Table_1"."Numeric_Column_2" ROWS BETWEEN %(param_1)s PRECEDING AND %(param_2)s FOLLOWING) AS anon_1 \nFROM "PUBLIC"."Table_1"'
print(c.cursor().execute(query, {'param_1': 5, 'param_2': 5}).fetchone())

print('Running with paramstyle qmark')
snowflake.connector.paramstyle = 'qmark'

c = snowflake.connector.connect(**CONNECTION_PARAMETERS)
query = 'SELECT avg("PUBLIC"."Table_1"."Numeric_Column_1") OVER (ORDER BY "PUBLIC"."Table_1"."Numeric_Column_2" ROWS BETWEEN ? PRECEDING AND ? FOLLOWING) AS anon_1 \nFROM "PUBLIC"."Table_1"'
print(c.cursor().execute(query, (5, 5)).fetchone())

What did you expect to see?

Queries with both pyformat and qmark paramstyle should succeed, but the query fails for qmark with the error.

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 108 unexpected '?'.

As per the docs this should work with sever side binding since there is no IN operator.

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 @suhas-dc ,

Thanks for raising the issue; we are taking a look and will update you.

Regards, Sujan

[sfc-gh-sghosh]

Hello @suhas-dc ,

We are able to reproduce the issue with 'qmark', will work on eliminating it, will update

Regards, Sujan