Cannot sort X-Axis on a bar chart if X-axis is a var_string (MySQL)

[mason-gordon]

Version: 3.1.3

My problem: Superset is interpreting varchar columns from MySQL as var_string, which when used in a bar chart, can only be sorted alphabetically. There is no way to sort the X-axis by a metric, even when explicitly adding a metric to the sort by field.

My company is trialing Superset and while messing around with charts, I ran into a very annoying bug when trying to create bar charts. We have two data sources, one in Postgres and the other in MySQL. For any query written against the MySQL database, almost every varchar column is imported into Superset as a var_string data type. Whenever I use a column with var_string as the X-axis for a bar chart, I am not given an option to sort the X-axis. If I manually drag a metric to the "Sort By" field, the data still refuses to be sorted by that measure. It seems that the X-axis is always sorted alphabetically, which is almost never useful. The chart I am trying to create is just looking at total hours assigned to departments.

I've tried casting the columns as CHAR(50) or any other number and it doesn't change anything. Oddly enough, there is one single column that is correctly interpreted as a string, and it only has 3 characters. Attempting to cast other varchar columns to 3 characters does not work. I have no issues with data from Postgres - I can easily use any text column as the X-axis for a bar chart and sort the X-axis by my metrics.

Any ideas why this is happening?

[mason-gordon]

Found the answer -https://github.com/apache/superset/blob/master/superset/db_engine_specs/mysql.py#L27

Looks like the data converter for MySQL only converts TINYTEXT, MEDIUMTEXT, and LONGTEXT to String. Most of our string columns in MySQL are VARCHARs, so it's not being processed as a String for Superset's purposes.

[jmoraleslascondes]

Thanks you !!

[edvanr]

So what is the fix to my queries?

[mason-gordon]

So what is the fix to my queries?

There's nothing you can do with the query - you need to change how the data is logically stored on your database. Highly recommend transferring all your data to a Postgres database instead of MySQL

[mcwleong]

I've found a workaround.

As raised by @mason-gordon that this is related to how superset converts MySQL data into its generic string datatype.

However, Under "Computed Column" of the dataset, you can create a new computed column with SQL Expression = your_varchar_col_name. Set the column data type to String. Use this computed column as x-axis of your bar chart. Superset will then allow you to sort the x axis using metrics.

This workaround comes with an extra benefit on allowing you to set your_varchar_col_name as dimension, which enables cross filtering across different chart in the dashboard. You will need to set the "Stacked Style" in the Customize tab to "Stack" to yield a visually identical chart tho.

(Note: cross filtering works on selecting chart dimensions on a bar chart only, but superset will not allow the same column to be x-axis and dimension at the same time).