Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.

[EBoisseauSierra]

When creating a “calculated column” foo (defined, e.g. as col1 + col2), I can't reuse it in the “custom metric” bar, as the generated SQL query uses foo literally (rather than expanding into its col1 + col2 definition).

This fails as foo doesn't actually exist as a column in the table I query.

Expected results

1. I define a calculated column (here, as the difference of 2 columns of my PostgreSQL table):
   
2. I define a custom metrics that notably reuses my previously defined calculated column:
   

I want to be able to now build charts using that custom metrics:

This means, running the following query:

SELECT DATE_TRUNC('hour', timestamp) AS __timestamp,
       AVG(ABS(temperature_feelslike - temperature))::numeric(10, 2)/AVG(temperature) AS avg_temp_offset
FROM public.weather_forecast
GROUP BY DATE_TRUNC('hour', timestamp)
LIMIT 50000;

Actual results

However, trying to use the custom metrics in Explore fails:

Indeed, the following query is executed:

SELECT DATE_TRUNC('hour', timestamp) AS __timestamp,
       AVG(ABS(feelslike_offset))::numeric(10, 2)/AVG(temperature) AS avg_temp_offset
FROM public.weather_forecast
GROUP BY DATE_TRUNC('hour', timestamp)
LIMIT 50000;

(I.e. the feelslike_offset calculated column is not expanded to its definition: temperature_feelslike - temperature.)

Environment

(please complete the following information):

• superset version: 1.1.0
• python version: 3.8.9
• node.js version: node -v

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.

[villebro]

Thanks for posting! I'm not sure this is a bug, rather a feature request to expand calculated columns when referenced in other calculated columns. Something to keep in mind: The implementation could get nasty fairly quickly, as it would involve having to parse the expression, make assumptions about which elements are column references (sqlparse might do this fairly well, not sure) and match them against existing physical and calculated columns, not to mention making sure there are no circular dependencies. But definitely worth considering.

[EBoisseauSierra]

You're right:

• I naively posted that as a bug, because I assumed that the default/expected behaviour is for calculated columns to be re-usable elsewhere. However, they aren't, for example, made available in the SQL-lab — cf. the discussion on Table vs. Dataset in SIP-68 ([SIP-68] A better model for Datasets #14909). I'm fine with reframing this as a feature request.
• I understand that expansion could lead to some possibly tricky situations. I believe it would make sense to wait for post SIP-68 to develop this feature (if ever), as significant changes are likely to happen in the meantime.

Meanwhile, the already implementable workaround is to manually expand the calculated columns in a custom metric definition — in other words, to base the metric on table's native columns only.

[villebro]

Another viable solution could be to make a virtual table where the expression is precalculated. This way you get to reference it similar to a regular column without that much trouble, as most modern DBs are able to effectively optimize subqueries so the performance hit should be negligible.

[zhaoyongjie]

currently, Superset's "Calculated columns" is SQL snippet for specific database, instead of defined in BI-side expression (for instance: tableau function, power bi dax).

It is not easy to know whether "Calculated columns" is a reference type, or a primitive type.