SELECT u, i, CASE WHEN SUM(amount) IS NOT NULL THEN SUM(amount) ELSE 0 END FROM MySQL.[path_to_table] GROUP BY u, i
And I get the error “The JDBC storage plugin failed while trying to setup the SQL query.” Looking at the query log I see that the MySQL plugin attempted to carry out the following query:
SELECT u, i, CASE WHEN SUM(amount) IS NOT NULL THEN SUM(amount) ELSE CAST(0 AS BIGINT) END
FROM [path_to_table]
GROUP BY u, i
which is invalid as a MySQL query. The correct syntax would be
SELECT u, i, CASE WHEN SUM(amount) IS NOT NULL THEN SUM(amount) ELSE CAST(0 AS SIGNED INTEGER) END
FROM [path_to_table]
GROUP BY u, i
Obviously this is a bug that will be fixed in due course: Can you suggest anything I could do as a temporary workaround?
It looks like our translation to MySql dialect for BIGINT type is incorrect. For now, as a work around, you can do the following. I believe we handle Doubles better. At least on my local reproduction, this fixed it but if it doesn’t please feel free to let us know.
SELECT u, i, CASE WHEN SUM(amount) IS NOT NULL THEN CAST(SUM(amount) AS DOUBLE) ELSE 0 END FROM MySQL.[path_to_table] GROUP BY u, i
I’m using Dremio 1.0.8 and face the same problem… but, not sure how to fix it as the query is translated by Dremio.
Is this issue fixed in latest releases? Original Query submitted to Dremio
SELECT
a.software_id, software_last_lambda, software_join_date,software_role_key
FROM “ds-redshift”.“db1”.“schema1”.“dim_software” a
JOIN “ds-mysql”.schema1.softwares b ON a.software_id = b.software_id
limit 10
The original issue of casting to BIGINT when querying MySQL was fixed to instead cast to SIGNED INTEGER. If you are able to, please upgrade to the latest version. Please let us know if you are unable to upgrade or find you are still having problems in the latest version.