Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Group by Month Date Encoding Issue with MySQL Database #10868

Open
nick-at opened this issue Sep 12, 2019 · 0 comments

Comments

@nick-at
Copy link

commented Sep 12, 2019

Describe the bug
When aggregating by month, the truncated dates are returned in the incorrect format, preventing Metabase for charting anything. This issue appears to be related to the str_to_date() MySQL function which Metabase uses in its truncation of dates.

For example, I want the count of rows by month from a table. Using the custom question UI to construct a count of rows with by month. The result I get back is a blank chart with an error:

image

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.

Note that in this case launched_at is a DATETIME type column.

Logs
This is the query that Metabase constructs with this error:

 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.

To Reproduce
You can reproduce this issue with the following query against a MySQL database.

SELECT str_to_date('2019-09-10', '%Y-%m-%d')
Which will return 2019-01-10 directly against the database but returns 0x32303139 in Metabase

Expected behavior
Dates returned as dates so that they can be graphed.

Information about your Metabase Installation:
Database: MemSQL v6 (MySQL v5.x)
Metabase Version: 0.33.2
Hosting Environment: docker
OS: MacOS 10.14.06
Browser: Chrome 76.0.3809.132

Severity
Monthly aggregation is a very common aggregation used. We may not roll out Metabase to non-technical users with this bug present (i.e. people who won't write SQL)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.