Clickhouse parametized query: unsupported operator expression in qual

[WcaleNieWolny]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I have a quite complex view in clickhouse that looks like this

CREATE VIEW IF NOT EXISTS mau_final_param as
SELECT DISTINCT ON (m.date,m.app_id) 
  m.date AS date,
  m.app_id AS app_id,
  COALESCE(m.total, 0) AS mau,
  COALESCE(l.get, 0) AS get,
  COALESCE(l.fail, 0) AS fail,
  COALESCE(l.install, 0) AS install,
  COALESCE(l.uninstall, 0) AS uninstall,
  COALESCE(l.bandwidth, 0) AS bandwidth,
  COALESCE(s.storage_added, 0) AS storage_added,
  COALESCE(s.storage_deleted, 0) AS storage_deleted
  FROM (SELECT result.1 date, uniqMerge(arrayJoin(result.2)) total, app_id
    FROM (
        SELECT 
            groupArray((date, value)) data,
            arrayMap(
                (x, index) -> (x.1, arrayMap(y -> y.2, arraySlice(data, index))), 
                data, 
                arrayEnumerate(data)) result_as_array,
            arrayJoin(result_as_array) result, app_id
        FROM (        
            SELECT app_id, date, total value
            FROM (
                /* emulate the original data */
                SELECT app_id, total, date from mau where hasAll({app_list:Array(String)}, [app_id]) 
            ORDER BY date desc, app_id)
        ) group by app_id
    ) group by app_id, date order by date desc) m
  LEFT JOIN logs_daily l ON m.date = l.date AND m.app_id = l.app_id
  LEFT JOIN app_storage_daily s ON l.date = s.date AND l.app_id = s.app_id
  group by m.app_id, m.date, l.get, l.install, l.uninstall, l.bandwidth, l.fail, s.storage_added, s.storage_deleted, m.total;

This view uses parametrized query, in clickhouse I can use this view like this:

select * from mau_final_param(app_list=['com.demo.app']);

Now, I would like to do the same in supabase with clickhouse fdw
As such, I created a foreign table:

create foreign table clickhouse_app_usage_parm (
 date date,
 app_id text,
 bandwidth bigint,
 mau bigint,
 get bigint,
 fail bigint,
 uninstall bigint,
 install bigint,
 storage_added bigint,
 storage_deleted bigint,
 _app_list text[]
)
server clickhouse_server
options (
 table '(select * from mau_final_param(app_list=${_app_list}))'
);

and then I tried to fetch this view like this:

DO $$
DECLARE app_ids text[];
BEGIN
    select array_agg(app_id) INTO app_ids FROM apps;
    select * from clickhouse_app_usage_parm where _app_list=app_ids;
END $$;

Unfortunately this did not work and I got the following error:

WARNING:  unsupported operator expression in qual: {OPEXPR :opno 1070 :opfuncid 744 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args ({VAR :varno 1 :varattno 11 :vartype 1009 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnosyn 1 :varattnosyn 11 :location 46} {CONST :consttype 1009 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 56 :constvalue 64 [ 0 1 0 0 1 0 0 0 0 0 0 0 25 0 0 0 2 0 0 0 1 0 0 0 84 0 0 0 99 111 109 46 100 101 109 111 97 100 109 105 110 46 97 112 112 0 0 0 64 0 0 0 99 111 109 46 100 101 109 111 46 97 112 112 ]}) :location 55}
DETAIL:  Wrappers
ERROR:  unmatched query parameter: _app_list
CONTEXT:  SQL statement "select * from clickhouse_app_usage_parm where _app_list=app_ids"
PL/pgSQL function inline_code_block line 5 at SQL statement

To Reproduce

Please see above

Expected behavior

I would expect the parametrized query to work one way or the other. And the unsupported operator expression in qual to be fixed

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: linux (endevourOS, based on arch)
• version of wrappers: 0.2.0
• Clickhouse version: 23.12.1.1368

[burmecia]

Thanks for reporting this issue. The array data type hasn't been supported yet, so it cannot be passed as parameter to ClickHouse FDW at this moment. The full list of supported types can be found in this docs.