VARCHAR and LONGVARCHAR date types syntax error

[dversoza]

Dear ones, Hello!

I am having problems of sorts with importing data. I checked in Issue #4 for the Int32/Int64 type problem and adjusted those settings, which no longer generated any impact.

However, I am having problems with the VARCHAR and LONGVARCHAR types, as we can see in these images:

If you need more information, let me know!

Edit-1:
I removed the failing columns and the import worked again.

[ilyagithub1]

@dversoza Thank you for reporting the issue. It has been fixed.

Root Cause

The root cause for the issue you experienced was the misreported COLUMN_SIZE for the VARCHAR data type.

By default, MariaDB ODBC driver reports COLUMN_SIZE = 255 which is incorrect:

SQLGetTypeInfo VARCHAR: [ TYPE_NAME = "VARCHAR", DATA_TYPE = -9, COLUMN_SIZE = 255, ...]

The correct size is 65532.
VARCHAR(M) data type in MariaDB has the range of M is 0 to 65,532.

In effect, when you have a VARCHAR(N) column in your data source with N > 255, Power BI Mashup Engine tries to convert this column to LONG VARCHAR using CAST. You can see in your diagnostics logs and sometimes in the error message displayed in the UI a SQL snippet like this:

cast(yourcolumn as LONG VARCHAR))

The conversion above fails, because using VARCHAR with CAST in MariaDB requires the column size to be provided.

The correct syntax in MariaDB is:

CAST(expr AS VARCHAR(N))

This is a known issue:
https://jira.mariadb.org/browse/MDEV-11283

So, the error message you saw was misleading in the sense that it did not really explain the root cause of the error.

The Fix

The fix addresses the root cause of the issue by updating the COLUMN_SIZE value for the VARCHAR data type. See e317a99 and 0ce6c1a

The fix does not address the issue about the combination of CAST and [LONG ]VARCHAR, because it is not possible to force Power BI Mashup Engine to use the size parameter when generating CAST(expr as [LONG ]VARCHAR) statement. The only possible solution for eliminating the CAST as VARCHAR issue would be to exclude the LONG VARCHAR data type from the list of supported data types in SQLGetTypeInfo (similar to BIGINT as in #4). But that does not really solve the problem and imposes questionable constraints onto what the Mashup Engine can do.

Links

The same issue was reported for MariaDB ODBC driver, but this is a Connector-related issue that's been fixed:
https://jira.mariadb.org/browse/ODBC-312

MariaDB issues related to CAST and VARCHAR:
https://jira.mariadb.org/browse/MDEV-11283
https://jira.mariadb.org/browse/MCOL-767

[tasso85]

This is marked as closed, however I am facing this same issue with MariaDB 10.6, MariaDB ODBC Connector 3.1.17 and the specific MariaDB PowerBI Connector 1.1.3.

It seems to happen when I try to concatenate numeric IDs to create a single ID columns, since PowerBI does not support joining on multiple columns.

How may I solve this issue, given that the specific ODBC and PowerBI connectors do not seem to fix it?

What other data might I provide you?

[ilyagithub1]

@tasso85 It sounds like in your case the issue is introduced by the data transformations you make.
To eliminate the VARCHAR-related issue, just avoid the transformations that result in the issue and achieve the goal in a different way. Here's a couple of options.

Option A

Power BI does support joining on multiple columns.

When using the Merge dialog, Ctrl+click on additional columns to use more than one column for the join.

Here's a screenshot from the article "Merge queries overview":

Option B

You can always write your custom SQL statement with a JOIN clause directly in the MariaDB data source dialog.

[tasso85]

As for your option A, you are totally right, I actually didn't know that was a possibility; however, I would still need to have the merged composite key in one column in order to define the relationship in the data model, as far as I can see that does not accept matching on multiple columns.

Then, for your option B, maybe I have a different version of PowerBI Desktop than you have, since I do not see the option to write my own query, not with the default MariaDB connector nor with the custom one:

[ilyagithub1]

@tasso85 Regarding Option B, it seems the custom SQL feature did not make it into the March, 2023 release of Power BI. It should appear in a later release.

For now, please use the Beta version that has been described in and attached to the issue #22.

Here is the direct link to the post with the download link and how to use instructions:
#22 (comment)

---

Thank you for using MariaDB Power BI Connector!

[tasso85]

@ilyagithub1 thanks for the link, I'll try that; I was in fact using a later version (1.1.3, while the one you linked is 1.0.7) but it does not appear to include that feature.

I'll give it a try and see how it works.

[seaotterseeks]

Ever find a fix for this?