Cohorts Broken with MySQL 8

[chronicaust]

Good evening,

In the line linked below, there is an issue with the MySQL syntax which causes cohort analysis to not work. https://github.com/ankane/blazer/blob/3cad1b13bb28f9a91239977cd322c71a0ae502f7/lib/blazer/adapters/sql_adapter.rb#L149

In MySQL there is no functionality for CAST(X AS INTEGER). There is however functionality for CAST(X AS UNSIGNED)

To be clear, the following snippet: CAST(CEIL(TIMESTAMPDIFF(SECOND, cohorts.cohort_time, query.conversion_time) / ?) AS INTEGER) Should be changed to the following one to resolve the issue: CAST(CEIL(TIMESTAMPDIFF(SECOND, cohorts.cohort_time, query.conversion_time) / ?) AS UNSIGNED)

To be clear, it is my opinion that the word INTEGER should be changed to UNSIGNED

Thank you

[chronicaust]

Also, based on my research WITH statements don't work in MySQL 5. You may want to update the docs to inform people that cohorts require MySQL 8.

[ankane]

Hey @chronicaust, thanks for the report and fix! It looks like INTEGER is specific to MariaDB. I've updated the docs as well.