Bug 58591 - Formula evaluation of IF-Equals "IF(A1=1, B1, C1)" returns incorrect result if A1 cell value is TRUE
Summary: Formula evaluation of IF-Equals "IF(A1=1, B1, C1)" returns incorrect result i...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.14-dev
Hardware: PC Linux
: P2 minor (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2015-11-06 07:36 UTC by Javen O'Neal
Modified: 2015-11-07 04:10 UTC (History)
0 users

formula evaluation unit tests (10.58 KB, patch)
2015-11-06 07:40 UTC, Javen O'Neal
Details | Diff
formula evaluation unit tests (12.58 KB, patch)
2015-11-06 08:58 UTC, Javen O'Neal
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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