Nested functions not pushed to the translator

[rakeshsagar]

Hi,

We have a SQL of the form

Select creator, AVG(CAST(colcount)) AS AVGColumn from Table t group by creator.

I this case, the AVG and CAST functions are not being pushed down to the translator.

Is there a way by which we can tell Teiid to push these two functions to the translator?

Thanks

Rakesh

[shawkins]

Is that a userdefined form of cast or is it just missing the target type?

You will want to double check the debug plan as it will have lines something like "could not push aggregate AVG as source does not support ...".  Assuming that you are using a custom translator, on ExecutionFactory there is an explicit support for AVG - supportsAggregateAvg.  Then to support cast it needs to be in the supportedFunctions list and you can refine it's support with supportsConvert - although that is affected by [TEIID-3187] Error Casting TimeStamp to Date using Impala - JBoss Issue Tracker

[rakeshsagar]

Hi Steve,

Is that a userdefined form of cast or is it just missing the target type?

Its not a userdefined function. I missed the target type in the SQL

supportsAggregateAvg() is true in our custom translator and the following SQL is being pushed to the translator.

Select creator, AVG(colcount) AS AVGColumn from Table t group by creator.

The problem is that the cast function when used with AVG or SUM is not being pushed to the translator.

We should override the getSupportedFunctions() from the ExecutionFactory and add the cast function would work right?

Thanks

Rakesh.

[shawkins]

> We should override the getSupportedFunctions() from the ExecutionFactory and add the cast function would work right?

Yes just make sure to provide the corresponding logic needed at the translator level for cast/convert.

[rakeshsagar]

Thanks Steve.