查询sql报错：ERROR 1064 (HY000): Constant 1 can't compare with Constant 0

💬 StarRocks 用户问答 功能使用相关
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】查询sql报错：ERROR 1064 (HY000): Constant 1 can’t compare with Constant 0
【背景】表是主键表，主键字段有6个，数据量只有10条，查询以下sql直接报错：

select group_id,
    comp_id,
    dblink_id,
    pur_num,
    pur_num_seq,
    pur_num_seq2,count(1) as cnt from test.odf_purchase 
    group by group_id,
    comp_id,
    dblink_id,
    pur_num,
    pur_num_seq,
    pur_num_seq2
    having cnt>0 limit 10;

test.odf_purchase 建表语句：

CREATE TABLE `odf_purchase` (
  `group_id` varchar(65533) NOT NULL COMMENT "集团编号",
  `comp_id` varchar(65533) NOT NULL COMMENT "公司编号",
  `dblink_id` varchar(65533) NOT NULL COMMENT "连线编号",
  `pur_num` varchar(65533) NOT NULL COMMENT "采购单号",
  `pur_num_seq` varchar(65533) NOT NULL COMMENT "采购项次",
  `pur_num_seq2` varchar(65533) NOT NULL COMMENT "采购分批项序",
  `pur_date` datetime NULL COMMENT "采购日期",
  `org_id` varchar(65533) NULL COMMENT "营运组织键值",
  `supp_id` varchar(65533) NULL COMMENT "供应商键值",
  `empl_id` varchar(65533) NULL COMMENT "采购人员键值",
  `dept_id` varchar(65533) NULL COMMENT "采购部门键值",
  `item_id` varchar(65533) NULL COMMENT "料品键值",
  `item_features` varchar(65533) NULL COMMENT "料品特征值",
  `plan_transp_date` datetime NULL COMMENT "预计出货日",
  `plan_recev_date` datetime NULL COMMENT "预计收货日",
  `plan_inwh_date` datetime NULL COMMENT "预计入库日",
  `pay_cond_code` varchar(65533) NULL COMMENT "付款条件编号",
  `pay_cond_name` varchar(65533) NULL COMMENT "付款条件说明",
  `price_cond_code` varchar(65533) NULL COMMENT "交易条件编号",
  `price_cond_name` varchar(65533) NULL COMMENT "交易条件说明",
  `pur_channel_code` varchar(65533) NULL COMMENT "采购通路",
  `pur_channel_name` varchar(65533) NULL COMMENT "采购通路说明",
  `pur_category_code` varchar(65533) NULL COMMENT "采购分类",
  `pur_category_name` varchar(65533) NULL COMMENT "采购分类说明",
  `buy_type_code` varchar(65533) NULL COMMENT "内外购",
  `buy_type_name` varchar(65533) NULL COMMENT "内/外购说明",
  `project_code` varchar(65533) NULL COMMENT "项目编号",
  `project_name` varchar(65533) NULL COMMENT "项目名称",
  `currency_code` varchar(65533) NULL COMMENT "交易币别编号",
  `currency_name` varchar(65533) NULL COMMENT "交易币别名称",
  `pur_unit_type` varchar(65533) NULL COMMENT "单位类型",
  `pur_unit2` varchar(65533) NULL COMMENT "交易小单位",
  `pur_unit` varchar(65533) NULL COMMENT "交易单位",
  `cost_unit` varchar(65533) NULL COMMENT "成本单位",
  `base_unit` varchar(65533) NULL COMMENT "基础单位",
  `tax_rate` double NULL COMMENT "税率",
  `exchange_orinat` double NULL COMMENT "汇率(原币兑本币)",
  `pur_qty_t` double NULL COMMENT "采购数量(交易)",
  `pur_amt_ori` double NULL COMMENT "采购金额(原币/未税)",
  `pur_amt_ori_t` double NULL COMMENT "采购金额(原币/含税)",
  `pur_amt` double NULL COMMENT "采购金额(本币/未税)",
  `pur_amt_t` double NULL COMMENT "采购金额(本币/含税)",
  `pur_perp_days` double NULL COMMENT "采购前置天数",
  `pur_type_code` varchar(65533) NULL COMMENT "采购性质",
  `pur_type_name` varchar(65533) NULL COMMENT "采购性质说明",
  `change_flag` varchar(65533) NULL COMMENT "采购变更码",
  `close_flag` varchar(65533) NULL COMMENT "结案否",
  `close_flag_desc` varchar(65533) NULL COMMENT "结案否说明",
  `outsourcing` varchar(65533) NULL COMMENT "委外",
  `udd01` varchar(65533) NULL COMMENT "客户自订维度01",
  `udd02` varchar(65533) NULL COMMENT "客户自订维度02",
  `udd03` varchar(65533) NULL COMMENT "客户自订维度03",
  `udd04` varchar(65533) NULL COMMENT "客户自订维度04",
  `udd05` varchar(65533) NULL COMMENT "客户自订维度05",
  `udd06` varchar(65533) NULL COMMENT "客户自订维度06",
  `udd07` varchar(65533) NULL COMMENT "客户自订维度07",
  `udd08` varchar(65533) NULL COMMENT "客户自订维度08",
  `udd09` varchar(65533) NULL COMMENT "客户自订维度09",
  `udd10` varchar(65533) NULL COMMENT "客户自订维度10",
  `udm01` double NULL COMMENT "客户自订计量01",
  `udm02` double NULL COMMENT "客户自订计量02",
  `udm03` double NULL COMMENT "客户自订计量03",
  `udm04` double NULL COMMENT "客户自订计量04",
  `udm05` double NULL COMMENT "客户自订计量05",
  `udm06` double NULL COMMENT "客户自订计量06",
  `udm07` double NULL COMMENT "客户自订计量07",
  `udm08` double NULL COMMENT "客户自订计量08",
  `udm09` double NULL COMMENT "客户自订计量09",
  `udm10` double NULL COMMENT "客户自订计量10",
  `sdd01` varchar(65533) NULL COMMENT "系统预留维度01",
  `sdd02` varchar(65533) NULL COMMENT "系统预留维度02",
  `sdd03` varchar(65533) NULL COMMENT "系统预留维度03",
  `sdd04` varchar(65533) NULL COMMENT "系统预留维度04",
  `sdd05` varchar(65533) NULL COMMENT "系统预留维度05",
  `sdd06` varchar(65533) NULL COMMENT "系统预留维度06",
  `sdd07` varchar(65533) NULL COMMENT "系统预留维度07",
  `sdd08` varchar(65533) NULL COMMENT "系统预留维度08",
  `sdd09` varchar(65533) NULL COMMENT "系统预留维度09",
  `sdd10` varchar(65533) NULL COMMENT "系统预留维度10",
  `sdm01` double NULL COMMENT "系统预留计量01",
  `sdm02` double NULL COMMENT "系统预留计量02",
  `sdm03` double NULL COMMENT "系统预留计量03",
  `sdm04` double NULL COMMENT "系统预留计量04",
  `sdm05` double NULL COMMENT "系统预留计量05",
  `sdm06` double NULL COMMENT "系统预留计量06",
  `sdm07` double NULL COMMENT "系统预留计量07",
  `sdm08` double NULL COMMENT "系统预留计量08",
  `sdm09` double NULL COMMENT "系统预留计量09",
  `sdm10` double NULL COMMENT "系统预留计量10"
) ENGINE=OLAP 
PRIMARY KEY(`group_id`, `comp_id`, `dblink_id`, `pur_num`, `pur_num_seq`, `pur_num_seq2`)
COMMENT "采购明细"
DISTRIBUTED BY HASH(`group_id`, `comp_id`) BUCKETS 8 
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "3"
);

【业务影响】查询sql报错
【是否存算分离】
【StarRocks版本】例如：3.3.4
【集群规模】例如：1fe（1 follower+2observer）+4be
【机器信息】CPU虚拟核/内存/网卡，例如：16C/64G/万兆
【联系方式】StarRocks社区群17-Golden
【附件】
查询报错：

ERROR 1064 (HY000): Constant 1 can't compare with Constant 0

并且，直接explain这条sql，也是报这个错。

京ICP备2022026421号-1