Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2058

Optimization places correlated subquery in the on clause, which is not supported by DB2

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 7.7.1, 8.1
    • 7.7
    • Query Engine
    • None
    • Hide

      Files to reproduce are supplied in the referenced SF ticket

      Show
      Files to reproduce are supplied in the referenced SF ticket
    • Workaround Exists
    • Hide

      including non-restricting criteria on tables not yet represented in the WHERE EXISTS clause

      Show
      including non-restricting criteria on tables not yet represented in the WHERE EXISTS clause

      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 '%')

              rhn-engineering-shawkins Steven Hawkins
              rhn-support-jolee Johnathon Lee
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: