Can't run UPDATE via SqlLab (mssql) with both pymssql and pyodbc: "Statement not executed or executed statement has no resultset"

[Yuval-Moshe]

When trying to perform an UPDATE on an MSSQL db via the SqlLab, Im getting and error pointing to the same stack trace in the logs.
I've tried doing so with pymssql and pyodbc and both seem to fail on the same stacktrace.
Running the same query directly to the DB via CLI or any other tool works fine.

pymssql stack trace:

WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1517, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1503, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 122, in wraps
raise ex
File "/app/superset/views/base_api.py", line 113, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1586, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 85, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 266, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 256, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 120, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 102, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 157, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 152, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message='mssql error: Statement not executed or executed statement has no resultset', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Microsoft SQL Server', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

pyodbc stacktrace:

WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1517, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1503, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 122, in wraps
raise ex
File "/app/superset/views/base_api.py", line 113, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1586, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 85, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 266, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 256, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 120, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 102, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 157, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 152, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message='mssql error: No results. Previous SQL was not a query.', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Microsoft SQL Server', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

The query is a simple UPDATE query.
SELECT queries work fine.

From the mssql DB logs everything seems to be working fine.
Attaching a SQL profiler to the DB showed an attempt to BEGIN the transaction and then ROLLBACK

How to reproduce the bug

1. Go to SqlLab
2. Configure mssql DB with pymssql or pyodbc (driver version 17)
3. run UPDATE query

Expected results

table should be updated with values, and return no error

Actual results

receiving back an error and DB is not updated.

Screenshots

Screenshot for the pymssql error in the GUI:

Environment

(please complete the following information):
Deployed using superset helm chart

• browser type and version: chrome
• superset version: Superset 2.1.0
• python version: Python 3.8.16

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

[Yuval-Moshe]

So just as an update, this one is a bug.
Unlike other db engines, the mssql one does not include a check to see if there is even a need to return a result set before trying to fetch the data, that's why we are failing on a non select queries with the Statement not executed or executed statement has no resultset error.
In order to see if there is any result to try and fetch, we should check if the cursor description is not None.

Just for a reference we can see that other db engines like oracle or postgres do have that check before trying to fetch:
Oracle
https://github.com/apache/superset/blob/85a7d5cb3ebe833cfc2980f0846f15bb7ce1dd01/superset/db_engine_specs/postgres.py#L176C1-L179

Postgres
https://github.com/apache/superset/blob/85a7d5cb3ebe833cfc2980f0846f15bb7ce1dd01/superset/db_engine_specs/postgres.py#L176C1-L179

So the fix is simply to add that check before calling the inherited fetch_data method.

[rusackas]

Looks like this should have been closed long ago by #24999. Correct me if I'm wrong ant this needs revisiting :)