PowerBI Desktop - Direct Query - Filter is not working - DB::Exception - ODBC Syntax error

[areshayevcaci]

The issue with filtering data in PowerBI Desktop was found in 2 scenarios - when we try filter Slicer and when we tried to apply Advanced Filter Contains in Table chart. Original issue was opened as PowerBI connector problem (ClickHouse/power-bi-clickhouse#19), but after investigation it looks like a problem with ODBC driver and setting parameters for query

Steps to reproduce

1. Create one table

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. Try filter the data in slicer or via Contains Advanced Filter in table

Slicer - try to Search "Test" - DB::Exception: Syntax error

Table - try to Advanced Filter - Contains "Test" - DB::Exception: Syntax error

Expected behaviour

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

see more details in this comment ClickHouse/power-bi-clickhouse#19 (comment)

Error log

(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):

Query log

Configuration

Environment

• Driver version: 1.03.00.55930
• OS: Windows 10
  clickhouse 24.3.2.25. PowerBI 2.138.1452

ClickHouse server

• ClickHouse Server version: tested on 2 versions: 24.3.2.25, 24.1.2.5
• Linux (ubuntu 22.04)