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

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