使用Postgresql作为数据源时，若数据源中存在bool类型，查询报错

[WandQ]

查询语句:
./brokerctl run "SELECT tb.ID FROM tb where tb.is_active=1;" --project-id "demo" --host http://localhost:8020 --timeout 3
./brokerctl run "SELECT tb.ID FROM tb where tb.is_active=true;" --project-id "demo" --host http://localhost:8020 --timeout 3

前端报错：
Error: run query: DoQuery response: {
"status": {
"code": 320
}
}

数据源配置：
--embed_router_conf={"datasources":[{"id":"ds001","name":"postgresql db","kind":"POSTGRESQL","connection_str":"dbname=bob user=gpadmin password=postgres host=127.0.0.1 port=5432 "}],"rules":[{"db":"","table":"","datasource_id":"ds001"}]}

数据集：
CREATE TABLE user_stats (
id VARCHAR(64) PRIMARY KEY,
order_amount FLOAT NOT NULL,
is_active BOOLEAN NOT NULL
);

BEGIN;
INSERT INTO user_stats VALUES
('id0001', 3598.0, true),
('id0002', 100.0, false),
('id0003', 2549.0, true),
('id0004', 21698.5, true),
('id0005', 4985.5, true),
('id0006', 3598.0, true),
('id0007', 322.0, false),
('id0008', 9816.2, true),
('id0009', 3598.0, true),
('id0010', 322.0, false),
('id0011', 9816.2, true),
('id0012', 3598.0, true),
('id0013', 322.0, false),
('id0014', 9816.2, true),
('id0015', 9816.2, true),
('id0016', 9816.2, true),
('id0017', 3598.0, true),
('id0018', 322.0, false),
('id0019', 9816.2, true),
('id0020', 9816.2, true);
COMMIT;

engine报错日志：
(8fe8a537-1823-11ef-8070-5a5bc97f2e7a) failed, catch std::exception=[engine/datasource/odbc_adaptor.cc:56] catch unexpected Poco::Data::DataException: PostgreSQL: [PostgreSQL]: postgresql_stmt_prepare error: ERROR: operator does not exist: boolean = integer
LINE 1: ...stats.ID from public.user_stats where user_stats.is_active=1
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
select user_stats.ID from public.user_stats where user_stats.is_active=1
2024-05-22 10:10:48.938 [info] [session_manager.cc:RemoveSession:172] [sciengine] session

貌似SCDB会将tb.is_active=true默认翻译成tb.is_active=1，在PG作为数据源的情况下，若源数据类型为bool类型时，会出现类型不一致的问题

[jingshi-ant]

check下在创建tb的时候需要指定数据类型，scql解析的时候以tb里的类型为准。

[WandQ]

但是SCQL中并不支持bool类型

[tongke6]

麻烦先补充一下信息哈，往 SCQL 里 create table 时，注册的 column 类型是什么？

[WandQ]

我当前用的./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020

尝试了以下语句是会报错的，因为不支持bool
(base) root@smpc:/home/admin/dev/scql-p2p/alice# ./brokerctl create table tb2 --project-id "demo" --columns "ID string, order_amount double, is_active bool" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020
Error: CreateTable status: code:300 message:"CreateTable: Table schema err: VerifyTableMeta: unsupported data type bool"
Usage:
brokerctl create {project|table |job} [flags]

Flags:
--columns string columns for table, format: 'column_name column_type [, name type]', e.g: 'ID string, age int'
--db-type string the database type to which the table belongs, e.g: 'mysql' (default "mysql")
--enable-psi-detail-log whether enable psi detail log
-h, --help help for create
--project-conf string spu conf for project in json format (default "{"protocol":"SEMI2K", "field": "FM64"}")
--query string the sql query for create job, e.g: 'select count(*) from ta'
--ref-table string the physical table name corresponding to the new table, e.g: 'test_table'

Global Flags:
--host string host to access broker, e.g: http://localhost:8080
--project-id string unique identifier for project
--timeout int timeout seconds for http requests to broker (default 1)

而PG中的数据类型是bool

