[ODBC-358] PowerBI mixed mode SQL with cast() in WHERE clause Created: 2022-04-15  Updated: 2024-01-13

Status: Open
Project: MariaDB Connector/ODBC
Component/s: None
Affects Version/s: 3.1.15
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Adi Assignee: Lawrin Novitsky
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File Tables definition.sql     PNG File image-2022-04-15-04-49-47-412.png     PNG File image-2022-04-15-04-52-41-011.png    
Issue Links:
Relates
relates to ODBC-409 PowerBI measure with text filter rais... Open

 Description   

Hello,

I have a PowerBI model composed of 2 tables connected to MariaDB:

  • DIM_DATE - Import mode
  • FACT_TRAN - DirectQuery mode

The 2 tables have a relation using Date_SK, which is of int datatype in both tables.

I've added a slicer with a date range, using [Date] column (datatype is date) from DIM_DATE table.

When I select a single date in the slicer, the query submitted to the fact table does a CAST() in the WHERE clause which prevents usage of the index in the fact table.

select count(1) as `C1`
from
(
select `TRAN_ID`
from `FACT_TRAN`
where cast(`TRAN_DATE_SK` as INTEGER) = 20220303
) as `IT

However, when I select a date range greater than 1 date the CAST() isn't applied anymore.

select count(1) as `C1`
from
(
select `TRAN_ID`
from `FACT_TRAN`
where `TRAN_DATE_SK` in (20220303, 20220304, 20220305, 20220306, 20220307, 20220308)
) as `ITBL`

You can easily reproduce this case.
I'm using latest version of Power BI Desktop and MariaDB Connector for PowerBI.

Please can you fix this bug so that when selecting a single date in the slicer there will be no cast in the WHERE clause anymore, which prevents index use,

Thanks.

 Comments   
Comment by Lawrin Novitsky [ 2022-04-15 ]

Thank you for your report. Are you sure, that the problem is in the connector, and not in the Power BI? Since it works fine on single day, it feels like metadata provided by the connector is correct, but on multiple dates the query, generated by Power BI is wrong. The connector itself doesn't do that - we only provide information on tables, fields, types. And that information used by Power BI to create queries. It's still can be some wrong metadata from our side, but atm I can't think of anything, since, again, with single date it works fine.

Comment by Adi [ 2022-04-15 ]

@Lawrin, it's with single date when the SQL goes wrong (with a cast) and multiple days when it's Ok (without the cast).

I've tested the same case with SQL Server and the cast is not done, which highlights more that it's a connector issue.

Comment by Lawrin Novitsky [ 2022-04-18 ]

Not that I am completely convinced, but we need to continue anyway.
Could you please provide `FACT_TRAN` table structure, and in particular I am interested in the exact type of the TRAN_DATE_SK field.
Also ODBC trace for creating of slicer and running it with both date ranges could help.

Comment by Adi [ 2022-04-27 ]

I've attached the tables definition Tables definition.sql

Comment by Adi [ 2022-05-03 ]

Hello, please let me know if there are any more updates?

Comment by Adi [ 2022-05-12 ]

@Lawrin, please can you provide an update.

Comment by Lawrin Novitsky [ 2022-05-13 ]

The ticket is not closed, that means that there is no solution so far. I asked also for ODBC trace. Or, maybe, alternatively some more detailed instructions how to repeat the issue or point me to some documents that can help - I am not very familiar with PowerBI.

Generated at Thu Feb 08 03:28:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.