Bug 42518 - No warning when wrong decimal separators are used, resulting in bad outcome for formulas in tables
Summary: No warning when wrong decimal separators are used, resulting in bad outcome f...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:6.3.0 target:6.2.0.1
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2011-11-02 07:35 UTC by Wolfgang Denk
Modified: 2020-04-25 14:01 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Table which shows the problem (12.50 KB, application/vnd.oasis.opendocument.text)
2011-11-03 00:41 UTC, Wolfgang Denk
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Denk 2011-11-02 07:35:38 UTC
Problem description: 

Using incorrect/unexpected number format in a formula in a table
results in incorrect results instead of an error message.

Steps to reproduce:
1. Create table with at least two fields. Set language as "German
   (Germany)". Set cell format as "Number", Number format "general".
2. Enter "100" in first cell.
3. Enter formula "=<A1>*0.19" in second cell.

Current behavior:

The formula will calculate the result as "1900".

Expected behavior:

The result should be "19".

The preoblem results froim the fact that due to the German environment
the expected number format uses ',' instead of '.' as decimal point.
Writing the formula as "=<A1>*0,19" gives the correct result.

The real bad problem is that the incorrect format goes through
undetected, and results in incorrect values being calculated.
There should be an error raised for invalid number formats.

Platform (if different from the browser): 
              
