TIDB count (distinct case when ...) 跨分区报错

🪐 TiDB 技术问题 部署&运维管理
1
  • 【TiDB 版本】：v4.0.5
  • 【问题描述】：

执行如下SQL时

SELECT count(distinct CASE WHEN IS_ARRIVED = 1 THEN BUYER_NICK ELSE NULL END) AS BUYER_NICK FROM marketing_summary WHERE (STORE_ID = ‘xxxx’ AND DISPATCH_TIMESTAMP >= ‘2020-09-22 00:00:00.0’ AND DISPATCH_TIMESTAMP <= ‘2020-09-25 23:59:59.0’);

若时间在同一分区中可顺利执行，若跨多个时间分区则会报如下错误:

ERROR 1105 (HY000): Can’t find column Column#29 in schema Column: [tb_lxk.marketing_summary.store_id,tb_lxk.marketing_summary.buyer_nick,tb_lxk.marketing_summary.dispatch_timestamp,tb_lxk.marketing_summary.is_arrived] Unique key:

把表的时间分区去掉执行不会报错

把 distinct 去掉 count(case when … )不会报错

count(distinct buyer_nick) 也不会报错

表结构如下:

CREATE TABLE marketing_summary (
biz_id varchar(128) NOT NULL,
store_id varchar(16000) NOT NULL,
store_name varchar(16000) DEFAULT NULL,
domain varchar(16000) DEFAULT NULL COMMENT ‘’,
buyer_nick varchar(16000) NOT NULL,
trigger_timestamp timestamp NOT NULL,
dispatch_timestamp timestamp NOT NULL,
assistant_nick varchar(16000) NOT NULL,
task_id int(11) NOT NULL,
scenario varchar(16000) NOT NULL COMMENT ‘’,
template_id int(11) NOT NULL,
channel varchar(16000) NOT NULL COMMENT ‘’,
mobile varchar(16000) DEFAULT NULL COMMENT ‘’,
msg_count int(11) DEFAULT NULL COMMENT ‘’,
created_trade_ids varchar(16000) DEFAULT NULL COMMENT ‘’,
paid_trade_ids varchar(16000) DEFAULT NULL COMMENT ‘’,
created_trade_count int(11) DEFAULT NULL COMMENT ‘’,
created_order_count int(11) DEFAULT NULL COMMENT ‘’,
created_amount_cent bigint(12) DEFAULT NULL COMMENT ‘’,
paid_trade_count int(11) DEFAULT NULL COMMENT ‘’,
paid_order_count int(11) DEFAULT NULL COMMENT ‘’,
paid_amount_cent bigint(12) DEFAULT NULL COMMENT ‘’,
replied_timestamp timestamp NULL DEFAULT NULL COMMENT ‘’,
is_arrived tinyint(1) NOT NULL COMMENT ‘’,
is_replied tinyint(1) NOT NULL COMMENT ‘’,
is_converted tinyint(1) NOT NULL COMMENT ‘’,
PRIMARY KEY (biz_id,dispatch_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE ( UNIX_TIMESTAMP(dispatch_timestamp) ) (
PARTITION dt_0 VALUES LESS THAN (0),
PARTITION dt_20200924 VALUES LESS THAN (1600963200),
PARTITION dt_20200925 VALUES LESS THAN (1601049600),
PARTITION dt_20200926 VALUES LESS THAN (1601136000)
)

2

感谢反馈，问题可以复现，我们查看下，多谢。

3

尝试升级到 v4.0.6，依然可以复现

4

你好，这个问题看起来是 TiDB planner 的一个问题，在 v4.0.6 上仍然存在，我们会记录一个 issue。目前需要改写 SQL 绕过一下，
如果只需要统计 IS_ARRIVED = 1 行的记录，可以考虑去掉 case when 然后把该条件放到 where 条件中，类似

SELECT count(distinct BUYER_NICK) AS BUYER_NICK FROM marketing_summary WHERE (STORE_ID = ‘xxxx’ AND DISPATCH_TIMESTAMP >= ‘2020-09-22 00:00:00.0’ AND DISPATCH_TIMESTAMP <= ‘2020-09-25 23:59:59.0’) AND IS_ARRIVED = 1;
1 个赞