clickhouse adapts to postgresql

[src-slu]

When I use the ch table and the table imported by the PostgreSql table (pg_algo_input_customer) engine as views, I filter the fields in pg_algo_input_customer. Clickhouse embedded the condition in the pg_algo_input_customer table sql as where 1, but the syntax of where 1 is not supported in pgsql, how do I make this view sql valid using the pg table engine?

The sql statement：
select date, customer, level
from pg_algo_input_customer
where level != 'ZZ'.

The view contains the 'level' above, And when i used 'level' column, ch embeds 'and 1' into sql: WHERE ("level" != 'ZZ') AND 1), then throw this error 'std::exception. Code: 1001, type: pqxx::sql_error, e.what() = ERROR: argument of AND must be type boolean, not type integer'.

Tasks

Beta Give feedback

No tasks being tracked yet.

[src-slu]

ch version: 23.4.2.11

[src-slu]

I'm sorry I opened it agent. I installed the latest version of ch-server today and used the same way to create views, this problem occurs again when there is custom parameter data in the view (for example, level = '全档'). I have tried other numbers, English, it will be like this.

[lampjian]

Try to replace "and 1" with "and true", note that newer version after 22 can deal with boolean type.

[nickitat]

full stack trace plz

[evillique]

Could you also please provide SHOW CREATE for pg_algo_input_customer table?

[fiend19961]

full stack trace plz

DROP TABLE IF EXISTS sys_nic;
CREATE TABLE IF NOT EXISTS sys_nic
(
id UInt64,
type Int16
) ENGINE = PostgreSQL(own_pg, table='sys_nic');

DROP TABLE IF EXISTS proxy.sys_nic;
CREATE TABLE IF NOT EXISTS proxy.sys_nic AS default.sys_nic
ENGINE = Distributed('setus','default','sys_nic',rand());
when execute sql like this :
SELECT * from proxy.sys_nic where id>0 and _shard_num >0;
ERROR FROM pg:
select * from sys_nic WHERE ("id" > 0) AND 1) , The argument for "AND" must be of type boolean, not type integer.

[fiend19961]

Could you also please provide SHOW CREATE for pg_algo_input_customer table?

I met the same problem
#57253

[ucasfl]

It should have been fixed in #56456 recently.