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

Steven Hawkins (JIRA) jira-events at lists.jboss.org
Fri Jan 7 10:22:18 EST 2011 

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

Steven Hawkins resolved TEIID-1411.
-----------------------------------

    Fix Version/s: 7.3
       Resolution: Done


Marking as resolved in 7.3 as we should automatically consider the join form of the query.  The workaround will have to suffice for 7.1.1.  There will be another jira created to handle dependent semi-joins if needed.

> 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