[IMPALA-9745] SELECT from view fails with "AnalysisException: No matching function with signature: to_timestamp(TIMESTAMP, STRING)" after expression rewrite.

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: Impala 2.11.0, Impala 4.0.0
• Fix Version/s: Impala 4.0.0
• Component/s: Frontend
• Epic Color: ghx-label-12
• Assignee: Aman Sinha
• Reporter: Andrew Sherman
• Created: 13/May/20 22:37
• Updated: 17/Feb/21 02:38
• Resolved: 17/Feb/21 02:38

Description

Simple test case

drop view if exists test_replication_view;
drop table if exists test_replication;
create table test_replication(cob string);
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-08');
select * from test_replication;

create view test_replication_view as select to_timestamp(cob, 'yyyy-MM-dd') cob_ts,cob trade_date from test_replication;
select 1 from test_replication_view deal WHERE trade_date = deal.cob_ts AND deal.cob_ts = '2018-06-07';

The problem seems to be that after expression rewrite the type of cob has become a timestamp and so we look for the function "to_timestamp(TIMESTAMP, STRING)" instead of "to_timestamp(STRING, STRING)".

A workaround is to run with

set enable_expr_rewrites=false;

For comparison a similar query runs OK in mysql

drop view if exists test_replication_view;
drop table if exists test_replication;
create table test_replication(cob varchar(255));
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-07');
insert into test_replication values('2018-06-08');
select * from test_replication;

create view test_replication_view as select str_to_date(cob, '%Y-%m-%d') cob_ts,cob trade_date from test_replication;
select 1 from test_replication_view deal WHERE trade_date = deal.cob_ts AND deal.cob_ts = '2018-06-07'

Issue Links

• relates to: IMPALA-7865 Repeated type widening of arithmetic expressions