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

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

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Critical
    • Resolution: Fixed
    • Impala 2.11.0, Impala 4.0.0
    • Impala 4.0.0
    • Frontend
    • None
    • ghx-label-12

    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'
      

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            amansinha Aman Sinha
            asherman Andrew Sherman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment