Power BI DirectQuery: ClickHouse fails to parse ODBC parameter placeholders in expressions (e.g., CAST/LEFT)

[atifiu]

Problem

When connecting ClickHouse to Power BI using DirectQuery via the ClickHouse ODBC driver, Power BI generates queries like:

left(cast(`persona_id` as String), {odbc_positional_1:Nullable(Int32)})

This syntax is valid for other ODBC-compliant databases (e.g., SQL Server, PostgreSQL), but ClickHouse throws a syntax error:

Expected colon between name and type. (SYNTAX_ERROR)

Environment
ClickHouse Version: 25.5.1.2782

ODBC Driver: 1.4.1.20250523

Client: Power BI (DirectQuery mode)

Why this matters
This severely limits ClickHouse usage in DirectQuery mode for BI tools that rely on ODBC (Power BI, Tableau, etc.), and there's currently no workaround that:

Preserves NULLs

Avoids SQL generation errors

Keeps full BI tool functionality without rewriting queries in views

[slabko]

Hello @atifiu,

Could you please provide the steps to reproduce the issue? It would also be helpful to know which table or tables you're using, and what specific actions you're performing in Power BI that lead to the error. Unfortunately, I'm unable to reproduce the issue based on the current description.

[atifiu]

@slabko So this bug is related to #502.

So If I have integer/Decimal column nullable defined in clickhouse MergeTree engine table and if I try to convert it into String and leads to attached error.

[slabko]

@atifiu, got it!

I’m curious why Power BI is using the left function here. Just wondering—how are you converting to a string? Are you doing it in the Power Query Editor or somewhere else?

I have some ideas on how to fix it, but I haven’t been able to reproduce the issue to be sure it's what I think it is.