Bug 55843 - Formula evaluation ROW() results in two different results.
Summary: Formula evaluation ROW() results in two different results.
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC Linux
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-12-04 16:45 UTC by Detlef Brendle
Modified: 2014-08-31 21:50 UTC (History)
1 user (show)



Attachments
junit test case (1.09 KB, text/x-java)
2013-12-04 16:45 UTC, Detlef Brendle
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Detlef Brendle 2013-12-04 16:45:25 UTC
Created attachment 31090 [details]
junit test case

Consider the attached test case:
- If I evaluate the formula:
IF(B1=0,"",((ROW()-ROW(A$1))*12))
the result is:
org.apache.poi.ss.usermodel.CellValue [-12.0]

which in my eyes is wrong.

When negating the condition and  the formula results in the correct value:
IF(NOT(B1=0),((ROW()-ROW(A$1))*12),"")
Comment 1 Alessandro Guarascio 2014-08-19 14:04:13 UTC
This problem occurs also with POI 3.10. Generally speaking, I've noticed that none of ROW() or COLUMN() functions work fine if used as THIRD argument of an IF() function.

Examples are:
IF(FALSE,0,ROW()) returns FALSE instead of <row number>
IF(FALSE,0,-ROW()) returns 0.0 instead of -<row number>

Same behavior with COLUMN() Function.
If other functions (SUM(), NOW(), etc..) are used, it works as expected
If these fumctions are used as SECOND argument (TRUE branch) it works as expected.

Debugging POI code I've noticed the following "strange" if statement, which, in my opinion is the cause of the wrong behavior:

WorkbookEvaluator line 479 (POI 3.10)
if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) {
    // this is an if statement without a false param [...]
    i++;							   
    stack.push(BoolEval.FALSE);
}
When processing IF function, nextPtg IS the ROW() function and this code makes it always return FALSE (or 0.0).
Comment 2 Dominik Stadler 2014-08-31 21:50:18 UTC
There was some optimization for IF() added some time ago under Bug 48195 which did not take a nested function as else-part into consideration.

This should be fixed with r1621641.