[teiid-issues] [JBoss JIRA] Closed: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values

Steven Hawkins (JIRA) jira-events at lists.jboss.org
Tue Apr 5 14:19:34 EDT 2011 

     [ https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-1411.
---------------------------------



> Incorrect IN behavior when using a SELECT clause as IN values
> -------------------------------------------------------------
>
>                 Key: TEIID-1411
>                 URL: https://issues.jboss.org/browse/TEIID-1411
>             Project: Teiid
>          Issue Type: Bug
>          Components: Salesforce Connector
>    Affects Versions: 7.1.1
>         Environment: Teiid 7.1.1 on EAP 5.1 with CXF enabled
>            Reporter: Wanja Pernath
>            Assignee: Steven Hawkins
>             Fix For: 7.3
>
>
> 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

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

More information about the teiid-issues mailing list