Bug 133588 - calc:The function SUM() does not produce the same result as the '+' operator
Summary: calc:The function SUM() does not produce the same result as the '+' operator
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.6.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-06-01 16:07 UTC by sebk
Modified: 2020-06-03 17:35 UTC (History)
6 users (show)

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


Attachments
document with the problem for easy test (7.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-06-02 09:39 UTC, BogdanB
Details
calc screenshot (82.46 KB, image/png)
2020-06-02 21:48 UTC, sebk
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sebk 2020-06-01 16:07:04 UTC
Description:
The function SUM() does not produce the same result as the '+' operator in certain situations. I have the feeling this should not be.

Steps to Reproduce:
1. enter "'1" in cell A1 (without the ")
2. enter "'2" in cell B1 (without the ")
3. enter "=sum(a1:b1)" in cell C1 (without the ")
4. enter "=a1+b1" in cell D1 (without the ")
5. watch


Actual Results:
the two cells C1 and D1 do not contain the same value

Expected Results:
The two cells C1 and D1 should contain the same value (either 0 or 3, I have no definitive answer on that point). I assume that when using the '+' operator, an automatic conversion to numerical values is done. If so, then why isn't that conversion also done when using SUM() ? This is odd.


Reproducible: Always


User Profile Reset: No



Additional Info:
The numerical values in A1 and B1 are in fact imported from another tool that (who knows why) exports them as strings, thus they are prefixed with "'"

If you feel this is not a bug but a "feature", could you give me a link on where that behavior is documented? Thank you.

Version: 6.0.6.2
Build ID: 1:6.0.6-0ubuntu0.14.04.1
CPU threads: 8; OS: Linux 4.4; UI render: default; VCL: gtk2; 
Locale: fr-FR (en_US.UTF-8); Calc: group
Comment 1 sebk 2020-06-01 16:09:23 UTC
Cell with SUM contains value 0, cell with operator + contains value 3
Comment 2 Telesto 2020-06-02 08:59:57 UTC Comment hidden (obsolete)
Comment 3 BogdanB 2020-06-02 09:39:20 UTC
Created attachment 161513 [details]
document with the problem for easy test

Confirm it with

Version: 6.4.4.2 (x64)
Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: GL; VCL: win; 
Locale: ro-RO (ro_RO); UI-Language: en-US
Calc: threaded
Comment 4 BogdanB 2020-06-02 09:40:31 UTC
Both should be zero, or both should be 3.
Comment 5 sebk 2020-06-02 10:19:13 UTC
(In reply to Telesto from comment #2)
> No repro with 
> Version: 7.0.0.0.alpha1
> Build ID: 6a03b2a54143a9bc0c6d4c7f1...
> CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; VCL: osx; 
> Locale: nl-NL (nl_NL.UTF-8); UI: en-US
> Calc: threaded

Ok, so this could actually be fixed in latest release.
Maybe someone else can check?
I'd prefer not to because I really need a stable version, I'll wait a little until release 7 is ready to go.
Comment 6 sebk 2020-06-02 14:18:51 UTC
But of course, having that fixed in a backport update to 6.x would be great.
Comment 7 BogdanB 2020-06-02 14:59:25 UTC
It is not fixed in the latest version.

Version: 7.1.0.0.alpha0+
Build ID: a0c90f1bccd9b5a349d3199746facab549f27dba
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: ro-RO (ro_RO.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2020-06-01_02:36:12
Calc: threaded
Comment 8 Telesto 2020-06-02 16:10:14 UTC Comment hidden (obsolete)
Comment 9 Telesto 2020-06-02 16:11:41 UTC Comment hidden (obsolete)
Comment 10 Telesto 2020-06-02 18:52:32 UTC
I missed the prefix part.. Excel does exactly the same thing.. so probably not a bug
Comment 11 m.a.riosv 2020-06-02 21:17:07 UTC
It is not a bug, LibreOffice it's very strict about numbers as text, only direct references are treated as numbers, text numbers in ranges a never converted.

There are some options in Menu/Tools/Options/LibreOffice Calc/Formula - Detailed calculation settings.
Comment 12 sebk 2020-06-02 21:48:16 UTC
Created attachment 161553 [details]
calc screenshot
Comment 13 sebk 2020-06-02 21:50:17 UTC
(In reply to m.a.riosv from comment #11)
> It is not a bug

So it's a feature ;-)

>, LibreOffice it's very strict about numbers as text, only
> direct references are treated as numbers, text numbers in ranges a never
> converted.

Thank you for that clarification.
 
> There are some options in Menu/Tools/Options/LibreOffice Calc/Formula -
> Detailed calculation settings.

Indeed, to have the same behavior, you have to check "treat as 0" in "conversion from text to numbers" (see attached calc screenshot).

Thanks to all.
Comment 14 Ming Hua 2020-06-03 17:35:52 UTC
(In reply to m.a.riosv from comment #11)
> It is not a bug, LibreOffice it's very strict about numbers as text, only
> direct references are treated as numbers, text numbers in ranges a never
> converted.
While I have no objections against marking this as NOTABUG or WONTFIX, the above explanation is faulty because SUM(A1,A2) with direct references gives the same result (0) as SUM(A1:A2) with range reference.