[IMPALA-2765] AnalysisException: operands of type BOOLEAN and TIMESTAMP are not comparable when OUTER JOIN with CASE statement

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 2.3.0
• Fix Version/s: Impala 2.5.0, Impala 2.2.10, Impala 2.3.4
• Component/s: Frontend
• Labels: ramp-up
• Target Version: Impala 2.3.4
• Assignee: Bharath Vissapragada
• Reporter: Eric Lin
• Created: 11/Dec/15 04:59
• Updated: 04/Jan/17 23:58
• Resolved: 19/Dec/15 10:11

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