COALESCE Function Not Working With NULL Values

[Jestan Nirojan]

Hi,

I am trying to use COALESCE function to handle default value in WHERE
condition like below.

select  * from table1 where created_date >= coalesce(null, trunc(now(),
'day'));

But it throws NullPointerException

Caused by: java.lang.NullPointerException
at org.apache.phoenix.schema.types.PDataType.equalsAny(PDataType.java:326)
at org.apache.phoenix.schema.types.PDate.isCoercibleTo(PDate.java:111)
at
org.apache.phoenix.expression.function.CoalesceFunction.<init>(CoalesceFunction.java:68)
... 47 more

I was able to reproduce the same error with following query

select coalesce(null, now()) as date;

Here are some other variant of same issue

1. select coalesce(now(), now()) as date; // returns 2019-05-14
2. select coalesce(now(), null) as date; // returns empty
3. select coalesce(null, now()) as date; // throws exception

I have tried the same for INT and VARCHAR, same outcome
Am I doing something wrong here or is coalesce suppose to return a non null
value ?

thanks and regards,
-Jestan Nirojan