Using Left Join returns wrong data in Teiid 8.7| JBoss.org Content Archive (Read Only)

15. Re: Using Left Join returns wrong data in Teiid 8.7

rakeshsagar Dec 1, 2014 3:46 AM (in response to shawkins)

Hi Steve,

Attached is the DDL files for each of the tables.

Thanks

Rakesh.

16. Re: Using Left Join returns wrong data in Teiid 8.7

rakeshsagar Dec 1, 2014 11:43 PM (in response to rakeshsagar)

Hi Steve,

Can you take a look at this and let me know if I need to do anything with respect to this issue?

Thanks

Rakesh

17. Re: Using Left Join returns wrong data in Teiid 8.7

shawkins Dec 2, 2014 8:23 AM (in response to rakeshsagar)

I don't believe that I am seeing any issue. What translators are you using? You need to ensure that the data is coming back in the expected sort order. From the sample data for base user you have:

DE29,JESSC03,TEST SECURTIY ID

DE29,JES2,JES2 STC ACID

Where the expected sort order is on the first two columns. However JES2 should appear before JESSC03. You can run your scenario with order by support turned off at the translator level via the supportsOrderBy property to see if that is the issue.

18. Re: Using Left Join returns wrong data in Teiid 8.7

rakeshsagar Dec 3, 2014 12:17 AM (in response to shawkins)

Hi Steve,

My apologies. The data I provided was without the order by clause in the SQL.

The following is the complete information with the patch applied

We are using the DB2 translator and the order by works fine. Attached is the Query Plans, Sample Data for all the source queries.

As you mentioned earlier , I think the difference in results is because of the performing the comparison "WHERE(g_0.SYSID, g_0.USERID) IN (<dependent values>)" at the source in the case of makedep vs in Teiid in the non-makedep case, which is clearly visible from the source queries which are executed against the source.

Client SQL:

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)

Source Queries without MakeDep

SELECT g_0."EVTSYSID" AS c_0, g_0."EVTUSERID" AS c_1, g_0."EVTJOBNAME" AS c_2, g_0."EVTTYPEDESC" AS c_3, g_0."EVTUSERNAME" AS c_4, g_0."EVTCATDESC" AS c_5, g_0."VIO1CODE" AS c_6, g_0."EVTESMCODE" AS c_7, g_0."VIO2CODE" AS c_8, g_0."USRFACILITY" AS c_9 FROM "CMGRD1"."VIEWSYSACCESS" 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 "CIADB41"."BASEUSER" AS g_0 ORDER BY c_0, c_1

SELECT g_0."LPAR" AS c_0, g_0."SYSID" AS c_1 FROM "CIADB41"."CMXREF" AS g_0 WHERE g_0."LPAR" = 'DE31 ' OR g_0."LPAR" = 'DE30 ' OR g_0."LPAR" = 'DE29 ' ORDER BY c_0

Source Queries with MakeDep

SELECT g_0."EVTSYSID" AS c_0, g_0."EVTUSERID" AS c_1, g_0."EVTJOBNAME" AS c_2, g_0."EVTTYPEDESC" AS c_3, g_0."EVTUSERNAME" AS c_4, g_0."EVTCATDESC" AS c_5, g_0."VIO1CODE" AS c_6, g_0."EVTESMCODE" AS c_7, g_0."VIO2CODE" AS c_8, g_0."USRFACILITY" AS c_9 FROM "CMGRD1"."VIEWSYSACCESS" AS g_0 ORDER BY c_0

SELECT g_0."LPAR" AS c_0, g_0."SYSID" AS c_1 FROM "CIADB41"."CMXREF" AS g_0 WHERE g_0."LPAR" = 'DE31 ' OR g_0."LPAR" = 'DE30 ' OR g_0."LPAR" = 'DE29 ' ORDER BY c_0

SELECT g_0."SYSID" AS c_0, g_0."USERID" AS c_1, g_0."NAME" AS c_2 FROM "CIADB41"."BASEUSER" AS g_0 WHERE (g_0."SYSID", g_0."USERID") = ('RACF', 'PLATA01') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'OMVSKERN') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'NATCO02') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'IBMUSER') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'HOGWA01') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'FLOW81') OR (g_0."SYSID", g_0."USERID") = ('RACF', 'CMGRALRT') OR (g_0."SYSID", g_0."USERID") = ('RACF', '*BYPASS*') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'PLATA01') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'OMVSKERN') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'NATCO02') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'IBMUSER') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'HOGWA01') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'FLOW81') OR (g_0."SYSID", g_0."USERID") = ('ERJA', 'CMGRALRT') OR (g_0."SYSID", g_0."USERID") = ('ERJA', '*BYPASS*') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'PLATA01') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'OMVSKERN') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'NATCO02') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'IBMUSER') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'HOGWA01') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'FLOW81') OR (g_0."SYSID", g_0."USERID") = ('DE31', 'CMGRALRT') OR (g_0."SYSID", g_0."USERID") = ('DE31', '*BYPASS*') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'TSO') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'SYSVIEW') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'RODER01') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'QACMGRL') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'OMVSKERN') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'OEDFLTU') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'OEDFLTG') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'NOOMVS') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'MASTER1') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'JES2') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'HOGWA01') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'ENF') OR (g_0."SYSID", g_0."USERID") = ('DE29', 'DEMO') OR (g_0."SYSID", g_0."USERID") = ('DE29', '*MISSING') OR (g_0."SYSID", g_0."USERID") = ('DE29', '*BYPASS*') OR (g_0."SYSID", g_0."USERID") = ('DE29', '') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'WENDE01') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'TUNEM01') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'TSO') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'TA5254') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'SYSVIEW') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'REPTH02') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'NOLID') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'NATCO02') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'JES2') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'HOGWA02') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'HOGWA01') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'GUJSA02') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'FTPD') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECAUSR') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECATST') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECASCP') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECARAL') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECANCL') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECAMNT') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECAINFV') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECAINF') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ECAADM') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'DEMO') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'DEFAULTU') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'CMGRALRT') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'CICTH01') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'BPXAS') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST9') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST7') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST6') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST5') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST4') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST3') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST23') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST20') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST2') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST19') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST18') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST17') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST16') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST15') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST14') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST13') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST12') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST11') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST10') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST1') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'AUDTST') OR (g_0."SYSID", g_0."USERID") = ('CIAS', 'ACFSTCID') ORDER BY c_0, c_1

