Search code examples
sqlhivenullimpala

Handling the NULL data in impala

I am trying understand the behavior of arithmetic operation in impala

i am using the following table

╔════╦══════════════╦══════════╗
║ id ║  name        ║ salary   ║
╠════╬══════════════╬══════════╣
║  1 ║  cde         ║ 5636     ║
║  2 ║  asd         ║  148     ║
║  3 ║  pwe         ║  null    ║
║  4 ║  lmn         ║  959     ║
╚════╩══════════════╩══════════╝

when i execute the following query

select salary+20 as sum1 from table where id=3;

it returns me

     |sum1
  ---|-----
   1 |NULL

when i run sum on the column with 

select sum(salary) as sum1 from table;

     |sum1
  ---|-----
   1 |6743

I am unable to understand how same arithmetic operations are behaving differently

Solution

  • Have you tried something like:

    select sum(COALESCE(salary,0)) as sum1 from table;

    This should ensure that the value returned is a number. Similar to leftjoin's answer. You could also do this with case statements or where statements.

    Source:

    https://www.cloudera.com/documentation/enterprise/5-4-x/topics/impala_conditional_functions.html