Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
2.2.3
-
None
-
None
-
None
Description
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