Function not being pushed down

[michal.warecki]

Hi guys,

I have an issue with queries which doesn't support "group by" in ExecutionFactory but should support custom function which is inside aggregate function.

Example:

Input query with not supported group by:

SELECT AAA, SUM(custom_function(BBB)) AS BBB_ALIAS FROM some_table WHERE some_table.CCC = 'VAL' GROUP BY AAA ORDER BY AAA

Output query (processed by teiid):

SELECT AAA, BBB AS BBB_ALIAS FROM some_table.CCC = 'VAL'

What I think it should looks like:

SELECT AAA, custom_function(BBB) AS BBB_ALIAS FROM some_table.CCC = 'VAL'

Why custom_function(BBB) is not being pushed down? In my case this results in exception:

org.teiid.api.exception.query.FunctionExecutionException: TEIID30341 Function NAMESPACE.custom_function is marked in the function metadata as a function that must be evaluated at the source.

I see that even with built it functions like LOG(), that function is also not pushed down but the results are correct.

With supported "group by", the query is not being translated so I'm able to process it correctly but for one Executor Factory I can't support grouping.

Thanks

[rareddy]

Which translator? If it is custom translator did you add the supported pushdown functions in the translator?

[michal.warecki]

We have custom Execution Factory and we'v registered that function using "addPushDownFunction".

[shawkins]

We do not yet perform sub-expression pushing of must pushdown functions.  A workaround would be to use an inline view:

SELECT AAA, SUM(x) AS BBB_ALIAS FROM /*+ NO_UNNEST */ (select AAA, custom_function(BBB) as x, CCC FROM some_table) as V WHERE CCC = 'VAL' GROUP BY AAA ORDER BY AAA

You can also log an issue to add support.

[michal.warecki]

Thanks Steven! JIRA ticket created: [TEIID-3227] Add support for sub-expression pushing of must pushdown functions - JBoss Issue Tracker

EDIT:

With /*+ NO_UNNEST */ it works.

[shawkins]

The reason no_unnest is required is that Teiid will by default unnest/flatten view layers prior to considering pushdown.

[michal.warecki]

I'd like to contribute to Teiid and add support for that sub-expression pushdown. I'm not familiar with the Teiid codebase so if you can point some areas where I could start, I'd be grateful.

Do you think, it will be hard to implement such functionality? I have limited time for this, that's why I'm asking.

Best

[shawkins]

> I'd like to contribute to Teiid and add support for that sub-expression pushdown.

Excellent.

> I'm not familiar with the Teiid codebase so if you can point some areas where I could start, I'd be grateful.

Do you think, it will be hard to implement such functionality? I have limited time for this, that's why I'm asking.

This may not be a good one to start.  The fix will probably broaden [TEIID-964] Must pushdown functions in select clause - JBoss Issue Tracker which targeted the whole expression for simplicity.  If you want to start on it, that would be great and at the very least you can compare what you come up with to whatever is checked in as the fix.

[shawkins]

I should add that if the translator support selectWithoutFrom, then it will evaluate must pushdown functions as needed - but that would be a single invocation at a time.

[shawkins]

[TEIID-3227] Add support for sub-expression pushing of must pushdown functions - JBoss Issue Tracker has been added and will be part of 8.10 Alpha2.

[michal.warecki]

That's great Steven, thank you! I'll test that when Alpha2 will be released.