Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: Tidb7.1.2 update 语句中带IFNULL函数，执行的结果与期望值不一致，与Mysql5.7.43不一致
|
username: TiDBer_EjqZheSi
【TiDB Usage Environment】Testing
【TiDB Version】v7.1.2
【Reproduction Path】
Table structure:
CREATE TABLE `order_center_order` (
`ORDER_CENTER_ORDER_ID` varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT 'Order Number',
`ACTUAL_RECEIVE_QUANTITY` double(10,3) DEFAULT '0' COMMENT 'Actual Received Quantity',
PRIMARY KEY (`ORDER_CENTER_ORDER_ID`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Order Table';
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 'Order Number',
`ACTUAL_RECEIVE_QUANTITY` double(11,3) DEFAULT NULL COMMENT 'Actual Received Quantity',
`DELETE_FLAG` int(11) DEFAULT NULL COMMENT 'Delete Flag',
PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */,
KEY `IDX_1` (`ORDER_CENTER_ORDER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Order Detail Table';
Executed SQL:
-- Update data
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';
-- Check update result 0 (Expected result is the summary value of the order details)
SELECT
actual_receive_quantity
FROM
order_center_order
WHERE
order_center_order_id = 'Pxxxxxxxxxxxxxx';
-- Check expected result 200 (Expected result is the summary value of the order details)
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';
-- Check actual data of related fields in the detail table
SELECT order_center_order_id,actual_receive_quantity,delete_flag FROM order_center_order_detail WHERE order_center_order_id = 'Pxxxxxxxxxxxxxx';
【Encountered Problem: Problem Phenomenon and Impact】
This update statement can be executed normally in MySQL 5.7.43, and the update result is 200.
In TiDB 7.1.2 version, the update result is 0; if the IFNULL function in the update statement is removed, the expected update result of 200 can also be obtained.
– Remove the IFNULL function from the update statement
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';
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: