[Explore]SORT BY metric incorrectly re-added to SELECT clause

[john-bodley]

If the metric name/expression is the same as the SORT BY metric, the SORT BY metric is re-added to the SELECT clause (necessary for sorting) which results in selecting the expression twice which then introduces ambiguity resulting in the query engine returning an error.

Expected results

The SORT BY metric should not be re-added to the SELECT clause.

Actual results

Depending on the engine an error may be thrown, i.e., for Presto:

line 65:10: Column 'sum(total)' is ambiguous

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

1. Go to explorer.
2. Add a metric to both the METRICS section (chart dependent) and SORT BY.
3. Select a GROUP BY.
4. Click RUN.
5. Possibly see the error and notice that the metric is duplicated in the SQL, i.e.,

SELECT 
    ...
    sum("total") AS "SUM(total)",
    sum("total") AS "SUM(total)"

Environment

(please complete the following information):

• superset version: master
• python version: 3.7
• 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.

[zuzana-vej]

fyi @junlincc

[junlincc]

thanks for reporting!
I'm not able to reproduce from local.@zuzana-vej Is it happening in a specific chart and db?

[john-bodley]

@junlincc we were seeing in on the area chart with a GROUP BY using the Presto engine. Note the issue could be engine specific (some may not error out) and thus in order see the issue you'll have to check the generated SQL.

Here's a screenshot of the relevant UI controls:

[junlincc]

ah, I see. It does happen when group by is added to area chart and bar and Pivottable. @john-bodley thanks for additional info!
Doesn't happen to line chart, time-series line.
Will give a fix soon.

cc @maloun96 Victor, could you please take a look, it might related to the sort-by project we recently did? thanks!

[john-bodley]

Note this may be related to #13426 which also seems to be a regression.

[ktmud]

#13228 seems to have the same root cause.

[ktmud]

This is acutally related to #13057. Previously Area chart doesn't even have a sort by field.

I just fixed it in #13473