Creation of a dimension using SQL Server data warehouse fails with 'no field of name'

[sfirke]

In Superset 2.1.0 when I try to create a dimension variable based on a column, it fails with "no field of name". Logs and screenshot below. Likely related to #23213.

How to reproduce the bug

1. With MS SQL Server backend (and likely Azure Synapse), create dimension variable using Custom SQL.
2. Press save, see error.

Expected results

This should succeed. I am confident this worked in 2.0.1, I just noticed the chart broken on the dashboard under 2.1.0. But I have not fired up an old 2.0.1 instance to confirm 100% that it's a regression.

Actual results

SQL Query sent to my database:
SELECT TOP 1 DATEPART(year, date_completed) FROM dbo.[myTable]

This is valid SQL Server and returns a variable with (No column name), which is causing the error.

Superset logs

no field of name
Traceback (most recent call last):
  File "/app/superset/result_set.py", line 139, in __init__
    pa_data.append(pa.array(array[column].tolist()))
ValueError: no field of name

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/app/superset/connectors/sqla/utils.py", line 163, in get_columns_description
    result_set = SupersetResultSet(result, cursor.description, db_engine_spec)
  File "/app/superset/result_set.py", line 149, in __init__
    stringified_arr = stringify_values(array[column])
ValueError: no field of name

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/superset/connectors/sqla/models.py", line 1041, in adhoc_column_to_sqla
    col_desc = get_columns_description(self.database, sql)
  File "/app/superset/connectors/sqla/utils.py", line 166, in get_columns_description
    raise SupersetGenericDBErrorException(message=str(ex)) from ex
superset.exceptions.SupersetGenericDBErrorException: no field of name

The above exception was the direct cause of the following exception:

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 87, 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/utils/log.py", line 266, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/charts/data/api.py", line 255, in data
    return self._get_data_response(
  File "/app/superset/charts/data/api.py", line 411, in _get_data_response
    result = command.run(force_cached=force_cached)
  File "/app/superset/charts/data/commands/get_data_command.py", line 45, in run
    payload = self._query_context.get_payload(
  File "/app/superset/common/query_context.py", line 100, in get_payload
    return self._processor.get_payload(cache_query_context, force_cached)
  File "/app/superset/common/query_context_processor.py", line 477, in get_payload
    query_results = [
  File "/app/superset/common/query_context_processor.py", line 478, in <listcomp>
    get_query_results(
  File "/app/superset/common/query_actions.py", line 226, in get_query_results
    return result_func(query_context, query_obj, force_cached)
  File "/app/superset/common/query_actions.py", line 103, in _get_full
    payload = query_context.get_df_payload(query_obj, force_cached=force_cached)
  File "/app/superset/common/query_context.py", line 121, in get_df_payload
    return self._processor.get_df_payload(query_obj, force_cached)
  File "/app/superset/common/query_context_processor.py", line 136, in get_df_payload
    query_result = self.get_query_result(query_obj)
  File "/app/superset/common/query_context_processor.py", line 214, in get_query_result
    result = query_context.datasource.query(query_object.to_dict())
  File "/app/superset/connectors/sqla/models.py", line 1903, in query
    query_str_ext = self.get_query_str_extended(query_obj)
  File "/app/superset/connectors/sqla/models.py", line 884, in get_query_str_extended
    sqlaq = self.get_sqla_query(**query_obj)
  File "/app/superset/connectors/sqla/models.py", line 1350, in get_sqla_query
    outer = self.adhoc_column_to_sqla(
  File "/app/superset/connectors/sqla/models.py", line 1044, in adhoc_column_to_sqla
    raise ColumnNotFoundException(message=str(ex)) from ex
superset.exceptions.ColumnNotFoundException: no field of name
2023-04-27 14:51:52,825:ERROR:superset.views.base:no field of name
Traceback (most recent call last):
  File "/app/superset/result_set.py", line 139, in __init__
    pa_data.append(pa.array(array[column].tolist()))
ValueError: no field of name

Environment

(please complete the following information):

• superset version: 2.1.0

[sfirke]

A simple way to reproduce this is to run this query in SQL Lab:

SELECT COUNT(*)
FROM my_table

[SeanStock-THG]

I can also confirm we have seen the same thing when upgrading from 2.0.1 to 2.1.0. A workaround we have is to use calculated columns at the dataset level until patched.

[JohnDietrich-Pepper]

Would be nice to see this fixed.

[satyndragautam]

I hope this issue will be fixed soon

[rusackas]

Tempted to close this as stale... but is anyone able to confirm if this is still an issue in 3.x?

[sfirke]

I can confirm it's still a bug in 4.0.0rc1.

[sfirke]

This persists in 4.0.2. It also just hit me when a new alert failed to fire because I had set the condition SELECT COUNT(*) FROM mytable instead of SELECT COUNT(*) AS x FROM mytable -- if this got fixed it would address this case too.