LATERAL LEFT JOIN can return duplicate rows at batch boundaries --------------------------------------------------------------- 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 Original Estimate: 4 hours Time Spent: 5 hours, 30 minutes Remaining Estimate: 0 minutes 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.