Hi William,

Thanks, It is working with coalesce(functionThatMightReturnNull(), now()) without an explicit null;
 
Phoenix Version is 5.0.0.0 which uses HBase 2.0.5 
I have not opened any issue for this, I am not sure how it is suppose to work.

I am developing  a phoenix driver for metabase (which is a BI/DataViz tool).
It seems for optional query parameter, null values are directly set by the base metabase driver which I am trying to extend.

I wish if phoenix can support explicit null values.

thanks and regards,
-Jestan


On Tue, May 14, 2019 at 11:52 PM William Shen <willshen@marinsoftware.com> wrote:
Just took a look at the implementation, seems like Phoenix relies on the first expression to not be an expression that is not just an explicit "null" because it needs to evaluate for data type coercion. What's the use case for specifying an explicit null? 

On the other hand, the following should work: 
select coalesce(functionThatMightReturnNull(), now()) as date; 

On Tue, May 14, 2019 at 11:14 AM William Shen <willshen@marinsoftware.com> wrote:
Jestan,
It seems like a bug to me. What version of Phoenix are you using, and did you create a ticket already?

On Tue, May 14, 2019 at 10:26 AM Jestan Nirojan <jestannirojan@gmail.com> wrote:
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