[Upstream] Libreoffice Calc's PRODUCT function doesn't calculate correctly with certain array formulas

[F.H.]

Binary package hint: libreoffice

The best way to explain this bug is with a simple example. Type the values 0.1, 0.2, and 0.3 in cells A1, A2, and A3; then on cell C1 type "=PRODUCT(1+A1:A3)-1" without the quotes; and then press <Control><Shift><Enter>. (If you do this correctly, cell C1 will display the formula in curly brackets, i.e., "{=PRODUCT(1+A1:A3)-1}", signifying that C1 contains an array formula.)

If you do this in OpenOffice Calc, cell C1 displays the correct result, 0.716 -- the product of (1+0.1) times (1+0.2) times (1+0.3), less 1. If you do this in LibreOffice Calc, cell C1 displays 0 !!!

[EDITED TO CORRECT TYPO]

[F.H. (fheinsen)]

ADDENDUM: Interestingly, this calculation error appears to be a problem only with the PRODUCT function in LibreOffice Calc. For instance, if in the above array formula you substitute PRODUCT with, say, SUM, both OpenOffice and LibreOffice Calc display the correct result, 2.6. Hope this is helpful.

[Björn Michaelsen (bjoern-michaelsen)]

Confirmed on 3.3.2-1ubuntu4 on natty.

[Björn Michaelsen (bjoern-michaelsen)]

Which version of OpenOffice.org was used for comparison?
Also: Does not show on current LibreOffice master.

[F.H. (fheinsen)]

Björn, in answer to your question: all versions of OpenOffice preinstalled by default with Ubuntu 10.10, 10.04, 9.10, etc. did not have this bug. I know this for a fact because I regularly use a spreadsheet with array formulas using the PRODUCT function that has worked fine with the default OpenOffice installation in all versions of Ubuntu released over the past five-plus years.

[F.H. (fheinsen)]

ADDENDUM: I just noticed this calculation error seems to occur only when constants are used as inputs. For example, "=PRODUCT({1, 2, 3})" produces an erroneous result, 1; however, if you enter the values 1, 2, and 3 in cells A1, A2, and A3, "=PRODUCT(A1:A3)" and "=PRODUCT(A1,A2,A3)" both produce the correct result, 6. Hope this is helpful.

[Björn Michaelsen (bjoern-michaelsen)]

From https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/775608 :

The best way to explain this bug is with a simple example. Type the values 0.1, 0.2, and 0.3 in cells A1, A2, and A3; then on cell C1 type "=PRODUCT(1+A1:A3)-1" without the quotes; and then press <Control><Shift><Enter>. (If you do this correctly, cell C1 will display the formula in curly brackets, i.e., "{=PRODUCT(1+A1:A3)-1}", signifying that C1 contains an array formula.)

If you do this in OpenOffice Calc, cell C1 displays the correct result, 0.716 -- the product of (1+0.1) times (1+0.2) times (1+0.3), less 1. If you do this in LibreOffice Calc, cell C1 displays 0 !!!

Interestingly, this calculation error appears to be a problem only with the PRODUCT function in LibreOffice Calc. For instance, if in the above array formula you substitute PRODUCT with, say, SUM, both OpenOffice and LibreOffice Calc display the correct result, 2.6.

ADDENDUM: I just noticed this calculation error seems to occur only when constants are used as inputs. For example, "=PRODUCT({1, 2, 3})" produces an erroneous result, 1; however, if you enter the values 1, 2, and 3 in cells A1, A2, and A3, "=PRODUCT(A1:A3)" and "=PRODUCT(A1,A2,A3)" both produce the correct result, 6.

I can reproduce this bug a vanilla tag on tag 3.3.2.2 with ./autogen.sh --disable-binfilter --disable-mozilla --with-max-jobs="4" --with-junit=".../junit-4.9b2.jar" --with-system-icu --with-system-db --with-external-tar=... --disable-gtk.

I can _not_reproduce this bug on master as of now, but think we should investigate this issue as it might lead to erroneous calculations that slip by unnoticed for long.

[Vitriol-vitriol (vitriol-vitriol)]

Reproducible in 3.3.2 but not in 3.4 beta 3. I suppose it was fixed.

[Kohei-yoshida-w (kohei-yoshida-w)]

Ok. I'll mark this fixed in 3.4.

[Björn Michaelsen (bjoern-michaelsen)]

IMHO this would warrant an investigation for the 3.3.3 release. Opinions?

[Kohei-yoshida-w (kohei-yoshida-w)]

To me the fact that this hasn't been reported until 3.3.2 suggests this is a minor case. I don't think it's worth fixing this again for the 3.3.x branch which is soon to become obsolete.

But if someone is willing to submit a patch, I'll review it, and if it's safe enough change, why not.

[Björn Michaelsen (bjoern-michaelsen)]

Upstreamed as https://bugs.freedesktop.org/show_bug.cgi?id=36810. Reproducable at upstream 3.3.2, but not on 3.4 beta and master.

[F.H. (fheinsen)]

Björn -- any idea as to when the fix might make it into Ubuntu 11.04?

[Björn Michaelsen (bjoern-michaelsen)]

Attaching a proposed patch against the upstream repo. I would assume this qualifies for a SRU.

Real patch in packaging will be bigger, because we would still need to tunnel the change through libreoffice-build.

[Björn Michaelsen (bjoern-michaelsen)]

Fix commited as:

 http://git.debian.org/?p=pkg-openoffice/libreoffice.git;a=commit;h=84be5a5ff23a6dc01d60f4233779ceaa61c30ba1

