You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "James Taylor (JIRA)" <ji...@apache.org> on 2014/02/28 07:12:19 UTC
[jira] [Resolved] (PHOENIX-86) Coalesce does not work in a where
clause with null values.
[ https://issues.apache.org/jira/browse/PHOENIX-86?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Taylor resolved PHOENIX-86.
---------------------------------
Resolution: Fixed
> 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)