Bug 146367 - SUM() does not use approx summation anymore since Kahan summation is used
Summary: SUM() does not use approx summation anymore since Kahan summation is used
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.0.0.beta1+
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:7.4.0 target:7.2.6 target:7.3.0
Keywords:
Depends on:
Blocks: Number-Format Function-Sum
  Show dependency treegraph
 
Reported: 2021-12-21 23:16 UTC by marcel.deneumostier
Modified: 2022-01-26 15:34 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screenshot open test.xls file (183.77 KB, image/jpeg)
2021-12-22 14:14 UTC, marcel.deneumostier
Details
file test.xls (42.00 KB, application/vnd.ms-excel)
2021-12-22 14:17 UTC, marcel.deneumostier
Details

Note You need to log in before you can comment on or make changes to this bug.
Description marcel.deneumostier 2021-12-21 23:16:06 UTC
Description:
Since last upgrade, all cells that normally give a result "0,00" are displayed for example as "-8,17E-14". To correct the problem, I have to change the format of the numbers from "Standard" to "-1.234,57"

Actual Results:
For example, for a cell "=SOMME(E15;K16;C15)" where the sum is normally "0,00"

Expected Results:
"-8,17E-14" is displayed


Reproducible: Always


User Profile Reset: No



Additional Info:
display "0,00"
Comment 1 stragu 2021-12-22 05:55:35 UTC
Hi Marcel
Thank you for the report.

I couldn't reproduce with:

Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Can you please:

- share the information included in Help > About LibreOffice... (it might be "Aide > À propos de LibreOffice..." en français). There is a button to copy everything.
- tell us which version you updated from (the version you were using before installing 7.2.4.1)
- provide us with a simple example file that shows the issue, or more precise steps to reproduce (including what values are in the cells you referenced).

Thank you!
Comment 2 marcel.deneumostier 2021-12-22 14:14:33 UTC
Created attachment 177095 [details]
Screenshot open test.xls file
Comment 3 marcel.deneumostier 2021-12-22 14:17:00 UTC
Created attachment 177096 [details]
file test.xls
Comment 4 marcel.deneumostier 2021-12-22 14:19:12 UTC
Info requested.

1)
Version: 7.2.4.1 (x64) / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: fr-BE (fr_BE); UI: fr-FR
Calc: threaded

2) 7.2.3.2 release

3) See the attached file "test.xls" where all numbers are formatted as "Standard".
For example, if I open the file on my PC Window 10, I see for cell E4 "-2,7E-13" iso 0.

4) See the attached file "screenshot 2021-12-22 150229.jpg", just what I see on my PC.

Regards.
Comment 5 QA Administrators 2021-12-23 04:29:54 UTC Comment hidden (obsolete)
Comment 6 stragu 2021-12-23 12:41:44 UTC
Confirmed with:

Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Version: 7.3.0.0.beta1 / LibreOffice Community
Build ID: 436f14c25ec1847646b953cf13d0db4f7ca3be57
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 17a4f4d5e4d49189b43e748271d2d4fa330eef9b
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

But I _don't_ reproduce with:

Version: 7.0.6.2
Build ID: 00(Build:2)
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Ubuntu package version: 1:7.0.6-0ubuntu0.18.04.1_lo1
Calc: threaded

Version: 7.1.8.1 / LibreOffice Community
Build ID: e1f30c802c3269a1d052614453f260e49458c82c
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

I am surprised this issue arises with the 7.2.4.1 release, as it was a security fix release with one single issue corrected...
Comment 7 stragu 2021-12-23 12:44:59 UTC
This is not XLS-specific: I could confirm with the same versions listed above with the file saved as ODS.

I can confirm that the workaround is to change the format from "general" to something else.
Comment 8 Eike Rathke 2022-01-03 19:31:38 UTC
This is what you get when repeatedly adding binary floating point values.
See also https://erack.de/bookmarks/D.html#010203

Note also what happens to the displayed values when column E is widened.
Fwiw, Gnumeric quite agrees (with marginal different values) in that results are not 0 as it does not try to eliminate accuracy problems.

Note that only changing the display format to a rounding one just hides the proliferation of accuracy errors. In fact you have to round all intermediate values in column E starting from the bottom to make this work, like in E72  =ROUND(SUM(C72;E73;A72);2)  and copy&paste that up. Or instead of SUM() use the + operator that is implemented to try to tie values to 0 for marginal minimal accuracy differences, like  =C72+E73+A72  and copy&paste that up. 

I also see no change in 7.2.4, I get the same display values in 7.2.0; however, I do see the difference compared with 7.1.8 and earlier.

I assume this is due to the Kahan summation algorithm being implemented for SUM() and some other functions taking cell ranges, see https://wiki.documentfoundation.org/ReleaseNotes/7.2#Calc and bug 137679, where previously SUM() for the first and one subsequent value did something similar as the + operator.

Maybe we can add that back as a special case for single values like they are used here.
Comment 9 Eike Rathke 2022-01-04 14:00:00 UTC
And it's not only about single values..
The famous sequence
0.1
0.2
-0.3
and permutations as cell range equally produces the raw 2.77555756156289E-17 result that the earlier one-value approxAdd handled.
Comment 10 Commit Notification 2022-01-15 12:52:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/730b8aba72356bb8ba0066a5517b1224a4f1e232

tdf#146367 reintroduce approxAdd() similar handling with last summand

It will be available in 7.4.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 11 Eike Rathke 2022-01-15 13:24:11 UTC
Note that with this change the results in the attached sample document are still not fully what would be expected, i.e. cells E32, E21, E20, E19 and E18 display unexpected fractional values, but that is due to the *better* accuracy of Kahan summation as used in SUM(), which for repeated summation of inexact (not exactly representable as binary floating point) values delivers these.

The better approach for this document would be to use the + operator instead of repeatedly SUM() with single values, for example in cell E32 use =C32+E33+A32 instead of =SUM(C32;E33;A32) to benefit from the + operator eliminating opposite sign small equalish fractions. (and use that throughout column E).
Comment 13 Commit Notification 2022-01-15 16:05:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/26b3fc1f31b682fcc7ac0c10af07e64cffbe86e7

A unit test for tdf#146367

It will be available in 7.4.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 14 Commit Notification 2022-01-22 19:09:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

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

tdf#146367 reintroduce approxAdd() similar handling with last summand

It will be available in 7.3.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 15 Commit Notification 2022-01-22 19:10:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/7fd980a607eead2c6cf6557c07a9c25cb5b1a5d4

tdf#146367 reintroduce approxAdd() similar handling with last summand

It will be available in 7.2.6.

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 16 Commit Notification 2022-01-26 13:37:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3-0":

https://git.libreoffice.org/core/commit/7f0bbbea121335dc7d9a97be88ec7803c3a90bb1

tdf#146367 reintroduce approxAdd() similar handling with last summand

It will be available in 7.3.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.