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)
