同一个SQL查询视图表多次查询返回结果不同

Question

查询的表是视图表

dws.dws_v_dcsp_const_proj_output_val_daily 这是一个视图表, 有多个视图表合成的

同一个SQL, 查询多次, 返回数据不一致

MySQL [(none)]>  select sum(
    ->       case when list_is_tax_included_code=1 then nvl(month_plan_const_output_val,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end)*0.0001
    ->              else nvl(month_plan_const_output_val_excl_tax,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end) * 0.0001 end
    ->              ) as ybz_byjh
    ->  from  dws.dws_v_dcsp_const_proj_output_val_daily
    ->  where curr_cycle='2025-03-01' and lv3_corp_shortname='三公司';


+--------------------+
| ybz_byjh           |
+--------------------+
| 60548.577694000000 |
+--------------------+
1 row in set (5.125 sec)

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>  select sum(
    ->       case when list_is_tax_included_code=1 then nvl(month_plan_const_output_val,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end)*0.0001
    ->              else nvl(month_plan_const_output_val_excl_tax,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end) * 0.0001 end
    ->              ) as ybz_byjh
    ->  from  dws.dws_v_dcsp_const_proj_output_val_daily
    ->  where curr_cycle='2025-03-01' and lv3_corp_shortname='三公司';


+--------------------+
| ybz_byjh           |
+--------------------+
| 56613.599494000000 |
+--------------------+
1 row in set (4.481 sec)

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>  select sum(
    ->       case when list_is_tax_included_code=1 then nvl(month_plan_const_output_val,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end)*0.0001
    ->              else nvl(month_plan_const_output_val_excl_tax,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end) * 0.0001 end
    ->              ) as ybz_byjh
    ->  from  dws.dws_v_dcsp_const_proj_output_val_daily
    ->  where curr_cycle='2025-03-01' and lv3_corp_shortname='三公司';


+--------------------+
| ybz_byjh           |
+--------------------+
| 65378.577694000000 |
+--------------------+
1 row in set (4.173 sec)

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>  select sum(
    ->       case when list_is_tax_included_code=1 then nvl(month_plan_const_output_val,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end)*0.0001
    ->              else nvl(month_plan_const_output_val_excl_tax,0) * (case when cny_code='156' THEN 1 ELSE 7.116285 end) * 0.0001 end
    ->              ) as ybz_byjh
    ->  from  dws.dws_v_dcsp_const_proj_output_val_daily
    ->  where curr_cycle='2025-03-01' and lv3_corp_shortname='三公司';


+--------------------+
| ybz_byjh           |
+--------------------+
| 61713.599494000000 |
+--------------------+
1 row in set (4.036 sec)

MySQL [(none)]>

zoro · Accepted Answer

show variables like '%local%'; 看下 experimental_enable_local_shuffle 值呢，之前也遇到的查询结果不一致的问题，社区同学让把这个值设置成false就好了，默认是true

徐振超@SelectDB · Answer

可以先按照这个文档排查下，有问题可以继续沟通
https://mp.weixin.qq.com/s/_b6D0rEDvC0yydWHJnP5Ag

XQG · Answer

经过排查表中的总条数, 不论查询几次, 都是一致, 符合理论的结果

select count(1) from dim.dim_v_dcsp_const_proj_stage;
select count(1) from dws.dws_v_dcsp_const_proj_info;
select count(1) from dwd.dwd_v_dcsp_const_proj_output_val_daily;
select count(1) from dwd.dwd_v_dcsp_const_proj_plan_output_val_quarterly;
SELECT count(1) FROM dwd.dwd_v_dcsp_const_proj_plan_output_val_yearly;
SELECT count(1) FROM dwd.dwd_v_dcsp_const_proj_output_val_monthly;
SELECT count(1) FROM dwd.dwd_v_dcsp_const_proj_work_output_val_daily;
SELECT count(1) FROM dwd.dwd_v_dcsp_const_proj_work_plan_output_val_quarterly;
select count(1) from dwd.dwd_v_dcsp_const_proj_work_plan_output_val_yearly;
select count(1) from dwd.dwd_v_dcsp_const_proj_work_output_val_monthly;

select count(1) from dws.dws_v_dcsp_const_proj_output_val_daily;

经过多次查询, dws.dws_v_dcsp_const_proj_output_val_daily 查询多次, 条数返回结果都是一致

select count(1) from dws.dws_v_dcsp_const_proj_output_val_daily;

非常奇怪的一个查询

select count(1) 
from dws.dws_v_dcsp_const_proj_output_val_daily 
where curr_cycle='2025-03-01' and lv3_corp_code='59417'

注: 生成 dws.dws_v_dcsp_const_proj_output_val_daily 视图表的底表中数据, 都是静止状态, 没有新的数据进来的时候, 每次查询可能有几条偏差

只要是带WHERE条件, 多次查询的返回结果条数不一致, 每次查询的结果都有几条偏差

XQG · Answer

问题发现(同一个SQL执行多次,返回数据集条数不一致)

 select *
 from  dws.dws_v_dcsp_const_proj_output_val_daily
 where curr_cycle='2025-03-01' and lv3_corp_code='59417'

同一个SQL查询视图表多次查询返回结果不同

查询的表是视图表

同一个SQL, 查询多次, 返回数据不一致

4 Answers

经过排查表中的总条数, 不论查询几次, 都是一致, 符合理论的结果

经过多次查询, dws.dws_v_dcsp_const_proj_output_val_daily 查询多次, 条数返回结果都是一致

非常奇怪的一个查询

问题发现(同一个SQL执行多次,返回数据集条数不一致)