同一个查询语句,多次执行结果不一致!!!

【 TiDB 使用环境】
生产环境
【 TiDB 版本】
V6.5.0
【资源配置】
tidb:3节点,pd:3节点,tivk:5节点
【问题描述】
同一个查询语句,每次执行结果都不一致!
sql语句如下:

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;

每次执行结果都不一致,结果在1250412507之间来回变动。
测试的时候 发现 只带1个条件时,结果是固定的,如下:

where  trim(t.ab_user)=trim(t2.code)
或者
where  t.last_card = 1

但是两个条件一起时,结果就开始在 1250412507之间来回变动
是因为分布式多节点环境导致的吗?有没有办法避免?

【更新】
脱敏后的测试数据和测试用的sql如下;在我的环境里能复现问题。
test.sql (1.9 MB)

测试用的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
;

各位大佬看看在自己的环境里能不能复现?
帮忙分析一下是什么问题?

用了tiflash吗,执行计划贴下

没有部署tiflash
执行计划:

mysql>  explain 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;
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                           | estRows  | task      | access object                    | operator info                                                                                                                                                                               |
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_29                                   | 1.00     | root      |                                  | funcs:count(busidata.f_ccs_card.card_nbr)->Column#600                                                                                                                                       |
| └─HashJoin_33                                | 12633.19 | root      |                                  | inner join, equal:[eq(Column#601, Column#602)]                                                                                                                                              |
|   ├─Projection_34(Build)                     | 71.00    | root      |                                  | trim(dgb-datamid.t_org.code)->Column#601                                                                                                                                                    |
|   │ └─TableReader_36                         | 71.00    | root      |                                  | data:TableFullScan_35                                                                                                                                                                       |
|   │   └─TableFullScan_35                     | 71.00    | cop[tikv] | table:t_org                      | keep order:false, stats:pseudo                                                                                                                                                              |
|   └─Projection_37(Probe)                     | 10106.55 | root      |                                  | busidata.f_ccs_card.card_nbr, trim(busidata.f_ccs_apma.ab_user)->Column#602                                                                                                                 |
|     └─Selection_38                           | 10106.55 | root      |                                  | eq(Column#595, 1)                                                                                                                                                                           |
|       └─Shuffle_69                           | 12633.19 | root      |                                  | execution info: concurrency:5, data sources:[HashJoin_41]                                                                                                                                   |
|         └─Window_39                          | 12633.19 | root      |                                  | row_number()->Column#595 over(partition by busidata.f_ccs_card.xaccount order by busidata.f_ccs_card.cdindex desc rows between current row and current row)                                 |
|           └─Sort_68                          | 12633.19 | root      |                                  | busidata.f_ccs_card.xaccount, busidata.f_ccs_card.cdindex:desc                                                                                                                              |
|             └─HashJoin_41                    | 12633.19 | root      |                                  | inner join, equal:[eq(busidata.f_ccs_card.xaccount, busidata.f_ccs_apma.account)]                                                                                                           |
|               ├─IndexJoin_46(Build)          | 10349.89 | root      |                                  | inner join, inner:IndexLookUp_45, outer key:busidata.f_ccs_card.xaccount, inner key:busidata.f_ccs_acct.xaccount, equal cond:eq(busidata.f_ccs_card.xaccount, busidata.f_ccs_acct.xaccount) |
|               │ ├─TableReader_60(Build)      | 10310.32 | root      |                                  | data:Selection_59                                                                                                                                                                           |
|               │ │ └─Selection_59              | 10310.32 | cop[tikv] |                                  | gt(busidata.f_ccs_card.active_day, 0), le(busidata.f_ccs_card.active_day, 20230112)                                                                                                         |
|               │ │   └─TableFullScan_58        | 16546.00 | cop[tikv] | table:a                          | keep order:false                                                                                                                                                                            |
|               │ └─IndexLookUp_45(Probe)      | 10310.32 | root      |                                  |                                                                                                                                                                                             |
|               │   ├─IndexRangeScan_42(Build) | 10310.32 | cop[tikv] | table:b, index:PRIMARY(XACCOUNT) | range: decided by [eq(busidata.f_ccs_acct.xaccount, busidata.f_ccs_card.xaccount)], keep order:false                                                                                        |
|               │   └─Selection_44(Probe)      | 10310.32 | cop[tikv] |                                  | ne(busidata.f_ccs_acct.close_code, "W ")                                                                                                                                                    |
|               │     └─TableRowIDScan_43      | 10310.32 | cop[tikv] | table:b                          | keep order:false                                                                                                                                                                            |
|               └─TableReader_66(Probe)        | 20040.00 | root      |                                  | data:Selection_65                                                                                                                                                                           |
|                 └─Selection_65               | 20040.00 | cop[tikv] |                                  | not(isnull(busidata.f_ccs_apma.account))                                                                                                                                                    |
|                   └─TableFullScan_64         | 20040.00 | cop[tikv] | table:c                          | keep order:false                                                                                                                                                                            |
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set

能提供表结构和示例数据么

看看能不能复现

是分区表吗

Cdindex是不是有重复数据

所有的表都不是分区表,t_org 是一个视图,指向另一个数据库中的表

cdindex字段是有重复数据

因为有敏感数据,所以表结构和实例数据没办法提供
我正在想办法创建几张类似的表,看看能不能复现

脱敏后的测试数据如下:
test.sql (1.9 MB)

测试的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
;

我在测试库上执行,能复现问题。
结果会在19892-19894之间变动。

你是根据cdindex排序,分布式数据库不同节点返回的数据每次的快慢导致顺序可能不一样,哪每次按cdindex排序的相同数据的数据每次出现的位置可能就不一样,每次取第一条数据可能就不太一样了,只带第一个条件的时候每次都是全量数据所以每次都一样,只带第二个每次都是t2的所有1的数据然后笛卡儿集所以每次都一样,你可以换一个唯一的条件去排序结果就一样了

又测试了一下,窗口函数中的排序字段换成一个唯一的条件去排序,还是一样的问题,结果还是会变动 :face_exhaling:

还有可能是什么问题呢?

测试结果跟排序列的数据统计方便贴下不

根据这个测试数据,最小复现是这个 sql 的结果不稳定:
select
a.xaccount,
a.cdindex,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card,
c.ab_user
from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account
order by xaccount, cdindex desc, last_card, ab_user;

但是这个不稳定是符合预期的,因为 <a.xaccount, a.cdindex> 的组合在这个 join 结果集中不是唯一的,这导致 ab_user 与 last_card 的对应关系不稳定。原始 sql 里面 join 条件中有对 ab_user 以及 last_card 得过滤/join 条件,所以导致整个 sql 结果不稳定。

关于 <a.xaccount, a.cdindex> 的组合在这个 join 结果集中不是唯一 是通过下面这个对比看出来的:
mysql> select count() from (select distinct a.xaccount, a.cdindex from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account) x;
±---------+
| count(
) |
±---------+
| 16489 |
±---------+
1 row in set (0.02 sec)

mysql> select count() from (select a.xaccount, a.cdindex from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account) x;
±---------+
| count(
) |
±---------+
| 16575 |
±---------+
1 row in set (0.02 sec)

可以看到对 a.xaccount, a.cdindex 去重之后的结果与不去重的结果不一样

这条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
;
  1. <a.xaccount, a.cdindex>的组合确实不唯一,但是窗口函数里已经指定了order by,那么
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

这个子查询的每条记录的last_card肯定是固定的,结果是一个稳定的结果集才符合预期,单独执行这个子查询,结果集也确实是固定

  1. 关联查询结果
    t2是固定,t的结果集也是固定的,两个数据固定的表做关联查询,返回固定的结果集才符合预期

  2. 和其他数据库执行结果对比
    在oracle里执行结果集稳定,在java的H2里执行结果集也稳定。
    mysql8.0之后才支持窗口函数,我这没有环境所以没有测试,谁有环境的话可以帮忙测试一下,看看结果。
    这个执行结果和传统数据库的执行结果有差异,并不符合预期

关于结果集不稳定,官方的文档
结果集不稳定 | PingCAP 文档中心

这个测试场景也不符合文档里说的所有情况。

我觉得这个场景下结果集稳定才符合预期,如果是个bug,希望能修复这个bug
如果不是bug,希望能找到不稳定的根本原因,更新到前边的官方文档里。

这个子查询输出的是
xaccount, cdindex, ab_user, last_card
这四列,而窗口函数本质上只是对 xaccount, cdindex 做了排序,所以 ab_user, last_card 的对应关系是不稳定的。例如有下面一组数据
xaccount, cdindex, ab_user
1 1 1
1 1 2
经过窗口函数之后,结果可能是
xaccount, cdindex, ab_user, last_card
1 1 1 1
1 1 1 2
也可能是
1 1 2 1
1 1 1 2
如果你在窗口函数里面除了 ORDER by a.cdindex DESC 再加上 order by ab_user 的话,应该结果就是稳定的了

1 Like

另外我说的不稳定不是指结果集条数不一样,条数是一样的,但是结果本身是不一样的,具体说就是相同的 ab_user 对应的 last_card 是不一样(不稳定)的

确实,我用下边的语句测试了一下,确实结果本身不一样

select ab_user,last_card,count(*) from 
(
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex desc
 ,c.ab_user DESC
) last_card
 from f_ccs_card a,f_ccs_apma c
   where a.xaccount = c.account
) t 
group by ab_user,last_card
order by ab_user,last_card;

T1

T2

在oracle上执行的结果是不变的
O1

经过测试窗口函数中的排序字段增加ab_user 后,结果集固定不变

是不是以后用窗口函数,所有的查询的字段都需要跟在order by 后边?类似mysql的 full group by一样?

1 Like

我觉得这就和 order by 语句一样吧,如果想得到稳定的结果,那结果集在order by的列上就不能有重复,如果没有主键,unique key之类的列保证唯一性的话,就需要把所有的列都放在order by后面。

1 Like