Bug 63054 - An error in a calculational "thread" of an array function shouldn't affect other "threads"
Summary: An error in a calculational "thread" of an array function shouldn't affect ot...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.0.x-dev
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-01-02 11:39 UTC by gallon.fizik@gmail.com
Modified: 2019-01-08 13:19 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description gallon.fizik@gmail.com 2019-01-02 11:39:15 UTC
Whenever in array mode, 
  a) operands are collected for an array function or 
  b) array function is evaluated, 
and an ErrorEval is encountered, an EvaluationException is thrown, and the whole result is set to ErrorEval. This doesn't correspond to Excel behavior, where calculations fall into separate "threads", so that an error in a "thread" doens't affect other ones.

Example:
A |   B   | C |   D   |   E   |   F   |
1 | #N/A! | 2 |     {=A1:C1*A1:C1}    |
=======================================
expected      |   1   | #N/A! |   4   |
actual        | #N/A! | #N/A! | #N/A! |

A testcase:

@Test
public void elementwiseEvaluation() {
    Workbook workbook = new XSSFWorkbook();
    Row row = workbook.createSheet().createRow(0);
    row.createCell(0).setCellValue(1);
    row.createCell(1).setCellErrorValue(FormulaError.NUM.getCode());
    row.createCell(2).setCellValue(2);
    row.getSheet().setArrayFormula("A1:C1*A1:C1", CellRangeAddress.valueOf("D1:F1"));

    workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

    assertEquals(CellType.NUMERIC, row.getCell(3).getCachedFormulaResultType());
    assertEquals(1, row.getCell(3).getNumericCellValue(), 0);

    assertEquals(CellType.ERROR, row.getCell(4).getCachedFormulaResultType());
    assertEquals(FormulaError.NUM.getCode(), row.getCell(4).getErrorCellValue());

    assertEquals(CellType.NUMERIC, row.getCell(5).getCachedFormulaResultType());
    assertEquals(4, row.getCell(5).getNumericCellValue(), 0);
}
Comment 1 Yegor Kozlov 2019-01-08 13:19:36 UTC
The fix in bug #62904 fixed this issue as well. 

The test cases are in TwoOperandNumericFunctionTestCaseData.xls
The only case that fails is #NAME?, but that's the way POI handles unknown names, it's not a problem in evaluation of array arguments.
When creating a spreadsheet manually you can set a cell formula to reference an unknown name, e.g. =not_here
and WorkbookEvaluator will fail early with

java.lang.RuntimeException: Don't now how to evaluate name 'not_here'
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForNameRecord(WorkbookEvaluator.java:771)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:681)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:548)

it doesn't even make it to the evaluation code. 


Regards,
Yegor