Issue with Power BI connection filtering on VARCHARS

[austinbeal-inecta]

Issue

When connecting a Power BI report to MotherDuck via DirectQuery, we are unable to filter on TEXT/VARCHAR columns. When using a Power BI slicer to filter on a VARCHAR value, the following message is displayed

"OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression"

When filtering a VARCHAR column in the PowerQuery editor, we get the following error message:

"This step results in a query that is not supported in DirectQuery mode."

Steps to Recreate

1. Create any table in MotherDuck that contains a VARCHAR column with at least two distinct values
2. Create a new Power BI report, and use DirectQuery to bring in the created table from MotherDuck
3. Add a slicer to the report with the VARCHAR column as a field
4. Use the slicer to filter the report based on one of the options

The error is only occurring on DirectQuery mode, and I have had no issues on Import Mode. You are also able to use Power BI's "Diagnose/Run Diagnostics" to give more information on the error. It seems like the datatype is being sent from DuckDB to PowerBI as an unsupported type. I can provide any more information needed. Thank you!

[staticlibs]

@austinbeal-inecta

I would like to investigate this, just I wonder if you can guide me through the steps in Power BI to reproduce this?

What I have so far:

Table in DuckDB:

create table tab1 (col1 int, col2 text)
insert into tab1 values(41, 'foo')
insert into tab1 values(42, 'bar')

Query in PowerBI:

= Odbc.Query("dsn=user_test_3", "select * from tab1")

Applying the slicer to col2 I cannot reproduce it so far:

[austinbeal-inecta]

Hi @staticlibs, thanks for the response. Is this coming from a local DuckDB file, or from a table in MotherDuck? I have exclusively using MotherDuck as my source db

[staticlibs]

@austinbeal-inecta

This is from local DuckDB accessed with ODBC (with the duckdb_odbc.dll that includes DuckDB engine and is built from this repo). I assumed MotherDuck uses this ODBC driver in this scenario with Power BI connection, I wonder if this is the case, perhaps @guenp can correct me on this?

[staticlibs]

@austinbeal-inecta

I can reproduce it now using DuckDB Power Query Connector by MotherDuck from PowerBI instead of the generic ODBC data source. I will follow up.

[staticlibs]

@austinbeal-inecta

The problem was in the metadata about VARCHAR columns returned by DuckDB in SQLGetTypeInfo function. The maximum size of VARCHAR fields is required by ODBC spec, but such maximum size does not exist in DuckDB - text types are unlimited.

-1 value that was returned by DuckDB was interpreted by Power Query as the fieldd of this type cannot be used in parameter binding. And this was breaking the query folding, and as a result - breaking the slices in Direct Query mode.

I've filed #67 to replace -1 with MAX_INT that fixes the problem.