Bug 133260 - Inline arrays and functions returning array/matrix should propagate array mode to calling functions.
Summary: Inline arrays and functions returning array/matrix should propagate array mod...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL: https://bbs.libreofficechina.org/thre...
Whiteboard: target:7.1.0 target:7.2.0
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-05-22 05:30 UTC by Liaison to zh-CN User Community
Modified: 2021-08-08 01:35 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file using SUM(SUMIFS()) with array as the third parameter of SUMIFS (8.79 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-05-22 05:30 UTC, Liaison to zh-CN User Community
Details
Edited sample file (5.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-05-23 01:44 UTC, m.a.riosv
Details
Same sample file as 161113, saved by MS Excel (9.18 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-06-02 09:39 UTC, Liaison to zh-CN User Community
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Liaison to zh-CN User Community 2020-05-22 05:30:18 UTC
Created attachment 161113 [details]
Sample file using SUM(SUMIFS()) with array as the third parameter of SUMIFS

This bug is forwarded and translated from a report in a Chinese user forum, original report (in Chinese) at: https://bbs.libreofficechina.org/thread-2459-1-1.html

The issue is that in MS Excel the function SUMIFS() allows its third parameter "criteria1" to be an array, and the return value is also an array corresponding the SUMIFS() result with the criteria being each member of the parameter array.

The attached example shows one usage of this by using SUM() on the SUMIFS() result array and calculate the sum of different people's salaries.  This works in Excel but not in Calc.  The expected value for cell D17 is 15501 (sum of D10 and D13), while in Calc (after "Recalculate Hard" if necessary) D17 is 9812 (just D10).

On the other hand, I can't find official documentation saying SUMIFS() supports array as the third parameter, and I don't know what the specification says, so I'm filing as an enhancement request.

The original report is for version 6.3.6 on Windows 10 and 6.4.3 on Ubuntu 20.04, I've reproduced with 6.4.4:
Version: 6.4.4.2 (x64)
Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: zh-CN (zh_CN); UI-Language: en-US
Calc: threaded
Comment 1 m.a.riosv 2020-05-23 01:44:22 UTC
Created attachment 161177 [details]
Edited sample file

For me:
=SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) => 9812

{=SUMIFS(D8:D14;C8:C14;{"li"|"liu"})}  => 9812   5689

=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) => 9812

{=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"}))} => 15501

I have not excel to test but I think you need to enter it as array [Ctrl+Shift+Enter]
Comment 2 Liaison to zh-CN User Community 2020-05-23 14:41:07 UTC
Hi Miguel,

Thanks for helping.

(In reply to m.a.riosv from comment #1)
> For me:
> =SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) => 9812
> 
> {=SUMIFS(D8:D14;C8:C14;{"li"|"liu"})}  => 9812   5689
> 
> =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) => 9812
> 
> {=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"}))} => 15501
> 
> I have not excel to test but I think you need to enter it as array
> [Ctrl+Shift+Enter]
I can indeed get the desired 15501 result using Shift+Ctrl+Enter on 6.4.4, it didn't work on 6.2.8 though.

Also I disagree "using Shift+Ctrl+Enter to input as if handling an array" is the proper solution for this bug, it's at most a workaround, because:

1. The major concern of the original reporter is compatibility with Excel (and Kingsoft's WPS), since in Excel simply =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) gives 15501;

2. Even from a logical point of view, the Calc behavior is questionable.  Using Shift+Ctrl+Enter for =SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) is justified because it returns an array.  However adding SUM() on top of it, =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) should be summing over an array and returning a scalar answer, and therefore shouldn't need Shift+Ctrl+Enter.
Comment 3 Liaison to zh-CN User Community 2020-05-23 15:08:49 UTC
(In reply to zh_CN User Community Liaison from comment #2)
> I can indeed get the desired 15501 result using Shift+Ctrl+Enter on 6.4.4,
> it didn't work on 6.2.8 though.
Actually, it's not the difference between 6.2.8 and 6.4.4 at all, but rather:

1. Make sure recalculation is done on load (Tools > Options > LibreOffice Calc > Formula > Recalculaion on File Load > Excel 2007 or newer, set to "Always recalculate";

2. Open the original sample file attachment 161113 [details];

3. Click in Cell D17, it shows the wrong result 9812;

4. Press Shift+Ctrl+Enter to try to change it to an array input;

5. [Here is what's wrong] Nothing changes, D17 is still 9812, checking D17's formula, no curly brackets so not an array.  Pressing Ctrl+Shift+Enter in formula bar without modifying the formula doesn't work either;

7. However, if one modifies the formula then press Shift+Ctrl+Enter, now it works, D17 shows correct result and the fomular has outmost curly brackets.

But this is a minor issue and probably will be resolved along with the main issue reported here.
Comment 4 Eike Rathke 2020-05-25 14:51:24 UTC
Can we please have an original Excel sample file attached, not a WPS generated one, to see what Excel actually stores for this case? Thanks.

And yes, one has to modify a formula expression before a change is detected, otherwise the formula cell is not re-entered, appending and removing a blank is already enough. This is independent of whether a function would force array mode or not.

Btw, can someone please check if Excel forces array mode whenever an inline array is present as argument where the parameter expects a scalar value (like SUMIFS() 3rd parameter)? For example
=SUM(ABS({-2,-4}))
does that yield 2 or 6?
Comment 5 Liaison to zh-CN User Community 2020-05-26 10:13:52 UTC
(In reply to Eike Rathke from comment #4)
> Can we please have an original Excel sample file attached, not a WPS
> generated one, to see what Excel actually stores for this case? Thanks.
Sure, asked the original reporter for one on the Chinese forum.

[...]
> Btw, can someone please check if Excel forces array mode whenever an inline
> array is present as argument where the parameter expects a scalar value
> (like SUMIFS() 3rd parameter)? For example
> =SUM(ABS({-2,-4}))
> does that yield 2 or 6?
This is easier to test.  According to two users with "Microsoft Office Home and Student 2019" and "Microsoft 365" respectively, Excel returns 6 for that formula.

So does this mean the scope of the issue is much larger than just SUMIFS()?
Comment 6 Eike Rathke 2020-05-26 18:16:26 UTC
Yes. It looks like an inline array (unlike a cell range reference) as argument where a scalar value is expected would force array mode of the entire formula expression. Question remains what happens if it is not an inline array but an array/matrix returned from a function that itself is not in a forced array context. The results of these examples could be interesting:

=SUM(ABS(MUNIT(2)))

=SUM(ABS(MUNIT(2)*-1))

If both behave the same as with the inline array the result should be 2 for both, otherwise 1 (for one or both expressions).
Comment 7 Liaison to zh-CN User Community 2020-06-02 09:39:00 UTC
Created attachment 161512 [details]
Same sample file as 161113, saved by MS Excel

Sample file saved by Excel attached.

Also,
(In reply to Eike Rathke from comment #6)
> Question remains what happens if it is not an
> inline array but an array/matrix returned from a function that itself is not
> in a forced array context. The results of these examples could be
> interesting:
> 
> =SUM(ABS(MUNIT(2)))
> 
> =SUM(ABS(MUNIT(2)*-1))
> 
> If both behave the same as with the inline array the result should be 2 for
> both, otherwise 1 (for one or both expressions).
Excel returns 2 for both formula.
Comment 8 Eike Rathke 2020-10-12 15:29:50 UTC
Taking.
Comment 9 Eike Rathke 2020-10-12 20:45:25 UTC
Can someone please check this in Excel:

A1: -4
A2: -8
A4: ={-1;-2}+ABS(A1:A2)
A5: =ABS(A1:A2)+{-1;-2}
A6: =SUM({-1;-2}+ABS(A1:A2))
A7: =SUM(ABS(A1:A2)+{-1;-2})

What are the results in A4:A7?
Comment 10 Liaison to zh-CN User Community 2020-10-13 06:11:31 UTC
(In reply to Eike Rathke from comment #9)
> Can someone please check this in Excel:
> 
> A1: -4
> A2: -8
> A4: ={-1;-2}+ABS(A1:A2)
> A5: =ABS(A1:A2)+{-1;-2}
> A6: =SUM({-1;-2}+ABS(A1:A2))
> A7: =SUM(ABS(A1:A2)+{-1;-2})
> 
> What are the results in A4:A7?
According to multiple testers, all four cells give "#VALUE!" errors in Excel.
Comment 11 Eike Rathke 2020-10-13 12:38:02 UTC
Brilliant..
Thanks!
Comment 12 Commit Notification 2020-10-13 19:37:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/3a33828b8de7554e497051738c722b1764960a86

Resolves: tdf#133260 Propagate ForceArrayReturn from inline arrays

It will be available in 7.1.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 Commit Notification 2020-10-14 01:53:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/5d9e33068e756b4e74aa2a5e8d9ed16dabe27f29

Derive FormulaExternalToken from FormulaByteToken, tdf#133260 follow-up

It will be available in 7.1.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 14 Commit Notification 2020-10-14 14:04:07 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/dd78ef9ff26fa35a03ac655b0d96738170e8e08b

tdf#133260: sc_ucalc: Add unittest

It will be available in 7.1.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 15 Commit Notification 2021-03-08 08:03:07 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a75799f3e904bd46cffd0bac78e3c0885ac5c447

tdf#133260: move unittest to function test

It will be available in 7.2.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 16 stragu 2021-08-08 01:35:41 UTC
Using steps in Comment 3, verified as fixed in:

Version: 7.2.0.2 / LibreOffice Community
Build ID: 614be4f5c67816389257027dc5e56c801a547089
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded