starrocks 查询结果不正确

Golden · 2025年12月12日 10:02

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】

建表语句：

CREATE TABLE `gjc_test_dws_invty_f` (
  `group_skey` bigint(20) NULL COMMENT "集团skey",
  `co_skey` bigint(20) NULL COMMENT "公司skey",
  `org_skey` bigint(20) NULL COMMENT "组织skey",
  `eff_end_date` bigint(20) NULL COMMENT "生效截止日期",
  `last_stkin_date` datetime NULL COMMENT "最近入库日期",
  `last_stkou_date` datetime NULL COMMENT "最近出库日期"
) ENGINE=OLAP 
DUPLICATE KEY(`group_skey`, `co_skey`, `org_skey`)
COMMENT "库存月结档"
DISTRIBUTED BY HASH(`group_skey`, `co_skey`) BUCKETS 3 
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);

数据样例：

mysql> select * from gjc_test_dws_invty_f limit 10;
+------------+---------+----------+--------------+---------------------+---------------------+
| group_skey | co_skey | org_skey | eff_end_date | last_stkin_date     | last_stkou_date     |
+------------+---------+----------+--------------+---------------------+---------------------+
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | 2025-08-25 00:00:00 | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | 2025-11-13 00:00:00 |
|          3 |       4 |      547 |     29991231 | NULL                | 2025-07-10 00:00:00 |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
|          3 |       4 |      547 |     29991231 | NULL                | NULL                |
+------------+---------+----------+--------------+---------------------+---------------------+

查询 sql：


select datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) as aaa
      ,datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))             as main_eff_days
      ,datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d'))                       as real_eff_days
      ,last_stkin_date                     
      ,last_stkou_date 
      ,eff_end_date  ,
       DATE_FORMAT(eff_end_date,'%Y-%m-%d')                                                    
from (    
  select if(length(last_stkin_date)>0,last_stkin_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkin_date                     
        ,if(length(last_stkou_date)>0,last_stkou_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkou_date 
        ,eff_end_date                                                 
  from gjc_test_dws_invty_f
)t
where datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) >5    
and datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) <=50
;

查询结果

image1402×306 142 KB

很明显 aaa 的值存在非5-50 之间的数据。

同样的表结构放
到 hive 里，用相同的 sql 查询，是没有问题的

【背景】做过哪些操作？
【业务影响】查询结果不对
【是否存算分离】否
【StarRocks版本】3.2.11 和 3.3.18 均存在此问题
【集群规模】例如：1fe+1be
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】StarRocks社区群17-Golden
【附件】
数据文件如下：
gjc_test_dws_invty_f.csv (40.9 KB)

Golden · 2025年12月12日 10:05

把以下的 sql 落成物理表，就可以规避此问题：

create table gjc_test_111 as 
select if(length(last_stkin_date)>0,last_stkin_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkin_date                     
        ,if(length(last_stkou_date)>0,last_stkou_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkou_date 
        ,eff_end_date                                                 
  from gjc_test_dws_invty_f

select datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) as aaa
      ,datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))             as main_eff_days
      ,datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d'))                       as real_eff_days
      ,last_stkin_date                     
      ,last_stkou_date 
      ,eff_end_date  ,
       DATE_FORMAT(eff_end_date,'%Y-%m-%d')                                                    
from gjc_test_111 t
where datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) >5    
and datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(last_stkin_date,'%Y-%m-%d'))-datediff(DATE_FORMAT(eff_end_date,'%Y-%m-%d'),DATE_FORMAT(now(),'%Y-%m-%d')) <=8
;

怀疑是 rank 函数的问题

Golden · 2025年12月12日 10:12

