[teiid-issues] [JBoss JIRA] (TEIID-2058) Optimization places correlated subquery in the on clause, which is not supported by DB2

Optimization places correlated subquery in the on clause, which is not supported by DB2 --------------------------------------------------------------------------------------- Key: TEIID-2058 URL: https://issues.jboss.org/browse/TEIID-2058 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 7.7 Reporter: Johnathon Lee Assignee: Steven Hawkins Fix For: 7.7.1, 8.1 Reproducible behavior with the following simplified query [1] Avoiding the error by including non-restricting criteria on tables not represented in the WHERE EXISTS clause [2]. ie: there seems to be a problem in the Optimization that writing the query similar to [2] will avoid. [1] SELECT 'X' as CMR_BPR_ID2 FROM Y93 INNER JOIN AG5 ON 1 = 1 LEFT OUTER JOIN MFSMFL.I58 as I58 ON 1 = 1 WHERE EXISTS (SELECT 'Y' FROM BH0 WHERE AG5.CONSENT_CLIENT_ID = 1 AND Y93.CMR_BPR_ID2 = 1 ) [2] SELECT 'X' as CMR_BPR_ID2 FROM Y93 INNER JOIN AG5 ON 1 = 1 LEFT OUTER JOIN MFSMFL.I58 as I58 ON 1 = 1 WHERE EXISTS (SELECT 'Y' FROM BH0 WHERE AG5.CONSENT_CLIENT_ID = 1 AND Y93.CMR_BPR_ID2 = 1 AND I58.FUND_CODE like '%')