jiezi2026 opened a new issue, #31694: URL: https://github.com/apache/doris/issues/31694
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version doris-2.0.5-rc02-67c9ccfa5d ### What's Wrong? When combining other additional conditions with a case when statement to form a where condition, the error should return type 'BOOLEAN' but returns type 'TINYINT' will be reported. ### What You Expected? Can execute normally without any errors ### How to Reproduce? /*创建用户库*/ create database if not exists test_a; create database if not exists test_b; create database if not exists test_v; /*创建样例表和视图*/ CREATE TABLE test_a.`ods_t_ec_oms_order_header` ( `order_id` varchar(80) NOT NULL COMMENT '订单id', `status_id` varchar(80) NOT NULL COMMENT '订单状态', INDEX index_order_header_status_id (`status_id`) USING BITMAP COMMENT 'index_order_header_status_id' ) ENGINE=OLAP UNIQUE KEY(`order_id`) COMMENT '订单主信息' DISTRIBUTED BY HASH(`order_id`) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "is_being_synced" = "false", "storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" ); insert into test_a.`ods_t_ec_oms_order_header` values('S001','ORDER_CREATED'); insert into test_a.`ods_t_ec_oms_order_header` values('S002','ORDER_COMPLETED'); insert into test_a.`ods_t_ec_oms_order_header` values('S003','ORDER_CANCELLED'); CREATE TABLE test_a.`ods_t_ec_oms_order_item` ( `order_id` varchar(80) NOT NULL COMMENT '订单id', `order_item_seq_id` varchar(80) NOT NULL COMMENT '订单条目id', `quantity` decimal(18, 2) NOT NULL COMMENT '数量' ) ENGINE=OLAP UNIQUE KEY(`order_id`,`order_item_seq_id`) COMMENT '订单项次' DISTRIBUTED BY HASH(`order_id`) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "is_being_synced" = "false", "storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" ); insert into test_a.`ods_t_ec_oms_order_item` values('S001','001',2); insert into test_a.`ods_t_ec_oms_order_item` values('S001','002',1); insert into test_a.`ods_t_ec_oms_order_item` values('S002','001',10); insert into test_a.`ods_t_ec_oms_order_item` values('S002','002',5); insert into test_a.`ods_t_ec_oms_order_item` values('S003','001',2); create view test_v.order_view as select oh.order_id `订单编号` ,CASE oh.status_id WHEN 'ORDER_COMPLETED' THEN '已完成' WHEN 'ORDER_WAIT_APPROVED' THEN '待审核' WHEN 'ORDER_WAIT_SENT' THEN '待发货' WHEN 'GROUP_BUYING_FAIL' THEN '订单拼团失败' WHEN 'ORDER_CREATED' THEN '订单已创建' WHEN 'ORDER_CANCELLED' THEN '已取消' WHEN 'ORDER_WAIT_PAYED' THEN '待支付' WHEN 'ORDER_PARTIAL_PAYED' THEN '已部分支付' WHEN 'ORDER_APPROVED' THEN '订单已审核' WHEN 'ORDER_PARTIAL_SENT' THEN '已部分发货' WHEN 'ORDER_SENT' THEN '已全部发货' WHEN 'ORDER_RETURN_APPROVE' THEN '退货审核中' WHEN 'ORDER_SPLIT' THEN '订单拆分' WHEN 'ORDER_WITHDREW' THEN '订单已撤回' WHEN 'ORDER_WAIT_CONFIRM' THEN '预生成订单' WHEN 'ORDER_WAIT_BUYER_ACK' THEN '待买家确认' WHEN 'ORDER_CANCELLING' THEN '取消申请中' WHEN 'ORDER_BREACH' THEN '订单违约' ELSE oh.status_id END AS `订单状态` ,oi.quantity `交易数量` from test_a.`ods_t_ec_oms_order_header` oh left join test_a.`ods_t_ec_oms_order_item` oi on oh.order_id = oi.order_id ; /*创建用户喝角色权限*/ create role if not exists role_a; CREATE USER 'a02162'@'%' IDENTIFIED BY 'a02162' DEFAULT ROLE 'role_a'; grant SELECT_PRIV,SHOW_VIEW_PRIV on test_v.order_view to role 'role_a'; --------------------------------------------------------------------------------------------------------------------------------------------- /*当使用a02162用户进行查询时*/ select * from test_v.order_view -- select * 正常 select * from test_v.order_view where `订单编号`='S001' and (CASE WHEN (`订单状态` = '已取消') THEN 0 ELSE 1 END) -- 报错 select sum(`交易数量`), `订单编号` from test_v.order_view where `订单编号`='S001' and (CASE WHEN (`订单状态` = '已取消') THEN 0 ELSE 1 END) group by 2 /* * 当单独 用 (CASE WHEN (`订单状态` = '已取消') THEN 0 ELSE 1 END) 做where条件时，可以正常查询 * 但 增加复合条件 `订单编号`='S001' and (CASE WHEN (`订单状态` = '已取消') THEN 0 ELSE 1 END) 时就会报 should return type 'BOOLEAN' but returns type 'TINYINT'. * */ ### Anything Else? _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
