7 Replies Latest reply on Jun 5, 2017 4:30 PM by shawkins

Optimizer and over constraining

damien_b Mar 16, 2017 11:52 AM

We deal with a particular OData almost v2 server that uses system functions disguised as entities. Basically, "/A('b')/C" works but not "/C?$filter=foo eq 'b'". In order to have that, we've enabled the joins support on the ODataTranslator, but we still haven't the right query generated. We've traced back the origin to the optimizer:

SELECT * FROM a join b ON a.x=b.y WHERE a.x='v'
is optimized as SELECT <columns> FROM a join b ON a.x=b.y WHERE a.x='v' AND b.y='v'

The extra induced condition "b.y = 'v'" messes up with the URL construction. Is there a way to hint the optimizer to not add the extra constraint?

1. Re: Optimizer and over constraining

shawkins Mar 16, 2017 4:27 PM (in response to damien_b)

> Is there a way to hint the optimizer to not add the extra constraint?

What version are you on?

It may be possible to suppress this in situations where the inner join is retained - in other cases this would simply be represented by a cross join in which case each of the predicates is needed.
Actions
2. Re: Optimizer and over constraining

damien_b Mar 16, 2017 7:58 PM (in response to shawkins)
I'm on 9.2.0.Final.

I've made a mistake in the original post, the right queries are:
SELECT * FROM a inner join b ON a.x=b.y WHERE a.x='v'
optimized as: SELECT <columns> FROM a INNER JOIN b WHERE a.x=b.y AND a.x='v' AND b.y='v'

So the inner join is retained, next time I should copy paste directly..
Actions
3. Re: Optimizer and over constraining

damien_b Mar 17, 2017 8:24 AM (in response to shawkins)

So, any idea? :-)
Actions
4. Re: Optimizer and over constraining

shawkins Mar 17, 2017 8:33 AM (in response to damien_b)

As to a workaround you mean? No. This would likely require a code change.
Actions
5. Re: Optimizer and over constraining

damien_b Mar 20, 2017 4:43 AM (in response to shawkins)

Yes, a workaround: if something could be configured in the "capabilities" to avoid the extra condition.
Actions
6. Re: Optimizer and over constraining

shawkins Mar 20, 2017 11:19 AM (in response to damien_b)

No, the only thing you could do at the translator level would be to check for this situation in the predicates and omit the ones you don't want. It would take an engine change to not push the extra predicate in the first place.
Actions
7. Re: Optimizer and over constraining

shawkins Jun 5, 2017 4:30 PM (in response to shawkins)

Logged [TEIID-4943] Allow translators to declare if copied criteria should be included - JBoss Issue Tracker to capture this as an engine issue.
Actions

Go to original post