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