[teiid-issues] [JBoss JIRA] (TEIID-5566) Outer join prevents dependent join creation

[Steven Hawkins (Jira)]

     [ https://issues.jboss.org/browse/TEIID-5566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5566:
----------------------------------
               Component/s: Query Engine
    Workaround Description: 
Use a preserve hint to prevent the manipulation of the join ordering.  In this example it can be applied around the top level join:

{code}... FROM /*+ preserve */ (meds_owner.compound ...) WHERE MEDS_OWNER... {code}


> Outer join prevents dependent join creation
> -------------------------------------------
>
>                 Key: TEIID-5566
>                 URL: https://issues.jboss.org/browse/TEIID-5566
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 11.0.1
>            Reporter: Mike Higgins
>            Assignee: Steven Hawkins
>            Priority: Major
>         Attachments: missing_dep_debugplan.txt, missing_dep_inner_debugplan.txt, missing_dep_inner_plan.txt, missing_dep_plan.txt
>
>
> This query:
> SELECT MEDS_OWNER.COMPOUND."COMPOUND_SEQ_ID", MEDS_OWNER.COMPOUND."COMPOUND_NUMBER", MEDS_OWNER.PARENT_EQUIV_V."COMPOUND_PARENT", 
> SCR_BATCH_INFO."PARENT_ID", SCR_BATCH_INFO."BATCH_NUMBER", SCR_EVT."COMPOUND_NUMBER", SCR_EVT."BATCH_TRACKING_ID", 
> SCR_PRJ."PGRD_THRPC_AREA_CHAR_TYPE", SCR_PRJ."PGRD_PROJ_SHORT_DESC", SCR_EVT."PGRD_PROJECT_CODE", 
> SCR_SITE."RSCH_SITE_SHORT_DESC", SCR_EVT."RSCH_SITE_CODE", SCR_EVT."CMPD_BATCH_EVENT_CODE", 
> SCR_EVT."CMPD_BTCH_SCR_EVENT_TS", SCR_SCREEN_INFO."SCR_PRTCL_SHORT_DESC", SCR_SCREEN_INFO."SCR_PRTCL_SHORT_NAME", 
> SCR_EVT."CMPD_BTCH_SCR_EVT_KEY" 
> FROM meds_owner.compound 
> LEFT OUTER JOIN meds_owner.parent AllParentCompounds ON (MEDS_OWNER.COMPOUND."PARENT_SEQ_ID" = AllParentCompounds."PARENT_SEQ_ID") LEFT OUTER JOIN meds_owner.parent_equiv_v 
> ON (AllParentCompounds."PARENT_SEQ_ID_EQV" = MEDS_OWNER.PARENT_EQUIV_V."PARENT_SEQ_ID") 
> LEFT OUTER /* inner join works */ JOIN (rifdm4.dmf_cet_cmpd_btch_scr_events SCR_EVT LEFT OUTER JOIN rifdwn_wp.dwn_pgrd_projects_v SCR_PRJ 
> ON (SCR_EVT."PGRD_PROJECT_CODE" = SCR_PRJ."PGRD_PROJECT_CODE") 
> LEFT OUTER JOIN rifods.ods_research_sites SCR_SITE ON (SCR_EVT."RSCH_SITE_CODE" = SCR_SITE."RSCH_SITE_CODE") 
> LEFT OUTER JOIN rifdwh.dw_screen_protocols SCR_SCREEN_INFO ON (SCR_EVT."SCREEN_PROTOCOL_ID" = SCR_SCREEN_INFO."SCR_PRTCL_ID")) 
> ON (((MEDS_OWNER.COMPOUND."COMPOUND_NUMBER" = SCR_EVT."COMPOUND_NUMBER") 
> AND (SCR_EVT."CMPD_BATCH_EVENT_CODE" 
> IN ('EXPSTART', 'EXPEND', 'EXPPUB'))) 
> AND ((SCR_EVT."CBTCH_EVENT_SRC_DEL_IND" = 'N') 
> AND ((SCR_EVT."CMPD_BATCH_EVENT_CODE" = 'EXPSTART') 
> OR (SCR_EVT."CMPD_BATCH_EVENT_CODE" = 'EXPEND') 
> OR (SCR_EVT."CMPD_BATCH_EVENT_CODE" = 'EXPPUB')))) 
> LEFT OUTER JOIN rifdm1.cpm_compound_batch SCR_BATCH_INFO ON (SCR_EVT."BATCH_TRACKING_ID" = SCR_BATCH_INFO."BATCH_TRACKING_NO") 
> WHERE MEDS_OWNER.COMPOUND."COMPOUND_SEQ_ID" IN (21475535)
>  option makedep SCR_EVT, SCR_BATCH_INFO
> does not create a dependent join on the SCR_EVT table.  If you change the join to SCR_EVT 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.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)