Unexpected value obtained when update with subquery and IFNULL function #49155
Assignees
Labels
may-affects-5.4
This bug maybe affects 5.4.x versions.
may-affects-6.1
may-affects-6.5
may-affects-7.1
may-affects-7.5
severity/major
sig/planner
SIG: Planner
type/bug
The issue is confirmed as a bug.
Comments
ti-chi-bot
bot
added
may-affects-5.4
|
Can you provide the explain analyze result of 4 SQLs:
explain analyze 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’;explain analyze SELECT ( 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 ) FROM order_center_order a WHERE order_center_order_id = ‘Pxxxxxxxxxxxxxx’; |
@XuHuaiyu Refer to the following attachment for the execution results： |
|
The explain result looks strange: mysql> explain analyze UPDATE order_center_order a SET a.UPDATED_BY = 'mii',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 = 'P2310270000018';
+-------------------+---------+---------+------+--------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------+---------+---------+------+--------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------+------+
| Update_11 | N/A | 0 | root | | time:781.5µs, loops:1, RU:1.969026 | N/A | 39.2 KB | N/A |
| └─Point_Get_1 | 1.00 | 1 | root | table:order_center_order, clustered index:PRIMARY(ORDER_CENTER_ORDER_ID) | time:451.7µs, loops:2, Get:{num_rpc:1, total_time:370µs}, total_process_time: 81.7µs, total_wait_time: 50.8µs, tikv_wall_time: 178.1µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 665, total_keys: 1, get_snapshot_time: 12.4µs, rocksdb: {block: {cache_hit_count: 5}}} | | N/A | N/A |
+-------------------+---------+---------+------+--------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------+------+
2 rows in set (0.00 sec) |
|
Can you provide the plan replay dump file? Thus we can reproduce this problem locally. |
|
Thiis is the plan replay dump file of executing the command "plan replayer dump explain UPDATE order_center_order a SET a.UPDATED_BY = 'mii',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 = 'P2310270000018'" |
|
|
|
we encountered this problem again, in the last analyzing process, there is a remained question:
through two part, we simple use part-1's schema to rewrite part-2 expression tree, once there is a because point-get schema is built directly out and not followed and allocated from the unique id
|
|
#47454 currently we quickly banned it from try point get |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Bug Report
1. Minimal reproduce step (Required)
Refer to the following link for complete reproduction steps:
jump to detail
2. What did you expect to see? (Required)
Summary data of detail table
3. What did you see instead (Required)
value = 0 after update
4. What is your TiDB version? (Required)
Release Version: v7.1.2
Edition: Community
Git Commit Hash: aa6ed99
Git Branch: heads/refs/tags/v7.1.2
UTC Build Time: 2023-10-21 07:46:04
GoVersion: go1.20.10
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
The text was updated successfully, but these errors were encountered: