Using Left Join returns wrong data in Teiid 8.7

[rakeshsagar]

Hi

When I execute the following SQL I get wrong data from teiid. If I use the OPTION MAKEDEP the SQL returns the correct data.

My understanding is that the "OPTION MAKEDEP" is optional and is used only to improve performance correct?

Why is there a difference in the results returned when MAKEDEP is not used and when MAKEDEP is used?

Attached is the Query plan for With MakeDep and without MakeDep

SELECT CHORUS_B."EVTSYSID", CHORUS_B."DATAMART_NAME", CHORUS_B."EVTUSERID", CHORUS_J0.NAME as "USEROFSYSACCESS@@@@NAME", CHORUS_B."EVTJOBNAME", CHORUS_B."EVTTYPEDESC", CHORUS_B."EVTUSERNAME", CHORUS_B."EVTCATDESC", CHORUS_B."VIO1CODE", CHORUS_B."EVTESMCODE", CHORUS_B."VIO2CODE", CHORUS_B."USRFACILITY", 'SECDM.VIEWSYSACCESS' as "__objecttype__" FROM "SECDM.VIEWSYSACCESS" as CHORUS_B 

left join SECURITY.CMXREF cx2 on CHORUS_B.evtsysid = cx2.lpar 

left join SECURITY_USERUPDATE.BASEUSER CHORUS_J0 on CHORUS_J0.sysid=cx2.sysid and CHORUS_J0.userid = RTRIM(CHORUS_B.evtuserid)

Thanks

Rakesh

[shawkins]

> My understanding is that the "OPTION MAKEDEP" is optional and is used only to improve performance correct?

That is correct.

> Why is there a difference in the results returned when MAKEDEP is not used and when MAKEDEP is used?

From your plans we see:

EXECUTING ChooseDependent

LOW Relational Planner node is on outer side of the join - Rejecting dependent join Join(groups=[SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS, SECURITY_CIA_DB2_D91BPTIB_CIADB41.CMXREF], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[evtsysid = lpar], OUTPUT_COLS=null, IS_COPIED=true, LEFT_EXPRESSIONS=[evtsysid], RIGHT_EXPRESSIONS=[lpar], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1.0, EST_COL_STATS={SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTTYPECODE=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTVERSION=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTCATCODE=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTSYSID=[-1.0, -1.0], ...}}

AFTER:

Project(groups=[SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS, SECURITY_CIA_DB2_D91BPTIB_CIADB41.BASEUSER])

  Join(groups=[SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS, SECURITY_CIA_DB2_D91BPTIB_CIADB41.CMXREF, SECURITY_CIA_DB2_D91BPTIB_CIADB41.BASEUSER], props={JOIN_TYPE=LEFT OUTER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[sysid = sysid, userid = RTRIM(evtuserid)], OUTPUT_COLS=null, IS_COPIED=true, LEFT_EXPRESSIONS=[sysid, RTRIM(evtuserid)], RIGHT_EXPRESSIONS=[sysid, userid], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=null})

    Join(groups=[SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS, SECURITY_CIA_DB2_D91BPTIB_CIADB41.CMXREF], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[evtsysid = lpar], OUTPUT_COLS=null, IS_COPIED=true, LEFT_EXPRESSIONS=[evtsysid], RIGHT_EXPRESSIONS=[lpar], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1.0, EST_COL_STATS={SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTTYPECODE=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTVERSION=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTCATCODE=[-1.0, -1.0], SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS.EVTSYSID=[-1.0, -1.0], ...}, DEPENDENT_VALUE_SOURCE=$dsc/id14})

This appears that the join type of the nested left outer join is being flipped to an inner join in the execution of RuleChooseDependent.  However I'm not familiar with any code that would be doing that.  Are you running on stock 8.7?

[rakeshsagar]

Hi Steve,

We have applied the following fixes on top of Stock Teiid 8.7

TEIID-2928, TEIID-2946,TEIID-3024,TEIID-3099,TEIID-3106,TEIID-3129,TEIID-3062,TEIID-3063

Thanks

Rakesh

[shawkins]

See if you can reproduce in a stock 8.7 or latest.

[rakeshsagar]

Hi Steve,

The same issue is reproducable in Stock 8.7.

Attached are the Query plans.

Thanks

Rakesh

[shawkins]

Yes there is an issue here.  [TEIID-3222] Dependent join planning modifies left outer to inner join - JBoss Issue Tracker will be addressed for 8.9 Final and 8.10.

[rakeshsagar]

Thanks Steve.

[rakeshsagar]

Hi Steve,

I have patched this to Teiid 8.7 code base and I still see the issue. Attached are the query plans for with MakeDep and without MakeDep.

Additionally the test case testNestedLeftOuterJoin for this also fails with 8.7 code.

Are there any additional patches that need to be applied on 8.7 along with this issue?

Thanks

Rakesh

[shawkins]

> Are there any additional patches that need to be applied on 8.7 along with this issue?

I have applied to our 8.7.x cleanly and everything works correctly.  I don't believe that any other change would be required.  You'll want to double check your patching and see what is failing about the unit test.

[rakeshsagar]

Hi Steve,

I re-applied the patch and the test case passes now.

But the data is still incorrect.

Attached is the data for the SQL with MAkeDep and Without MakeDep.

Also attached is the Query Plans WithMakeDep and WithoutMakeDep.

[shawkins]

The plans you are showing are identical to the last set.  It does not appear that you applied the patch for that test run.

[rakeshsagar]

My apologies. I attached the older query plan.

Attached are the new query plans.

[shawkins]

So if the data is from a patched run and not from an unpatched run, then there are two next steps.  The first is to retry your scenario in 8.9 final to see if there is a different intervening issue.  If it still doesn't seem correct, then what I would actually need is the data (or at least something representative) from the source queries so that I can easily reproduce what you are seeing.

[rakeshsagar]

Hi Steve,

The following are the source queries and attached is the data for those queries.

SELECT g_0.EVTSYSID AS c_0, g_0.EVTUSERID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTJOBNAME AS c_4, g_0.EVTTYPEDESC AS c_5, g_0.EVTUSERNAME AS c_6, g_0.EVTCATDESC AS c_7, g_0.EVTESMCODE AS c_8, g_0.VIO1CODE AS c_9, g_0.VIO2CODE AS c_10, g_0.USRFACILITY AS c_11 FROM SECURITY_WH_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS AS g_0

SELECT g_0.LPAR AS c_0, g_0.SYSID AS c_1 FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.CMXREF AS g_0 ORDER BY c_0

SELECT g_0.SYSID AS c_0, g_0.USERID AS c_1, g_0.NAME AS c_2 FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.BASEUSER AS g_0 ORDER BY c_0, c_1

Thanks

Rakesh

[shawkins]

If you could provide the relevant DDL that would be good as well.  Also you'll want to check that the difference in results after the patch is not simply coming from performing the comparison "WHERE (g_0.SYSID, g_0.USERID) IN (<dependent values>)" at the source in the makedep case vs. in Teiid in the non-makedep case.