mysql> explain select datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(last_stkin_date,’%Y-%m-%d’))-datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(now(),’%Y-%m-%d’)) as aaa
-> ,datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(last_stkin_date,’%Y-%m-%d’)) as main_eff_days
-> ,datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(now(),’%Y-%m-%d’)) as real_eff_days
-> ,last_stkin_date
-> ,last_stkou_date
-> ,eff_end_date ,
-> DATE_FORMAT(eff_end_date,’%Y-%m-%d’)
-> from (
-> select if(length(last_stkin_date)>0,last_stkin_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkin_date
-> ,if(length(last_stkou_date)>0,last_stkou_date,date_sub(now(),INTERVAL floor(rand()*1000) day)) as last_stkou_date
-> ,eff_end_date
-> from gjc_test_dws_invty_f
-> )t
-> where datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(last_stkin_date,’%Y-%m-%d’))-datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(now(),’%Y-%m-%d’)) >5
-> and datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(last_stkin_date,’%Y-%m-%d’))-datediff(DATE_FORMAT(eff_end_date,’%Y-%m-%d’),DATE_FORMAT(now(),’%Y-%m-%d’)) <=8
-> ;
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:9: expr | 10: datediff | 11: datediff | 7: if | 8: if | 4: eff_end_date | 12: date_format |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| UNPARTITIONED |
| |
| 1:Project |
| | <slot 4> : 4: eff_end_date |
| | <slot 7> : 25: if |
| | <slot 8> : if(length(CAST(6: last_stkou_date AS VARCHAR)) > 0, 6: last_stkou_date, days_sub(‘2025-12-12 18:12:24’, CAST(floor(rand() * 1000.0) AS INT))) |
| | <slot 9> : CAST(28: datediff AS BIGINT) - CAST(23: datediff AS BIGINT) |
| | <slot 10> : 28: datediff |
| | <slot 11> : 23: datediff |
| | <slot 12> : 18: date_format |
| | common expressions: |
| | <slot 13> : CAST(5: last_stkin_date AS VARCHAR) |
| | <slot 14> : rand() |
| | <slot 15> : CAST(4: eff_end_date AS DATETIME) |
| | <slot 16> : length(13: cast) |
| | <slot 17> : 14: rand * 1000.0 |
| | <slot 18> : date_format(15: cast, ‘%Y-%m-%d’) |
| | <slot 19> : 16: length > 0 |
| | <slot 20> : floor(17: multiply) |
| | <slot 21> : CAST(18: date_format AS DATETIME) |
| | <slot 22> : CAST(20: floor AS INT) |
| | <slot 23> : datediff(21: cast, ‘2025-12-12 00:00:00’) |
| | <slot 24> : days_sub(‘2025-12-12 18:12:24’, 22: cast) |
| | <slot 25> : if(19: expr, 5: last_stkin_date, 24: days_sub) |
| | <slot 26> : date_format(25: if, ‘%Y-%m-%d’) |
| | <slot 27> : CAST(26: date_format AS DATETIME) |
| | <slot 28> : datediff(21: cast, 27: cast) |
| | |
| 0:OlapScanNode |
| TABLE: gjc_test_dws_invty_f |
| PREAGGREGATION: ON |
| PREDICATES: CAST(datediff(CAST(date_format(CAST(4: eff_end_date AS DATETIME), ‘%Y-%m-%d’) AS DATETIME), CAST(date_format(if(length(CAST(5: last_stkin_date AS VARCHAR)) > 0, 5: last_stkin_date, days_sub(‘2025-12-12 18:12:24’, CAST(floor(rand() * 1000.0) AS INT))), ‘%Y-%m-%d’) AS DATETIME)) AS BIGINT) - CAST(datediff(CAST(date_format(CAST(4: eff_end_date AS DATETIME), ‘%Y-%m-%d’) AS DATETIME), ‘2025-12-12 00:00:00’) AS BIGINT) > 5, CAST(datediff(CAST(date_format(CAST(4: eff_end_date AS DATETIME), ‘%Y-%m-%d’) AS DATETIME), CAST(date_format(if(length(CAST(5: last_stkin_date AS VARCHAR)) > 0, 5: last_stkin_date, days_sub(‘2025-12-12 18:12:24’, CAST(floor(rand() * 1000.0) AS INT))), ‘%Y-%m-%d’) AS DATETIME)) AS BIGINT) - CAST(datediff(CAST(date_format(CAST(4: eff_end_date AS DATETIME), ‘%Y-%m-%d’) AS DATETIME), ‘2025-12-12 00:00:00’) AS BIGINT) <= 8 |
| partitions=1/1 |
| rollup: gjc_test_dws_invty_f |
| tabletRatio=3/3 |
| tabletList=2219767,2219769,2219771 |
| cardinality=289 |
| avgRowSize=9.0 |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
52 rows in set (0.02 sec)