Bug 63054

Summary: An error in a calculational "thread" of an array function shouldn't affect other "threads"
Product: POI Reporter: gallon.fizik <gallon.fizik>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: 4.0.x-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   

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