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 <https://metabase.com/> (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 <wills...@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 <wills...@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 <jestanniro...@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 >>> >>
