[IMPALA-1468] Analytic query with left outer join throws error. "CAUSED BY: AnalysisException: No matching function with signature: upper(BOOLEAN)."

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 2.0
• Fix Version/s: Impala 2.1
• Assignee: Alexander Behm
• Reporter: Mala Chikka Kempanna
• Created: 14/Nov/14 00:34
• Updated: 20/Dec/15 00:05
• Resolved: 19/Nov/14 02:54

Description

Problem can be reproduced like below
Step1:
drop table if exists hie_cluster;
drop table if exists hie_cell;
drop table if exists kpi_stats;

create table if not exists hie_cell (cell_id string, market_id string);
create table if not exists hie_cluster (cell_id string, cluster_id string, market_id string);
create table if not exists kpi_stats (cell_id string, drop_call int);

insert into hie_cell values ('sea_1', 'seattle');
insert into hie_cell values ('prt_1', 'portland');
insert into hie_cluster values ('sea_1', 'sea_clus_1', 'seattle mkt');
insert into kpi_stats values ('sea_1', 100);
insert into kpi_stats values ('prt_1', 200);

Step2:
Query: select hie.cell, hie.cluster, hie.market, kpi.drop_call
from
kpi_stats kpi
left outer join
(
select
cell.cell_id cell, cls.cluster_id cluster, decode(cls.cluster_id, NULL, upper(cell.market_id), upper(cls.market_id)) market
from
hie_cell cell left outer join hie_cluster cls on cell.cell_id = cls.cell_id
) hie
on kpi.cell_id = hie.cell
-------------------------------------+

cell

cluster

market

drop_call

-------------------------------------+

prt_1	NULL	PORTLAND	200
sea_1	sea_clus_1	SEATTLE MKT	100

-------------------------------------+
Fetched 2 row(s) in 1.52s

Step 3: Error reported here

Query: select hie.cell, hie.cluster, hie.market, kpi.drop_call
from
kpi_stats kpi
left outer join
(
select
cell.cell_id cell, cls.cluster_id cluster, upper(decode(cls.cluster_id, NULL, cell.market_id, cls.market_id)) market
from
hie_cell cell left outer join hie_cluster cls on cell.cell_id = cls.cell_id
) hie
on kpi.cell_id = hie.cell
ERROR: AnalysisException: null
CAUSED BY: IllegalStateException: java.lang.IllegalStateException: Failed analysis after expr substitution.
CAUSED BY: IllegalStateException: Failed analysis after expr substitution.
CAUSED BY: AnalysisException: No matching function with signature: upper(BOOLEAN).