2 Replies Latest reply on Sep 20, 2016 10:43 AM by Don Krapohl

    Impala translator - Won't push down from_unixtime(timestamp, string)

    Don Krapohl Newbie

      I have a source with native type timestamp, Teiid source type timestamp, using the Impala translator, no translator overrides.  I can't get the combination of from_unixtime(timestamp, string) to work at all.  This is the most common way to reformat timestamps in Teiid and from_unixtime is the only way to do it using date/time functions (see from_unixtime() in  Impala Date and Time Functions ) .  I've tried many workarounds and am unsuccessful.  Performing a unix_timestamp(match_date) results in appropriate values such as 1421388155 as expected.


      Example query:


      select from_unixtime(unix_timestamp(match_date), 'yyyy-MM-dd') FROM TransactionProcessDateDetail


      Here's the planner exception:

      org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30258 Expression(s) timestampadd(SQL_TSI_SECOND, unix_timestamp(convert(fact_transaction_process_date_detail.fact_transaction_process_date_detail.u1_match_date, string)), {ts'1969-12-31 19:00:00.0'}) cannot be pushed to source.


      I have tested this on 8.13.3, 8.13.6, and 9.0.3.