Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1763

EDS inner join returns no data, left join does

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Obsolete
    • Icon: Blocker Blocker
    • 7.1.1
    • 7.1.1
    • Query Engine
    • None
    • Hide

      The query which returns 0 rows is below, it's the "inner join Entity_Model.RefData.Persons p" where the problem seems to centre.

      select
      p.NtSystemLogin as book_trader_id,
      hierarchy.book_code,hierarchy.book_description,hierarchy.trader_portfolio_id,hierarchy.division_id,
      hierarchy.business_area_id,hierarchy.desk_id,hierarchy.desk_portfolio_id,hierarchy.risk_portfolio_id,hierarchy.product_portfolio_id,
      p.Name as book_trader_desc,
      sourceBooks.alternatebookid
      from buchierarchy hierarchy
      inner join Entity_Model.Ledger.LedgerBooks books on hierarchy.book_code = books.book_code
      inner join Entity_Model.RefData.SourceBooks sourceBooks on sourceBooks.ledgerBookId = books.book_code
      inner join Entity_Model.RefData.Persons p on books.book_trader_id = p.personId
      where books.BOOK_BUSINESS_DATE = '2011-09-22'
      and sourceBooks.sourcesystemid IN ('GDSLDN','GDSHKG')
      and books.book_active_indicator = 'A'
      and sourceBooks.activeindicator = 'A'
      and hierarchy.division_id = 'GBM'
      and hierarchy.business_area_id = 'FMRates'
      and hierarchy.desk_id = 'DelLdnTrd'
      and hierarchy.desk_portfolio_id = 'FWD'
      and sourcebooks.alternatebookid = 'SWAPGDSLDN2806'

      Changing that join to "left join Entity_Model.RefData.Persons p" returns the 1 row we expect. book_trader_id

      Another way to get the query to return this one row is to put a makedep hint on the ledgerbooks table (inner join on Persons). Obviously we wouldn't expect these kind of hints to influence the results.

      (Modelset attached)

      Show
      The query which returns 0 rows is below, it's the "inner join Entity_Model.RefData.Persons p" where the problem seems to centre. select p.NtSystemLogin as book_trader_id, hierarchy.book_code,hierarchy.book_description,hierarchy.trader_portfolio_id,hierarchy.division_id, hierarchy.business_area_id,hierarchy.desk_id,hierarchy.desk_portfolio_id,hierarchy.risk_portfolio_id,hierarchy.product_portfolio_id, p.Name as book_trader_desc, sourceBooks.alternatebookid from buchierarchy hierarchy inner join Entity_Model.Ledger.LedgerBooks books on hierarchy.book_code = books.book_code inner join Entity_Model.RefData.SourceBooks sourceBooks on sourceBooks.ledgerBookId = books.book_code inner join Entity_Model.RefData.Persons p on books.book_trader_id = p.personId where books.BOOK_BUSINESS_DATE = '2011-09-22' and sourceBooks.sourcesystemid IN ('GDSLDN','GDSHKG') and books.book_active_indicator = 'A' and sourceBooks.activeindicator = 'A' and hierarchy.division_id = 'GBM' and hierarchy.business_area_id = 'FMRates' and hierarchy.desk_id = 'DelLdnTrd' and hierarchy.desk_portfolio_id = 'FWD' and sourcebooks.alternatebookid = 'SWAPGDSLDN2806' Changing that join to "left join Entity_Model.RefData.Persons p" returns the 1 row we expect. book_trader_id Another way to get the query to return this one row is to put a makedep hint on the ledgerbooks table (inner join on Persons). Obviously we wouldn't expect these kind of hints to influence the results. (Modelset attached)

      We've encountered a rather concerning case of the wrong data (no data) being returned on a federated query. We're pretty sure it should return data, and when we change an inner join to a left join it does return the data we expect (complete data, with a value for the column being left joined rather than a null).

      This issue has been tested on 5.2 ER3 and is fixed, but the customer needs a CP for the 5.1 release.

              rhn-engineering-shawkins Steven Hawkins
              rhn-support-dsteigner Deborah Steigner (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Created:
                Updated:
                Resolved: