SNOW-872652: Race condition in execute_async() when an error occurs

[trotterdylan]

Python version

3.10.5

Operating system and processor architecture

macOS-13.4.1-arm64-arm-64bit

Installed packages

aiohttp==3.8.5
aiopg==1.4.0
aiosignal==1.3.1
alembic==1.10.2
alembic-utils==0.8.1
altair==5.0.1
amqp==5.1.1
anyio==3.7.1
asn1crypto==1.5.1
async-timeout==4.0.2
asyncpg==0.27.0
attrs==23.1.0
bcrypt==4.0.1
beautifulsoup4==4.12.2
billiard==3.6.4.0
boto3==1.26.71
botocore==1.29.165
cachetools==5.3.1
celery==5.2.7
certifi==2023.5.7
cffi==1.15.1
charset-normalizer==2.1.1
click==8.1.6
click-didyoumean==0.3.0
click-plugins==1.1.1
click-repl==0.3.0
coverage==7.2.7
cramjam==2.6.2
cryptography==40.0.2
datadog==0.44.0
Deprecated==1.2.14
dnspython==2.4.0
dubo==0.2.2
email-validator==2.0.0.post2
exceptiongroup==1.1.2
fast-autocomplete==0.9.0
fastapi==0.88.0
fastparquet==2023.1.0
filelock==3.12.2
flake8==6.0.0
flake8-mypy==17.8.0
flupy==1.2.0
frozenlist==1.4.0
fsspec==2023.6.0
future==0.18.3
google-api-core==2.11.1
google-auth==2.22.0
google-cloud-bigquery==3.4.1
google-cloud-bigquery-storage==2.20.0
google-cloud-core==2.3.3
google-crc32c==1.5.0
google-resumable-media==2.5.0
googleapis-common-protos==1.59.1
greenlet==2.0.2
grpcio==1.56.2
grpcio-status==1.56.2
h11==0.14.0
httpcore==0.17.3
httpx==0.23.3
huggingface-hub==0.16.4
idna==3.4
iniconfig==2.0.0
Jinja2==3.1.2
jmespath==1.0.1
joblib==1.3.1
JSON-log-formatter==0.5.2
jsonschema==4.17.3
kombu==5.3.1
Levenshtein==0.21.1
limits==2.8.0
lxml==4.9.2
Mako==1.2.4
MarkupSafe==2.1.3
mccabe==0.7.0
mpmath==1.3.0
multidict==6.0.4
mypy==1.4.1
mypy-extensions==1.0.0
networkx==3.1
nltk==3.8.1
numpy==1.25.1
openai==0.27.8
orjson==3.9.2
oscrypto==1.3.0
packaging==21.3
pandas==2.0.2
paramiko==3.2.0
parse==1.19.1
pglast==5.2
Pillow==10.0.0
playwright==1.36.0
pluggy==1.2.0
prompt-toolkit==3.0.39
proto-plus==1.22.3
protobuf==4.23.4
psycopg2==2.9.5
psycopg2-binary==2.9.6
pyarrow==10.0.1
pyasn1==0.5.0
pyasn1-modules==0.3.0
pycodestyle==2.10.0
pycparser==2.21
pycryptodomex==3.18.0
pydantic==1.10.4
pyee==9.0.4
pyflakes==3.0.1
PyJWT==2.6.0
PyNaCl==1.5.0
pyOpenSSL==23.2.0
pyparsing==3.1.0
pyrsistent==0.19.3
pytest==7.2.0
pytest-cov==4.1.0
python-dateutil==2.8.2
python-dotenv==0.21.0
python-Levenshtein==0.21.1
python-multipart==0.0.6
pytz==2023.3
PyYAML==6.0
rapidfuzz==3.1.1
redis==4.5.1
regex==2023.6.3
requests==2.31.0
rfc3986==1.5.0
rsa==4.9
s3transfer==0.6.1
safetensors==0.3.1
scikit-learn==1.3.0
scipy==1.11.1
sentence-transformers==2.2.2
sentencepiece==0.1.99
six==1.16.0
slowapi==0.1.7
sniffio==1.3.0
snowflake-connector-python==3.0.2
snowflake-sqlalchemy==1.4.7
sortedcontainers==2.4.0
soupsieve==2.4.1
SQLAlchemy==1.4.47
sqlalchemy-bigquery==1.5.0
SQLAlchemy-Utils==0.41.1
sqlglot==10.6.1
sqlparse==0.4.3
sse-starlette==1.6.1
sshtunnel==0.4.0
starlette==0.22.0
streaming-form-data==1.11.0
sympy==1.12
tabulate==0.9.0
tenacity==8.2.2
threadpoolctl==3.2.0
tiktoken==0.4.0
tokenizers==0.13.3
tomli==2.0.1
toolz==0.12.0
torch==2.0.1
torchvision==0.15.2
tqdm==4.65.0
transformers==4.31.0
typing_extensions==4.7.1
tzdata==2023.3
urllib3==1.26.16
uvicorn==0.20.0
vcrpy==4.3.0
vine==5.0.0
watchdog==3.0.0
wcwidth==0.2.6
wrapt==1.15.0
yarl==1.9.2

What did you do?

There is a race condition in the `SnowflakeConnection.execute_async()` functionality where, depending on how quickly results are returned, an exception will either be raised by get_query_status_throw_if_error() [1] within the get_results_from_sfqid() call, or within wait_until_ready() [2] within (for example) a fetchall() call.

[1]: https://github.com/snowflakedb/snowflake-connector-python/blob/v3.0.2/src/snowflake/connector/cursor.py#L1400
[2]: https://github.com/snowflakedb/snowflake-connector-python/blob/v3.0.2/src/snowflake/connector/cursor.py#L1374

Here's an example:

conn = snowflake.connector.connect(...)
cur = conn.cursor()
cur.execute_async(sql)

# A generic DatabaseError may be raised here...
cur.get_results_from_sfqid(cur.sfqid)
# .. or a more helpful ProgrammingError may happen here.
rows = cur.fetchall()

What did you expect to see?

I would expect the same exception to be produced from both get_results_from_sfqid() or fetchall() regardless of when the network request finishes. In my case the exception produced in wait_until_ready() is less useful since it discards the real SQL error code and message in favor of the more generic "Status of query '{}' is {}, results are unavailable".

Can you set logging to DEBUG and collect the logs?

Sorry, a bit difficult to isolate this into useful logs.

[sfc-gh-aalam]

I have added this issue in our work queue. This will be prioritized based on engineering capacity but you are welcome to submit a PR to fix this issue.

[sfc-gh-dszmolka]

hopefully this has been already fixed with one of the releases since. could you please re-test with the latest version of the connector (3.7.1) and let us know if you still can reproduce the issue ? thank you !