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 ]

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 (https://github.com/ClickHouse/clickhouse-odbc/issues/214) in the ODBC repo, therefore closing this one.