Incubator-superset: [BUG]: Sort By on table viz produces undesired results.

[mmuru]

A clear and concise description of what the bug is.
Sort By on table viz produces undesired results.

Expected results

It should able to sort the result set.

Actual results

Adding an extra column in the result set - which is confusing.

Screenshots

How to reproduce the bug

1. Go to source->tables->select bart_lines
2. Click on bart_lines to explore data set
3. Scroll down to "Sort By" (choose a column or aggregate function). Unable to select the Group by columns (see the screen shot). It forces to select aggregate function or custom sql otherwise it throws duplicate columns exception if custom sql column doesn't have alias name.
4. See error
   See attached screen shot (result window)
   

   Environment

(please complete the following information):

• superset version: 0.36.0, 0.35.2 and the master
• python version: 3.6.8
• node.js version: node -v
• npm version: npm -v

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [x] I have reproduced the issue with at least the latest released version of superset.
• [x] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

The explore request
http://localhost:8088/superset/explore/?form_data={"datasource":"13__table","viz_type":"table","url_params":{},"time_range_endpoints":["inclusive","exclusive"],"granularity_sqla":null,"time_grain_sqla":"P1D","time_range":"Last+week","groupby":["name"],"metrics":[{"expressionType":"SIMPLE","column":{"id":456,"column_name":"color","verbose_name":null,"description":null,"expression":null,"filterable":true,"groupby":true,"is_dttm":false,"type":"VARCHAR(255)","optionName":"_col_color"},"aggregate":"COUNT","sqlExpression":null,"hasCustomLabel":false,"fromFormData":true,"label":"COUNT(color)","optionName":"metric_qqaak8p1tnh_c7yss63hqv7"}],"percent_metrics":[],"timeseries_limit_metric":{"expressionType":"SIMPLE","column":{"id":455,"column_name":"name","verbose_name":null,"description":null,"expression":null,"filterable":true,"groupby":true,"is_dttm":false,"type":"VARCHAR(255)","optionName":"_col_name"},"aggregate":"COUNT_DISTINCT","sqlExpression":null,"hasCustomLabel":false,"fromFormData":false,"label":"COUNT_DISTINCT(name)","optionName":"metric_r322b0kf66c_hctq4pk133v"},"row_limit":10000,"include_time":false,"order_desc":true,"all_columns":[],"order_by_cols":[],"adhoc_filters":[],"table_timestamp_format":"%Y-%m-%d+%H:%M:%S","page_length":0,"include_search":false,"table_filter":false,"align_pn":false,"color_pn":true,"show_cell_bars":true}

the query:
SELECT name AS name, count(color) AS "COUNT(color)", **count(DISTINCT name) AS "COUNT_DISTINCT(name)"** FROM bart_lines GROUP BY name ORDER BY "COUNT_DISTINCT(name)" DESC LIMIT 10000;

To explore table viz, it should not force to select metrics aggregation instead it should provide columns to be selectable. I noticed the explore request "metrics" key in form_data includes "Sort By" value and it should not add it to the list. The "order_by" key has already the same value. Before, I submit the PR for this issue, need to understand the intend of "sort_by" for Table viz. Please, provide the context.

[mmuru]

@villebro: Please, can you review this issue and provide your feedback?

[villebro]

I believe this is a problem in the way table viz is currently implemented, and getting this functionality into the current sorting control can be difficult. The table viz is currently undergoing some refactoring, and I'll try to keep this in mind to make sure it is addressed when the time is right.

[rea725]

Expected Result addendum

• "Sort By" parameter should be basis for sort, but not basis for display.
• Chart can be configured to sort by a column that is not the last (right-most) column in the table.
• If the column being sorted upon is a metric with a d3 number format specified, the number format should be applied.

Current Behavior

• Including a column as a "Sort By" parameter will cause it to be displayed.
• The column will be displayed without its intended d3 number format.

[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.

[RageAgainstTheMachine101]

I believe this is a problem in the way table viz is currently implemented, and getting this functionality into the current sorting control can be difficult. The table viz is currently undergoing some refactoring, and I'll try to keep this in mind to make sure it is addressed when the time is right.

Hello, @villebro!
Do you have some news about this bug?
Is there some patches witch can fix this?

[ktmud]

@RageAgainstTheMachine101 I think the issue with sort by being automatically added to display has already been fixed.

[ktmud]

Pin the issue to track the progress on allowing GROUP BY columns in sorting.