search

mariadb-corporation / mariadb-powerbi

Power BI DirectQuery Connector
https://mariadb.com
3 stars 1 forks source link

Cannot change data type in Power Query #4

Open Akos90 opened 3 years ago

Akos90 commented 3 years ago

Changing data type throws error. Column is calculated and only contains 0 and 1. image

Is there a way to disable query folding?

ilyagithub1 commented 3 years ago

@Akos90 Thank you for reporting the issue. It's been fixed.

Tip: to resolve the issue before the next release of MariaDB Power BI Connector and without rebuilding the Connector manually:

Replace Int64 with Int32 in a statement generated by Power Query Editor: Table.TransformColumnTypes(myTable,{{"Some Column", Int64.Type}})` yields driver error 1064 "check for the right syntax". Table.TransformColumnTypes(myTable,{{"Some Column", Int32.Type}})` works without the error.

Insight into the driver error 1064 "check for the right syntax"

When data type is changed to Whole Number, Power Query Editor uses M data type Int64 and generates a line of code similar to this: = Table.TransformColumnTypes(myTable,{{"Some Column", Int64.Type}})

Power BI Mashup Engine translates M data type Int64 to SQL data type BIGINT by default. Data type transformation in SQL is done with CAST or CONVERT.

In your screenshot the error message says check... for the right syntax to use near 'BIGINT) as C2... SQL snippet that the Mashup Engine generated and that resulted in the driver error (code 1064) should look like this: CAST (`your_column` AS BIGINT) as `C2` where use of BIGINT with CAST produces the error 1064.

MariaDB implementation of CAST and CONVERT functions supports a limited set of data types and BIGINT is not supported.

This is a long-standing issue with CAST and CONVERT in MariaDB: MDEV-17686 BIGINT type doesn't work within CAST

The fix in MariaDB Power BI Connector excludes BIGINT from the list of of the supported SQL data types. Effectively, Power BI Mashup Engine assumes BIGINT is not supported by the database driver and does not use the data type when transforming M statements to SQL statements. This workaround does not affect consuming BIGINT data from MariaDB, because in a typical SELECT statement data type is not explicitly specified. The fix has been tested with MariaDB 10.5.8 in strict mode by fetching data from BIGINT and BIGINT UNSIGNED columns. The was no loss of data observed, so the fix is safe to use with actual BIGINT data.

gcarneiro commented 6 months ago

I am having same issue here, but i am trying to alter type from int to text.

image

Tried to update MariadbODBC Driver but it is in the newer version. Any ideas?

ilyagithub1 commented 6 months ago

@gcarneiro Thank you for reporting the issue. It is being looked into.

gcarneiro commented 6 months ago

I am using MariaDB 11.1.2 on ubuntu. Tried to update mariadb jdbc connector but no success...