[teiid-issues] [JBoss JIRA] (TEIID-5932) HAVING in combination with LEFT JOIN of two XMLTABLE sub-queries returns an incorrect result

[Steven Hawkins (Jira)]

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

Steven Hawkins resolved TEIID-5932.
-----------------------------------
    Fix Version/s: 14.0
                   13.0.3
                   13.1.1
       Resolution: Done


The issue was with the left outer join handling in the nested table processing.  When the batch was filled due to no outer match it was skipping the remaining processing that should have been run regardless.

Also note that the way queries are written are not equivalent forms.  The lateral join form is the same as d cross join (xt left outer join xt2 on ...)

The others associate the other way:  (d inner join xt on ...) left outer join xt2 on ...

> HAVING in combination with LEFT JOIN of two XMLTABLE sub-queries returns an incorrect result
> --------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5932
>                 URL: https://issues.redhat.com/browse/TEIID-5932
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 13.1
>            Reporter: Dmitrii Pogorelov
>            Assignee: Steven Hawkins
>            Priority: Blocker
>             Fix For: 14.0, 13.0.3, 13.1.1
>
>
> HAVING in combination with LEFT JOIN of two XMLTABLE sub-queries returns an incorrect result if one of XMLTABLE sub-queries has INNER, LEFT or CROSS JOIN instead of a lateral one and amount of rows in the source is equal or more than 1024. For example, the query:
> {code:sql}
> SELECT
> 	xt.event_id
> FROM test_dwh_pg.test_having AS d
> --here
> INNER JOIN XMLTABLE(
> 			'/root'
> 			PASSING JSONTOXML('root', d.str)
> 			COLUMNS
> 				event_id string PATH 'eventid'
> 		) xt
> 	ON TRUE
> LEFT JOIN XMLTABLE(
> 			'/anything'
> 			PASSING JSONTOXML('anything', d.str)
> 			COLUMNS
> 				some_col string PATH 'anything'
> 		) xt2 
> 	ON xt.event_id = 'xxx'
> 	GROUP BY event_id
> 	HAVING COUNT(*) > 1 ;;
> {code}
> will return a result but it should return no results as there are no duplicates in test data.



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