Seeing as we’re talking COALESCE and NULLs, depending on the version Ankit is running, this could also be the issue in PHOENIX-2994:-

 

https://issues.apache.org/jira/browse/PHOENIX-2994

 

Michael McAllister

Staff Data Warehouse Engineer | Decision Systems

mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha | webex: https://h.a/mikewebex

This electronic communication (including any attachment) is confidential.  If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.

 

From: Lukáš Lalinský <lalinsky@gmail.com>
Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org>
Date: Friday, August 12, 2016 at 4:57 AM
To: "user@phoenix.apache.org" <user@phoenix.apache.org>
Subject: Re: Phoenix Ifnull

 

I think this is a problem with the WHERE clause. NULL values are neither equal nor not-equal to any other values. You might need to add "OR API_KEY IS NULL" to the WHERE clause.

 

Lukas

 

 

On Fri, Aug 12, 2016 at 9:51 AM, ankit beohar <ankitbeohar90@gmail.com> wrote:

Hi Lukáš/James,

 

I have one table in which only one rowkey is available and for my null check case I am firing below queries and in that null check is not working:-

 

nline image 1

 

Please see and let me know if I am doing right thing or missed something.


Best Regards,

ANKIT BEOHAR

 

 

On Thu, Aug 11, 2016 at 9:18 PM, James Taylor <jamestaylor@apache.org> wrote:

Lukáš is correct, but if the CASE WHEN variant of the same didn't work, please file a JIRA (ideally with a unit test that repros the problem).

 

Thanks,

James

 

On Thu, Aug 11, 2016 at 12:20 AM, Lukáš Lalinský <lalinsky@gmail.com> wrote:

On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <ankitbeohar90@gmail.com> wrote:

I want to implement If null in apache phoenix like mysql.

 

select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where rowkey='abc';

 

I tried below query but did not work:-

 

select case when APP_KEY is null then 'nullvalue' else APP_KEY end from "CheckDump";

 

The standard SQL function for this is coalesce(col, 'nullvalue').

 

 

Lukas