Bug 61116 - Formula evaluation fails when using matrix addition within index function call with RuntimeException
Summary: Formula evaluation fails when using matrix addition within index function cal...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.16-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-05-23 15:02 UTC by Mario
Modified: 2017-12-30 13:19 UTC (History)
0 users



Attachments
Example workbook showing the problematic formulas (9.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-05-23 15:02 UTC, Mario
Details
Patch with JUnit testcase for example workbook (2.25 KB, patch)
2017-10-26 10:50 UTC, Mario
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Mario 2017-05-23 15:02:40 UTC
Created attachment 35003 [details]
Example workbook showing the problematic formulas

The formula evaluation fails when using following formula:
  "=INDEX(A+B,1,1)"
where A and B are both matrices. I've attached an example workbook that shows the problem (matrix-addition-within-index-function.xlsx). Simply use the FormulaEvaluator to evaluate the cell 'sample2'!B2 and you will get the following exception:

"java.lang.RuntimeException: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
	at org.apache.poi.ss.formula.functions.Index.convertFirstArg(Index.java:106)
	at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:75)
	at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:116)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
	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.evaluateReference(WorkbookEvaluator.java:750)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)"

The currently implementation assumes that the maxtrix addition always returns a single double value which is fine for maxtrix formulas like "=A+B". In this case it does the same and so "A+B" is evaluated to a sum of single values from each matrix. As soon this resulting double value is passed to the index function it will fail with the exception.

How to fix?
I guess the method TwoOperandNumericOperation.evaluate(int, int, ValueEval, ValueEval) need to distiguish whether the expected output of the operation (affects not only AddPtg) is a single value or still an area.
Comment 1 Dominik Stadler 2017-09-19 20:55:42 UTC
Can you check if the changes done in bug 61469 solve this?
Comment 2 Mario 2017-10-26 10:11:58 UTC
I just checked with 3.16.final with applied patch of bug 61469 - the result is still the same exception with same error message

java.lang.RuntimeException: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
	at org.apache.poi.ss.formula.functions.Index.convertFirstArg(Index.java:107)
	at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:75)
	at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:116)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:139)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:524)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:291)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:233)
	at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
	at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:101)
Comment 3 Mario 2017-10-26 10:50:38 UTC
Created attachment 35466 [details]
Patch with JUnit testcase for example workbook
Comment 4 Yegor Kozlov 2017-12-30 13:19:17 UTC
Fixed in r1819596

I added your file in our collection of test cases. 

The main difficulty was to identify when addition should operate on matrices vs single operands. Normally, the array mode is used if the formula was saved as an array, i.e. using Ctrl-Shift-Enter. The INDEX function seems to be special, if evaluates the first arguments as a matrix even if the formula itself is single-value.