Skip to Content
0
Jul 01, 2015 at 09:42 PM

Question about ifnull() function

68 Views

Dear colleague,

I am confused about ifnull() function

According to documentation,

IFNULL ( expression1, expression2 [ , expression3 ] )

Parameters

expression1 The expression to be evaluated. Its value determines whether expression2 or expression3 is returned.

expression2 The return value if expression1 is NULL.

expression3 The return value if expression1 is not NULL.

But when I test with it, if expression1 is not NULL, the returned value is the expression1 itself.

Here is my test ccl file:

**********************

CREATE INPUT STREAM inString SCHEMA (Col1 string);

CREATE OUTPUT STREAM outString

as select inString.Col1 Col1,

ifnull(inString.Col1, 'null value', 'NOT null value') Col2

from inString;

CREATE INPUT STREAM inInteger SCHEMA (Col1 integer);

CREATE OUTPUT STREAM outInteger

as select inInteger.Col1 Col1,

ifnull(inInteger.Col1, 0, 2) Col2

from inInteger;

**********************

It does not seem correct to me:

with integer type

Col1 Col2 500 500 NULL 0

when the expression to be evaluated is not NULL (in this case, 500), the output should be the third expression (2), not the evaluated expression itself.

With string type:

Col1 Col2 string string NULL null value

when the expression to be evaluated is not NULL (in this case, ‘string’), the output should be the third expression (‘NOT null value’), not the evaluated expression itself.

Could you please help to clarify?

Thanks,

Tao