starrocks报错：Right expr of binary predicate should be value

陈腾飞同学

已于 2024-08-24 22:25:46 修改

阅读量315

点赞数 7

文章标签：大数据

于 2024-08-24 16:50:25 首次发布

本文链接：https://blog.csdn.net/tengfei0973/article/details/141499682

版权

starrocks报错：Right expr of binary predicate should be value，可能是在sql中使用了delete from t_user_info_unique_key where dt >= to_date(date_trunc(‘month’, ‘2024-08-24’))这种包含函数或数值计算的where过滤。可以视情况将其改为delete from t_user_info_unique_key where dt >= ‘2024-08-01’ and dt <= '2024-08-24’或者delete from t_user_info_unique_key where dt >= ‘2024-08-01’。
starrocks的表类型分为主键表PRIMARY KEY，明细表DUPLICATE KEY，聚合表AGGREGATE KEY，更新表UNIQUE KEY，只有主键表支持完整的delete where语义，即支持在where后进行函数、数值计算或子查询。

使用更新表验证delete where

CREATE TABLE `t_user_info_unique_key` (
  `dt` date NULL COMMENT "分区日期",
  `uid` bigint NULL COMMENT "主键",
  `name` varchar(100) NULL COMMENT "姓名",
  `nickname` varchar(100) NULL COMMENT "昵称",
  `phone_number` varchar(20) NULL COMMENT "手机号",
  `gender` varchar(10) NULL COMMENT "性别",
  `birthday` date NULL COMMENT "出生日期",
  `is_delete` int NULL COMMENT "是否注销：0否 1是",
  `create_time` datetime NULL COMMENT "创建时间",
  `update_time` datetime NULL COMMENT "更新时间"
) ENGINE = OLAP
UNIQUE KEY(`dt`,`uid`)
COMMENT "用户信息表"
PARTITION BY RANGE(`dt`) (
  PARTITION p20240801 VALUES [("2024-08-01"), ("2024-08-02")),
  ...
  PARTITION p20240824 VALUES [("2024-08-24"), ("2024-08-25"))
)
DISTRIBUTED BY HASH(`uid`) BUCKETS 5
PROPERTIES (
  "replication_num" = "3",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "DAY",
  "dynamic_partition.time_zone" = "Asia/Shanghai",
  "dynamic_partition.start" = "-2147483648",
  "dynamic_partition.end" = "30",
  "dynamic_partition.prefix" = "p",
  "dynamic_partition.buckets" = "5",
  "dynamic_partition.history_partition_num" = "0",
  "in_memory" = "false",
  "storage_format" = "DEFAULT",
  "enable_persistent_index" = "false",
  "compression" = "LZ4"
);

delete from t_user_info_unique_key where dt >= to_date(date_trunc('month', '2024-08-24'))

删除失败
在这里插入图片描述

使用主键表验证delete where

CREATE TABLE `t_user_info_primary_key` (
  `dt` date COMMENT "分区日期",
  `uid` bigint COMMENT "主键",
  `name` varchar(100) NULL COMMENT "姓名",
  `nickname` varchar(100) NULL COMMENT "昵称",
  `phone_number` varchar(20) NULL COMMENT "手机号",
  `gender` varchar(10) NULL COMMENT "性别",
  `birthday` date NULL COMMENT "出生日期",
  `is_delete` int NULL COMMENT "是否注销：0否 1是",
  `create_time` datetime NULL COMMENT "创建时间",
  `update_time` datetime NULL COMMENT "更新时间"
) ENGINE = OLAP
PRIMARY KEY(`dt`,`uid`)
COMMENT "用户信息表"
PARTITION BY RANGE(`dt`) (
  PARTITION p20240801 VALUES [("2024-08-01"), ("2024-08-02")),
  ...
  PARTITION p20240824 VALUES [("2024-08-24"), ("2024-08-25"))
)
DISTRIBUTED BY HASH(`uid`) BUCKETS 5
PROPERTIES (
  "replication_num" = "3",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "DAY",
  "dynamic_partition.time_zone" = "Asia/Shanghai",
  "dynamic_partition.start" = "-2147483648",
  "dynamic_partition.end" = "30",
  "dynamic_partition.prefix" = "p",
  "dynamic_partition.buckets" = "5",
  "dynamic_partition.history_partition_num" = "0",
  "in_memory" = "false",
  "storage_format" = "DEFAULT",
  "enable_persistent_index" = "false",
  "compression" = "LZ4"
);

delete from t_user_info_primary_key where dt >= to_date(date_trunc('month', '2024-08-24'))