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

[Dmitrii Pogorelov (Jira)]

Dmitrii Pogorelov created TEIID-5932:
----------------------------------------

             Summary: 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


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)