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

[don.krapohl]

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.

[shawkins]

from_unixtime is also a Teiid system function, but is mapped to timestampadd early in planning.  This leads to the exception you are seeing.

The workaround is to reference the impala function specifically:

impala.from_unixtime(unix_timestamp(match_date))

We should be handling this better - by moving the mapping to timestamp add to later in planning so that sources can declare from_unixtime support.  Do you want an issue for that?

[don.krapohl]

Feature request filed:  [TEIID-4455] Impala Translator - Change planning step for from_unixtime() pushdown - JBoss Issue Tracker

Thanks!