Bug 76663 - LibreOffice Calc's PRODUCT function returns incorrect results in array formula
Summary: LibreOffice Calc's PRODUCT function returns incorrect results in array formula
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.6.2 release
Hardware: x86-64 (AMD64) Windows (All)
: highest critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.4 target:4.1.7
Keywords: regression
: 70784 (view as bug list)
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2014-03-26 23:24 UTC by Mike Smith
Modified: 2014-08-20 11:14 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file (9.49 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-03-27 00:36 UTC, m.a.riosv
Details
sample file (11.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-04-08 10:44 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Smith 2014-03-26 23:24:00 UTC
Example: enter following in cells A1:A4: 0.1, 0.2, 0.3, 0.4. In cell C1 enter following array function: {=PRODUCT(1+A1:A4)-1}. Correct answer is 1.4024 = ((1.1)*(1.2)*(1.3)*(1.4))-1. LibreOffice Calc ver. 4.0.6.2 returns answer of 1.1840, which is equivalent to {=PRODUCT(1+A2:A4)-1} or ((1.2)*(1.3)*(1.4))-1. LibreOffice Calc is performing the computation, but ignoring the first number in cell A1. The error is not obvious on its face and might not be detected. The consequences could be devastating for users relying on LibreOffice to perform critical work.
Comment 1 m.a.riosv 2014-03-27 00:36:16 UTC
Created attachment 96434 [details]
Sample file

Hi Mike, thanks for reporting.

Reproducible:
Win7x64Ult.
Version 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24)
Version: 4.3.0.0.alpha0+ Build ID: 926435ef5ab26e647c09887d471bde25b24e1c16
   TinderBox: Win-x86@39, Branch:master, Time: 2014-03-22_00:09:58

Last working:
Versión 3.6.7.2 (ID de compilación: e183d5b)
Comment 2 Winfried Donkers (retired) 2014-04-08 10:44:37 UTC
Created attachment 97070 [details]
sample file

Reproduced on version 4.1.5.3 op openSUSE 13.1; changed platform in bug header.

Changed sample file; removed irrelevant parts of function call and added function call in 2 steps to show the correct result.

In sc/source.core/tool/interpr6.cxx, IterateMatrix(...), lines
             ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero);
             fRes *= aRes.mfRest;
produce the wrong result.

