Teiid Impala LIMIT Pushdown Not Working as Expected in Some Cases

[walla2sl]

In Impala JDBC connection LIMIT push down to Impala does not happen in some cases causing Teiid to have to wait to project all rows before limiting results. This slows down large requests.

Noticed this behavior appears to be limited to aggregation of a math operation.

For example, the following case sends LIMIT to Impala.

SELECT id, sum(metric)

FROM

  vdb

GROUP BY

  id

LIMIT 25

While this does not:

SELECT id, sum(metric1 + metric2)

FROM

  vdb

GROUP BY

  id

LIMIT 25

And this does send limit:

SELECT id, sum(metric1), sum(metric2)

FROM

  vdb

GROUP BY

  id

LIMIT 25

Any ideas what could be causing this. We are using translator-hive-8.10.0.Final.jar.

[walla2sl]

Also, forgot to mention we are running teiid-8.10.0.Final-jboss-dist

[shawkins]

What are the types of metric1 and metric2?  More than likely the addition of them is adding an implicit conversion that is not being seen as supported by the translator.

[walla2sl]

Thanks for your response, Steven.

The data types are bigdecimal and long. If I try with two bigdecimal metrics, you are correct, the pushdown works as expected. If I try to cast the long as bigdecimal within the sum, I still don't see pushdown. Also, I didn't mention that the aggregation is also not pushed down.

Any ideas on how I could resolve this?

BTW, I have another Teiid instance, running Teiid 8.8.0, with translator-hive-8.8.0.CR1.jar, and the push down appears to be working. Strange that a later translator version appears to not be supporting similar behavior.

[walla2sl]

sum(cast(metric1 as double) + cast(metric2 as double)) seems to work. I believe this is a suitable workaround.

[shawkins]

> BTW, I have another Teiid instance, running Teiid 8.8.0, with translator-hive-8.8.0.CR1.jar, and the push down appears to be working. Strange that a later translator version appears to not be supporting similar behavior.

I believe there was an earlier issue where the translators were not being checked for convert/cast restrictions, but now they are.

> sum(cast(metric1 as double) + cast(metric2 as double)) seems to work. I believe this is a suitable workaround.

Yes, that would work as there is a type mapping for double.

Someone also opened a pull request: https://github.com/teiid/teiid/pull/519

To add a type mapping for decimal, but that is not supported by older versions of impala, so we're not quite ready to commit that yet.

[shawkins]

This was logged and worked as [TEIID-3700] Add a type mapping for impala decimal - JBoss Issue Tracker which will be in Teiid 8.12 CR1.