[teiid-issues] [JBoss JIRA] (TEIID-3183) Join sort order altered inappropriately

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-3183.
-----------------------------------
    Resolution: Done


Updated the logic to check to see if a sort order is already defined.

A potential enhancement moving forward would be to check the parent join and see if an enhanced sort could be used.  This would require forcing using the subquery as the sorted index and updating the join logic to understand semi join and anti-semi join.  This would allow the sort order to be persevered.

> Join sort order altered inappropriately
> ---------------------------------------
>
>                 Key: TEIID-3183
>                 URL: https://issues.jboss.org/browse/TEIID-3183
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.4
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 8.7.1, 8.9
>
>
> If a merge or other join configuration is chosen and there is a subquery affecting only a side of the join that is to be sorted such that the subquery can be rewritten as a semi/anti-semi join, then the subquery rewrite will alter the intended sort order of the original join.  This can result in incorrect results.
> An example query:
> select pm2.g2.e1 from pm1.g1 inner join pm2.g2 on (pm1.g1.e1 = pm2.g2.e1) where pm2.g2.e2 in /*+ mj */ (select count(e2) FROM pm1.g2 group by e1 having e1 < pm2.g2.e3)



--
This message was sent by Atlassian JIRA
(v6.3.1#6329)