I will dig further when I have some more time.
Comment 3 Mike Smith 2014-04-17 13:14:05 UTC
Google Spreadsheets performs the calculation correctly. The formula is entered as: "ArrayFormula(product(1+A1:A4)-1)" (ignore the quotes "").
Comment 4 Winfried Donkers (retired) 2014-04-17 16:03:11 UTC
(In reply to comment #3)
> Google Spreadsheets performs the calculation correctly. The formula is
> entered as: "ArrayFormula(product(1+A1:A4)-1)" (ignore the quotes "").

Yes, it is clear that Calc does not calculate correctly in the case of
{=PRODUCT(1+A1:A4)-1}
whereas
{=PRODUCT(A1:A4)-1}
is calculated correctly.
Internally, these 2 formulas are calculated differently.
Unfortunately, I haven't found time yet to look into the problem thoroughly as I am currently working on at least two other bugs.

Let's hope that either someone else*) will fix this before I finish my current bug fixes, otherwise it will take some more time.

*: I could give some code pointers to start with.
Comment 5 Winfried Donkers (retired) 2014-04-24 11:21:31 UTC
Added some traces to find out where the calculation starts going wrong.
The problem is in 
/sc/source/core/tool/scmatrix.cxx, function
ScMatrix::IterateResult ScMatrixImpl::Product(bool bTextAsZero),
which produces the wrong result.

Traces show that the WalkElementBlocks::operator() produces a wrong intermediate result:
(code snippet:
 class WalkElementBlocks : std::unary_function<MatrixImplType::element_block_node_type, void>
 [...]
     void operator() (const MatrixImplType::element_block_node_type& node)
     {
         switch (node.type)
         {
             case mdds::mtm::element_numeric:
             {
                 typedef MatrixImplType::numeric_block_type block_type;
 
                 block_type::const_iterator it = block_type::begin(*node.data);
                 block_type::const_iterator itEnd = block_type::end(*node.data);
                 for (; it != itEnd; ++it)
                 {
                     if (mbFirst)
                     {
                         maOp(maRes.mfFirst, *it);
 SAL_WARN( "76663", "WalkElementBlocks::operator() mbFirst = true, maRes.mfFirst=" << maRes.mfFirst      << ", maRes.mfRest=" << maRes.mfRest << ", maRes.mnCount=" << maRes.mnCount );
                         mbFirst = false;
                     }
                     else
                         maOp(maRes.mfRest, *it);
 SAL_WARN( "76663", "WalkElementBlocks::operator() maRes.mfFirst=" << maRes.mfFirst << ", maRes.mfR     est=" << maRes.mfRest << ", maRes.mnCount=" << maRes.mnCount );
                 }
                 maRes.mnCount += node.size;
             }
             break;
)
traces:
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1009: WalkElementBlocks::operator() mbFirst = true, maRes.mfFirst=0, maRes.mfRest=1, maRes.mnCount=0
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1014: WalkElementBlocks::operator() maRes.mfFirst=0, maRes.mfRest=1, maRes.mnCount=0  <<== msRes.mfRest should be 1.1 !!
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1014: WalkElementBlocks::operator() maRes.mfFirst=0, maRes.mfRest=1.2, maRes.mnCount=0
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1014: WalkElementBlocks::operator() maRes.mfFirst=0, maRes.mfRest=1.56, maRes.mnCount=0
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1014: WalkElementBlocks::operator() maRes.mfFirst=0, maRes.mfRest=2.184, maRes.mnCount=0
warn:76663:32404:1:sc/source/core/tool/scmatrix.cxx:1661: ScMatrixImpl::Product aRes.mfFirst=0, aRes.mfRest=2.184, aRes.mnCount=4
Comment 6 Winfried Donkers (retired) 2014-04-24 11:23:43 UTC
@Kohei: I took the liberty to cc you. Since you committed most changes of /sc/source/core/tool/scmatrix.cxx, could you give me a hint where to look for the cause of the problem?
Comment 7 Kohei Yoshida 2014-04-24 14:04:45 UTC
(In reply to comment #6)
> @Kohei: I took the liberty to cc you. Since you committed most changes of
> /sc/source/core/tool/scmatrix.cxx, could you give me a hint where to look
> for the cause of the problem?

Well, you already have found the problem area. Beyond that, I can't really give you any extra hint without myself taking a deeper look into it.
Comment 8 Kohei Yoshida 2014-04-24 16:12:29 UTC
I'll take a good look at this.
Comment 9 Kohei Yoshida 2014-04-24 16:14:25 UTC
=PRODUCT({1.1,1.2,1.3,1.4}) is a much simpler way to demonstrate the problem.
Comment 10 Kohei Yoshida 2014-04-24 16:24:41 UTC
Yup, indeed. The first element of a matrix was always skipped for PRODUCT.  Will fix this.
Comment 11 Kohei Yoshida 2014-04-24 16:33:38 UTC
And we did have a unit test for this, but unfortunately the first value in the test was 1.  So it wasn't detecting the first value being skipped.  Duh!  I'll fix that as well.
Comment 12 Commit Notification 2014-04-24 16:52:49 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#76663: Better test to really test PRODUCT with array input.



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 13 Commit Notification 2014-04-24 16:53:02 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4158d8843d50d50e9830c8dc24af8722de77f4af

fdo#76663: Let's not skip the first element of a matrix in PRODUCT.



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 14 Kohei Yoshida 2014-04-24 17:07:07 UTC
4.2 backport: https://gerrit.libreoffice.org/9153
Comment 15 Winfried Donkers (retired) 2014-04-25 05:45:33 UTC
(In reply to comment #7)
> Well, you already have found the problem area. Beyond that, I can't really
> give you any extra hint without myself taking a deeper look into it.

@Kohei: thank you for your fast fix! Judging by the timestamps of the comments, you took 45 minutes to fix it plus improve the unittest plus backport to 4.2. It would have taken me at least tenfold that time and I learned from your patch how the iterating takes place (hadn't completely figured that out yet).
Comment 16 Commit Notification 2014-04-25 12:27:40 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#76663: Let's not skip the first element of a matrix in PRODUCT.


It will be available in LibreOffice 4.2.5.

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 17 Eike Rathke 2014-04-25 12:39:06 UTC
Pending review for 4-2-4 at https://gerrit.libreoffice.org/9158
Comment 18 Commit Notification 2014-04-25 12:59:55 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#76663: Let's not skip the first element of a matrix in PRODUCT.


It will be available in LibreOffice 4.1.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 19 Eike Rathke 2014-04-25 13:01:57 UTC
Pending review for 4-1-6 at https://gerrit.libreoffice.org/9160
Comment 20 Kohei Yoshida 2014-04-25 15:24:15 UTC
I'll mark this fixed meanwhile.
Comment 21 Kohei Yoshida 2014-04-25 23:49:36 UTC
*** Bug 70784 has been marked as a duplicate of this bug. ***
Comment 22 Commit Notification 2014-04-29 10:47:43 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=12fef6566fbdad29ebfd8493e2fff56071cf0be3&h=libreoffice-4-2-4

fdo#76663: Let's not skip the first element of a matrix in PRODUCT.


It will be available already in LibreOffice 4.2.4.

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 23 Commit Notification 2014-04-29 10:49:16 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8d1f528394bdd81057c9776cbd5445a7da073c25&h=libreoffice-4-1-6

fdo#76663: Let's not skip the first element of a matrix in PRODUCT.


It will be available already in LibreOffice 4.1.6.

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 24 Mike Smith 2014-04-29 13:44:28 UTC
Following Kohei Yoshida's patch announcement, I just downloaded and installed Version: 4.1.6.2, Build ID: 40ff705089295be5be0aae9b15123f687c05b0a, and tested with the array formula {=PRODUCT(1+A1:A4)}. On my computer, the problem persists, skipping the value in A1 (first element of the matrix).
Comment 25 Winfried Donkers (retired) 2014-04-29 14:16:43 UTC
(In reply to comment #24)
> Following Kohei Yoshida's patch announcement, I just downloaded and
> installed Version: 4.1.6.2, Build ID:
> 40ff705089295be5be0aae9b15123f687c05b0a, and tested with the array formula
> {=PRODUCT(1+A1:A4)}. On my computer, the problem persists, skipping the
> value in A1 (first element of the matrix).

Mike, version 4.1.6.(rc)2 was out before Kohei's patch was pushed and is not yet the release version, but a release candidate. I expect it will be in the next version (4.1.6.3), scheduled later this week.
Comment 26 Mike Smith 2014-04-29 17:21:14 UTC
Thanks. Welcome news. Eagerly awaited.
Comment 27 Eike Rathke 2014-04-30 18:07:35 UTC
There was no 4.1.6.3 and the release was build before the change appeared on the branch. It would be in 4.1.7 if we released that.
Comment 28 m.a.riosv 2014-05-07 01:01:20 UTC
Verified with:
Win7x64Ultimate
Version: 4.2.5.0.0+ Build ID: b26b9606efa30c0a44e20dcf638fbd1e27f05089
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-05-06_06:43:43
Version: 4.3.0.0.alpha1+ Build ID: a1dd961c3093f5f7624e4d1f2240e9120fd13f23
   TinderBox: Win-x86@39, Branch:master, Time: 2014-05-06_11:47:48

Still in:
Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
Filename: LibreOffice_4.2.4.2_Win_x86.msi
Path: /libreoffice/old/4.2.4.2/win/x86/LibreOffice_4.2.4.2_Win_x86.msi
Size: 209M (219451392 bytes)
Last modified: Mon, 05 May 2014 22:19:21 GMT (Unix time: 1399328361)
Comment 29 Winfried Donkers (retired) 2014-05-07 05:42:35 UTC
(In reply to comment #28)
> Still in:
> Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
> Filename: LibreOffice_4.2.4.2_Win_x86.msi
> Path: /libreoffice/old/4.2.4.2/win/x86/LibreOffice_4.2.4.2_Win_x86.msi
> Size: 209M (219451392 bytes)
> Last modified: Mon, 05 May 2014 22:19:21 GMT (Unix time: 1399328361)

Are you sure?
I have it fixed in version 4.2.4.2, Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8 on Windows 7-64
Comment 30 m.a.riosv 2014-05-16 10:10:13 UTC
> Are you sure?
> I have it fixed in version 4.2.4.2, Build ID:
> 63150712c6d317d27ce2db16eb94c2f3d7b699f8 on Windows 7-64
> 
No, please forgive me, I don't know what I did.
Retrying again works for me with Version: 4.2.4.2 Build ID: 3150712c6d317d27ce2db16eb94c2f3d7b699f8
Thanks Winfried.