bug, 详情看代码, 打扰了, 找到问题了

Question

Doris 版本

版本: 2.1.8

Step1

第一步: 确认表中没有 htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9'相关的数据

select *
FROM ods.ods_cfhec_pro_con_htxx_bg_i_d
WHERE end_date = '2999-12-31' AND trim(del_flag) = '0' and htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9'

Step2

第二步: 直接限制WHERE条件, htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9' 查询结果是正确的

 SELECT trim(htxx_id) AS con_code
           ,trim(del_flag) as del_flag
           ,sum(zzcdzjhte) AS auto_inc_dec_con_amt_excl_tax -- 自主承担增减合同额不含税
           ,sum(bgzjzzsj) AS chg_inc_dec_auto_taxes -- 变更增减自主承担税金
     FROM ods.ods_cfhec_pro_con_htxx_bg_i_d
     WHERE end_date = '2999-12-31' AND trim(del_flag) = '0'
       and htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9'
     GROUP BY trim(htxx_id), trim(del_flag)

Step3

第三步: 当where不限制具体编码时, 查询就是错误的, 太离谱了

     SELECT trim(htxx_id) AS con_code
           ,trim(del_flag) as del_flag
           ,sum(zzcdzjhte) AS auto_inc_dec_con_amt_excl_tax -- 自主承担增减合同额不含税
           ,sum(bgzjzzsj) AS chg_inc_dec_auto_taxes -- 变更增减自主承担税金
     FROM ods.ods_cfhec_pro_con_htxx_bg_i_d
     WHERE end_date = '2999-12-31' AND trim(del_flag) = '0'
     -- and htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9'
     GROUP BY trim(htxx_id), trim(del_flag)

Step4

第四步: 正常情况, 不可能出现 htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9' 的数据, 因为FROM 表中就没有这个数据

SELECT htxx_id AS con_code
           ,del_flag as del_flag
           ,sum(zzcdzjhte) AS auto_inc_dec_con_amt_excl_tax -- 自主承担增减合同额不含税
           ,sum(bgzjzzsj) AS chg_inc_dec_auto_taxes -- 变更增减自主承担税金
     FROM ods.ods_cfhec_pro_con_htxx_bg_i_d
     WHERE end_date = '2999-12-31' AND trim(del_flag) = '0'
     -- and htxx_id = 'b1b3bdff53a54685b6acaa9215d373a9'
     GROUP BY htxx_id, del_flag

这种方式, 也是异常的结果, 查询结果不对

XQG · Answer

标题名称可能不准, 应该说是 SUM 级别的BUG

SUM计算数据结果异常, 添加具体的where htxx_id, 计算结果正确, 不加 htxx_id 计算结果, 异常多出数据

     SELECT htxx_id AS con_code
           ,SUM(zjhte) AS chg_inc_dec_con_amt_excl_tax -- 变更增减合同额不含税
           ,SUM(bgzjsj) AS chg_inc_dec_taxes -- 变更增减税金
           ,SUM(zzcdzjhte) AS auto_inc_dec_con_amt_excl_tax -- 自主承担增减合同额不含税
           ,SUM(bgzjzzsj) AS chg_inc_dec_auto_taxes -- 变更增减自主承担税金
     FROM ods.ods_cfhec_pro_con_htxx_bg_i_d
     WHERE end_date = '2999-12-31' AND trim(del_flag) = '0' -- and htxx_id= 'b1b3bdff53a54685b6acaa9215d373a9'
     GROUP BY htxx_id