Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-86

Coalesce does not work in a where clause with null values.

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.3
    • Fix Version/s: None
    • Labels:
      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

        Attachments

        1. coalesce.diff
          7 kB
          James Taylor

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              jviolettedsiq James Violette
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: