[
https://issues.apache.org/jira/browse/PHOENIX-86?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-86:
--------------------------------
Attachment: coalesce.diff
Here's a hastily prepared patch (for master). I'll run the full unit tests and
check it in soon.
In the meantime, you should have better luck with the CASE statement using CASE
WHEN foo IS NULL THEN <second arg of coalesce> ELSE <first arg of coalesce> END
> 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
> Attachments: coalesce.diff
>
>
> 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)
