Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 同一个查询语句，多次执行结果不一致！！！
|
username: TiDBer_rrt4FOef
[TiDB Usage Environment]
Production Environment
[TiDB Version]
V6.5.0
[Resource Configuration]
tidb: 3 nodes, pd: 3 nodes, tikv: 5 nodes
[Problem Description]
The same query statement produces inconsistent results each time it is executed!
The SQL statement is as follows:
select count(t.card_nbr) as balance from t_org t2,
(
select c.ab_user, a.card_nbr, a.active_day,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a, F_CCS_ACCT b, f_ccs_apma c
where a.xaccount = b.xaccount and a.xaccount = c.account
and b.close_code != 'W' and a.active_day <= '20230112' and a.active_day > 0
)
t where
trim(t.ab_user) = trim(t2.code) AND
t.last_card = 1;
The result fluctuates between 12504 and 12507 each time it is executed.
During testing, it was found that the result is consistent when only one condition is used, as follows:
where trim(t.ab_user) = trim(t2.code)
or
where t.last_card = 1
However, when both conditions are used together, the result fluctuates between 12504 and 12507.
Is this caused by the distributed multi-node environment? Is there any way to avoid this?
[Update]
The desensitized test data and the test SQL are as follows; the issue can be reproduced in my environment.
test.sql (1.9 MB)
Test SQL:
select count(t.xaccount) as balance from t_org t2,
(
select c.ab_user, a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a, f_ccs_apma c
where a.xaccount = c.account
)
t where
t.ab_user = t2.code and
t.last_card = 1;
Can anyone reproduce this issue in their environment?
Please help analyze what the problem might be?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: