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

Bug #775608 reported by F.H. on 2011-05-02
12
This bug affects 1 person
Affects Status Importance Assigned to Milestone
LibreOffice
Fix Released
Critical
libreoffice (Ubuntu)
High
Björn Michaelsen
Natty
High
Unassigned

Bug Description

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) wrote :

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.

F.H. (fheinsen) on 2011-05-02
description: updated

Confirmed on 3.3.2-1ubuntu4 on natty.

summary: - Libreoffice Calc's PRODUCT function doesn't calculate correctly with
- certain array formulas
+ [Upstream] Libreoffice Calc's PRODUCT function doesn't calculate
+ correctly with certain array formulas
Changed in df-libreoffice:
status: New → Confirmed
Changed in libreoffice (Ubuntu):
status: New → Confirmed

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

F.H. (fheinsen) wrote :

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) wrote :

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.

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.

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

Ok. I'll mark this fixed in 3.4.

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

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.

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.

Changed in df-libreoffice:
importance: Undecided → Unknown
status: Confirmed → Unknown
Changed in libreoffice (Ubuntu):
assignee: nobody → Björn Michaelsen (bjoern-michaelsen)
Changed in df-libreoffice:
importance: Unknown → Critical
status: Unknown → Fix Released
F.H. (fheinsen) wrote :

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

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.

Changed in libreoffice (Ubuntu):
status: Confirmed → In Progress
Changed in libreoffice (Ubuntu):
status: In Progress → Fix Committed
F.H. (fheinsen) wrote :

Björn -- THANK YOU so much.

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.

Reassigning to kohei for review.

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

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.

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.

Martin Pitt (pitti) on 2011-05-04
Changed in libreoffice (Ubuntu):
importance: Undecided → Critical
Changed in libreoffice (Ubuntu Natty):
importance: Undecided → High
Changed in libreoffice (Ubuntu):
importance: Critical → High

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!

Changed in libreoffice (Ubuntu Natty):
status: New → Fix Committed
tags: added: verification-needed
F.H. (fheinsen) wrote :

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.

tags: added: verification-done
removed: verification-needed
Launchpad Janitor (janitor) wrote :

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

Changed in libreoffice (Ubuntu Natty):
status: Fix Committed → Fix Released
Martin Pitt (pitti) wrote :

Copied natty-proposed to oneiric as well.

Changed in libreoffice (Ubuntu):
status: Fix Committed → Fix Released

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.

Changed in df-libreoffice:
status: Fix Released → Confirmed

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.

Changed in df-libreoffice:
status: Confirmed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.