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.
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)
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.
Google Spreadsheets performs the calculation correctly. The formula is entered as: "ArrayFormula(product(1+A1:A4)-1)" (ignore the quotes "").
(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.
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
@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?
(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.
I'll take a good look at this.
=PRODUCT({1.1,1.2,1.3,1.4}) is a much simpler way to demonstrate the problem.
Yup, indeed. The first element of a matrix was always skipped for PRODUCT. Will fix this.
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.
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.
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.
4.2 backport: https://gerrit.libreoffice.org/9153
(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).
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.
Pending review for 4-2-4 at https://gerrit.libreoffice.org/9158
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.
Pending review for 4-1-6 at https://gerrit.libreoffice.org/9160
I'll mark this fixed meanwhile.
*** Bug 70784 has been marked as a duplicate of this bug. ***
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.
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.
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).
(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.
Thanks. Welcome news. Eagerly awaited.
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.
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)
(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
> 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.