Bug 134234 - FILEOPEN: SUMPRODUCT formulas are broken
Summary: FILEOPEN: SUMPRODUCT formulas are broken
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.0.0.alpha0+ Master
Hardware: All All
: high critical
Assignee: Not Assigned
URL:
Whiteboard: target:7.1.0 target:7.0.0.1
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2020-06-22 15:29 UTC by Joel Madero
Modified: 2020-06-25 13:19 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Document with PDFs from 6.3 and 7.1 Showing Bug (107.18 KB, application/x-7z-compressed)
2020-06-22 15:32 UTC, Joel Madero
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joel Madero 2020-06-22 15:29:05 UTC
Description:
For the past week or two, the 7.1 git branch is breaking formulas. This is a regression. Open attached document in version 6.3 vs. 7.1 and you'll see that formulas are borked (Current Portfolio Analysis sheet).

Steps to Reproduce:
1.Open attached document in 6.3 vs. 7.1 master


Actual Results:
Formulas entirely broken, given #value instead of correct value.

Expected Results:
Formulas should behave the same between 6.3 and 7.1.


Reproducible: Always


User Profile Reset: No



Additional Info:
Bodhi Linux
Version: 7.1.0.0.alpha0+
Build ID: ba33a51ff1eb34a5983870dcb50e975002e6d3a4
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 1 Joel Madero 2020-06-22 15:30:01 UTC
I am upping this to critical based on my experience in QA in years past. Also adding regression. Hoping that someone can confirm the issue isn't environmental and that this gets patched before any actual release occurs.

Thanks everyone as always for the ongoing commitment of the LibreOffice teams.
Comment 2 Joel Madero 2020-06-22 15:32:23 UTC
Created attachment 162312 [details]
Document with PDFs from 6.3 and 7.1 Showing Bug
Comment 3 Xisco Faulí 2020-06-22 16:16:50 UTC
Regression introduced by:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=9a5f2961b085ce2f23ecdf0a03d1114bacac8e2c

author	Noel Grandin <noel.grandin@collabora.co.uk>	2020-06-10 13:46:09 +0200
committer	Noel Grandin <noel.grandin@collabora.co.uk>	2020-06-10 20:43:44 +0200
commit	9a5f2961b085ce2f23ecdf0a03d1114bacac8e2c (patch)
tree	1b079ce205c2178af825e5dd92ef217fff6804f6
parent	474a9171e7e996116037bb9ca6c985d0a3d6c0c3 (diff)
tdf#133858 opening spreadsheet with large array formula takes 10 mins

Bisected with: bibisect-linux64-7.1

Adding Cc: to Noel Grandin
Comment 4 Joel Madero 2020-06-25 03:48:39 UTC
Xisco - I'm not sure the title accurately gets the issue (SUM). Look at column E on "Current Portfolio Analysis". There isn't a SUM value there. The formula is: 

=SUMPRODUCT(--($'Trade Ledger'.$C$2:$'Trade Ledger'.$C$10000=A2),--($'Trade Ledger'.$B$2:$'Trade Ledger'.$B$10000="Buy"),$'Trade Ledger'.$I$2:$'Trade Ledger'.$I$10000,$'Trade Ledger'.$E$2:$'Trade Ledger'.$E$10000)/SUMIFS($'Trade Ledger'.$E$2:$'Trade Ledger'.$E$10000,$'Trade Ledger'.$C$2:$'Trade Ledger'.$C$10000,A2,$'Trade Ledger'.$B$2:$'Trade Ledger'.$B$10000,"Buy")

There is SUMPRODUCT and SUMIFS but no just SUM.

Just wanted to point it out. Updated my git pull today and notice that the problem is still there. Going to revert that patch from Noel to confirm that the issue goes away.
Comment 5 Joel Madero 2020-06-25 03:50:45 UTC
Confirmed - reverting that commit resolved it. I think that the commit should be removed for the time being as the problem is much more serious than the issue the patch addressed.
Comment 6 Noel Grandin 2020-06-25 06:35:46 UTC
Reverting now.
Comment 7 Commit Notification 2020-06-25 07:24:40 UTC
Noel Grandin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/6f151be2994b0e78a890c13ccdd9249f1800c6de

Revert "tdf#133858 opening spreadsheet with large array formula takes 10 mins" (tdf#134234)

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 8 Commit Notification 2020-06-25 09:55:24 UTC
Noel Grandin committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

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

Revert "tdf#133858 opening spreadsheet with large array formula takes 10 mins" (tdf#134234)

It will be available in 7.0.0.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 9 Commit Notification 2020-06-25 13:09:36 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#134234: sc: 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.