PowerBI Desktop - Direct Query - Slicer - Search is not working - DB::Exception: Syntax error

[areshayevcaci]

Describe the bug
PowerBI Desktop, Direct Query, Slicer - try to Search "Test" - DB::Exception: Syntax error

To Reproduce
Steps to reproduce the behavior:
1.

create table test_pbi_slicer_search (code String not null) Engine = MergeTree() order by (code);
insert into test_pbi_slicer_search values ('test_01'),('test_02'),('test_03'),('test_04'),('test_05');

2. PowerBI Desktop, standard Slicer, try to search "test"

3. ERROR: see screenshot

4. Exception in Logs

(version 24.1.2.5 (official build))
2024.02.16 11:42:50.431448 [ 1173 ]  
<Error> executeQuery: Code: 62. DB::Exception: Syntax error: failed at position 101 ('locate') (line 6, col 16): locate({odbc_positional_1:LowCardinality(String)}
,(case^M
        when `code` is not null^M
        then `code`^M
        else {odbc_positional_2:LowCardinality(S. Expected colon between name and type. (SYNTAX_ERROR) 
(version 24.1.2.5 (official build))  
(in query: select `code` from ( select `code` from `dev`.`test_pbi_slicer_search` where { fn locate({odbc_positional_1:LowCardinality(String)}, (case when `code` is not null then `code` else {odbc_positional_2:LowCardinality(String)} end), {odbc_positional_3:Nullable(Int32)}) } >= {odbc_positional_4:Nullable(Int32)} ) as `ITBL` group by `code` order by `code` limit 101), Stack trace (when copying this message, always include the lines below):

5. QUERY from logs

select `code` from ( 
select `code` from `dev`.`test_pbi_slicer_search` 
	where { fn locate(
					{odbc_positional_1:LowCardinality(String)}
				   ,(case when `code` is not null then `code` else {odbc_positional_2:LowCardinality(String)} end)
				   ,{odbc_positional_3:Nullable(Int32)}) } 
		>= {odbc_positional_4:Nullable(Int32)} 
	) as `ITBL` group by `code` order by `code` limit 101

Screenshots

Configuration

Environment

• PowerBI version: MS PowerBI Desktop. Version: 2.124.2028.0 64-bit (December 2023)
• power-bi-clickhouse version: v0.1.2-beta
• ClickHouse ODBC driver version: 1.2.1.20220905
• ClickHouse: 24.1.2.5
• Operating system: Microsoft Windows [Version 10.0.19045.3930]; Ubuntu 20.04.5 LTS

[BentsiLeviav]

Hi @areshayevcaci.

Thank you for your feedback.
I investigated this issue and managed to reproduce it in my environment.

As you mentioned, Power BI generates the following query when performing a search using the slicer (in my case on a district column):

select `district`
from 
(
    select `district`
    from `default`.`uk_price_paid`
    where { fn locate({odbc_positional_1:LowCardinality(String)}, (case
        when `district` is not null
        then `district`
        else {odbc_positional_2:LowCardinality(String)}
    end), {odbc_positional_3:Nullable(Int32)}) } >= {odbc_positional_4:Nullable(Int32)}
) as `ITBL`
group by `district`
order by `district`
limit 101

This approach uses query parameters, but according to ClickHouse's query_log table, a proper SET param_...=XX was never executed.

I tried to locate where this is supposed to happen in the driver's code, and it seems like the driver never tries to set the parameters:

• As you can read here, the parameters are being injected to the query
• But here, the query is being sent to ClickHouse, without taking care of the binding and setting themselves.

Even if such a query was executed, the ODBC driver does not support multi-statements/session stickiness (for now).

Therefore, this is an issue related to the ODBC driver itself.

CC: @mshustov @slvrtrn

[BentsiLeviav]

Found this Issue (ClickHouse/clickhouse-odbc#214) in the ODBC repo, therefore closing this one.

[mshustov]

a proper SET param_...=XX was never executed.

yeah, the ODBC driver must attach them to a query as an url parameter)param_$NAME=$VALUE. It's likely a bug in ODBC. Let me see how to address this

[areshayevcaci]

yeah, the ODBC driver must attach them to a query as an url parameter)param_$NAME=$VALUE. It's likely a bug in ODBC. Let me see how to address this

Hi
tnx for confirmation, I raised the issue in ClickHouse ODBC: ClickHouse/clickhouse-odbc#462

[mshustov]

Could you please retest this issue on the latest version (1.3.1.20250213) and let us know if it still occurs?

[areshayevcaci]

Hi
I switched my dashboards from Import to Direct Mode and it works, I think original bug was fixed
thanks for the changes filtering in Direct Mode looks ok

found other issue, but I think it relates to PowerBI Connector
with English UK all works ok, but if change to RUS and try to Search anything in Slicer - it fails with this exception
Error fetching data for this visual. OLE DB or ODBC error: Expression.Error

I don't see anything in clickhouse server logs related to the error - I think it fails on client and don't fetch anything from server