[tyrone-yu]

我当前用的./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020

尝试了以下语句是会报错的，因为不支持bool (base) root@smpc:/home/admin/dev/scql-p2p/alice# ./brokerctl create table tb2 --project-id "demo" --columns "ID string, order_amount double, is_active bool" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020 Error: CreateTable status: code:300 message:"CreateTable: Table schema err: VerifyTableMeta: unsupported data type bool" Usage: brokerctl create {project|table |job} [flags]

Flags: --columns string columns for table, format: 'column_name column_type [, name type]', e.g: 'ID string, age int' --db-type string the database type to which the table belongs, e.g: 'mysql' (default "mysql") --enable-psi-detail-log whether enable psi detail log -h, --help help for create --project-conf string spu conf for project in json format (default "{"protocol":"SEMI2K", "field": "FM64"}") --query string the sql query for create job, e.g: 'select count(*) from ta' --ref-table string the physical table name corresponding to the new table, e.g: 'test_table'

Global Flags: --host string host to access broker, e.g: http://localhost:8080 --project-id string unique identifier for project --timeout int timeout seconds for http requests to broker (default 1)

而PG中的数据类型是bool

这里确实有点问题，后面会提供一种 workaround 的方式来使这个 query 跑起来。十分抱歉！

[WandQ]

好的，感谢解答，期待早日修复～

[github-actions]

Stale issue message. Please comment to remove stale tag. Otherwise this issue will be closed soon.

[tyrone-yu]

我当前用的./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020
尝试了以下语句是会报错的，因为不支持bool (base) root@smpc:/home/admin/dev/scql-p2p/alice# ./brokerctl create table tb2 --project-id "demo" --columns "ID string, order_amount double, is_active bool" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020 Error: CreateTable status: code:300 message:"CreateTable: Table schema err: VerifyTableMeta: unsupported data type bool" Usage: brokerctl create {project|table |job} [flags]
Flags: --columns string columns for table, format: 'column_name column_type [, name type]', e.g: 'ID string, age int' --db-type string the database type to which the table belongs, e.g: 'mysql' (default "mysql") --enable-psi-detail-log whether enable psi detail log -h, --help help for create --project-conf string spu conf for project in json format (default "{"protocol":"SEMI2K", "field": "FM64"}") --query string the sql query for create job, e.g: 'select count(*) from ta' --ref-table string the physical table name corresponding to the new table, e.g: 'test_table'
Global Flags: --host string host to access broker, e.g: http://localhost:8080 --project-id string unique identifier for project --timeout int timeout seconds for http requests to broker (default 1)
而PG中的数据类型是bool

这里确实有点问题，后面会提供一种 workaround 的方式来使这个 query 跑起来。十分抱歉！

新版本中可以通过 cast(xx as signed) 来绕过 tb.is_active=1 在 pg 中无法正常运行的问题

[WandQ]

我当前用的./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020
尝试了以下语句是会报错的，因为不支持bool (base) root@smpc:/home/admin/dev/scql-p2p/alice# ./brokerctl create table tb2 --project-id "demo" --columns "ID string, order_amount double, is_active bool" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020 Error: CreateTable status: code:300 message:"CreateTable: Table schema err: VerifyTableMeta: unsupported data type bool" Usage: brokerctl create {project|table |job} [flags]
Flags: --columns string columns for table, format: 'column_name column_type [, name type]', e.g: 'ID string, age int' --db-type string the database type to which the table belongs, e.g: 'mysql' (default "mysql") --enable-psi-detail-log whether enable psi detail log -h, --help help for create --project-conf string spu conf for project in json format (default "{"protocol":"SEMI2K", "field": "FM64"}") --query string the sql query for create job, e.g: 'select count(*) from ta' --ref-table string the physical table name corresponding to the new table, e.g: 'test_table'
Global Flags: --host string host to access broker, e.g: http://localhost:8080 --project-id string unique identifier for project --timeout int timeout seconds for http requests to broker (default 1)
而PG中的数据类型是bool

