Doris CASE WHEN 嵌套 IF 查询精度丢失情况

Question

Doris 版本

doris: 3.1.4

发现问题

原始字段类型: DECIMAL(18,2)

SELECT t1.proj_md_code AS proj_md_code
      ,t1.proj_code AS proj_code
      ,t1.proj_name AS proj_name
      ,t1.curr_cycle AS curr_cycle
      ,t1.cny_code AS cny_code
      ,t1.cny_name AS cny_name
      ,t1.org_code AS org_code
      ,t1.org_name AS org_name
      ,t1.lv3_corp_code AS lv3_corp_code
      ,t1.lv3_corp_name AS lv3_corp_name
      ,t1.lv3_corp_shortname AS lv3_corp_shortname
      ,t1.invest_spot AS invest_spot
      ,t1.list_is_tax_included_code AS list_is_tax_included_code
      ,t1.list_is_tax_included_name AS list_is_tax_included_name
      ,hl.weight_exchange_rate AS weight_exchange_rate
      
      , t1.month_qtd_const_output_val as 原始值
      ,CASE WHEN t1.list_is_tax_included_code = '1' THEN IF(t1.cny_code = '156', '正确', '错误') END AS cccc2
      ,CASE WHEN t1.list_is_tax_included_code = '1' 
                 THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val, t1.month_qtd_const_output_val * hl.weight_exchange_rate)
            WHEN t1.list_is_tax_included_code = '0'
                 THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val_excl_tax, t1.month_qtd_const_output_val_excl_tax * hl.weight_exchange_rate)
            END AS 'CASE_WHEN_IF判断值丢失精度'

      ,CASE WHEN t1.list_is_tax_included_code = '1' 
                 THEN (case when t1.cny_code = '156' then t1.month_qtd_const_output_val else t1.month_qtd_const_output_val * hl.weight_exchange_rate end) 
            WHEN t1.list_is_tax_included_code = '0' 
                 THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val_excl_tax, t1.month_qtd_const_output_val_excl_tax * hl.weight_exchange_rate) 
            END AS 'CASE_WHEN_嵌套CASE_WHEN_正常'
FROM dws.dws_v_dcsp_const_proj_output_val_monthly t1
LEFT JOIN dim.dim_v_fun_dcsp_weight_exchange_rate hl
     ON t1.curr_cycle = hl.curr_cycle
where  lv3_corp_code = '50301'  and invest_spot = '现汇' and proj_code = '73501'
order by curr_cycle

使用临时数据无法复现

select *
        ,CASE WHEN t1.list_is_tax_included_code = '1' 
                   THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val, t1.month_qtd_const_output_val * hl.weight_exchange_rate)
              WHEN t1.list_is_tax_included_code = '0'
                   THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val_excl_tax, t1.month_qtd_const_output_val_excl_tax * hl.weight_exchange_rate)
              END AS 'CASE_WHEN_IF判断值丢失精度_无法复现'
        , CASE WHEN t1.list_is_tax_included_code = '1' 
                    THEN (case when t1.cny_code = '156' then t1.month_qtd_const_output_val else t1.month_qtd_const_output_val * hl.weight_exchange_rate end) 
               WHEN t1.list_is_tax_included_code = '0' 
                    THEN IF(t1.cny_code = '156', t1.month_qtd_const_output_val_excl_tax, t1.month_qtd_const_output_val_excl_tax * hl.weight_exchange_rate) 
          END AS 'CASE_WHEN_嵌套CASE_WHEN_正常'
FROM (
     select 1 as no
           ,'202608' as curr_cycle
           ,'1' as list_is_tax_included_code
           ,'156'as cny_code
           ,100.98 as month_qtd_const_output_val
           ,10 as month_qtd_const_output_val_excl_tax
     union all
     select 2 as no
           ,'202608' as curr_cycle
           ,'1' as list_is_tax_included_code
           ,'156'as cny_code
           ,10500155.29 as month_qtd_const_output_val
           ,10 as month_qtd_const_output_val_excl_tax
     ) t1
left join (
     select '202608' as curr_cycle
           ,7.116285 as weight_exchange_rate
    ) hl
    ON t1.curr_cycle = hl.curr_cycle

阿渊@SelectDB (没回帖直接加我主页微信) · Accepted Answer

浮点数类型本身就丢失精度，SELECT cast(xxxx.x AS float) 这种属于正常现象
从提供的explain和其他信息结合分析，目前是 if(cond,decimal(38.2),decimal(38.2) * decimal(38.6)) ，decimal 被cast成了float 导致精度丢失。但是换成 case when是正常的，所以问题是在这里，但是不好复现。
目前这块在排查中，后续有结论进一步同步～

同步后续处理结论：
当前的排查结论是，if 和 case when 这两种方式，由于视图实际上内部都有SUM，两个字段都是 decimal(38,x)， Decimal SUM的返回 precision 扩到 76，也就是(76,x)，一个是 DECIMAL(76,2)，一个是DECIMAL(76,6)，超过了76 后，当前的逻辑来看会推导成公共类型，if() 推导成了float ，case when 推导成了 double。在这个版本上，即使开启decimal256 也容不下这两个声明类型的无损公共类型，所以当时测试开启decimal256 也无用。

目前的解决方案是，手动cast 转成 decimal 类型，手动转换类型保证精度。在4版本上这个问题有优化，开启 decimal256后，这个场景下依然能保证推导位decimal 类型。

如果是4版本，那么设置以下参数即可：

Doris CASE WHEN 嵌套 IF 查询精度丢失情况

Doris 版本

发现问题

使用临时数据无法复现

1 Answers