]
Wanja Pernath commented on TEIID-1409:
--------------------------------------
I can be more precise here: The NPE only occurs if the retrieved element to sum up is NULL
in SFDC.
Here: SYBAmount can be NULL for some rows of the result set. Whereas oli.TotalPrice is
never null.
Would it make sense to treat NULL values in aggregate functions as zero?
Cheers,
Wanja
NPE when trying to sum up from OUTER JOIN elements
--------------------------------------------------
Key: TEIID-1409
URL:
https://issues.jboss.org/browse/TEIID-1409
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 7.1, 7.1.1
Environment: EAP 5.1 with CXF
Reporter: Wanja Pernath
Assignee: Steven Hawkins
Following query results in a NPE:
select PrimaryBusinessUnit, sum(SYBAmount), accountId
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, accountId
note that SYBAmount is a calculated value. Also please note that a query like this:
select PrimaryBusinessUnit, sum(oli.TotalPrice), accountId
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, accountId
works as expected.
LOGs:
2010-12-30 02:26:03,896 DEBUG [org.teiid.CONNECTOR] (Worker80_QueryProcessorQueue1859)
3tWvUX8+cunn.0.6.151 Create State
2010-12-30 02:26:03,897 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
3tWvUX8+cunn.0.6.151 Processing NEW request: SELECT
sfdc.salesforce.OpportunityLineItem.PricebookEntryId,
sfdc.salesforce.Opportunity.AccountId, sfdc.salesforce.OpportunityLineItem.Year1Amount__c
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:26:03,897 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
3tWvUX8+cunn.0.6.151 Obtained execution
2010-12-30 02:26:03,897 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
sfdc.3tWvUX8+cunn.3tWvUX8+cunn.0.6: Incoming Query: SELECT
OpportunityLineItem.PricebookEntryId, Opportunity.AccountId,
OpportunityLineItem.Year1Amount__c 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:26:03,898 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
sfdc.3tWvUX8+cunn.3tWvUX8+cunn.0.6: Executing Query: SELECT Opportunity.AccountId,
(SELECT OpportunityLineItem.PricebookEntryId, OpportunityLineItem.Year1Amount__c 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:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
3tWvUX8+cunn.0.6.151 Executed command
2010-12-30 02:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
3tWvUX8+cunn.0.6.151 Sending results from connector
2010-12-30 02:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
SalesForce Object Name = Opportunity
2010-12-30 02:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
FieldCount = 2
2010-12-30 02:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
Field # 0 is AccountId
2010-12-30 02:26:05,112 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
Field # 1 is OpportunityLineItems
2010-12-30 02:26:05,112 ERROR [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1860)
Connector worker process failed for atomic-request=3tWvUX8+cunn.0.6.151
java.lang.NullPointerException
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.setElementValueInColumn(QueryExecutionImpl.java:323)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.extractValuesFromElement(QueryExecutionImpl.java:276)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.extactJoinResults(QueryExecutionImpl.java:234)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.getObjectData(QueryExecutionImpl.java:216)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.loadBatch(QueryExecutionImpl.java:199)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.getRow(QueryExecutionImpl.java:174)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.next(QueryExecutionImpl.java:166)
at
org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:281)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:266)
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)
2010-12-30 02:26:05,115 ERROR [org.teiid.PROCESSOR] (Worker80_QueryProcessorQueue1861)
Unexpected exception for request 3tWvUX8+cunn.0
java.lang.NullPointerException
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.setElementValueInColumn(QueryExecutionImpl.java:323)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.extractValuesFromElement(QueryExecutionImpl.java:276)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.extactJoinResults(QueryExecutionImpl.java:234)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.getObjectData(QueryExecutionImpl.java:216)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.loadBatch(QueryExecutionImpl.java:199)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.getRow(QueryExecutionImpl.java:174)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.next(QueryExecutionImpl.java:166)
at
org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:281)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:266)
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)
2010-12-30 02:26:05,115 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1862)
3tWvUX8+cunn.0.6.151 Processing Close : SELECT
sfdc.salesforce.OpportunityLineItem.PricebookEntryId,
sfdc.salesforce.Opportunity.AccountId, sfdc.salesforce.OpportunityLineItem.Year1Amount__c
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:26:05,115 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1862)
SynchQueryExecution.close() called
2010-12-30 02:26:05,115 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1862)
3tWvUX8+cunn.0.6.151 Closed execution
2010-12-30 02:26:05,115 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1862)
3tWvUX8+cunn.0.6.151 Remove State
2010-12-30 02:26:05,115 DEBUG [org.teiid.CONNECTOR] (Worker81_QueryProcessorQueue1862)
3tWvUX8+cunn.0.6.151 Closed connection
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: