2 Replies Latest reply on Nov 11, 2014 7:16 AM by rakeshsagar

    Join SQL not working in Teiid 8.1 and working in Teiid 8.7

    rakeshsagar

      Hi all,

       

      I am having the following SQL which doesn't return any data in Teiid 8.1 and this works fine in Teiid 8.7. The data sources are same and the difference is just the teiid version.

      SELECT CHORUS_B.evtsysid, CHORUS_B.evtutctod, CHORUS_B.datamart_name, CHORUS_B.evtsystod, 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 JOIN SECURITY.CMXREF AS cx2 ON CHORUS_B.evtsysid = cx2.lpar) JOIN SECURITY_USERUPDATE.BASEUSER AS CHORUS_J0 ON CHORUS_J0.sysid = cx2.sysid AND CHORUS_J0.userid = RTRIM(CHORUS_B.evtuserid)

       

      We are using Teiid 8.1 for an earlier version of our product and the customer has reported an issue against that version. This SQL doesn't return any data in Teiid 8.1.

      Attached are the Query Plans for Teiid 8.1 and Teiid 8.7.

       

      Is there a way to find out the patches that were applied on top of Teiid 8.1 to fix the issue? We would like to apply the patches on 8.1 and see if that fixes the issue.

      If not is there a workaround to have this SQL return data as in Teiid 8.1?

       

      I understand that there would be many changes between the two releases and we need your help in fixing this issue.

      Your help is much appreciated.

        • 1. Re: Join SQL not working in Teiid 8.1 and working in Teiid 8.7
          shawkins

          > Is there a way to find out the patches that were applied on top of Teiid 8.1 to fix the issue? We would like to apply the patches on 8.1 and see if that fixes the issue.

           

          There are quite a few changes that generally are too broad.  You'd have to narrow it down to a particular issue or coupled set of issues.  The differences between the plans are:

           

          - 8.7 is using a dependent join - it looks like the cardinality information may be different on the models between the two versions.  You can also add a makedep hint to the 8.1 query to produce a similar plan.

          - The 8.7 plan is using an array comparison ((g_1.USERID, g_0.LPAR) IN (<dependent values>)).  I believe this will work with 8.1 as well - [TEIID-2036] Use array comparison for dependent join using multiple attributes. - JBoss Issue Tracker

           

          So on 8.1 with a makedep hint confirm that the plans are the same and then see if you get the same results.  If not then ideally you are looking for a much narrower issue.

           

          > If not is there a workaround to have this SQL return data as in Teiid 8.1?

           

          Since the plan seems simplistic as is, you would probably want to investigate more about what is happening.  On the 8.1 version what do the results from each of the source query look like?  Are they in the expected sort order to be processed by the merge join?

          • 2. Re: Join SQL not working in Teiid 8.1 and working in Teiid 8.7
            rakeshsagar

            Using OPTION MAKEDEP resolves the issue.

            Thanks Steve.