Automatically rename _col columns, to fix user flow SQLLab -> Explore resulting in error

[zuzana-vej]

Is your feature request related to a problem? Please describe.
Currently when writing a query with an aggregation in SQL Lab (e.g. select avg(my_column) from my_table) the unnamed column is renamed as _col0, When clicking Explore to go to Chart Explore, the Presto query will fail with  presto error: line 3:8: Column '_col0' cannot be resolved.

This causes the user to either think it's a BUG in Superset, or user realizes they need to go back to Explore, rename the column (select avg(my_column) AS avg_my_column from my_table , wait for the query to run again, and click "Explore again".

So the current user flow is:
SQL Lab --> Explore --> User sees error --> back to SQL Lab --> Explore & it hopefully works

Instead of:
SQL Lab --> Explore

Describe the solution you'd like
There are two options really:
(1) Renaming the column automatically, so that Explore doesn't result in error. This isn't necessary when displaying results in SQL Lab, only when user clicks on "Explore".

In an ideal case, the columns return as _col0 would be renamed based on some logic - for example concatenation of the aggregate function and the column (so count(my_column) can be named count_my_column, avg(load_time) could be avg_load_time.

(2) Better Error so that user can fix it before going to Explore:
An alternative solution is to display an error message when user is trying to click explore and not allow user to go to Explore, forcing the user to update the query to add the column name. 

Describe alternatives you've considered
Current state isn't acceptable (looks like a bug to end user). Two options described in above section.

Additional context
Step 1:

Step 2 --> Error:

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #enhancement to this issue, with a confidence of 0.70. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[zuzana-vej]

Here screenshots to different existing flow (similar use case) when user tries to click explore, after joining and having multiple timestamp type of columns in the result, and receives error warning. Option (2) from solution options would align with this experience - providing warning and not letting user proceed to Explore.

1. Situation: User joins two tables and has two timestamp type columns in result
   

2. Onclick(Explore) user receives this notification.
   

[junlincc]

🏷 error

[willbarrett]

@mistercrunch what do you think of this?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.