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

Only the first 1000 items in the IN criteria are being pushed down in the query

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • 7.4.1
    • 7.4.1, 7.6
    • Query Engine
    • None

    Description

      In the below query, we are expecting a large number of book codes (46987), to be passed from ledgerbookid in t_sdm_ledgerbook into the TSL_FINANCIAL_ACCOUNT_ITEMS_T2 table. We can see from the query plan that it got the correct number of rows.

      I understand that Oracle can only take 1000 items in an IN() construct, so EDS will split the criteria into multiple sets of 1000 IN criteria. I have seen this work previously.

      It seems that for some reason in this query, possibly the other dependent join or hints, EDS pushes exactly 1000 criteria. Looking at the source specific SQL command in the EDS debug log, which I have attached, I counted the number of question marks in that IN() and it comes to 1000. We have checked that all of these book codes are unique, by doing a select distinct to the source model.

      select cc.costcentreid, cc.description , ac.accountdescription, sum(func_amount)
      from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
      join t_sdm_glaccount ac on tsl.account_code = ac.accountid
      join /*+ MAKEIND */t_sdm_ledgerbook lb on tsl.book_code = lb.ledgerbookid
      join /*+ MAKEIND */t_sdm_costcentre cc on lb.parentcostcentreid = cc.costcentreid
      join /*+ MAKEIND */t_sdm_desk dsk on cc.parentdeskid = dsk.deskid
      join /*+ MAKEIND */t_sdm_businessarea ba on dsk.parentbusinessareaid = ba.businessareaid
      join /*+ MAKEIND */t_sdm_division div on ba.parentdivisionid = div.divisionid
      where tsl.business_date = '2011-06-29'
      and div.divisionid = 'GBM'
      and ac.classificationid in (3,4)
      group by cc.costcentreid, cc.description, ac.accountdescription;

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: