Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-2765

AnalysisException: operands of type BOOLEAN and TIMESTAMP are not comparable when OUTER JOIN with CASE statement

    XMLWordPrintableJSON

    Details

      Description

      See the full test case below:

      drop table IF EXISTS main_table;
      create table main_table (
          id string,
          code string,
          dt_a timestamp,
          dt_b timestamp
      );
      
      drop table IF EXISTS code_table;
      create table code_table (
          code string,
          val  string
      );
      
      insert overwrite main_table  (id, code, dt_a, dt_b)
      values
      ('1', 'a',  '2015-01-01 00:00:11', '2015-02-01 00:00:21'),
      ('2', 'b',  '2015-01-02 00:00:12', '2015-02-02 00:00:22'),
      ('3', null, '2015-01-03 00:00:13', '2015-02-03 00:00:23');
      
      insert overwrite code_table (code, val)
      values
      ('a', 'A'),
      ('b', 'B');
      
      -- No join: produces the correct result:
      -- 1    a       2015-01-01 00:00:11
      -- 2    b       2015-02-02 00:00:22
      -- 3    NULL    2015-02-03 00:00:23
      select m.id, m.code, (case when m.code = 'a' then m.dt_a else m.dt_b end) as dt
      from main_table m
      where
      (case when m.code = 'a' then m.dt_a else m.dt_b end) >= cast('2001-01-01 00:00:00' as timestamp)
      ;
      
      -- Inner join: produces the correct result:
      -- 1    a       2015-01-01 00:00:11
      -- 2    b       2015-02-02 00:00:22
      select m.id, m.code, (case when m.code = 'a' then m.dt_a else m.dt_b end) as dt
      from main_table m
      inner join code_table c on m.code = c.code
      where
      (case when m.code = 'a' then m.dt_a else m.dt_b end) >= cast('2001-01-01 00:00:00' as timestamp)
      ;
      
      -- Left join: expected result:
      -- 1    a       A       2015-01-01 00:00:11
      -- 2    b       B       2015-02-02 00:00:22
      -- 3    NULL    NULL    2015-02-03 00:00:23
      -- Actual result:
      -- ERROR: IllegalStateException: Failed analysis after expr substitution.
      -- CAUSED BY: AnalysisException: operands of type BOOLEAN and TIMESTAMP are not comparable: (CASE WHEN NULL = 'a' THEN NULL ELSE NULL END) >= CAST('2001-01-01 00:00:00' AS TIMESTAMP)
      select m.id, m.code, c.val, (case when m.code = 'a' then m.dt_a else m.dt_b end) as dt
      from main_table m
      left outer join code_table c on m.code = c.code
      where
      (case when m.code = 'a' then m.dt_a else m.dt_b end) >= cast('2001-01-01 00:00:00' as timestamp)
      ;
      

      The workaround is to CAST both m.dt_a and m.dt_b to TIMESTAMP in the case statement:

      (case when m.code = 'a' then CAST(m.dt_a AS TIMESTAMP) else CAST(m.dt_b AS TIMESTAMP) end) >= cast('2001-01-01 00:00:00' as timestamp);
      

      This has be re-produced in CDH5.5 and CDh5.4.x

        Attachments

          Activity

            People

            • Assignee:
              bharathv Bharath Vissapragada
              Reporter:
              ericlin Eric Lin
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: