Extra % added in SQL Lab during visualisation

[vschoener]

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

• I have checked the superset logs for python stacktraces and included it here as text if 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

Superset version

{"GIT_SHA": "", "version": "0.18.5"}

Expected results

A list grouped by time (hourly) and type (24 columns, with 2 type, breakdown to display them

Actual results

It works well when I edit my query, then when I visualize, the result is not right. Only 2 column instead of 24

Steps to reproduce

Here is my SQL query:

SELECT
count(*) as 'total_activities', type, hour(datetime) as hourly
FROM activity
GROUP BY type, DATE_FORMAT(datetime, "%%H:00:00") ORDER BY datetime` ASC;

---

The visualised query looks like :

SELECT hourly AS hourly,
type AS type,
sum(total_activities) AS sum__total_activities
FROM
(SELECT count(*) as 'total_activities',
type,
hour(datetime) as hourly
FROM activity
GROUP BY type,
DATE_FORMAT(datetime, "%%%%H:00:00")
ORDER BY datetime ASC) AS expr_qry
GROUP BY hourly,
type
ORDER BY sum__total_activities DESC
LIMIT 50000

As you see, it adds 2 extra "%%" in the DATE_FORMAT, so the result for hourly will return only "0" instead of the 12 or 24 hours (depending of the format needed)

I copy paste this query and delete the 2 extra '%', it seems to work as expected.

Is this normal ? Or did I miss something ? Maybe better way to display ma data by hour for a 24 hour grouped with type of activity ?

Thanks

Edit: I tried to trick it like having online one '%' as "%H" mais I got the error : {"status": "failed", "query_id": 46, "error_essage": "unsupported format character 'H' (0x48) at index 250"}

[vschoener]

I found a way to get the right format.

• I let the double %% to have the right result in the preview.
• Then after validation I edit the datasource to remove the extra '%'.

Now the query looks as expected, but I need to check if its the right now.

[mistercrunch]

Which database engine are you querying? I have a fix out on master for Presto. We can replicate the fix for other DB. It has to be dealt with on a per-engine basis as DBAPI implementation vary a bit on how and whether to escape %

[mistercrunch]

#3186

[vschoener]

Hi @mistercrunch, it's a Mysql from AWS RDS. I looked to your fix, indeed, that the way ;)

[mistercrunch]

Somehow with the mysql driver I use (mysqlclient) I don't need to double the %

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 264 days. Feel free to comment and request for this issue to be reopened.