Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
3.1.20
-
None
-
None
-
MariaDB Connector/ODBC: 3.1.20
Power BI Desktop: 2.130.930.0 64-bit (June 2024)
Description
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC.
From the trace log this looks to be the query that the filter is executing:
select `C1` |
from
|
(
|
select `trans_code`, |
? as `C1` |
from `sales_order_test` |
where cast(`trans_code` as VARCHAR) = ? |
) as `ITBL` |
order by `ITBL`.`trans_code` |
limit 1
|
The where cast(`trans_code` as VARCHAR) part is missing the length?
To repro:
Create the following in the MariaDB:
1. CREATE TABLE sales_order_test (trans_code char(3));
2. INSERT INTO sales_order_test VALUES('ABC'),('DEF');
Go to Power BI Desktop:
1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect'
2. Select the sales_order_test db -> 'Load'
3. Select 'DirectQuery' for Connection Settings
4. Add the trans_code to 'Filters on this visual'
5. Filter on 'ABC' value and see the pop up error message.
Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this
Any help would be much appreciated.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `Pronto_Xi_Data_Whse`.`sales_order_test_new`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test_new`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test_new`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hi there, thank you for your reply. I also thought setting the SQL mode would allow the query to work as well but still getting the error in Power BI. The documentation seems to suggest with Oracle mode it's creating functions where you don't need to specify the length?
My MariaDB version: 10.5.22
My global sql mode output:
MariaDB [(none)]> select @@GLOBAL.sql_mode; |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| @@GLOBAL.sql_mode | |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,SIMULTANEOUS_ASSIGNMENT |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The only thing I haven't done is restart the MariaDB service.
Changing the trans_code type to varchar will do the job but this is just a test table and in reality there are numerous production tables with char columns I need to filter on with Power BI.
Yeah, your table did look like a test table and yes, cast as varchar still requires length for it. Not sure whose bug it is more - my or powerbi's, but I will look into it. There were reports about similar issues, but iirc not about char type, but some text blobs and people workarounded them by changing type. Thanks for detailed instructions - it will definitely help. unlikely that will be an easy fix, though.
| Description |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:java} select `C1`\ d\ afrom \ d\ a(\ d\ a select `trans_code`,\ d\ a ? as `C1`\ d\ a from `sales_order_test`\ d\ a where cast(`trans_code` as VARCHAR) = ?\ d\ a) as `ITBL`\ d\ aorder by `ITBL`.`trans_code`\ d\ alimit 1\ 0 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Hey there, I am not sure this is an issue from Power BI or the ODBC, please let me know if this is not where the issue stems from.
In Power BI Desktop I am getting the error in the attached image when I try to filter the values on a char type column using MariaDB Connector/ODBC. From the trace log this looks to be the query that the filter is executing: {code:sql} select `C1` from ( select `trans_code`, ? as `C1` from `sales_order_test` where cast(`trans_code` as VARCHAR) = ? ) as `ITBL` order by `ITBL`.`trans_code` limit 1 {code} The where cast(`trans_code` as VARCHAR) part is missing the length? To repro: Create the following in the MariaDB: 1. CREATE TABLE sales_order_test (trans_code char(3)); 2. INSERT INTO sales_order_test VALUES('ABC'),('DEF'); Go to Power BI Desktop: 1. Go to Power BI Desktop -> 'Get data' -> 'MariaDB' -> 'Connect' 2. Select the sales_order_test db -> 'Load' 3. Select 'DirectQuery' for Connection Settings 4. Add the trans_code to 'Filters on this visual' 5. Filter on 'ABC' value and see the pop up error message. Note: I have not been able to try this on the v3.2.2 MariaDB Connector, let me know if the newer version has fixed this Any help would be much appreciated. |
Thank you for your report. It's hard to say atm - can be both. For beginning, cast as varchar works in oracle sql mode(from here
Maybe your problem can be workarounded by changing sql mode of your server to oracle. Or maybe just changing trans_code type to varchar will do the job.
I am not sure why PowerBI generates such query. Possibly the driver can affect the way PowerBI constructs query in some way. I will need to look into that