Custom questions: date issue when grouped by month

Hey! I’ve picked up an issue aggregating by month in custom questions.

Example
I want the count of rows by month from a table. Using the custom question UI to construct a count of rows with the date field ‘launched_at’ by month. The result I get back is a blank chart with an error:

The error reads:

Data includes missing dimension values
We encountered an invalid date: “0x32303131”
“Launched At” is an unaggregated field: if it has more than one value at a point on the x-axis, the values will be summed.

This is the query that Metabase constructs:

 SELECT str_to_date(concat(date_format(`fact_surveys`.`launched_at`, '%Y-%m'), '-01'), '%Y-%m-%d') AS `launched_at`, count(*) AS `count`
 FROM `fact_surveys`
    GROUP BY str_to_date(concat(date_format(`fact_surveys`.`launched_at`, '%Y-%m'), '-01'), '%Y-%m-%d')
    ORDER BY str_to_date(concat(date_format(`fact_surveys`.`launched_at`, '%Y-%m'), '-01'), '%Y-%m-%d') ASC

When I run this query directly against the database (in a SQL workbench) I get the expected results back, with a date in the first column and count in the second. However when I run the same query as a ‘native query’ in Metabase against the same database, the dates come back in what looks like some kind of encoding.

This issue only exists when aggregating by month - aggregating by day, for example, works as expected.

You can replicate this issue with the following query;

SELECT str_to_date(concat(date_format('2019-09-10', '%Y-%m'), '-01'), '%Y-%m-%d')

Which should return 2019-01-01 but returns 0x32303139

Any help on this issue would be great!

Database: MemSQL (MySQL v5.x)
Metabase Version 0.33.2

Hi @nick_at
I’m migrating test-machine, so I don’t have a MySQL server to test with, but you’ve created an excellent report, so it would great if you created an issue for this (so it doesn’t get lost and more eyes will be seeing it)
https://github.com/metabase/metabase/issues/new/choose

Thanks. Will submit on the repo!

Just for reference: