Error in new get_async_execution_result() function in 3.7.0

[hayescode]

It seems the requested async support has been added in #463 however I get errors in the new get_async_execution_result() method which seems necessary.
 

Sync (works)

from databricks import sql
import pandas as pd

with sql.connect(
    server_hostname=dbx._url,
    http_path=dbx._http_path,
    credentials_provider=dbx.credential_provider
) as connection:
    with connection.cursor() as cursor:
        cursor.execute('SELECT 1+10')
        result = cursor.fetchall()
        df = pd.DataFrame([row.asDict() for row in result])

display(df)

Async (error)

from databricks import sql
import pandas as pd

with sql.connect(
    server_hostname=dbx._url,
    http_path=dbx._http_path,
    credentials_provider=dbx.credential_provider
) as connection:
    with connection.cursor() as cursor:
        cursor.execute_async('SELECT 1+10')
        cursor.get_async_execution_result() # Error happens here
        result = cursor.fetchall()
        df = pd.DataFrame([row.asDict() for row in result])

display(df)

Async Error:

---------------------------------------------------------------------------
RequestError                              Traceback (most recent call last)
Cell In[42], [line 18](vscode-notebook-cell:?execution_count=42&line=18)
---> [18](vscode-notebook-cell:?execution_count=42&line=18) cursor.get_async_execution_result()
     [19](vscode-notebook-cell:?execution_count=42&line=19) result = cursor.fetchall()
     [21](vscode-notebook-cell:?execution_count=42&line=21) df = pd.DataFrame([row.asDict() for row in result])

File c:\Users\user/repo\.venv\Lib\site-packages\databricks\sql\client.py:893, in Cursor.get_async_execution_result(self)
    [887](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:887) def is_executing(operation_state) -> "bool":
    [888](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:888)     return not operation_state or operation_state in [
    [889](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:889)         ttypes.TOperationState.RUNNING_STATE,
    [890](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:890)         ttypes.TOperationState.PENDING_STATE,
    [891](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:891)     ]
--> [893](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:893) while is_executing(self.get_query_state()):
    [894](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:894)     # Poll after some default time
    [895](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:895)     time.sleep(self.ASYNC_DEFAULT_POLLING_INTERVAL)
    [897](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:897) operation_state = self.get_query_state()

File c:\Users\user\repo\.venv\Lib\site-packages\databricks\sql\client.py:876, in Cursor.get_query_state(self)
    [870](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:870) """
    [871](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:871) Get the state of the async executing query or basically poll the status of the query
    [872](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:872) 
    [873](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:873) :return:
    [874](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:874) """
    [875](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:875) self._check_not_closed()
--> [876](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/client.py:876) return self.thrift_backend.get_query_state(self.active_op_handle)

File c:\Users\user\repo\.venv\Lib\site-packages\databricks\sql\thrift_backend.py:850, in ThriftBackend.get_query_state(self, op_handle)
    [849](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:849) def get_query_state(self, op_handle) -> "TOperationState":
--> [850](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:850)     poll_resp = self._poll_for_status(op_handle)
    [851](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:851)     operation_state = poll_resp.operationState
    [852](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:852)     self._check_command_not_in_error_or_closed_state(op_handle, poll_resp)

File c:\Users\user\repo\.venv\Lib\site-packages\databricks\sql\thrift_backend.py:607, in ThriftBackend._poll_for_status(self, op_handle)
    [602](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:602) def _poll_for_status(self, op_handle):
    [603](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:603)     req = ttypes.TGetOperationStatusReq(
    [604](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:604)         operationHandle=op_handle,
    [605](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:605)         getProgressUpdate=False,
    [606](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:606)     )
--> [607](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:607)     return self.make_request(self._client.GetOperationStatus, req)

File c:\Users\user\repo\.venv\Lib\site-packages\databricks\sql\thrift_backend.py:483, in ThriftBackend.make_request(self, method, request, retryable)
    [481](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:481) error_info = response_or_error_info
    [482](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:482) # The error handler will either sleep or throw an exception
--> [483](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:483) self._handle_request_error(error_info, attempt, elapsed)

File c:\Users\user\repo\.venv\Lib\site-packages\databricks\sql\thrift_backend.py:313, in ThriftBackend._handle_request_error(self, error_info, attempt, elapsed)
    [308](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:308)     network_request_error = RequestError(
    [309](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:309)         user_friendly_error_message, full_error_info_context, error_info.error
    [310](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:310)     )
    [311](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:311)     logger.info(network_request_error.message_with_context())
--> [313](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:313)     raise network_request_error
    [315](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:315) logger.info(
    [316](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:316)     "Retrying request after error in {} seconds: {}".format(
    [317](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:317)         error_info.retry_delay, full_error_info_context
    [318](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:318)     )
    [319](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:319) )
    [320](file:///C:/Users/user/repo/.venv/Lib/site-packages/databricks/sql/thrift_backend.py:320) time.sleep(error_info.retry_delay)

RequestError: Error during request to server: RESOURCE_DOES_NOT_EXIST: Command 01efc932-3f38-12c2-824f-44c0ed853014 does not exist.. RESOURCE_DOES_NOT_EXIST: Command 01efc932-3f38-12c2-824f-44c0ed853014 does not exist.

[lschoen-kio]

I'm also getting this error using the same function / following the example in https://github.com/databricks/databricks-sql-python/blob/main/tests/e2e/test_driver.py#L180-L199

[lschoen-kio]

@jprakash-db any idea why this occurs, is the fix related to https://github.com/databricks/databricks-sql-python/pull/480/files?

[hayescode]

@jprakash-db I am still getting this error in 3.7.2 and 4.0.0. Can't run async due to this.

[jprakash-db]

@hayescode The issue is that for very small/quick executing queries where results can be quickly served there is no state left on the server side. For queries that run for a long time as in SELECT COUNT(*) FROM RANGE(10000 * 16) x JOIN RANGE(10000) y ON FROM_UNIXTIME(x.id * y.id, 'yyyy-MM-dd') LIKE '%not%a%date%' it seems to work properly. I will raise a PR to fix this issue. Thanks for pointing it out

[jprakash-db]

@hayescode We have raised the fix in the new version of the PySQL Connector v3.7.3

[hayescode]

@jprakash-db tested and working now. Thank you very much!

[jprakash-db]

cc @deeksha-db