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
Created attachment 53095 [details] Table which shows the problem
(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.
(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.
Is sets properly?: Menu/Tools/Options/LaguageSettings/Languages - Decimal separator key. To use dot as decimal separator the option must be unchecked.
(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.
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).
(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.
Wolfgang: Do you reproduce this behaviour with last LO version ?
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
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: *Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later) https://www.libreoffice.org/download/ *If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior *If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT *Update the version field *Reply via email (please reply directly on the bug tracker) *Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-04-18
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
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
The bug is still present in Version: 5.1.5.2 Build ID: 5.1.5.2-3.fc24
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?
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
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
(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
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!
(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)
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?
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.
Things being fed through XCharacterClassification::parseAnyToken() doesn't make it easier to fix.. taking.
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.
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.