[teiid-issues] [JBoss JIRA] Commented: (TEIID-1409) NPE when trying to sum up from OUTER JOIN elements
Wanja Pernath (JIRA)
jira-events at lists.jboss.org
Sat Jan 1 16:41:17 EST 2011
[ https://issues.jboss.org/browse/TEIID-1409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572601#comment-12572601 ]
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: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list