UNBOUND_SQL_PARAMETER with Runtime 15.4

Open
#466 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
python, sql, sqlalchemy
Domain
backend-api-design, databases

Research direction

Start by reproducing the query with Cursor.execute in databricks/sql/client.py, then trace the prepared operation and parameters into ThriftBackend.execute_command in databricks/sql/thrift_backend.py. Compare this path with the working notebook behavior and issue 288; done means the :myparam mapping is bound successfully without inlining values.

Written by the indexing model from the issue text.

Description

Hello, I have trouble using parameters with databricks-sql-python, using a Databricks cluster with Unity Catalog with Runtime 15.4.

This will fail:

from databricks import sql as databricks_sql

with databricks_sql.connect(
    server_hostname="...",
    http_path="...",
    auth_type="databricks-oauth",
    catalog="...",
    schema="...",
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT * FROM experimentation.time_series WHERE experimentation.time_series.name = :myparam",
            {"myparam": "myvalue"},
        )
        result = cursor.fetchall()
result

with the following exception: ServerOperationError: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: myparam. Please, fix args and provide a mapping of the parameter to a SQL literal.; line 1 pos 83

Stacktrace 
---------------------------------------------------------------------------
ServerOperationError                      Traceback (most recent call last)
Cell In[1], line 14
      6 with databricks_sql.connect(
      7     server_hostname="...",
      8     http_path="...",
   (...)
     11     schema="...",
     12 ) as connection:
     13     with connection.cursor() as cursor:
---> 14         cursor.execute(
     15             "SELECT * FROM experimentation.time_series WHERE experimentation.time_series.name = :myparam",
     16             {"myparam": "myvalue"},
     17         )
     18         result = cursor.fetchall()
     19 result

File ...\Lib\site-packages\databricks\sql\client.py:796, in Cursor.execute(self, operation, parameters)
    794 self._check_not_closed()
    795 self._close_and_clear_active_result_set()
--> 796 execute_response = self.thrift_backend.execute_command(
    797     operation=prepared_operation,
    798     session_handle=self.connection._session_handle,
    799     max_rows=self.arraysize,
    800     max_bytes=self.buffer_size_bytes,
    801     lz4_compression=self.connection.lz4_compression,
    802     cursor=self,
    803     use_cloud_fetch=self.connection.use_cloud_fetch,
    804     parameters=prepared_params,
    805 )
    806 self.active_result_set = ResultSet(
    807     self.connection,
    808     execute_response,
   (...)
    811     self.arraysize,
    812 )
    814 if execute_response.is_staging_operation:

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:849, in ThriftBackend.execute_command(self, operation, session_handle, max_rows, max_bytes, lz4_compression, cursor, use_cloud_fetch, parameters)
    831 req = ttypes.TExecuteStatementReq(
    832     sessionHandle=session_handle,
    833     statement=operation,
   (...)
    846     parameters=parameters,
    847 )
    848 resp = self.make_request(self._client.ExecuteStatement, req)
--> 849 return self._handle_execute_response(resp, cursor)

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:941, in ThriftBackend._handle_execute_response(self, resp, cursor)
    938 cursor.active_op_handle = resp.operationHandle
    939 self._check_direct_results_for_error(resp.directResults)
--> 941 final_operation_state = self._wait_until_command_done(
    942     resp.operationHandle,
    943     resp.directResults and resp.directResults.operationStatus,
    944 )
    946 return self._results_message_to_execute_response(resp, final_operation_state)

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:787, in ThriftBackend._wait_until_command_done(self, op_handle, initial_operation_status_resp)
    785     poll_resp = self._poll_for_status(op_handle)
    786     operation_state = poll_resp.operationState
--> 787     self._check_command_not_in_error_or_closed_state(op_handle, poll_resp)
    788 return operation_state

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:572, in ThriftBackend._check_command_not_in_error_or_closed_state(self, op_handle, get_operations_resp)
    570 if get_operations_resp.operationState == ttypes.TOperationState.ERROR_STATE:
    571     if get_operations_resp.displayMessage:
--> 572         raise ServerOperationError(
    573             get_operations_resp.displayMessage,
    574             {
    575                 "operation-id": op_handle
    576                 and self.guid_to_hex_id(op_handle.operationId.guid),
    577                 "diagnostic-info": get_operations_resp.diagnosticInfo,
    578             },
    579         )
    580     else:
    581         raise ServerOperationError(
    582             get_operations_resp.errorMessage,
    583             {
   (...)
    587             },
    588         )
Dependencies 
sqlalchemy = "^2.0.36"
jupyter = "^1.1.1"
databricks-connect = "15.4.3"
databricks-sql-connector = {version = "^3.6.0", extras = ["sqlalchemy", "alembic"]}

However, this works using a Databricks notebook:

%python
myparam = 'myvalue'

%sql
SELECT * FROM experimentation.time_series 
WHERE experimentation.time_series.name = :myparam

Please note that this issue seems related to https://github.com/databricks/databricks-sql-python/issues/288.

As a temporary workaround, I can inline the parameters inside the query as documented here for SqlAlchemy: https://docs.sqlalchemy.org/en/20/faq/sqlexpressions.html#rendering-bound-parameters-inline However, this is not recommended for security reasons.

Dominant language
Python
Stars
233
Forks
152
Avg merge
21h 5m
Merged PRs (30d)
10

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from databricks/databricks-sql-python

All issues in databricks/databricks-sql-python

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.