Andrew Sherman created IMPALA-9745:
--------------------------------------
Summary: SELECT from view fails with "AnalysisException: No
matching function with signature: to_timestamp(TIMESTAMP, STRING)" after
expression rewrite.
Key: IMPALA-9745
URL: https://issues.apache.org/jira/browse/IMPALA-9745
Project: IMPALA
Issue Type: Bug
Affects Versions: Impala 2.11.0, Impala 4.0
Reporter: Andrew Sherman
Simple test case
{code}
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';
{code}
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
{code}
set enable_expr_rewrites=false;
{code}
For comparison a similar query runs OK in mysql
{code}
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'
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