[F.H. (fheinsen)]

Björn -- THANK YOU so much.

[Björn Michaelsen (bjoern-michaelsen)]

Test release uploaded as:
https://launchpad.net/~bjoern-michaelsen/+archive/libreoffice-nattytest2/+sourcepub/1710880/+listing-archive-extra
to:
https://launchpad.net/~bjoern-michaelsen/+archive/libreoffice-nattytest2

Still compiling, but should be finished in ~4 hours.

[Björn Michaelsen (bjoern-michaelsen)]

Created attachment 46313
patch from launchpad lp#775608

It looks like the error has been introduced with:
http://cgit.freedesktop.org/libreoffice/calc/commit/?id=c9d53a9a25770f6736c5f2342d57c237bde2f23

Copy-paste from https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/775608 :

= RATIONALE =
This is:
- a severe regression from the previous release of Ubuntu
- while it might directly not cause a loss of user data, it might cause corruption of user data which might be even worse

= RESOLUTION =
The bug has been found to be caused by an call to the function IterateMatrix() in sc/source/core/tool/interpr1.cxx with the wrong order of parameters. This causes the initial value of the iteration to be wrong for PRODUCT. It seems to be right still for other functions by pure luck (i.e. as fMem is 0 it works out correctly for most calls - but for PRODUCT calls fRes has to be 1 initially).

= REPRODUCING THE BUG =
TEST CASE:
- Open calc spreadsheet
- type in "=PRODUCT({1, 2, 3})" as formula for a cell
- expected result: 6
- actual result: 1

Reopening from review and commit to 3-3 branch as patch is available.

[Björn Michaelsen (bjoern-michaelsen)]

Reassigning to kohei for review.

[Kohei-yoshida-w (kohei-yoshida-w)]

Ah, that looks very safe. I'll commit this to the libreoffice-3-3 branch with my sign-off. Good work Bjoern and thanks. :-)

[Kohei-yoshida-w (kohei-yoshida-w)]

Fixed in the libreoffice-3-3 branch

http://cgit.freedesktop.org/libreoffice/calc/commit/?h=libreoffice-3-3&id=3152922a95e18814e32bcf8bc29c872077d05a65

As this is a generic 3.3 branch (not a specific branch for 3.3.3) one sign-off is all that's needed to commit.

[Björn Michaelsen (bjoern-michaelsen)]

SRU details:

= RATIONALE =
This is:
- a severe regression from the previous release of Ubuntu
- while it might directly not cause a loss of user data, it might cause corruption of user data which might be even worse

= RESOLUTION =
The bug has been found to be caused by an call to the function IterateMatrix() in sc/source/core/tool/interpr1.cxx with the wrong order of parameters. This causes the initial value of the iteration to be wrong for PRODUCT. It seems to be right still for other functions by pure luck (i.e. as fMem is 0 it works out correctly for most calls - but for PRODUCT calls fRes has to be 1 initially).

= REPRODUCING THE BUG =
TEST CASE:
- Open calc spreadsheet
- type in "=PRODUCT({1, 2, 3})" as formula for a cell
- expected result: 6
- actual result: 1

= REGRESSION POTENTIAL =
No potential regressions.

= RELEVANT PACKAGES =
The bug has been addressed in source package libreoffice-1:3.3.2-1ubuntu5 as uploaded to https://launchpad.net/~libreoffice/+archive/ppa . This is a joined SRU with bug 746375 because of LibreOffice package size. Attaching one debdiff with both fixes, but as the fixes are injected using patches in the build process, both are clearly separated.

[Björn Michaelsen (bjoern-michaelsen)]

Patch has been reviewed and commited upstream for the 3.3.3 release:

 http://cgit.freedesktop.org/libreoffice/calc/commit/?h=libreoffice-3-3&id=3152922a95e18814e32bcf8bc29c872077d05a65

[Martin Pitt (pitti)]

Accepted libreoffice into natty-proposed, the package will build now and be available in a few hours. Please test and give feedback here. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Thank you in advance!

[F.H. (fheinsen)]

I just tested the natty-proposed packages, and I can verify that the bug is no longer present. (Tested on a large spreadsheet with numerous array formulas.) I'm marking it as no longer affecting me. THANK YOU so much.

[Launchpad Janitor (janitor)]

This bug was fixed in the package libreoffice - 1:3.3.2-1ubuntu5

---------------
libreoffice (1:3.3.2-1ubuntu5) natty-proposed; urgency=high

  * hotfix for Libreoffice Calc's PRODUCT function doesn't calculate correctly (LP: #775608)
  * patch for gtk a11y crash on shutdown (LP: #746375)
 -- Bjoern Michaelsen <email address hidden> Wed, 04 May 2011 12:10:27 +0200

[Martin Pitt (pitti)]

Copied natty-proposed to oneiric as well.

[Moyon-davy (moyon-davy)]

I'm not sure if it should be considered a new bug. But on LO 4.0.03 on osx, product has strange behavior with array:
if you type 2, 3 in A1, A2
and 2 , 2 in B1, B2
if you type =power(A1:A2,B1:B2) in C1 (and press ctrl+shift+enter), you get the good 2x1 array 4,3 in C1:C2
if you do =product(C1:C2) in C3 you get the good result 12
if you do everything in on shot =product(power(A1:A2,B1:B2)) (and press ctrl+shift+enter) you get 3 as a result.

[Mstahl (mstahl)]

according to comment #1 and comment #2 the original bug was fixed in release 3.4.

if the PRODUCT function gives incorrect results in 4.x releases
please file a separate bug for that.