[teiid-issues] [JBoss JIRA] (TEIID-3386) Mixed resutl with (double) full outer join and where clause
Steven Hawkins (JIRA)
issues at jboss.org
Thu Apr 2 11:14:19 EDT 2015
[ https://issues.jboss.org/browse/TEIID-3386?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins updated TEIID-3386:
----------------------------------
Workaround Description:
Use a preserve hint around from clauses containing multiple full outer joins that can be pushed to oracle:
SELECT BQT1.SmallA.IntKey AS SmallA_IntKey, BQT1.MediumB.IntKey AS MediumB_IntKey, BQT1.MediumA.IntKey AS MediumA_IntKey FROM /*+ preserve */ ((BQT1.SmallA FULL OUTER JOIN BQT1.MediumB ON BQT1.SmallA.IntKey = BQT1.MediumB.IntKey) FULL OUTER JOIN BQT1.MediumA ON BQT1.MediumB.IntKey = BQT1.MediumA.IntKey) WHERE BQT1.MediumA.IntKey < 1500 ORDER BY MediumA_IntKey, MediumB_IntKey, SmallA_IntKey
Workaround: Workaround Exists
Added a workaround. I will likely resolve this without a code change based upon a workaround being available and that this an oracle bug that is addressed on later versions.
> Mixed resutl with (double) full outer join and where clause
> -----------------------------------------------------------
>
> Key: TEIID-3386
> URL: https://issues.jboss.org/browse/TEIID-3386
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> If a query contains double FULL OUTER JOIN and WHERE clause that contains condition on column from last table, teiid returns mixed result (underlying oracle10).
> Query:
> SELECT BQT1.SmallA.IntKey AS SmallA_IntKey, BQT2.MediumB.IntKey AS MediumB_IntKey, BQT2.LargeB.IntKey AS LargeB_IntKey
> FROM (BQT1.SmallA FULL OUTER JOIN BQT2.MediumB ON BQT1.SmallA.IntKey = BQT2.MediumB.IntKey) FULL OUTER JOIN BQT2.LargeB ON BQT2.MediumB.IntKey = BQT2.LargeB.IntKey
> WHERE BQT2.LargeB.IntKey < 1500 ORDER BY LargeB_IntKey, MediumB_IntKey, SmallA_IntKey;
> Actual result:
> 0 0 0
> 1 1 1
> 2 2 2
> 3 3 3
> ... ... ...
> 49 49 49
> 50 50 50
> ... ... ...
> 999 999 999
> <null> <null> 1000
> ... ... ...
> <null> <null> 1499
> Expected result:
> 0 0 0
> 1 1 1
> 2 2 2
> 3 3 3
> ... ... ...
> 49 49 49
> <null> 50 50
> ... ... ...
> 999 999 999
> <null> <null> 1000
> ... ... ...
> <null> <null> 1499
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
More information about the teiid-issues
mailing list