MALFORMED Query when trying to execute a query with OUTER JOINs which only contains elements from JOINed source --------------------------------------------------------------------------------------------------------------- Key: TEIID-1413 URL: https://issues.jboss.org/browse/TEIID-1413 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