Unexpected Error Message at Chart Query runtime

[kevin-struckhoff]

Upon running the query for a newly built chart, an error message popped up saying 'Unexpected error' 'day_of_week'. Viewing the SQL for the query reveals a perfectly constructed SQL statement which runs just fine on my postgres db server. In the dataset for this table, the 'day_of_week' column is marked as 'is temporal' and it self-populated the time column box as expected. I also used the column 'day_of_week' as the 'sort by' column by adding it using 'custom sql'. The error is caused by the custom sql sort by setting. If I remove the sort by the query runs but the data isn't sorted correctly. Sorting by non-metric columns is a must-have.

I expected the query to run without error of course. In the attached screenshot, the query references the table calories_dow_agg which has 7 rows of data:

create table calories_dow_agg (day_of_week char(8), avg_calories smallint);

day_of_week | SUM(avg_calories)
-------------+-------------------
1 - Sun | 1593
2 - Mon | 1618
3 - Tue | 1466
4 - Wed | 1431
5 - Thu | 1730
6 - Fri | 1559
7 - Sat | 1787
(7 rows)

How to reproduce the bug

1. Create table and load data using the schema and data provided above.
2. Add a new dataset for the table.
3. Create a Bar chart and update the metric as 'SUM(avg_calories)', the series as 'day_of_week' and the sort by as 'day_of_week' in the custom sql tab.
4. Press the Run button
5. see the error

Environment

(please complete the following information):

• superset version: 1.3.0

• python version: 3.8.6

• any feature flags active: --with-threads --reload --debugger

[bkyryliuk]

Couple more clarifications:
it looks like sorting works for functions / metrics that return the number and fails for strings e.g.
number:

string:

[joyfullservice]

Thanks for the tip on converting this value to a number! 👍 I was able to get this to work for a date field in my dataset (MySQL) by wrapping it in the UNIX_TIMESTAMP() function.

[stale]

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.

[rusackas]

Looks like the solution proposed worked out, and things have gone cold, so I'll just close this. If anyone here sees something that needs to be addressed further (perhaps in code, perhaps in docs) than say the word, and we can rekindle the discussion.