[JBoss JIRA] Created: (TEIID-1410) MALFORMED Query when trying to execute a query with OUTER JOINs which only contains elements from JOINed source
by Wanja Pernath (JIRA)
MALFORMED Query when trying to execute a query with OUTER JOINs which only contains elements from JOINed source
---------------------------------------------------------------------------------------------------------------
Key: TEIID-1410
URL: https://issues.jboss.org/browse/TEIID-1410
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 7.1.1
Environment: EAP 5.1 with CXF and Teiid 7.1.1
Reporter: Wanja Pernath
Assignee: Steven Hawkins
If you're executing a query like this:
select PrimaryBusinessUnit, sum(oli.totalPrice)
from general.salesforce.Opportunity o left outer join general.salesforce.OpportunityLineItem oli ON oli.OpportunityId = o.id,
general.salesforce.PricebookEntry pbe, general.salesforce.Product2 p
where (PricebookEntryId = pbe.Id) AND (p.Id = Product2Id)
and isClosed=true
and isWon = true
and CloseDate > '2008-03-01'
and accountId = '00130000009SwiLAAS'
group by PrimaryBusinessUnit
you end up with a MALFORMED_QUERY exception.
2010-12-30 02:32:07,946 DEBUG [org.teiid.CONNECTOR] (Worker82_QueryProcessorQueue1867) iQQ1QVCn8ndT.0.6.153 Create State
2010-12-30 02:32:07,946 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1868) iQQ1QVCn8ndT.0.6.153 Processing NEW request: SELECT sfdc.salesforce.OpportunityLineItem.PricebookEntryId, sfdc.salesforce.OpportunityLineItem.TotalPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.OpportunityLineItem.OpportunityId = sfdc.salesforce.Opportunity.Id WHERE (sfdc.salesforce.Opportunity.StageName <> 'Closed Lost') AND (sfdc.salesforce.Opportunity.ForecastCategory <> 'Omitted') AND (sfdc.salesforce.Opportunity.OpportunityType__c <> 'REBOOK') AND (sfdc.salesforce.Opportunity.IsClosed = TRUE) AND (sfdc.salesforce.Opportunity.IsWon = TRUE) AND (sfdc.salesforce.Opportunity.CloseDate > {d'2008-03-01'}) AND (sfdc.salesforce.Opportunity.AccountId = '00130000009SwiLAAS')
2010-12-30 02:32:07,946 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1868) iQQ1QVCn8ndT.0.6.153 Obtained execution
2010-12-30 02:32:07,946 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1868) sfdc.iQQ1QVCn8ndT.iQQ1QVCn8ndT.0.6: Incoming Query: SELECT OpportunityLineItem.PricebookEntryId, OpportunityLineItem.TotalPrice FROM Opportunity LEFT OUTER JOIN OpportunityLineItem ON OpportunityLineItem.OpportunityId = Opportunity.Id WHERE Opportunity.StageName <> 'Closed Lost' AND Opportunity.ForecastCategory <> 'Omitted' AND Opportunity.OpportunityType__c <> 'REBOOK' AND Opportunity.IsClosed = TRUE AND Opportunity.IsWon = TRUE AND Opportunity.CloseDate > {d '2008-03-01'} AND Opportunity.AccountId = '00130000009SwiLAAS'
2010-12-30 02:32:07,947 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1868) sfdc.iQQ1QVCn8ndT.iQQ1QVCn8ndT.0.6: Executing Query: SELECT , (SELECT OpportunityLineItem.PricebookEntryId, OpportunityLineItem.TotalPrice FROM OpportunityLineItems) FROM Opportunity WHERE (Opportunity.StageName != 'Closed Lost') AND ((Opportunity.ForecastCategory != 'Omitted') AND ((Opportunity.OpportunityType__c != 'REBOOK') AND ((Opportunity.IsClosed = true) AND ((Opportunity.IsWon = true) AND ((Opportunity.CloseDate > 2008-03-01) AND (Opportunity.AccountId = '00130000009SwiLAAS'))))))
2010-12-30 02:32:08,434 WARN [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1868) Connector worker process failed for atomic-request=iQQ1QVCn8ndT.0.6.153
[TranslatorException]com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','
1 [ResourceException]com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','
2 [MalformedQueryFault]MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:149)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:263)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:281)
at org.teiid.dqp.internal.process.DataTierTupleSource.access$0(DataTierTupleSource.java:274)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:123)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:1)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:108)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: javax.resource.ResourceException: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:176)
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:135)
... 12 more
Caused by: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','
at org.apache.cxf.jaxws.JaxWsClientProxy.invoke(JaxWsClientProxy.java:131)
at $Proxy169.query(Unknown Source)
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:171)
... 13 more
2010-12-30 02:32:08,436 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1870) iQQ1QVCn8ndT.0.6.153 Processing Close : SELECT sfdc.salesforce.OpportunityLineItem.PricebookEntryId, sfdc.salesforce.OpportunityLineItem.TotalPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.OpportunityLineItem.OpportunityId = sfdc.salesforce.Opportunity.Id WHERE (sfdc.salesforce.Opportunity.StageName <> 'Closed Lost') AND (sfdc.salesforce.Opportunity.ForecastCategory <> 'Omitted') AND (sfdc.salesforce.Opportunity.OpportunityType__c <> 'REBOOK') AND (sfdc.salesforce.Opportunity.IsClosed = TRUE) AND (sfdc.salesforce.Opportunity.IsWon = TRUE) AND (sfdc.salesforce.Opportunity.CloseDate > {d'2008-03-01'}) AND (sfdc.salesforce.Opportunity.AccountId = '00130000009SwiLAAS')
2010-12-30 02:32:08,436 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1870) SynchQueryExecution.close() called
2010-12-30 02:32:08,436 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1870) iQQ1QVCn8ndT.0.6.153 Closed execution
2010-12-30 02:32:08,436 WARN [org.teiid.PROCESSOR] (Worker82_QueryProcessorQueue1869) Processing exception 'com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
SELECT , (SELECT OpportunityLineItem.PricebookEntryId
^
ERROR at Row:1:Column:7
unexpected token: ','' for request iQQ1QVCn8ndT.0. Exception type org.teiid.core.TeiidProcessingException thrown from org.apache.cxf.jaxws.JaxWsClientProxy.invoke(JaxWsClientProxy.java:131). Enable more detailed logging to see the entire stacktrace.
2010-12-30 02:32:08,436 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1870) iQQ1QVCn8ndT.0.6.153 Remove State
2010-12-30 02:32:08,437 DEBUG [org.teiid.CONNECTOR] (Worker84_QueryProcessorQueue1870) iQQ1QVCn8ndT.0.6.153 Closed connection
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 5 months
[JBoss JIRA] Created: (TEIID-1402) SELECTing data from salesforce with a WHERE condition which includes ID results in a NPE
by Wanja Pernath (JIRA)
SELECTing data from salesforce with a WHERE condition which includes ID results in a NPE
----------------------------------------------------------------------------------------
Key: TEIID-1402
URL: https://issues.jboss.org/browse/TEIID-1402
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 7.1.1, 7.3
Environment: Teiid 7.1.x, 7.2 and current 7.3 Alpha2 trunk running on EAP 5.1
Reporter: Wanja Pernath
Assignee: Steven Hawkins
Steps to reproduce:
1. Use teiid-designer to import salesforce structure
2. Build and deploy a VDB to teiid<version>
3. Now find an existing ID of for example an Account
// just to get a valid ID
select ID from Account where Name like 'xyz';
// now try to fetch the Account with just ID in WHERE condition
select ID, Name from Account where ID = 'xyz.id'
You'll get a NPE with the following stacktrace (this is current subversion trunk).
Note, this only happens if you're using salesforce connector. It does not happen if the table is materialized.
2010-12-20 23:27:22,753 DEBUG [org.teiid.CONNECTOR] (Worker185_QueryProcessorQueue2804) uPHr01U+UKiU.0.1.231 Create State
2010-12-20 23:27:22,754 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2805) uPHr01U+UKiU.0.1.231 Processing NEW request: SELECT gss.salesforce.Account.Id, gss.salesforce.Account.Name FROM gss.salesforce.Account WHERE gss.salesforce.Account.Id = '001A000000K65hEIAR'
2010-12-20 23:27:23,805 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker186_QueryProcessorQueue2805) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.
2010-12-20 23:27:24,926 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2805) Login was successful for username wpernath(a)redhat.com.gss
2010-12-20 23:27:24,928 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker186_QueryProcessorQueue2805) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.
2010-12-20 23:27:26,125 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2805) uPHr01U+UKiU.0.1.231 Obtained execution
2010-12-20 23:27:26,125 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2805) gss.uPHr01U+UKiU.uPHr01U+UKiU.0.1: Incoming Query: SELECT Account.Id, Account.Name FROM Account WHERE Account.Id = '001A000000K65hEIAR'
2010-12-20 23:27:26,125 ERROR [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2805) Connector worker process failed for atomic-request=uPHr01U+UKiU.0.1.231
java.lang.NullPointerException
at org.teiid.translator.salesforce.execution.visitors.SelectVisitor.getIdInCriteria(SelectVisitor.java:214)
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:140)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:263)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:281)
at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:71)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:123)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:120)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:108)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
2010-12-20 23:27:26,126 ERROR [org.teiid.PROCESSOR] (Worker185_QueryProcessorQueue2806) Unexpected exception for request uPHr01U+UKiU.0
java.lang.NullPointerException
at org.teiid.translator.salesforce.execution.visitors.SelectVisitor.getIdInCriteria(SelectVisitor.java:214)
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:140)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:263)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:281)
at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:71)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:123)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:120)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:108)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
2010-12-20 23:27:26,126 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2807) uPHr01U+UKiU.0.1.231 Processing Close : SELECT gss.salesforce.Account.Id, gss.salesforce.Account.Name FROM gss.salesforce.Account WHERE gss.salesforce.Account.Id = '001A000000K65hEIAR'
2010-12-20 23:27:26,126 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2807) SynchQueryExecution.close() called
2010-12-20 23:27:26,126 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2807) uPHr01U+UKiU.0.1.231 Closed execution
2010-12-20 23:27:26,126 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2807) uPHr01U+UKiU.0.1.231 Remove State
2010-12-20 23:27:26,126 DEBUG [org.teiid.CONNECTOR] (Worker186_QueryProcessorQueue2807) uPHr01U+UKiU.0.1.231 Closed connection
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 5 months
[JBoss JIRA] Created: (TEIID-1376) Add support for pushing joins through a union
by Steven Hawkins (JIRA)
Add support for pushing joins through a union
---------------------------------------------
Key: TEIID-1376
URL: https://jira.jboss.org/browse/TEIID-1376
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Affects Versions: 7.2
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 7.3
Given a query structure such as:
inner join
union all
a
b
union all
c
d
where a,b,c,d are access nodes to a source that supports joins and there is a constant column projected by each union branch and that column is used in the join predicates, then we should be able examine
union all
inner join
a
b
inner join
a
c
...
to determine if any of the joins will no longer be necessary.
This is a possible pattern in a multi-source or partitioning scenario.
In some scenarios there may not be an actual projected literal, which could be accommodated if a foreign key could be marked as partitioned.
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 5 months
[JBoss JIRA] Created: (TEIID-1388) Appears to be a limit of only one functional SalesForce connector in Teiid at a time.
by John Doyle (JIRA)
Appears to be a limit of only one functional SalesForce connector in Teiid at a time.
-------------------------------------------------------------------------------------
Key: TEIID-1388
URL: https://issues.jboss.org/browse/TEIID-1388
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 7.1.1
Environment: Teiid 7.1.1 in EDS ER5, JBDS Beta2, RHEL 5.1
Reporter: John Doyle
Assignee: John Doyle
Priority: Blocker
It appears they only one SalesForce connector can be run at a time. If you preview a model from Designer and there are no SF connections in Teiid, it works. When you create a DS for the VDB and deploy, the VDB cannot get connections from SalesForce. If you delete the preview VDB and Datasource, then the deployed VDB begins working.
I originally suspected that the problem might be that we are running out of connections; SalesForce limits you to 5 simultaneous connections per username/password. But I deployed the two DataSources with different credentials, and the result did not change. Need to investigate more.
Think that we do need to default the SF DS to a pool of 1 and then doc that it should be bumped to 5 for production. The current default is 10, and that should never be. Even 5 will exhaust the limit with just a preview connection.
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 5 months