4

I've created UDF in Clickhouse, for example:

create function test_function AS (in_param)  -> (select col1 from table1 t1 join table2 t2 on t1.key=t2.key where t1.key = in_param);

It works fine when I try the following example, just passing the string value excplicitly:

select test_function('10000');

But the following example fails:

select test_function(x_id) from tablex where colx='somevalue';

Fails with error:

Code: 47. DB::Exception: Missing columns: 'x_id' while processing query: 'select col1 from table1 t1 join table2 t2 on t1.key=t2.key where t1.key = x_id'

As if the value of x_id is not passed to the function, looks like the function is working with string 'x_id' and not the value of field x_id. Any suggestions ?

Josiptl
  • 41
  • 1

1 Answers1

0

Me too faced the similar not obvious behavior in Clickhouse.

Clickhouse passes column x_id as in_param, but there is no such column in table1 and table2

select col1 from table1 t1 join table2 t2 on t1.key=t2.key where t1.key = in_param

So the query fails.

When I asked about this behavior in Clikhouse developers telegram chat, they answered me that's expected behavior because of performance considerations.

The only solution is to use dictionaries instead of table1 and table2 and use dictGet in UDF body instead of querying regular tables.

Mikhail Baksheev
  • 1,394
  • 11
  • 13