You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jestan Nirojan <je...@gmail.com> on 2019/05/14 17:25:55 UTC
COALESCE Function Not Working With NULL Values
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
Re: COALESCE Function Not Working With NULL Values
Posted by William Shen <wi...@marinsoftware.com>.
Hi,
You can find more release-related discussion on the dev mailing list.
There're ongoing discussion and planning around the next 5.x release and a
4.14.x patch release in the work.
On Tue, May 21, 2019 at 12:45 AM lishuang0826 <li...@gmail.com>
wrote:
> Hi,
>
> I wonder what will the phoenix roadmap be since it has been nearly a year
> since the last office release publised.
>
> Thanks
>
>
> On 05/15/2019 13:03，Francis Chuang<fr...@apache.org>
> <fr...@apache.org> wrote：
>
> Due to the incompatibility, you will need to wait for a new release in
> the 5.x branch to get 5.x working with HBase 2.0.x.
>
> Phoenix 5.0.0 is also only compatible with 2.0.0 (found this out a few
> months ago) as PHOENIX-4826[1] adds support for HBase 2.0.1, but is
> currently unreleased.
>
> My suggestion would be to wait for the 5.1.0 release if you're able to.
> If you really need to deploy something right now, I'd suggest using the
> latest version of the 4.x.x branch (4.14.1) and HBase 1.4.x.
>
> Francis
>
> [1] https://issues.apache.org/jira/browse/PHOENIX-4826
>
> On 15/05/2019 2:50 pm, Jestan Nirojan wrote:
>
> Hi Jaanai,
>
> Sorry I could not understand much from
> https://issues.apache.org/jira/browse/PHOENIX-5268
> Because of this recent change, there will not be a Phoenix release for
> HBase 2.0.x in future Or there is an existing compatibility issue ?
> What is the Phoenix version recommended for a new deployment ? :) ,
>
> thanks and regards,
> -Jestan Nirojan
>
> On Wed, May 15, 2019 at 7:04 AM Jaanai Zhang <cloud.poster@gmail.com
> <ma...@gmail.com>> wrote:
>
> Hi, Jestan
>
> Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
> https://issues.apache.org/jira/browse/PHOENIX-5268
>
> ----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
> Jestan Nirojan <jestannirojan@gmail.com
> <ma...@gmail.com>> 于2019年5月15日周三 上午5:04写道：
>
> 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 <ma...@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
> <ma...@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
> <ma...@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
>
>
Re: COALESCE Function Not Working With NULL Values
Posted by lishuang0826 <li...@gmail.com>.
Hi,
I wonder what will the phoenix roadmap be since it has been nearly a year
since the last office release publised.
Thanks
On 05/15/2019 13:03，[Francis
Chuang<fr...@apache.org>](mailto:francischuang@apache.org) wrote：
> Due to the incompatibility, you will need to wait for a new release in
> the 5.x branch to get 5.x working with HBase 2.0.x.
>
> Phoenix 5.0.0 is also only compatible with 2.0.0 (found this out a few
> months ago) as PHOENIX-4826[1] adds support for HBase 2.0.1, but is
> currently unreleased.
>
> My suggestion would be to wait for the 5.1.0 release if you're able to.
> If you really need to deploy something right now, I'd suggest using the
> latest version of the 4.x.x branch (4.14.1) and HBase 1.4.x.
>
> Francis
>
> [1] https://issues.apache.org/jira/browse/PHOENIX-4826
>
> On 15/05/2019 2:50 pm, Jestan Nirojan wrote:
>
>
>> Hi Jaanai,
>
> Sorry I could not understand much from
> https://issues.apache.org/jira/browse/PHOENIX-5268
> Because of this recent change, there will not be a Phoenix release for
> HBase 2.0.x in future Or there is an existing compatibility issue ?
> What is the Phoenix version recommended for a new deployment ? :) ,
>
> thanks and regards,
> -Jestan Nirojan
>
> On Wed, May 15, 2019 at 7:04 AM Jaanai Zhang <cloud.poster@gmail.com
> <ma...@gmail.com>> wrote:
>
> Hi, Jestan
>
> Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
> https://issues.apache.org/jira/browse/PHOENIX-5268
>
> \----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
> Jestan Nirojan <jestannirojan@gmail.com
> <ma...@gmail.com>> 于2019年5月15日周三 上午5:04写道：
>
> 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 <ma...@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
> <ma...@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
> <ma...@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
>
>
Re: COALESCE Function Not Working With NULL Values
Posted by Jestan Nirojan <je...@gmail.com>.
Hi Francis/Jaanai
Thank you, I was about to start a new deployment, I will stick with 4.14.1
and HBase 1.4.x
On Wed, May 15, 2019 at 10:33 AM Francis Chuang <fr...@apache.org>
wrote:
> Due to the incompatibility, you will need to wait for a new release in
> the 5.x branch to get 5.x working with HBase 2.0.x.
>
> Phoenix 5.0.0 is also only compatible with 2.0.0 (found this out a few
> months ago) as PHOENIX-4826[1] adds support for HBase 2.0.1, but is
> currently unreleased.
>
> My suggestion would be to wait for the 5.1.0 release if you're able to.
> If you really need to deploy something right now, I'd suggest using the
> latest version of the 4.x.x branch (4.14.1) and HBase 1.4.x.
>
> Francis
>
> [1] https://issues.apache.org/jira/browse/PHOENIX-4826
>
> On 15/05/2019 2:50 pm, Jestan Nirojan wrote:
> > Hi Jaanai,
> >
> > Sorry I could not understand much from
> > https://issues.apache.org/jira/browse/PHOENIX-5268
> > Because of this recent change, there will not be a Phoenix release for
> > HBase 2.0.x in future Or there is an existing compatibility issue ?
> > What is the Phoenix version recommended for a new deployment ? :) ,
> >
> > thanks and regards,
> > -Jestan Nirojan
> >
> > On Wed, May 15, 2019 at 7:04 AM Jaanai Zhang <cloud.poster@gmail.com
> > <ma...@gmail.com>> wrote:
> >
> > Hi, Jestan
> >
> > Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
> > https://issues.apache.org/jira/browse/PHOENIX-5268
> >
> > ----------------------------------------
> > Jaanai Zhang
> > Best regards!
> >
> >
> >
> > Jestan Nirojan <jestannirojan@gmail.com
> > <ma...@gmail.com>> 于2019年5月15日周三 上午5:04写道：
> >
> > 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 <ma...@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
> > <ma...@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
> > <ma...@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
> >
>
Re: COALESCE Function Not Working With NULL Values
Posted by Francis Chuang <fr...@apache.org>.
Due to the incompatibility, you will need to wait for a new release in
the 5.x branch to get 5.x working with HBase 2.0.x.
Phoenix 5.0.0 is also only compatible with 2.0.0 (found this out a few
months ago) as PHOENIX-4826[1] adds support for HBase 2.0.1, but is
currently unreleased.
My suggestion would be to wait for the 5.1.0 release if you're able to.
If you really need to deploy something right now, I'd suggest using the
latest version of the 4.x.x branch (4.14.1) and HBase 1.4.x.
Francis
[1] https://issues.apache.org/jira/browse/PHOENIX-4826
On 15/05/2019 2:50 pm, Jestan Nirojan wrote:
> Hi Jaanai,
>
> Sorry I could not understand much from
> https://issues.apache.org/jira/browse/PHOENIX-5268
> Because of this recent change, there will not be a Phoenix release for
> HBase 2.0.x in future Or there is an existing compatibility issue ?
> What is the Phoenix version recommended for a new deployment ? :) ,
>
> thanks and regards,
> -Jestan Nirojan
>
> On Wed, May 15, 2019 at 7:04 AM Jaanai Zhang <cloud.poster@gmail.com
> <ma...@gmail.com>> wrote:
>
> Hi, Jestan
>
> Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
> https://issues.apache.org/jira/browse/PHOENIX-5268
>
> ----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
> Jestan Nirojan <jestannirojan@gmail.com
> <ma...@gmail.com>> 于2019年5月15日周三 上午5:04写道：
>
> 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 <ma...@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
> <ma...@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
> <ma...@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
>
Re: COALESCE Function Not Working With NULL Values
Posted by Jestan Nirojan <je...@gmail.com>.
Hi Jaanai,
Sorry I could not understand much from
https://issues.apache.org/jira/browse/PHOENIX-5268
Because of this recent change, there will not be a Phoenix release for
HBase 2.0.x in future Or there is an existing compatibility issue ?
What is the Phoenix version recommended for a new deployment ? :) ,
thanks and regards,
-Jestan Nirojan
On Wed, May 15, 2019 at 7:04 AM Jaanai Zhang <cl...@gmail.com> wrote:
> Hi, Jestan
>
> Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
> https://issues.apache.org/jira/browse/PHOENIX-5268
>
> ----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
> Jestan Nirojan <je...@gmail.com> 于2019年5月15日周三 上午5:04写道：
>
>> 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 <wi...@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
>>>>>
>>>>
Re: COALESCE Function Not Working With NULL Values
Posted by Jaanai Zhang <cl...@gmail.com>.
Hi, Jestan
Now Phoenix 5.0.0 is not compatible with HBase 2.0.5,
https://issues.apache.org/jira/browse/PHOENIX-5268
----------------------------------------
Jaanai Zhang
Best regards!
Jestan Nirojan <je...@gmail.com> 于2019年5月15日周三 上午5:04写道：
> 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 <wi...@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 <wi...@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 <je...@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
>>>>
>>>
Re: COALESCE Function Not Working With NULL Values
Posted by Jestan Nirojan <je...@gmail.com>.
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 <wi...@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 <wi...@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 <je...@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
>>>
>>
Re: COALESCE Function Not Working With NULL Values
Posted by William Shen <wi...@marinsoftware.com>.
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 <wi...@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 <je...@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
>>
>
Re: COALESCE Function Not Working With NULL Values
Posted by William Shen <wi...@marinsoftware.com>.
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 <je...@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
>