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

Incorrect IN behavior when using a SELECT clause as IN values

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 7.3
    • 7.1.1
    • Salesforce Connector
    • None
    • Workaround Exists
    • Hide

      Don't use a subselect for figuring out the condition. Just directly use the required IDs like here:

      Instead of:
      SELECT CaseNumber from Case where AccountId in (select id from account where <a filter condition>);

      Use this:
      SELECT id from Account where <a filter condition>;

      And then:
      SELECT CaseNumber from Case where accountId in (<use result from above>);

      Show
      Don't use a subselect for figuring out the condition. Just directly use the required IDs like here: Instead of: SELECT CaseNumber from Case where AccountId in (select id from account where <a filter condition>); Use this: SELECT id from Account where <a filter condition>; And then: SELECT CaseNumber from Case where accountId in (<use result from above>);

      A query like this:

      select CaseNumber
      from gss.general.Case
      where accountId in (select id from gss.general.Account where name like 'A condition which should return 1 or more accounts')

      results in a Teiid client side calculation of the result set where Teiid is scanning all Cases first and and filtering out the necessary ones.

      Expected is that Teiid would let Salesforce figure out the condition by pushing down the query:

      SELECT id from Account where name like 'A condition which should return 1 or more accounts';

      SELECT CaseNumber from Case where AccountId IN ('id-1', 'id-2', 'id-3'....)

      Here's the log detail:

      2010-12-30 22:26:00,162 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) hO4lUcI/KrcA.0.5.165 Obtained execution
      2010-12-30 22:26:00,162 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) gss.hO4lUcI/KrcA.hO4lUcI/KrcA.0.5: Incoming Query: SELECT Case.AccountId, Case.OwnerId, Case.CaseNumber FROM Case
      2010-12-30 22:26:00,162 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) gss.hO4lUcI/KrcA.hO4lUcI/KrcA.0.5: Executing Query: SELECT Case.AccountId, Case.OwnerId, Case.CaseNumber FROM Case
      2010-12-30 22:26:09,847 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) hO4lUcI/KrcA.0.5.165 Executed command
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) hO4lUcI/KrcA.0.5.165 Sending results from connector
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) SalesForce Object Name = Case
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) FieldCount = 3
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) Field # 0 is AccountId
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) Field # 1 is OwnerId
      2010-12-30 22:26:09,848 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1961) Field # 2 is CaseNumber
      2010-12-30 22:26:09,853 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1963) hO4lUcI/KrcA.0.5.165 Processing MORE request
      2010-12-30 22:26:09,854 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1963) hO4lUcI/KrcA.0.5.165 Sending results from connector
      2010-12-30 22:26:09,855 DEBUG [org.teiid.CONNECTOR] (Worker90_QueryProcessorQueue1962) hO4lUcI/KrcA.0.1.166 Create State
      2010-12-30 22:26:09,856 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) hO4lUcI/KrcA.0.1.166 Processing NEW request: SELECT gss.salesforce.Account.Id FROM gss.salesforce.Account WHERE gss.salesforce.Account.Name LIKE 'A condition%'
      2010-12-30 22:26:09,861 INFO [org.apache.cxf.service.factory.ReflectionServiceFactoryBean] (Worker92_QueryProcessorQueue1964) Creating Service

      {urn:partner.soap.sforce.com}

      SforceService from WSDL: vfszip:/home/jboss/teiid73/server/teiid73/deploy/teiid/connectors/salesforce-api-7.1.1.jar/partner_v17.wsdl
      2010-12-30 22:26:09,861 INFO [org.apache.cxf.service.factory.ReflectionServiceFactoryBean] (Worker92_QueryProcessorQueue1964) Creating Service

      {urn:partner.soap.sforce.com}

      SforceService from WSDL: vfszip:/home/jboss/teiid73/server/teiid73/deploy/teiid/connectors/salesforce-api-7.1.1.jar/partner_v17.wsdl
      2010-12-30 22:26:11,967 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) hO4lUcI/KrcA.0.1.166 Obtained execution
      2010-12-30 22:26:11,967 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) gss.hO4lUcI/KrcA.hO4lUcI/KrcA.0.1: Incoming Query: SELECT Account.Id FROM Account WHERE Account.Name LIKE 'A condition%'
      2010-12-30 22:26:11,967 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) gss.hO4lUcI/KrcA.hO4lUcI/KrcA.0.1: Executing Query: SELECT Account.Id FROM Account WHERE Account.Name LIKE 'A condition%'
      2010-12-30 22:26:12,214 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) hO4lUcI/KrcA.0.1.166 Executed command
      2010-12-30 22:26:12,214 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) hO4lUcI/KrcA.0.1.166 Sending results from connector
      2010-12-30 22:26:12,214 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) SalesForce Object Name = Account
      2010-12-30 22:26:12,214 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) FieldCount = 0
      2010-12-30 22:26:12,214 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1964) hO4lUcI/KrcA.0.1.166 Obtained last batch, total row count: 2
      2010-12-30 22:26:12,225 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1967) hO4lUcI/KrcA.0.5.165 Processing MORE request
      2010-12-30 22:26:12,225 DEBUG [org.teiid.CONNECTOR] (Worker91_QueryProcessorQueue1967) hO4lUcI/KrcA.0.5.165 Sending results from connector
      2010-12-30 22:26:12,228 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1968) hO4lUcI/KrcA.0.1.166 Processing Close : SELECT gss.salesforce.Account.Id FROM gss.salesforce.Account WHERE gss.salesforce.Account.Name LIKE 'A condition%'
      2010-12-30 22:26:12,228 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1968) SynchQueryExecution.close() called
      2010-12-30 22:26:12,228 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1968) hO4lUcI/KrcA.0.1.166 Closed execution
      2010-12-30 22:26:12,228 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1968) hO4lUcI/KrcA.0.1.166 Remove State
      2010-12-30 22:26:12,228 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1968) hO4lUcI/KrcA.0.1.166 Closed connection
      2010-12-30 22:26:12,232 DEBUG [org.teiid.CONNECTOR] (Worker93_QueryProcessorQueue1969) hO4lUcI/KrcA.0.5.165 Processing MORE request
      2010-12-30 22:26:12,232 DEBUG [org.teiid.CONNECTOR] (Worker93_QueryProcessorQueue1969) hO4lUcI/KrcA.0.5.165 Sending results from connector

      [.... n times repeated ]

      2010-12-30 22:26:32,093 DEBUG [org.teiid.CONNECTOR] (New I/O server worker #1-2) hO4lUcI/KrcA.0.5.165 Processing CANCEL request
      2010-12-30 22:26:32,093 DEBUG [org.teiid.CONNECTOR] (New I/O server worker #1-2) SynchQueryExecution.cancel() called
      2010-12-30 22:26:32,093 DEBUG [org.teiid.CONNECTOR] (New I/O server worker #1-2) The atomic request hO4lUcI/KrcA.0.5.165 has been canceled.
      2010-12-30 22:26:32,657 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1997) hO4lUcI/KrcA.0.5.165 Processing Close : SELECT gss.salesforce.Case_.AccountId, gss.salesforce.Case_.OwnerId, gss.salesforce.Case_.CaseNumber FROM gss.salesforce.Case_
      2010-12-30 22:26:32,658 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1997) SynchQueryExecution.close() called
      2010-12-30 22:26:32,658 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1997) hO4lUcI/KrcA.0.5.165 Closed execution
      2010-12-30 22:26:32,659 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1997) hO4lUcI/KrcA.0.5.165 Remove State
      2010-12-30 22:26:32,659 DEBUG [org.teiid.CONNECTOR] (Worker92_QueryProcessorQueue1997) hO4lUcI/KrcA.0.5.165 Closed connection

            rhn-engineering-shawkins Steven Hawkins
            wpernath_jira Wanja Pernath (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: