[teiid-issues] [JBoss JIRA] (TEIID-3386) Mixed resutl with (double) full outer join and where clause

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13051330#comment-13051330 ] 

Steven Hawkins commented on TEIID-3386:
---------------------------------------

This is an oracle bug.

Our source query:

SELECT g_1.INTKEY AS c_0, g_2.INTKEY AS c_1, g_0.INTKEY AS c_2 FROM LargeB g_0 LEFT OUTER JOIN (SmallA g_1 FULL OUTER JOIN MediumB g_2 ON g_1.INTKEY = g_2.INTKEY) ON g_2.INTKEY = g_0.INTKEY WHERE g_0.INTKEY < 1500 ORDER BY c_2, c_1, c_0

Will produce the correct results against a different database, such as SQL Server.  The same is true even if we preserve the full outer join:

SELECT g_1.INTKEY AS c_0, g_2.INTKEY AS c_1, g_0.INTKEY AS c_2 FROM LargeB g_0 FULL OUTER JOIN (SmallA g_1 FULL OUTER JOIN MediumB g_2 ON g_1.INTKEY = g_2.INTKEY) ON g_2.INTKEY = g_0.INTKEY WHERE g_0.INTKEY < 1500 ORDER BY c_2, c_1, c_0

Reversing the full outer join, does produce the correct results against Oracle 10:

SELECT g_1.INTKEY AS c_0, g_2.INTKEY AS c_1, g_0.INTKEY AS c_2 FROM (SmallA g_1 FULL OUTER JOIN MediumB g_2 ON g_1.INTKEY = g_2.INTKEY) FULL OUTER JOIN LargeB g_0 ON g_2.INTKEY = g_0.INTKEY WHERE g_0.INTKEY < 1500 ORDER BY c_2, c_1, c_0

So that's obviously a bug as the results from a full outer join should be the same either way.  I'm not finding good information on how to categorize this type of oracle bug other than seeing that there are a lot of known issues even through Oracle 10 with full outer joins.  And this has been fixed in Oracle 11.  So I'm not sure what we should do to resolve this at the moment.

> 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)