1:50 p.m.
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
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
8:20 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values
[
https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1411:
---------------------------------------
This should be marked as related to TEIID-1412, but the major concern here is being memory
safe (e.g. what if the subquery returns millions of rows?).
Looking forward to Teiid 7.3, with costing information and a pk/fk relationship between
case.accountid and account.id, the engine should be able to do exactly what you are asking
because it will first convert the subquery into a join and then use a dependent join.
Just to clarify from your log, are you seeing a repeated execution of an uncorrelated
subquery? Uncorrelated subqueries should only be executed once per execution of the
containing query.
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
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
3:34 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values
[
https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin...
]
Wanja Pernath commented on TEIID-1411:
--------------------------------------
To be clear here: The inner select statement for getting accounts is executed exactly once
with the correct behavior.
Unfortunately, the select statement for getting Case information is executed without *any*
condition. This means all millions of Case rows are read and then compared with the
account information on the teiid client side.
Cheers,
wanja
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
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
9:39 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values
[
https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1411:
---------------------------------------
For 7.1.1 the workaround would be to emulate the 7.3 handling and write the query as a
join:
select CaseNumber from gss.general.Case MAKEDEP inner join (select id from
gss.general.Account where name like 'A condition which should return 1 or more
accounts') x on Case.accountid = x.id
The MAKEDEP is not required if there is costing information.
There would still be an issue in 7.3 though in true semi-join situations as the current
dependent join logic does not apply. Do you want to change this issue into an enhancement
request along for allowing dependent semi-joins?
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
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
9:22 a.m.
New subject: [JBoss JIRA] Resolved: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values
[
https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin...
]
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
1:19 p.m.
New subject: [JBoss JIRA] Closed: (TEIID-1411) Incorrect IN behavior when using a SELECT clause as IN values
[
https://issues.jboss.org/browse/TEIID-1411?page=com.atlassian.jira.plugin...
]
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
5012
days inactive
5108
days old
5 comments
2 participants
participants (2)
-
Steven Hawkins (JIRA) -
Wanja Pernath (JIRA)