Bug Hunting Session
Bug 88576 - functions IF, IFNA and IFERROR empty arguments are treated incorrectly
Summary: functions IF, IFNA and IFERROR empty arguments are treated incorrectly
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: Other All
: medium normal
Assignee: Winfried Donkers (retired)
URL:
Whiteboard: target:4.5.0 target:4.3.7 target:4.4.2
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-19 09:42 UTC by raal
Modified: 2015-02-13 12:51 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test documents before and after the export to xlsx (15.21 KB, application/x-compressed)
2015-01-19 09:42 UTC, raal
Details
use cases jump functions (4.80 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-23 16:41 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2015-01-19 09:42:35 UTC
Created attachment 112452 [details]
test documents before and after the export to xlsx

In bug 59819 functions IFNA and IFERROR was added.

According to specification is second argument of functions mandatory.

spec> http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018448_715980110

Calc allows create functions without second argument and returns results. After export to XLSX this create incompatibility.
Also export of function IFNA to XLSX is broken (same symptoms as in bug 59819). 
Steps to reproduce Error1:
- open iferror_ifna_second argument.ods
- save as .xlsx
- open in excel

Actual results
Error message: broken file, repair. After repair:
A1 = 1 (cached value; formula lost)
A2= =_xlfn.IFNA(F1)

***
IFERROR with second argument is correctly exported to .xlsx.  
IFNA with second argument is not correctly exported to .xlsx -> =_xlfn.IFNA(E1;E2)

Tested with LO 4.3.5, win7
Comment 1 raal 2015-01-19 12:02:13 UTC
According to MS help (https://support.office.com/en-nz/article/IFNA-function-6626c961-a569-42fc-a49d-79b4951fd461?ui=en-US&rs=en-NZ&ad=NZ) is function IFNA for for Excel 2013 - i tested with Excel 2010 => saving IFNA is probably correct.
Comment 2 Winfried Donkers (retired) 2015-01-19 12:09:00 UTC
I will investigate.
Comment 3 Winfried Donkers (retired) 2015-01-19 16:30:23 UTC
Calc's IFERROR and IFNA function use the same code. The code does not check the number of arguments.
Neither does the code use an empty string as second argument when not given (as is the case with the Excel functions IFERROR and IFNA).

I set the status to new.

I suggest we use the Excel behaviour in Calc too, and put this as an OpenFormula extension in the wiki (https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/List_of_LibreOffice_OpenFormula_Extensions).

Eike, Raal, do you agree?
Comment 4 raal 2015-01-19 19:13:04 UTC
(In reply to Winfried Donkers from comment #3)
Hello,

> I suggest we use the Excel behaviour in Calc too, and put this as an
> OpenFormula extension in the wiki
> (https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/
> List_of_LibreOffice_OpenFormula_Extensions).
> 
> Eike, Raal, do you agree?

Tested iferror with excel2010 and excel doesn't allow to create formula with only one argument,  so we should do the same
Comment 5 Winfried Donkers (retired) 2015-01-20 12:59:14 UTC
(In reply to raal from comment #4)
> Tested iferror with excel2010 and excel doesn't allow to create formula with
> only one argument,  so we should do the same

OK, that's clear. I will start working on a fix.
Comment 6 Winfried Donkers (retired) 2015-01-23 16:41:17 UTC
Created attachment 112732 [details]
use cases jump functions

@Raal:
Could you please copy the results from Excel into row B, so that I can use that to compare the behaviour?

As you can see, IFERROR( a ) is no longer accepted, but empty arguments still are (until they are to be returned).
IFNA is not in the document as the code is shared with IFERROR.
Comment 7 Winfried Donkers (retired) 2015-02-11 08:27:28 UTC
As the problems applies to the second and third argument of function IF() as well, I changed subject of this bug report. Fix will cover all three functions.
Comment 8 Commit Notification 2015-02-11 13:36:10 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0b75eda1090f92adc678ceff2565da2dc7d9328c

tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA()

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2015-02-11 14:02:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6583f4e30015164af4972921b5bb7880dfb65f65

tdf#88576 check that two parameters are given for IFERROR() and IFNA()

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Eike Rathke 2015-02-11 14:24:43 UTC
Pending reviews
https://gerrit.libreoffice.org/14421 for 4-4
https://gerrit.libreoffice.org/14422 for 4-4-1
https://gerrit.libreoffice.org/14423 for 4-3
Comment 11 Commit Notification 2015-02-13 12:51:02 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=14b76ff3a98898fcccfa6e48ca8ae1e7bea802e2&h=libreoffice-4-3

tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA()

It will be available in 4.3.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2015-02-13 12:51:15 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3f7558360c4805076a1705c204c9e8dba12b7358&h=libreoffice-4-4

tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA()

It will be available in 4.4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.