Browser: Mozilla/5.0 (X11; Linux x86_64; rv:7.0.1) Gecko/20100101 Firefox/7.0.1
Comment 1 Wolfgang Denk 2011-11-03 00:41:31 UTC
Created attachment 53095 [details]
Table which shows the problem
Comment 2 GerardF 2011-11-03 02:50:18 UTC
(In reply to comment #0)

> 3. Enter formula "=<A1>*0.19" in second cell.

The result is also 1900 for me but IMHO expected result is #VALUE!
in German language, comma is decimal separator.
Comment 3 Wolfgang Denk 2011-11-03 04:30:38 UTC
(In reply to comment #2)
> (In reply to comment #0)
> 
> > 3. Enter formula "=<A1>*0.19" in second cell.
> 
> The result is also 1900 for me but IMHO expected result is #VALUE!
> in German language, comma is decimal separator.

Agreed, it's a format error.  But this can easily happen when you have to deal a lot with documents in English and German language settings.  My point is thatin no case such a miscalculation must result.  Silently converting "0.19" (or 0,19)
into "19" is a serious bug - it should result in an invalid data format error.
Comment 4 m_a_riosv 2012-01-22 13:01:43 UTC
Is sets properly?:

Menu/Tools/Options/LaguageSettings/Languages - Decimal separator key.

To use dot as decimal separator the option must be unchecked.
Comment 5 Wolfgang Denk 2012-01-22 13:45:33 UTC
(In reply to comment #4)
> Is sets properly?:
> 
> Menu/Tools/Options/LaguageSettings/Languages - Decimal separator key.
> 
> To use dot as decimal separator the option must be unchecked.

You mean "Decimal separator key - Same as locale setting" ?

I am not 100% sure, but most likely this was not checked.

BUt note that this does not actually matter: the Big Problem is that some bogus value gets computed - instead, an error should be raised.
Comment 6 Julien Nabet 2012-02-28 13:31:40 UTC
Some questions :
- There are different locations to change language, could you tell which one did you change ?
- I saw that it was reported with 3.3.3. Did you try to reproduce this behaviour with 3.4.5 or 3.5.0 ? (first uninstall previous version then delete or move your LO profile located on ~/.libreoffice and/or ~/.config/.libreoffice then install new version).
Comment 7 Wolfgang Denk 2012-03-04 06:27:52 UTC
(In reply to comment #6)
> Some questions :
> - There are different locations to change language, could you tell which one
> did you change ?

In the "Number Format" form.

> - I saw that it was reported with 3.3.3. Did you try to reproduce this
> behaviour with 3.4.5 or 3.5.0 ? (first uninstall previous version then delete
> or move your LO profile located on ~/.libreoffice and/or ~/.config/.libreoffice
> then install new version).

I see the same in a fresh install of 3.4.5 (as shipping with Fedora 16 + latest updates).

There was no directory ~/.config/.libreoffice on my system.

I tried renaming ~/.libreoffice and then started LO again, the problem is still there.

Just try with both "=<A1>*0.19" and "=<A1>*0,19" - one will show the correct result, and the other will show the incorrect one - without any warning or error message.
Comment 8 Julien Nabet 2012-08-15 13:13:04 UTC
Wolfgang: Do you reproduce this behaviour with last LO version ?
Comment 9 sasha.libreoffice 2012-08-21 10:08:26 UTC
reproduced in 3.6.0rc on Fedora 64 bit, Russian language for text
"=<A1>*0.19" produces 0
"=<A1>*1A" also produces 0, no warnings or marks of error in cell
Comment 10 QA Administrators 2015-04-19 03:23:45 UTC Comment hidden (obsolete)
Comment 11 Wolfgang Denk 2015-04-19 08:39:57 UTC
The bug is still present in LibreOffice 4.3.6.2
Tested under Fedora 21 using LibreOffice Build ID: 4.3.6.2-3.fc21
Comment 12 QA Administrators 2016-09-20 09:24:55 UTC Comment hidden (obsolete)
Comment 13 Wolfgang Denk 2016-09-20 09:39:28 UTC
The bug is still present in
Version: 5.1.5.2
Build ID: 5.1.5.2-3.fc24
Comment 14 Wolfgang Denk 2016-09-20 09:42:26 UTC
BTW: we are close to the 5th anniversary of this bug.

That's a really impressive fix rate :-(

Has ANYBODY of the QA team ever even LOOKED at this?  The bug is trivial to reproduce, it is critical as it causes sielnt miscalculations, but nobody cares?
Comment 15 Cor Nouws 2017-01-16 21:45:32 UTC
I see the same results with Dutch locale

A1 = 0,19
formula: 100*A1
Outcome: 19


A1 = 0.19  (wrong)
formula: 100*A1
Outcome: 0


A1 = 100
formula: 0,19*A1
Outcome: 19



A1 = 100
formula: 0.19*A1 (0.19 is Wrong)
Outcome: 1900
Comment 16 jenny 2018-11-29 08:33:57 UTC
Still exists in version

Version: 6.3.0.0.alpha0+ (x64)
Build ID: 0f25a3c36f27fd51453b9a9115f236b83c143684
CPU threads: 8; OS: Windows 10.0; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2018-11-27_20:06:55
Locale: zh-TW (zh_TW); UI-Language: en-US
Calc: threaded
Comment 17 jenny 2018-11-29 08:38:38 UTC
(In reply to jenny from comment #16)
> Still exists in version
> 
> Version: 6.3.0.0.alpha0+ (x64)
> Build ID: 0f25a3c36f27fd51453b9a9115f236b83c143684
> CPU threads: 8; OS: Windows 10.0; UI render: GL; VCL: win; 
> TinderBox: Win-x86_64@42, Branch:master, Time: 2018-11-27_20:06:55
> Locale: zh-TW (zh_TW); UI-Language: en-US
> Calc: threaded

A1 = 100
formula: 0.19*A1
Outcome: 19

A1 = 100
formula: 0,19*A1
Outcome: 1900
Comment 18 Wolfgang Denk 2018-11-29 08:49:24 UTC
So it is more than  7 (SEVEN !!!) YEARS since I rpeported this bug, which can cause silent miscalculation (so IMHO it should be raised to CRITICAL), but nobody cares.

Thanks LibreOffice team for your great support and top quality!

Yes, I *am* frustrated!
Comment 19 jenny 2018-11-29 08:50:28 UTC
(In reply to jenny from comment #17)
> (In reply to jenny from comment #16)
> > Still exists in version
> > 
> > Version: 6.3.0.0.alpha0+ (x64)
> > Build ID: 0f25a3c36f27fd51453b9a9115f236b83c143684
> > CPU threads: 8; OS: Windows 10.0; UI render: GL; VCL: win; 
> > TinderBox: Win-x86_64@42, Branch:master, Time: 2018-11-27_20:06:55
> > Locale: zh-TW (zh_TW); UI-Language: en-US
> > Calc: threaded
> 
> A1 = 100
> formula: 0.19*A1
> Outcome: 19
> 
> A1 = 100
> formula: 0,19*A1
> Outcome: 1900

A1 = 0,19
formula: 100*A1
Outcome: 19

A1 = 0.19
formula: 100(any number)*A1
Outcome: 0(same result)
Comment 20 Julien Nabet 2018-11-29 09:18:19 UTC
Eike: I noticed some improvements on decimal separator managing considering https://cgit.freedesktop.org/libreoffice/core/log/?qt=grep&q=decimal but it seems not sufficient for this bug.
Any thoughts about this one?
Comment 21 Eike Rathke 2018-11-29 12:37:23 UTC
This is not a problem of decimal separator, it is the group separator being accepted and skipped, which is convenient when entering/pasting data to be converted to number but bad when entering a formula.
Comment 22 Eike Rathke 2018-11-29 13:18:06 UTC
Things being fed through XCharacterClassification::parseAnyToken() doesn't make it easier to fix.. taking.
Comment 23 Commit Notification 2018-11-30 19:02:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/776f7e7463de3e97f3056712ee567f49a314829d%5E%21

[API CHANGE] Resolves: tdf#42518 new KParseTokens::GROUP_SEPARATOR_IN_NUMBER

It will be available in 6.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.
Comment 24 Commit Notification 2018-11-30 21:16:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/9336286a7ea5385541344f444e6f8702c85bdacb%5E%21

[API CHANGE] Resolves: tdf#42518 new KParseTokens::GROUP_SEPARATOR_IN_NUMBER

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