【 TiDB 使用环境】测试
【 TiDB 版本】v7.1.2
【复现路径】
表结构：
CREATE TABLE order_center_order (
ORDER_CENTER_ORDER_ID varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘订单号’,
ACTUAL_RECEIVE_QUANTITY double(10,3) DEFAULT ‘0’ COMMENT ‘实际到货数’,
PRIMARY KEY (ORDER_CENTER_ORDER_ID) /*T![clustered_index] CLUSTERED */,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘订单表’;
CREATE TABLE order_center_order_detail (
ID varchar(32) COLLATE utf8_general_ci NOT NULL COMMENT ‘ID’,
ORDER_CENTER_ORDER_ID varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘订单号’,
ACTUAL_RECEIVE_QUANTITY double(11,3) DEFAULT NULL COMMENT ‘实际到货数’,
DELETE_FLAG int(11) DEFAULT NULL COMMENT ‘删除标记’,
PRIMARY KEY (ID) /*T![clustered_index] CLUSTERED */,
KEY IDX_1 (ORDER_CENTER_ORDER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘订单详情表’;
执行的sql：
– 变更数据
UPDATE order_center_order a
SET a.UPDATED_BY = ‘xxx’,
a.UPDATE_TIME = NOW(),
a.actual_receive_quantity = IFNULL(( SELECT sum( b.actual_receive_quantity ) FROM order_center_order_detail b WHERE a.order_center_order_id = b.order_center_order_id AND b.delete_flag = 0 ), 0 )
WHERE
a.order_center_order_id = ‘Pxxxxxxxxxxxxx’;
– 查看变更结果 0 （预期结果是该笔订单明细的汇总值）
SELECT
actual_receive_quantity
FROM
order_center_order
WHERE
order_center_order_id = ‘Pxxxxxxxxxxxxxx’;
– 查看预期结果 200 （预期结果是该笔订单明细的汇总值）
SELECT
IFNULL(
( SELECT sum( b.actual_receive_quantity ) FROM order_center_order_detail b WHERE b.order_center_order_id = a.order_center_order_id AND b.delete_flag = 0 ),
0
)
FROM
order_center_order a
WHERE
order_center_order_id = ‘Pxxxxxxxxxxxxxx’;
–查看详情表中相关字段实际数据
SELECT order_center_order_id,actual_receive_quantity,delete_flag FROM order_center_order_detail WHERE order_center_order_id = ‘Pxxxxxxxxxxxxxx’;
【遇到的问题：问题现象及影响】
该update语句在Mysql5.7.43可以正常执行，update的结果为200。
在tidb7.1.2版本，update的结果为0；如果将该update语句的IFNULL函数去掉，也可以得到预期的变更结果：200。
– 去掉update语句中的IFNULL函数
UPDATE order_center_order a SET a.UPDATED_BY = ‘xxx’,a.UPDATE_TIME = NOW(),a.actual_receive_quantity = ( SELECT sum( b.actual_receive_quantity ) FROM order_center_order_detail b WHERE a.order_center_order_id = b.order_center_order_id AND b.delete_flag = 0 ) WHERE a.order_center_order_id = ‘Pxxxxxxxxxxxxxx’;
SELECT actual_receive_quantity FROM order_center_order WHERE order_center_order_id = ‘Pxxxxxxxxxxxxxx’;
