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

[Mike Higgins (Jira)]

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

Mike Higgins updated TEIID-5566:
--------------------------------
    Description: 
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.

  was:
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.



> 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
>             Fix For: 11.1.2, 12.0, 11.2.2
>
>         Attachments: missing_dep_debugplan.txt, missing_dep_inner_debugplan.txt, missing_dep_inner_plan.txt, missing_dep_plan.txt
>
>
> 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.



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