Bug 122301 - FREQUENCY: force array mode on arguments and immediate caller
Summary: FREQUENCY: force array mode on arguments and immediate caller
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.3.0 target:6.2.1 target:6.2.3
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-12-24 05:25 UTC by TETSUYA GESHI
Modified: 2019-03-19 12:47 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
The location of the bug is P7 - S7. (19.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-12-24 09:03 UTC, TETSUYA GESHI
Details
Comparison LibreOffice 6.3 Master and MSO 2010 (46.68 KB, image/png)
2018-12-26 16:09 UTC, Xisco Faulí
Details
picture_excel_result (22.47 KB, image/png)
2019-01-07 19:59 UTC, GerardF
Details
Results of formulas from comment 8 and comment 11 using cell ranges instead of literal arrays (29.62 KB, image/png)
2019-01-08 05:10 UTC, Mike Kaganski
Details
What does Excel produce in A9:A13 here? (5.16 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-01-11 21:59 UTC, Eike Rathke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description TETSUYA GESHI 2018-12-24 05:25:23 UTC
Description:
The following function becomes an error in Calc.
=MAX(FREQUENCY(ROW($L$11:$L$1000),($L$11:$L$1000>=0)*ROW($L$11:$L$1000))-1)
The error message is #VALUE !.
The file does not get an error in EXCELL.
If you read an error in Calc with EXCELL, save it, and read the file in Calc, you will not get an error.
If you copy and paste the function on the file where no error occurs, you will get an error again.

Actual Results:
It will happen every time

Expected Results:
I get an error


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 raal 2018-12-24 05:44:42 UTC
Hello,

Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
(Please note that the attachment will be public, remove any sensitive information before attaching it.)
How can I eliminate confidential data from a sample document?
https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F
Thank you
Comment 2 TETSUYA GESHI 2018-12-24 09:03:15 UTC
Created attachment 147799 [details]
The location of the bug is P7 - S7.
Comment 3 GerardF 2018-12-24 13:22:37 UTC
OK, I see.

With LO Calc, the formula need to be validated as an array formula.

Excel seems to have "forced array parameters" for FREQUENCY|MAX (no need CSE)

@Eike ?
Comment 4 Xisco Faulí 2018-12-26 16:09:05 UTC
Created attachment 147843 [details]
Comparison LibreOffice 6.3 Master and MSO 2010
Comment 5 Xisco Faulí 2018-12-26 16:10:54 UTC
Also reproduced in

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

In

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

P7 and Q7 have #VALUE as well...
Comment 6 Eike Rathke 2019-01-07 15:02:40 UTC
Odd. The Microsoft documentation for FREQUENCY() says "Because FREQUENCY returns an array, it must be entered as an array formula."
https://support.office.com/en-US/article/FREQUENCY-function-44E3BE2B-ECA0-42CD-A3F7-FD9EA898FDB9

Which on the other hand is misleading, because if just a data range and a bins range is given and not in array mode the function returns an array nevertheless, but they probably mean that the function is to be entered as array formula across a selected cell range to display the array and not just the first value.

What's problematic here is the $L$11:$L$1000>=0 subexpression, which in this context with a range works only in array mode and otherwise doesn't result in an implicit intersection when given on row 7, hence the #VALUE! error; also ROW() not in array mode returns only the first row of a range, not an array.

ECMA-376-1:2016 OOXML 18.17.7.127 FREQUENCY
says "A call to FREQUENCY shall be an array formula."
but on the other hand doesn't say anything like that about 18.17.7.309 SUMPRODUCT which definitely forces array mode on its arguments, or any other function known to force array mode.

We may have to submit an update to ODFF as well that doesn't state the ForceArray attribute
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#FREQUENCY
Comment 7 Eike Rathke 2019-01-07 17:05:38 UTC
(In reply to Eike Rathke from comment #6)
> ECMA-376-1:2016 OOXML 18.17.7.127 FREQUENCY
> says "A call to FREQUENCY shall be an array formula."
Maybe even wants to say that the entire formula expression is to be forced to array mode, not just the parameters to FREQUENCY().

Don't have an adhoc example at hand how that could be checked and verified/falsified.
Comment 8 Eike Rathke 2019-01-07 17:30:55 UTC
Ok, what does Excel produce for

  =SUM(-ABS(FREQUENCY({1,1,-1},{0,1})))

If it forces array mode on the entire formula the result should be -3
If it does not force array mode the result should be -1

Explanation:
The FREQUENCY result here is an array {1,2,0}.
ABS() in non-array mode takes one (the first) scalar value and returns 1, which negated is -1, of which the SUM() is -1.
ABS() in array mode iterates over the array and returns {1,2,0}, which negated is {-1,-2,0}, of which the SUM() is -3. That would be the same as if the formula was entered in array mode.

Could someone with access to Excel please check? Thanks.
Comment 9 GerardF 2019-01-07 17:38:50 UTC
(In reply to Eike Rathke from comment #8)
> Ok, what does Excel produce for
> 
>   =SUM(-ABS(FREQUENCY({1,1,-1},{0,1})))
> 
> If it forces array mode on the entire formula the result should be -3
> If it does not force array mode the result should be -1
> 
> Explanation:
> The FREQUENCY result here is an array {1,2,0}.
> ABS() in non-array mode takes one (the first) scalar value and returns 1,
> which negated is -1, of which the SUM() is -1.
> ABS() in array mode iterates over the array and returns {1,2,0}, which
> negated is {-1,-2,0}, of which the SUM() is -3. That would be the same as if
> the formula was entered in array mode.
> 
> Could someone with access to Excel please check? Thanks.

Result in Office 365 is -3
Comment 10 Eike Rathke 2019-01-07 17:54:22 UTC
Thanks! More work to do..
Comment 11 Eike Rathke 2019-01-07 18:37:00 UTC
@GerardF:
Umm.. to check whether my assumption about the "array in non-array mode" actually holds, this

  =SUM(-ABS({1,2}))

does produce -1, yes?

If -1, one more please to check whether array mode is indeed forced onto the entire formula or just to the expression part that contains the FREQUENCY() call:

  =SUM(-ABS(FREQUENCY({1,1,-1},{0,1})))+SUM(-ABS({1,2}))

Should produce -6 if the entire formula in array mode (which I'd assume), or -4 if only the subexpression containing FREQUENCY() is in array mode.
Comment 12 Commit Notification 2019-01-07 19:43:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/a6572f5f014ed159e340ed31b6e5a08527c09088%5E%21

Resolves: tdf#122301 ReferenceOrForceArray for FREQUENCY() parameters

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 GerardF 2019-01-07 19:59:05 UTC
Created attachment 148115 [details]
picture_excel_result

First result is -3, second is -6.
Array constant are treated as array without CSE while cell range are not.

See attached picture (French XL version).
Comment 14 Mike Kaganski 2019-01-08 05:10:28 UTC
Created attachment 148124 [details]
Results of formulas from comment 8 and comment 11 using cell ranges instead of literal arrays
Comment 15 Eike Rathke 2019-01-08 11:19:47 UTC
(In reply to GerardF from comment #13)
> First result is -3, second is -6.
> Array constant are treated as array without CSE while cell range are not.
Sigh.. ok, let's forget that path for now, thanks.

(In reply to Mike Kaganski from comment #14)
> Results of formulas from comment 8 and comment 11 using cell ranges instead
> of literal arrays
A3 is -5 ??!? So that places the first SUM(-ABS(...)) into array mode and the second SUM(-ABS(E2:E3)) not which then takes the intersection E3? If so, moving the same formula to a different position should change result, and for example on A4 it should be #VALUE! because there is no intersection with E2:E3.
Comment 16 Mike Kaganski 2019-01-08 12:00:34 UTC
(In reply to Eike Rathke from comment #15)
> A3 is -5 ??!? So that places the first SUM(-ABS(...)) into array mode and
> the second SUM(-ABS(E2:E3)) not which then takes the intersection E3? If so,
> moving the same formula to a different position should change result, and
> for example on A4 it should be #VALUE! because there is no intersection with
> E2:E3.

True, as seen on screenshot attachment 148115 [details] from comment 13 :-( a mess, isn't it?
Comment 17 Eike Rathke 2019-01-11 21:59:16 UTC
Created attachment 148245 [details]
What does Excel produce in A9:A13 here?
Comment 18 Mike Kaganski 2019-01-11 23:46:26 UTC Comment hidden (obsolete)
Comment 19 Regina Henschel 2019-01-12 00:18:39 UTC
(In reply to Eike Rathke from comment #17)
> Created attachment 148245 [details]
> What does Excel produce in A9:A13 here?

I get
7
#VALUE!
#VALUE!
#VALUE!
#VALUE!
using Microsoft Office 365, Version 1812 (Build 11126.20196 Click-to-Run)
Comment 20 Mike Kaganski 2019-01-12 07:44:13 UTC
(In reply to Regina Henschel from comment #19)

Regina is absolutely correct - yes, that's 7 - sorry for not checking that the file opened "locked", and unlocking was needed to recalculate. (It was 2 am local time :-))
Comment 21 Commit Notification 2019-01-16 10:18:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/d0ded163d8e93dc5b10d7a7c9bdab1d0a6a50bac%5E%21

Related: tdf#122301 FREQUENCY() with ForceArrayReturn on caller

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 22 Eike Rathke 2019-01-16 21:30:20 UTC
Pending review https://gerrit.libreoffice.org/66487 for 6-2
Comment 23 Commit Notification 2019-01-17 09:25:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/cb40558531c35b89510f0276bc78d9db4a76a9f3%5E%21

Resolves: tdf#122301 FREQUENCY() ReferenceOrForceArray, ForceArrayReturn

It will be available in 6.2.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 24 Commit Notification 2019-01-17 11:38:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/d8886a051abcb5abf52038bf3e5bced9f10f28af%5E%21

Add tests for FREQUENCY() ReferenceOrForceArray, ForceArrayReturn, tdf#122301

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 25 BogdanB 2019-01-26 13:08:48 UTC
Fixed on Version: 6.3.0.0.alpha0+
Build ID: 3424004cca7cb61043800f0ff0acc9de64768276
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-01-26_00:45:09
Locale: ro-RO (ro_RO.UTF-8); UI-Language: en-US
Calc: threaded

On P7:S7 I have 6 8 6 8, so it's ok now.
Comment 26 Commit Notification 2019-02-27 22:38:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/5413c8871dec08eff19f514f5f391b946a45c86c%5E%21

Related: tdf#122301 TRANSPOSE() with ForceArrayReturn on caller

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2019-03-19 12:47:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/e0baddc00570692e09834f165b4e1913575fc338%5E%21

Related: tdf#122301 TRANSPOSE() with ForceArrayReturn on caller

It will be available in 6.2.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.