Bug Hunting Session
Bug 94079 - NETWORKDAYS gives error #VALUE! when Holidays range is empty
Summary: NETWORKDAYS gives error #VALUE! when Holidays range is empty
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.1.2 release
Hardware: Other All
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:5.1.0 target:5.0.3
Keywords: regression
Depends on:
Blocks:
 
Reported: 2015-09-09 23:13 UTC by Mike Kaganski
Modified: 2016-10-25 19:21 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
some uses of NETWORKDAYS (7.79 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-09-11 07:52 UTC, Winfried Donkers (retired)
Details
NETWORKDAYS with empty third param in MS Excel 2013 (22.41 KB, image/png)
2015-09-11 08:14 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2015-09-09 23:13:19 UTC
Steps to reproduce:

1. Create an empty calc sheet
2. In A1, put formula "=NOW()"
3. In A2, put formula "=NETWORKDAYS(NOW();NOW()+6;A1)"
(see that result is 4)
4. Clear A1

Expected result:
A2 should change to 5

Actual result:
A2 becomes #VALUE!

It worked as expected in 4.4 -> regression, adjusting severity appropriately.

Tested with Version: 5.0.1.2 (x64)
Build ID: 81898c9f5c0d43f3473ba111d7b351050be20261
Locale: ru-RU (ru_RU)

under Win7x64
Comment 1 raal 2015-09-10 06:15:33 UTC
Reproducible with Verze: 5.0.1.2
ID sestavení: 81898c9f5c0d43f3473ba111d7b351050be20261, win7

Works with Version 4.0.0.3 (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)
Comment 2 Winfried Donkers (retired) 2015-09-10 07:06:45 UTC
A first quick glance indicates that the problem was introduced with
commit 111952dccc1bf9e28e61c0233816248c848cbf53
(fdo#77985 make calc function NETWORKDAYS comply with ODFF1.2)

I will correct this.
Comment 3 Winfried Donkers (retired) 2015-09-11 07:28:42 UTC
The problem is connected with the changed handling of empty cells in arrays, see bug 41481.
Working on a proper fix.
Comment 4 Winfried Donkers (retired) 2015-09-11 07:52:53 UTC
Created attachment 118599 [details]
some uses of NETWORKDAYS

(In reply to Winfried Donkers from comment #3)
> The problem is connected with the changed handling of empty cells in arrays,
> see bug 41481.
> Working on a proper fix.

And the tricky part is this: technically the result #VALUE! is correct when argument3 (sequence of holidays) points to an invalid array.
In version before 4.4 an empty value was accepted, but this is not strictly correct. With version 5, this has been fixed.

I am not sure yet if this behaviour is to be regarded as a bug (which I will then fix) or 'as defined in ODFF1.2, (http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018198_715980110)
Comment 5 Winfried Donkers (retired) 2015-09-11 07:58:00 UTC
@Eike: Can you give your views with respect to this behaviour?
SCInterpreter::GetSortArray() sets nGlobalValue to errNoValue when step 4 of the description is executed (see attachment 118599 [details]). This is IMO correct behaviour.
The question is, should I set nGlobalValue back to 0 in this case, or is the use of a reference to an empty array not to be accepted?

What is the behaviour of Excel in this case?
Comment 6 Mike Kaganski 2015-09-11 08:14:09 UTC
Created attachment 118600 [details]
NETWORKDAYS with empty third param in MS Excel 2013

(In reply to Winfried Donkers from comment #5)
> What is the behaviour of Excel in this case?

It doesn't give an error, it works as expected.

Actually, I don't see requirement to throw error on empty array at the link you cited.
It feels natural to be able to define a (named) range, say, "Holidays", and use it as third parameter; then fill it as appropriate, and leave empty if needed.
Comment 7 Winfried Donkers (retired) 2015-09-11 08:33:07 UTC
(In reply to Mike Kaganski from comment #6)

> It doesn't give an error, it works as expected.
Thank you for your fast feedback
> Actually, I don't see requirement to throw error on empty array at the link
> you cited.
See http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017866_715980110

> It feels natural to be able to define a (named) range, say, "Holidays", and
> use it as third parameter; then fill it as appropriate, and leave empty if
> needed.
I understand your feelings. That's why I called it 'tricky' in comment 4 .
Comment 8 Mike Kaganski 2015-09-12 11:51:09 UTC
(In reply to Winfried Donkers from comment #7)
> See
> http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.
> html#__RefHeading__1017866_715980110

I followed every clause of 3.3 Non-Scalar Evaluation (aka 'Array expressions'), and still cannot find a place that would cover this case.

Both Parts 1 and 2 are about functions taking scalar where a matrix is passed, and discuss which cell of passed matrix should be used as actual argument to the function in specific position.

Part 2.2 describes how to process when (different-sized) matrices are passed as arguments that are expected to combine with each other. Example in Note 13 gives a good example for that.

The NETWORKDAYS problem lies in a different area: it expects a matrix in its third argument, and the size of array isn't required to correspond to something in any way; its function is only to extract those dates that are in the range of bounding dates and not weekends, and to subtract their number from scalar result. If the rmatrix contain empty cells, but at least one cell is non-empty (but even if it is outside of dates range), current implementation works OK:

A1: 2001-01-01.
B1: empty
A2: =NETWORKDAYS(NOW();NOW()+6;A1:B1)
A2 -> 5

Here A1 is outside the range, and B1 is empty, still function works.
I beleive that all-empty-cells array should be equally legal.
Comment 9 Eike Rathke 2015-09-14 13:08:15 UTC
Actually http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#NETWORKDAYS has the third parameter type defined as DateSequence, which is unrelated to non-scalar aka array expressions. A sequence can consist of zero elements, e.g. SUM(X11:X22) can be empty. Only a few functions treat empty sequences special. For DateSequence see also http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017996_715980110 and previous sections.
Comment 10 Commit Notification 2015-09-15 15:15:25 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=75bde904d5b4f756037889f2b2ddee3e34dd81b8

tdf#94079 allow empty array for holiday sequence

It will be available in 5.1.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 11 Commit Notification 2015-09-15 15:48:17 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=04ef6cf59052010cb6103e260dff6367b634acd8&h=libreoffice-5-0

tdf#94079 allow empty array for holiday sequence

It will be available in 5.0.3.

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.