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

Outer join prevents dependent join creation

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 11.1.2, 12.0, 11.2.2
    • 11.0.1
    • Query Engine
    • None
    • Hide

      Three databases are involved with joins that chain them together. A makedep option is given for the lean table of the second and third databases. If the second database is outer joined, no dependent join is created for it.

      Show
      Three databases are involved with joins that chain them together. A makedep option is given for the lean table of the second and third databases. If the second database is outer joined, no dependent join is created for it.
    • Hide

      Use a preserve hint to prevent the manipulation of the join ordering. In this example it can be applied around the top level join:

      ... FROM /*+ preserve */ (meds_owner.compound ...) WHERE MEDS_OWNER...
      Show
      Use a preserve hint to prevent the manipulation of the join ordering. In this example it can be applied around the top level join: ... FROM /*+ preserve */ (meds_owner.compound ...) WHERE MEDS_OWNER...

      This query:

      Edit: <I was asked to remove the query text from the top level description>
      The query is between three different databases chained together with joins (see plans).

      does not create a dependent join on the middle table. If you change the join to the middle table to inner, it does.

      Plans and debug plans for both cases are attached. As a note, an older version of Teiid that we are also using (9.3.4) always creates the dependent join. 11.0.1 and 10.2.3 do not create the dependent join if the table is outer joined.

            rhn-engineering-shawkins Steven Hawkins
            mike.higgins@certara.com Mike Higgins (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: