KeyError when using uppercase Metrics with Athena (in Superset 1.0.1)

[mrshu]

After updating to Superset 1.0.1 we started running into an issue with the Athena adapter, where Metrics with uppercase name would stop working with the following error:

Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc. Textual SQL expression 'New_metric' should be explicitly declared as text('New_metric')

Strangely enough, we found out that this issue does not persist when the name of metric gets lowercased. It therefore seems like the following issues/PRs may be related:

• KeyError when using Redshift #5308
• Force lowercase column names for Snowflake and Oracle #4994

Expected results

The computation of the defined metric.

Actual results

An error:

Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc. Textual SQL expression 'New_metric' should be explicitly declared as text('New_metric')

The stacktrace is as follows:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 835, in visit_textual_label_reference
    col = with_cols[element.element]
KeyError: 'New_metric'

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/superset/viz.py", line 540, in get_df_payload
    df = self.get_df(query_obj)
  File "/usr/local/lib/python3.8/site-packages/superset/viz.py", line 270, in get_df
    self.results = self.datasource.query(query_obj)
  File "/usr/local/lib/python3.8/site-packages/superset/connectors/sqla/models.py", line 1295, in query
    query_str_ext = self.get_query_str_extended(query_obj)
  File "/usr/local/lib/python3.8/site-packages/superset/connectors/sqla/models.py", line 767, in get_query_str_extended
    sql = self.database.compile_sqla_query(sqlaq.sqla_query)
  File "/usr/local/lib/python3.8/site-packages/superset/models/core.py", line 409, in compile_sqla_query
    sql = str(qry.compile(engine, compile_kwargs={"literal_binds": True}))
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 481, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 487, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 592, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 322, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2201, in visit_select
    text = self._compose_select_body(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2314, in _compose_select_body
    text += self.order_by_clause(select, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2373, in order_by_clause
    order_by = select._order_by_clause._compiler_dispatch(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1040, in visit_clauselist
    text = sep.join(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1040, in <genexpr>
    text = sep.join(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1043, in <genexpr>
    c._compiler_dispatch(self, **kw) for c in clauselist.clauses
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1277, in visit_unary
    return self._generate_generic_unary_modifier(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1424, in _generate_generic_unary_modifier
    return unary.element._compiler_dispatch(self, **kw) + opstring
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 837, in visit_textual_label_reference
    elements._no_text_coercion(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 4757, in _no_text_coercion
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
sqlalchemy.exc.CompileError: Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc. Textual SQL expression 'New_metric' should be explicitly declared as text('New_metric')

Screenshots

Here is what the chart rendering looks like:

And this is what one can find in the Dataset editor (Metrics tab):

How to reproduce the bug

1. Go to an Athena-based Dataset in Superset (1.0.1
2. Add a new metric called New_metric
3. Create a (say) Bar Chart and choose New_metric as the metric to be used in it
4. See the error after clicking "Run query"

Environment

• superset version: 1.0.1
• python version: 3.8

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.

[junlincc]

@mrshu thanks for reporting! we may not get to any data-specific issues soon. If you have a solution in-place, feel free to ope n a PR. We will make sure it get reviewed. 🙏

[mrshu]

Thank you @junlincc!

Sadly, the only solution that worked for us was manually renaming the Metric in question.

I find this strange, as the _mutate_label function should take care of this (i.e. it basically does the same thing):

superset/superset/db_engine_specs/athena.py

Lines 58 to 66 in c91c455

	@staticmethod
	def _mutate_label(label: str) -> str:
	"""
	Athena only supports lowercase column names and aliases.
	:param label: Expected expression label
	:return: Conditionally mutated label
	"""
	return label.lower()

If you could provide any pointers, I would be happy to put a PR together!

[didva]

Hey guys.
Faced with the same issue and after some investigation found that it seems to be resolved in master branch as part of #13059
@mrshu please give it a try.

[brucearctor]

@mrshu -- can you confirm whether this issue can be closed ( based on @didva comment, seems possible that this needn't be an open issue )?

[rusackas]

Sounds like this can be closed. Thanks all!