[teiid-issues] [JBoss JIRA] (TEIID-5930) FULL JOIN missing data when not using LIMIT clause

[Steven Hawkins (Jira)]

    [ https://issues.redhat.com/browse/TEIID-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14025751#comment-14025751 ] 

Steven Hawkins commented on TEIID-5930:
---------------------------------------

The issue is that the levels nesting is causing rule merge virtual to inappropriately remove a source node, which would represent an inline view, to preserve the location of the predicates.  Using the limit causes the inline view to be preserved.  This could also be worked around with the NO_UNNEST hint.

> FULL JOIN missing data when not using LIMIT clause
> --------------------------------------------------
>
>                 Key: TEIID-5930
>                 URL: https://issues.redhat.com/browse/TEIID-5930
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 13.1
>            Reporter: Dmitrii Pogorelov
>            Assignee: Steven Hawkins
>            Priority: Blocker
>
> When commenting out/uncommenting limit clause in the query:
> {code:sql}
> SELECT COUNT(*) 
> FROM 
>     (
>         SELECT 
>             date_value1 
>             , id
>         FROM views.view1_m 
>         Limit 1000000000
>     ) v1 
> full 
> JOIN  
>     (
>         SELECT 
>             date_value2 
>             , id
>         FROM views.view2_m 
>         LIMIT 1000000000
>     ) v2 
>     ON 
>         v1.date_value1 = v2.date_value2 
>         and v1.id = v2.id ;;
> {code}
> teiid returns different results, for example, when using limits in all subqueries Teiid will return 3652, but when commenting out all limit clauses teiid in turn will return already 0. 



--
This message was sent by Atlassian Jira
(v7.13.8#713008)