Bug 58591

Summary: Formula evaluation of IF-Equals "IF(A1=1, B1, C1)" returns incorrect result if A1 cell value is TRUE
Product: POI Reporter: Javen O'Neal <onealj>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: minor    
Priority: P2    
Version: 3.14-dev   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: formula evaluation unit tests
formula evaluation unit tests

Description Javen O'Neal 2015-11-06 07:36:47 UTC
There was a discussion on dev@poi.apache.org about how to use formula evaluation, and I realized we're a little sparse on unit tests.

I wrote a few unit tests and discovered that POI's formula evaluation of A1=1 when A1's value is TRUE (boolean cell type) is inconsistent with LibreOffice's formula evaluation implementation. For the formula "IF(A1=1, B1, C1)", all other data types for A1 are consistent with LibreOffice's evaluation of the formula.
Comment 1 Javen O'Neal 2015-11-06 07:40:30 UTC
Created attachment 33262 [details]
formula evaluation unit tests

testIFEqualsFormulaEvaluation_Boolean currently fails with POI trunk (r1712907, 2015-11-05, post-POI 3.13, pre POI 3.14beta1)
It has been annotated with an @Ignore until this bug is resolved.
Comment 2 Javen O'Neal 2015-11-06 08:58:28 UTC
Created attachment 33263 [details]
formula evaluation unit tests

I added in a few more tests:
numeric coerced to string
string coerced to numeric
boolean simple (no IF statement)
blank cell inverted with NOT (another failing case)
blank cell inverted with NOT simple (no IF statement)
Comment 3 Javen O'Neal 2015-11-07 04:10:20 UTC
All of these formulas can probably be reduced down to an equality test between different data types that can be coerced to the same data types then compared with regular boolean logic.

Thanks for Jason Tomforde for asking the question on user@[1] that triggered the finding of this bug.

Added these unit tests in r1713080, with @Ignore annotations applied to failing tests.

[1] https://mail-archives.apache.org/mod_mbox/poi-user/201511.mbox/%3CSNT150-W744ED91F30ADE685B52EF4C5290%40phx.gbl%3E
Thread: http://thread.gmane.org/gmane.comp.jakarta.poi.user/21417