这里确实有点问题，后面会提供一种 workaround 的方式来使这个 query 跑起来。十分抱歉！

新版本中可以通过 cast(xx as signed) 来绕过 tb.is_active=1 在 pg 中无法正常运行的问题

这是一个不错的办法，这一属性是在哪一个版本更新更新进来的呢？

[tyrone-yu]

我当前用的./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020
尝试了以下语句是会报错的，因为不支持bool (base) root@smpc:/home/admin/dev/scql-p2p/alice# ./brokerctl create table tb2 --project-id "demo" --columns "ID string, order_amount double, is_active bool" --ref-table bob.user_stats --db-type postgresql --host http://localhost:8020 Error: CreateTable status: code:300 message:"CreateTable: Table schema err: VerifyTableMeta: unsupported data type bool" Usage: brokerctl create {project|table |job} [flags]
Flags: --columns string columns for table, format: 'column_name column_type [, name type]', e.g: 'ID string, age int' --db-type string the database type to which the table belongs, e.g: 'mysql' (default "mysql") --enable-psi-detail-log whether enable psi detail log -h, --help help for create --project-conf string spu conf for project in json format (default "{"protocol":"SEMI2K", "field": "FM64"}") --query string the sql query for create job, e.g: 'select count(*) from ta' --ref-table string the physical table name corresponding to the new table, e.g: 'test_table'
Global Flags: --host string host to access broker, e.g: http://localhost:8080 --project-id string unique identifier for project --timeout int timeout seconds for http requests to broker (default 1)
而PG中的数据类型是bool

这里确实有点问题，后面会提供一种 workaround 的方式来使这个 query 跑起来。十分抱歉！

新版本中可以通过 cast(xx as signed) 来绕过 tb.is_active=1 在 pg 中无法正常运行的问题

这是一个不错的办法，这一属性是在哪一个版本更新更新进来的呢？

https://github.com/secretflow/scql/releases/tag/0.8.0b1 可以试下这个版本

[WandQ]

@tyrone-yu
[demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS int) = 1;
[fetch]err: Code: 102, message:line 1 column 89 near "int) = 1"
[demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,int) = 1;
[fetch]err: Code: 102, message:line 1 column 83 near ",int) = 1"
[demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,integer) = 1;
[fetch]err: Code: 102, message:line 1 column 83 near ",integer) = 1"
[demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS integer) = 1;
[fetch]err: Code: 102, message:line 1 column 93 near "integer) = 1"

我尝试了多种cast方法的使用，但是都是错的，能否提供一个使用的例子呢？

[tyrone-yu]

@tyrone-yu [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS int) = 1; [fetch]err: Code: 102, message:line 1 column 89 near "int) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,int) = 1; [fetch]err: Code: 102, message:line 1 column 83 near ",int) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,integer) = 1; [fetch]err: Code: 102, message:line 1 column 83 near ",integer) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS integer) = 1; [fetch]err: Code: 102, message:line 1 column 93 near "integer) = 1"

我尝试了多种cast方法的使用，但是都是错的，能否提供一个使用的例子呢？

我们虽然支持 pg 作为数据源，但是我们本身采用的是 mysql 的语法。
可以尝试使用 cast(xxx as signed)

[WandQ]

@tyrone-yu [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS int) = 1; [fetch]err: Code: 102, message:line 1 column 89 near "int) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,int) = 1; [fetch]err: Code: 102, message:line 1 column 83 near ",int) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active,integer) = 1; [fetch]err: Code: 102, message:line 1 column 83 near ",integer) = 1" [demo]department1> select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS integer) = 1; [fetch]err: Code: 102, message:line 1 column 93 near "integer) = 1"
我尝试了多种cast方法的使用，但是都是错的，能否提供一个使用的例子呢？

我们虽然支持 pg 作为数据源，但是我们本身采用的是 mysql 的语法。 可以尝试使用 cast(xxx as signed)

经过测试select d1.id from d1 join d2 on d1.id=d2.id where d1.age>20 and cast(d2.is_active AS signed) = 1;是通过的，感谢解答