Closed
Description
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
- 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');
- 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)