> Is there a way to find out the patches that were applied on top of Teiid 8.1 to fix the issue? We would like to apply the patches on 8.1 and see if that fixes the issue.
There are quite a few changes that generally are too broad. You'd have to narrow it down to a particular issue or coupled set of issues. The differences between the plans are:
- 8.7 is using a dependent join - it looks like the cardinality information may be different on the models between the two versions. You can also add a makedep hint to the 8.1 query to produce a similar plan.
- The 8.7 plan is using an array comparison ((g_1.USERID, g_0.LPAR) IN (<dependent values>)). I believe this will work with 8.1 as well - [TEIID-2036] Use array comparison for dependent join using multiple attributes. - JBoss Issue Tracker
So on 8.1 with a makedep hint confirm that the plans are the same and then see if you get the same results. If not then ideally you are looking for a much narrower issue.
> If not is there a workaround to have this SQL return data as in Teiid 8.1?
Since the plan seems simplistic as is, you would probably want to investigate more about what is happening. On the 8.1 version what do the results from each of the source query look like? Are they in the expected sort order to be processed by the merge join?
Using OPTION MAKEDEP resolves the issue.