[jira] [Created] (PHOENIX-86) Coalesce does not work in a where clause with null values.

["James Violette (JIRA)" <ji...@apache.org>]

James Violette created PHOENIX-86:
-------------------------------------

             Summary: Coalesce does not work in a where clause with null values.
                 Key: PHOENIX-86
                 URL: https://issues.apache.org/jira/browse/PHOENIX-86
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 2.2.3
            Reporter: James Violette


Coalesce works as a select expression with null values, but does not work in a where clause with null values. (v2.2.3)
Workaround is to add a value to all null columns, at increased cost in space.

create TABLE IF NOT EXISTS MY_SCHEMA.MY_TABLE
 (NAME varchar not null,
  D.CAT VARCHAR,
  S.ACTIVE INTEGER,
  S.LSTATUS VARCHAR,
  S.LTIME TIME,
  S.EXPTIME TIME 
 CONSTRAINT pk PRIMARY KEY (NAME));	

items.csv
NAME,CAT,ACTIVE,LSTATUS
ITEM1,NOTSET,1,NEVER
ITEM2,NOTSET,1,NEVER
ITEM3,NOTSET,1,NEVER
ITEM4,NOTSET,1,NEVER
ITEM5,NOTSET,1,NEVER

./psql.sh -t MY_SCHEMA.MY_TABLE -h in-line zookeeper items.csv 

-- at this point, all items have null exptime.

UPSERT INTO MY_SCHEMA.MY_TABLE(NAME,LSTATUS,LTIME,EXPTIME) VALUES('ITEM1','NEVER',CURRENT_DATE(),CURRENT_DATE()+5);
UPSERT INTO MY_SCHEMA.MY_TABLE(NAME,LSTATUS,LTIME,EXPTIME) VALUES('ITEM2','NEVER',CURRENT_DATE(),CURRENT_DATE()+10);
UPSERT INTO MY_SCHEMA.MY_TABLE(NAME,LSTATUS,LTIME,EXPTIME) VALUES('ITEM3','NEVER',CURRENT_DATE(),CURRENT_DATE()-10);

-- at this point, item1,item2,item3 have non-null exptime, and item4,item5 have null exptime

SELECT NAME,CAT,LSTATUS,ACTIVE, EXPTIME, COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00')) as cexptime, current_date() as cdate
,COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00'))-current_date() as DIFF  FROM COLLECT_PLACE.FS_CHAIN_NAMES ;

Should show item1...item5
Actually shows item1...item5

NAME	CAT	LSTATUS	ACTIVE	EXPTIME	CEXPTIME	CDATE	DIFF
item1	NOTSET	RESERVED	1	17:38:35	17:38:35	2014-02-27	9
item2	NOTSET	RESERVED	1	16:38:03	16:38:03	2014-02-27	4
item3	NOTSET	NEVER	1	16:48:14	16:48:14	2014-02-27	-10
item4	NOTSET	NEVER	1	(null)	16:00:00	2014-02-27	-5172
item5	NOTSET	NEVER	1	(null)	16:00:00	2014-02-27	-5172
 
SELECT NAME,CAT,LSTATUS,ACTIVE, EXPTIME, COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00')) as cexptime, current_date() as cdate
,COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00'))-current_date() as DIFF  FROM COLLECT_PLACE.FS_CHAIN_NAMES 
WHERE (current_date()>COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00')));

Should show item3,item4,item5
Actually shows item3, missing item4,item5

NAME	CAT	LSTATUS	ACTIVE	EXPTIME	CEXPTIME	CDATE	DIFF
item3	NOTSET	NEVER	1	16:48:14	16:48:14	2014-02-27	-10

SELECT NAME,CAT,LSTATUS,ACTIVE, EXPTIME, COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00')) as cexptime, current_date() as cdate
,COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00'))-current_date() as DIFF  FROM COLLECT_PLACE.FS_CHAIN_NAMES 
WHERE (current_date()<COALESCE(EXPTIME, TO_DATE('2000-01-01 00:00:00')));

Should show item1,item2
Actually shows item1,item2

NAME	CAT	LSTATUS	ACTIVE	EXPTIME	CEXPTIME	CDATE	DIFF
item1	NOTSET	RESERVED	1	17:38:35	17:38:35	2014-02-27	9
item2	NOTSET	RESERVED	1	16:38:03	16:38:03	2014-02-27	4




--
This message was sent by Atlassian JIRA
(v6.1.5#6160)