phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jestan Nirojan <jestanniro...@gmail.com>
Subject Re: COALESCE Function Not Working With NULL Values
Date Tue, 14 May 2019 21:03:47 GMT
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 <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
>>>
>>

Mime
View raw message