Thanks

Rakesh

QueryPlan_Without_MakeDep.txt.zip 22.7 KB
QueryPlan_With_MakeDep.txt.zip 22.7 KB
BASEUSER.csv.zip 84.5 KB
CMXREF.csv.zip 182 bytes
SYSVIEWACCESS.csv.zip 6.2 KB

19. Re: Using Left Join returns wrong data in Teiid 8.7

shawkins Dec 3, 2014 7:38 AM (in response to rakeshsagar)

Did you run your scenario with order by support turned off?

Again as long as I have the sample data ordered correctly I get the same results in any configuration (order by support on / off, with or without a makedep) way. You need to ensure that the source collation matches Teiid's expectation, which is UTF16 by default. There are several issues / workarounds related to this if you sorting in an unexpected way at the source.

Beyond that you will need to produce a unit test or embedded test case to reproduce this if you believe that we aren't seeing the same thing.

20. Re: Using Left Join returns wrong data in Teiid 8.7

rakeshsagar Dec 5, 2014 4:18 AM (in response to shawkins)

Hi Steve,

As per the new sample data attached to my yesterday's thread, I see that all the data is sorted in the correct order. I am missing something here.

Our DB2 Database resides on the mainframe and the encoding is EBCDIC.

You said there are workarounds to this. Can you please let me know the workaround for this problem other than using MAKEDEP?

Thanks

Rakesh

21. Re: Using Left Join returns wrong data in Teiid 8.7

shawkins Dec 5, 2014 9:31 AM (in response to rakeshsagar)

> As per the new sample data attached to my yesterday's thread, I see that all the data is sorted in the correct order. I am missing something here.

It is not in ASCII/UTF16 order.

CIAS,</BODY>,

appears before

CIAS,#WOLF,

Which may be correct for EBCDIC, but you can see from http://www.asciitable.com/ that # has a lower value that <

Did you run your scenario with source support for order by turned off?

> You said there are workarounds to this. Can you please let me know the workaround for this problem other than using MAKEDEP?

The first thing that you can do is turn off source order by support. The next is more targeted. Using TEIID-3156 you can tell Teiid that a different collation locale is in effect - that will make it so that only string sorts are not pushed.

22. Re: Using Left Join returns wrong data in Teiid 8.7

rakeshsagar Dec 6, 2014 10:34 AM (in response to shawkins)

Hi Steve,

>>>The first thing that you can do is turn off source order by support. The next is more targeted. Using TEIID-3156 you can tell Teiid that a different collation locale is in effect - that will make it so that only string sorts are not pushed.

We cannot turnoff order by support as this will affect the performance. There will be huge data in our datasources.

>>> Using TEIID-3156

Can you please let me know how to use this?

Just set the property collationLocale as a translator property in the DB2 Translator?

Thanks

Rakesh

23. Re: Using Left Join returns wrong data in Teiid 8.7

shawkins Dec 7, 2014 4:17 PM (in response to rakeshsagar)

> Just set the property collationLocale as a translator property in the DB2 Translator?

Yes with the translator property set, Teiid will know not to push the order by specifically for joins and then only if a string column is involved. The various DB2 versions appear to have mechanism for specifying an alternative collation, possibly a cast, the collation_key function, etc. which may be of use when an alternative collation is in use rather than simply not pushing down - but an enhancement would be needed.

You can also tell Teiid to use a different collation locale - System Properties - Teiid 8.10 (draft) - Project Documentation Editor but I'm not sure if there are any built-in that would match and you'd need all sources to sort in that order.

24. Re: Using Left Join returns wrong data in Teiid 8.7

gadeyne.bram Dec 16, 2014 7:12 AM (in response to rakeshsagar)

I had a similar problem with teiid 8.7 (non patched version). I now upgraded to version 8.9 and for my case the problem seems to be solved.