SUM函数里面有减法运算,结果溢出

【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.5
【遇到的问题:问题现象及影响】
字段都是非负整数,但是sum(a-b)的查询结果很大很大

贴个文本怎么样

CREATE TABLE result_daily_sku (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
day date NOT NULL DEFAULT ‘1970-01-01’ COMMENT ‘日期’,
brand_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘品牌id’,
shop_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘店铺id’,
sku_sn varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘商家编码’,
num_total int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘销售件数’,
refund_num_fixed int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘退款件数(退款时间)’,
add_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘系统新增时间’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘系统更新时间’,
PRIMARY KEY (day,brand_id,shop_id,sku_sn) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY idx_uniq_id (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SELECT
sum(result_daily_sku.num_total) as sale_num,
sum( result_daily_sku.refund_num_fixed ) as refund_num,
sum(result_daily_sku.num_total - result_daily_sku.refund_num_fixed) as net_num
FROM
result_daily_sku
WHERE
result_daily_sku.shop_id = ‘16’;

±---------±-----------±--------------------------+
| sale_num | refund_num | net_num |
±---------±-----------±--------------------------+
| 1680038 | 631479 | 3392614489572218476953583 |
±---------±-----------±--------------------------+

猜测是因为 result_daily_sku.num_total - result_daily_sku.refund_num_fixed 有负数,但是这就溢出了也不好呀

加个where a-b>值,找找异常数据

试了一下,没有 a-b 小于0 的记录

sum()函数中做减法运算可能存在bug,分开sum后再做减法更准确点

但是有大于最大值的情况

explain 一下,看下有没有走到 tiflash 上去

走了,然后呢

看这里: https://docs.pingcap.com/zh/tidb/stable/tiflash-compatibility , TiFlash 是不会检查溢出的

1 个赞
如果您的SUM函数返回的值超过了数据类型的范围,可以考虑将数据类型更改为更大的类型,例如从INT改为BIGINT.

应该是字段类型都是 unsigned 的,所以 tiflash 把字段也当成 unsigned 来计算了,计算出来是个负数,因为 TiFlash 不检查溢出,就变成了很大的值。
你可以设置一下 set SESSION tidb_isolation_read_engines = "tikv,tidb";,只让走 TiKV再查一下,看结果正常么。

在计算之前过滤掉那些可能导致减法运算结果为负数的行,尤其是在您知道这些情况很少发生时。

这样设置会OOM,SQLSTATE[HY000]: General error: 1105 Out Of Memory Quota!

最后试了一下,不止是tidb有这个问题,mysql8也有类似的问题;
只不过表现不一样,tidb查到错误值,mysql直接报错BIGINT UNSIGNED value is out of range

字符类型改成BIGINT 可不可行

环境能临时改动下 tidb_mem_quota_query?

溢出的原因很多,需要看着执行计划。

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。