SNOW-997888: util_text.split_statements() breaks stored procedure statements (LANGUAGE SQL)

[irvingpop]

Python version

Python 3.11.6 (main, Nov 2 2023, 04:39:43) [Clang 14.0.3 (clang-1403.0.22.14.1)]

Operating system and processor architecture

macOS-14.2.1-arm64-arm-64bit

Installed packages

agate==1.7.1
appdirs==1.4.4
asn1crypto==1.5.1
asttokens==2.4.1
attrs==23.1.0
Babel==2.13.1
backoff==2.2.1
certifi==2023.11.17
cffi==1.16.0
chardet==5.2.0
charset-normalizer==3.3.2
click==8.1.7
colorama==0.4.6
cryptography==41.0.7
dbt-core==1.7.3
dbt-extractor==0.5.1
dbt-semantic-interfaces==0.4.2
dbt-snowflake==1.7.0
decorator==5.1.1
Deprecated==1.2.14
diff_cover==8.0.1
executing==2.0.1
filelock==3.13.1
googleapis-common-protos==1.62.0
grpcio==1.60.0
idna==3.6
importlib-metadata==6.11.0
iniconfig==2.0.0
ipdb==0.13.13
ipython==8.18.1
isodate==0.6.1
jaraco.classes==3.3.0
jedi==0.19.1
Jinja2==3.1.2
jsonschema==4.20.0
jsonschema-specifications==2023.11.2
keyring==24.3.0
leather==0.3.4
Logbook==1.5.3
MarkupSafe==2.1.3
mashumaro==3.11
matplotlib-inline==0.1.6
minimal-snowplow-tracker==0.0.2
more-itertools==10.1.0
msgpack==1.0.7
networkx==3.2.1
opentelemetry-api==1.22.0
opentelemetry-distro==0.43b0
opentelemetry-exporter-otlp==1.22.0
opentelemetry-exporter-otlp-proto-common==1.22.0
opentelemetry-exporter-otlp-proto-grpc==1.22.0
opentelemetry-exporter-otlp-proto-http==1.22.0
opentelemetry-instrumentation==0.43b0
-e git+https://github.com/irvingpop/opentelemetry-python-contrib@ecceaaa809538b8375d790f773c6657a39dd0de6#egg=opentelemetry_instrumentation_dbt_core&subdirectory=instrumentation/opentelemetry-instrumentation-dbt-core
opentelemetry-instrumentation-jinja2==0.43b0
-e git+ssh://git@github.com/pridhi-arora/opentelemetry-python-contrib.git@22a396fa7486f02ef5946a1190bb564cfe7638c3#egg=opentelemetry_instrumentation_threading&subdirectory=instrumentation/opentelemetry-instrumentation-threading
opentelemetry-proto==1.22.0
opentelemetry-sdk==1.22.0
opentelemetry-semantic-conventions==0.43b0
packaging==23.2
parsedatetime==2.6
parso==0.8.3
pathspec==0.11.2
pexpect==4.9.0
platformdirs==3.11.0
pluggy==1.3.0
prompt-toolkit==3.0.43
protobuf==4.25.1
ptyprocess==0.7.0
pure-eval==0.2.2
py-spy==0.3.14
pycparser==2.21
pydantic==1.10.13
Pygments==2.17.2
PyJWT==2.8.0
pyOpenSSL==23.3.0
pytest==7.4.3
python-dateutil==2.8.2
python-slugify==8.0.1
pytimeparse==1.1.8
pytz==2023.3.post1
PyYAML==6.0.1
referencing==0.32.0
regex==2023.10.3
requests==2.31.0
rpds-py==0.13.2
six==1.16.0
snowflake-connector-python==3.6.0
sortedcontainers==2.4.0
sqlfluff==2.3.5
sqlparse==0.4.4
stack-data==0.6.3
tblib==3.0.0
text-unidecode==1.3
tomlkit==0.12.3
tqdm==4.66.1
traitlets==5.14.0
typing_extensions==4.8.0
urllib3==1.26.18
wcwidth==0.2.12
wrapt==1.16.0
zipp==3.17.0

What did you do?

I wish to manage a Snowflake stored procedure using dbt. Given the following dbt macro:

{% macro macro_sp_test() %}

CREATE OR REPLACE PROCEDURE macro_sp_test()
  RETURNS NUMBER
  LANGUAGE SQL
  AS
  DECLARE
    var1 NUMBER;
  BEGIN
    var1 := (select 1);
    RETURN :var1;
  END
;

{% endmacro %}

The statement is being broken because it's split on the semicolons:

23:35:10  On master: /* {"app": "dbt", "dbt_version": "1.7.3", "profile_name": "default", "target_name": "dev", "connection_name": "master"} */
CREATE OR REPLACE PROCEDURE macro_sp_test()
  RETURNS NUMBER
  LANGUAGE SQL
  AS
  DECLARE
    var1 NUMBER;
23:35:10  Opening a new connection, currently in state init
23:35:11  Snowflake adapter: Snowflake query id: 01b15827-0404-180b-0025-160311680546
23:35:11  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 6 at position 16 unexpected '<EOF>'.
23:35:11  Database error while running on-run-start

I traced this down to the use of snowflake.connector.util_text.split_statements

What did you expect to see?

I expected that split_statements() would correctly detect that it's in a stored procedure and not break up the SP.

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)

[irvingpop]

This was my bad. someone pointed out that I should've surrounded the statement with $$ and that totally fixed it.