Impala incompatible return types in case when statement

I am running an Impala query and try to use a case when statement:

SELECT *,
CASE WHEN typ_prtctn  = 2 then  0.5*PRTCTN_ALLCTD_VL  -- life insurance policies pledged
WHEN typ_prtctn  = 18 then  2*PRTCTN_ALLCTD_VL  -- equity and investment funds shares
ELSE PRTCTN_ALLCTD_VL END as PROTECTION_VALUE
FROM database.tablename

It complains

AnalysisException: Incompatible return types 'DECIMAL(38,9)' and 'DECIMAL(38,10)' of exprs '0.5 * PRTCTN_ALLCTD_VL' and 'PRTCTN_ALLCTD_VL'.

This however works fine:

SELECT *,
0.5*PRTCTN_ALLCTD_VL as test  
FROM database.tablename

As the error message indicates, PRTCTN_ALLCTD_VL is of type decimal(38,10). Any advice is appreciated

Answer

This is a curious problem, one that I would not expect. A case expression returns a single type so all the conditions have to converted to the same time within the expression.

You can fix the problem with explicit type casts. I’m not sure what the type should be, so I’ll guess:

(CASE WHEN typ_prtctn  = 2 
      THEN CAST(0.5 * PRTCTN_ALLCTD_VL AS DECIMAL(38, 10)) -- life insurance policies pledged
      WHEN typ_prtctn  = 18 
      THEN CAST(2 * PRTCTN_ALLCTD_VL AS DECIMAL(38, 10))  -- equity and investment funds shares
      ELSE CAST(PRTCTN_ALLCTD_VL as DECIMAL(38, 10))
 END) as PROTECTION_VALUE

I suspect this is happening because 38 is the maximum precision. The multiplications are probably changing the precision/scale and because the value already has the maximum precision, there is a problem. I would not expect this to generally happen with decimals.