Bug 60858 - Sumifs throws NullPointerException
Summary: Sumifs throws NullPointerException
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 56420
Blocks:
  Show dependency tree
 
Reported: 2017-03-13 20:09 UTC by Daniel John
Modified: 2017-03-15 08:56 UTC (History)
4 users (show)



Attachments
Complex sheet producing a lot of errors while evaluating (5.09 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-14 10:08 UTC, Daniel John
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Daniel John 2017-03-13 20:09:38 UTC
+++ This bug was initially created as a clone of Bug #56420 +++

When I use SUMIFS function in xlsx file.
If predicate value is cell and cell value is null, org.apache.poi.ss.formula.functions.Sumifs.accumulate method throws NullPointerException.

This is stack trace:

java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Sumifs.sumMatchingCells(Sumifs.java:138)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:79)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:550)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:317)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:259)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellEnum(BaseFormulaEvaluator.java:193)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells(BaseFormulaEvaluator.java:268)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:92)

And this is the code that throws NullPointerException:

if (!mp.matches(aeRange.getRelativeValue(r, c))) {
                        matches = false;
                        break;
                    }

It is the same problem as in Bug #56420.
Comment 1 Greg Woolsey 2017-03-13 20:35:02 UTC
Can you try with the latest 3.16 nightly?  I suspect I fixed this when I completely redid SUMIFS() and COUNTIFS() for 

https://bz.apache.org/bugzilla/show_bug.cgi?id=56822

in commit r1783037.
Comment 2 Daniel John 2017-03-14 10:05:43 UTC
Problem is still there:

Error while evaluating formula:IF(Q2="LT",SUMIFS(O2:O1,N2:N1,N2,A2:A1,A2)," ")
java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Baseifs.aggregateMatchingCells(Baseifs.java:138)
at org.apache.poi.ss.formula.functions.Baseifs.evaluate(Baseifs.java:70)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:42)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:137)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:77)
at com.decadis.jira.xport.Exporter.createXSSFWorkbook2_0(Exporter.java:169)
at com.decadis.jira.xport.rest.QuickExportRest.getWorkbook(QuickExportRest.java:310)
at com.decadis.jira.xport.rest.QuickExportRest.access$0(QuickExportRest.java:238)
at com.decadis.jira.xport.rest.QuickExportRest$1.write(QuickExportRest.java:94)

But the file I have produces more than one failure....

I will attach it.
Comment 3 Daniel John 2017-03-14 10:08:03 UTC
Created attachment 34823 [details]
Complex sheet producing a lot of errors while evaluating

Complex sheet producing a lot of errors while evaluating:

Error while evaluating formula:IF(Q2="LT",SUMIFS(O2:O1,N2:N1,N2,A2:A1,A2)," ")
java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Baseifs.aggregateMatchingCells(Baseifs.java:138)
at org.apache.poi.ss.formula.functions.Baseifs.evaluate(Baseifs.java:70)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:42)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:137)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:77)

--------------------------

Error while evaluating formula:IF(Q2="LT",SUMIFS(E2:E1,N2:N1,N2,A2:A1,A2)-X2," ")
java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Baseifs.aggregateMatchingCells(Baseifs.java:138)
at org.apache.poi.ss.formula.functions.Baseifs.evaluate(Baseifs.java:70)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:42)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:137)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:77)


-----------------
Error while evaluating formula:IF(V2=0,IF(Q2<>"LT"," ",IF(G2<=6," ",IF(AND(G2<=9,F2>=0.02)," ",IF(F2>=0.03," ","zu kurze Pausen"))))," ")
java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Baseifs.aggregateMatchingCells(Baseifs.java:138)
at org.apache.poi.ss.formula.functions.Baseifs.evaluate(Baseifs.java:70)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:42)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:723)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.RelationalOperationEval.evaluate(RelationalOperationEval.java:64)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:137)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:77)

----------------------------

Error while evaluating formula:IF(G2<>" ",IF(G2>10,G2-10," ")," ")
java.lang.NullPointerException
at org.apache.poi.ss.formula.functions.Baseifs.aggregateMatchingCells(Baseifs.java:138)
at org.apache.poi.ss.formula.functions.Baseifs.evaluate(Baseifs.java:70)
at org.apache.poi.ss.formula.functions.Sumifs.evaluate(Sumifs.java:42)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:723)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.RelationalOperationEval.evaluate(RelationalOperationEval.java:64)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:137)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:77)

------------------------------
Comment 4 Greg Woolsey 2017-03-14 21:42:33 UTC
Thank you for the test file and stack trace.  I was able to confirm the regression introduced by the refactoring to fix #56822, add your file as a unit test, and fix the bug in r1786953.
Comment 5 Daniel John 2017-03-15 08:56:47 UTC
Hi Greg,

great Job! Thank you!