Bug 67822 - Digits in number formatting are lost
Summary: Digits in number formatting are lost
Status: CLOSED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2013-08-06 10:58 UTC by sworddragon2
Modified: 2018-07-20 07:14 UTC (History)
1 user (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description sworddragon2 2013-08-06 10:58:41 UTC
After the old formation bug is fixed I have found another bug on it:

1. Open a new Calc document.
2. Write in the cell A1 "-1" (without double quotes).
3. Calculate the cell B1 with "=A1".
4. Format B1 with "#,##0.00;[RED]-#,##0.00".
5. Save the document and close it.
6. Open the document.
7. The cell contains now "-1" but it should contain "-1.00000".
Comment 1 sworddragon2 2013-08-06 11:03:04 UTC
I'm now a little wondering about the formation of "#,##0.00;[RED]-#,##0.00". I'm not an expert with it but are the 5 digits Calc is showing me after applying it correct?
Comment 2 Mike Kaganski 2013-08-06 12:46:22 UTC
Well, this is reproducible in 4.1.0.4 under Win7x64. The Format string changes on save and reopening: it becomes “#,#####.”. This is a bug.

Still, the original format string is illegal (and logicaly meaningless). #,##0.00 means “if absent, omit all digits to the left of decomal separator, and two digits to the right of it, and show the third to the right, after which show a dot and two zeroes”. How is it possible to omit two first fractional digits and show third?

I don't know the reason for it, but the correct format string would be #,000”.00” (it shows the same, and persist after save/reopen).

I hesitate to set it to NEW, as it is logically inconsistent bug. Someone more experienced advise would be very helpful.
Comment 3 Jean-Baptiste Faure 2013-09-14 09:00:08 UTC
I do not reproduce with LO 4.1.3.0.0+ under Ubuntu 13.04 x86-64 with French locale.

With the format "#,##0.00;[RED]-#,##0.00", you should see: 1.00 (only 2 decimals)
If you want 5 decimals, you need with "#,##0.00000;[RED]-#,##0.00000".

Please, could you try again with the current version of the 4.1 branch which is 4.1.1? You may want try too the first RC of LO 4.1.2 which is available at http://dev-builds.libreoffice.org/pre-releases/

Best regards. JBF
Comment 4 Jean-Baptiste Faure 2013-09-14 09:02:11 UTC
changed component to spreadsheet and made the summary more expressive (hopefully).
Comment 5 sworddragon2 2013-09-15 20:23:32 UTC
In the development version 4.1.2.1 the bug still exists (even with 5 digits).
Comment 6 Jean-Baptiste Faure 2013-09-16 04:12:36 UTC
Please, could you attach your test file?

Best regards. JBF
Comment 7 Mike Kaganski 2013-09-16 09:30:55 UTC
(In reply to comment #3)
> I do not reproduce with LO 4.1.3.0.0+ under Ubuntu 13.04 x86-64 with French
> locale.

It really works differently under Windows. With my Ubuntu, I cannot reproduce it, too.
But under Windows, it is easily reproducible with 4.1.1.2 and 4.1.2.1.

(In reply to comment #6)
> Please, could you attach your test file?

As described in comment 2, in affected systems, on saving and reopening, the format string changes. So it's not possible to attach a test file; but the steps in comment 0 are pretty accurate.

The only thing that I cannot understand is that the problem is clearly visible under Windows, but is not under Ubuntu; though the issue platform says "Linux (All)"...
Comment 8 Jean-Baptiste Faure 2013-09-16 20:01:19 UTC
Please, could you check what you have as option under Ubuntu and MS-Windows for:
menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for general number format ? For me, under Ubuntu, the checkbox is not checked.

Best regards. JBF
Comment 9 sworddragon2 2013-09-16 21:18:23 UTC
> The only thing that I cannot understand is that the problem is clearly visible > under Windows, but is not under Ubuntu; though the issue platform says "Linux > (All)"...

I'm using Ubuntu 13.10 dev.


> Please, could you check what you have as option under Ubuntu and MS-Windows for:
> menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for > general number format ? For me, under Ubuntu, the checkbox is not checked.

It is not checked on my system too.
Comment 10 Mike Kaganski 2013-09-17 01:30:10 UTC
(In reply to comment #8)
> Please, could you check what you have as option under Ubuntu and MS-Windows
> for:
> menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for
> general number format ? For me, under Ubuntu, the checkbox is not checked.

Unchecked in 4.1.1.2 under Win7x64
Comment 11 Joel Madero 2013-11-16 03:29:46 UTC
This seems to be a possible profile issue - I cannot reproduce in Linux while reporter can but Mike can't.

I suggest resetting profile and trying with latest release
https://wiki.documentfoundation.org/UserProfile

If you still experience the issue with a fresh profile and the latest release please set to UNCONFIRMED and we'll go to the next step of trying to figure out what's going on.


Thanks for helping us out!
Comment 12 sworddragon2 2013-11-19 22:34:36 UTC
Even after deleting ~/.config/libreoffice and testing with the upstream version 4.1.3.2 I'm still able to reproduce this problem as described in the startpost.
Comment 13 Mike Kaganski 2013-11-20 12:54:15 UTC
This has direct relation to regional settings.

The original format string ("#,##0.00;[RED]-#,##0.00") should be read like this:
"Show at least one decimal digit before decimal separator, and at least two after; show thousand separators; for negatives, show the sign and mark the number red". Here, the comma (",") stands for thousand separator, the dot (".") for decimal separator.

But in the format string, these symbols are locale-dependent. This format string is only valid for locales where the comma is used for thousands, and the dot is decimal separator.

In other locales, the corresponding characters from the locale must be used instead. For example, here in Russia, we use comma (",") as decimal separator, and space (" ") as thousands separator. In this case, the correct format string looks like this: "# ##0,00;[RED]-# ##0,00". And it works.

(See more details here: https://help.libreoffice.org/Common/Number_Format_Codes)

If I manually set another locale for a cell (say, English (US)), I can use that locale codes.

But if I use wrong codes (like when I use original reporter's code for Russian locale cell), the bug appears.
Comment 14 Mike Kaganski 2013-11-20 14:03:41 UTC
Sorry, in the third line of comment 13, "and at least two after" should be read as "and exactly two after".
Comment 15 Joel Madero 2013-11-20 15:09:20 UTC
@Mike - with that comment it seems like this is just user error? Basically if you put in a valid format, it works fine
Comment 16 Mike Kaganski 2013-11-20 21:44:21 UTC
(In reply to comment #15)
No, not exactly.

As I stated in comment 2, when I wrongly assumed that this format string is illegal from the start (didn't take the locale dependency into the account, sorry for that), the software should not change the format string itself silently, even if it is illegal. It could perform a check on that, and emit a notification on illegal entry; otherwise, it should keep the entered value. That is the bug. And doing that change on save-and-reopen is even less forgivable, as this looks like silent data corruption at a stage when user is likely to overlook this.
Comment 17 Mike Kaganski 2013-11-20 23:02:06 UTC
Here arise other related concerns, that deserve to have their own issue reports.

1. When a cell locale is directly set, corresponding format codes must be used. A user may be unaware of it, or may be unaware of specific characters, and the documentation is not very verbose on this, too. It is desirable to at least have a notification area in the formatting dialog showing active codes, or (better) have buttons inserting those characters (a keyboard may lack the required keys);

2. There is no way (at least in UI) to change the locale of a cell without dropping the format string. But it should be possible, especially given that LO does this internally when the locale of a cell is "default", and this default is changed in LO settings;

3. Possibly, the very idea to use locale-specific characters in format string is bad. Besides being not very obvious and leading to this kind of mistakes, there is another issue: imagine having a format string of a cell like "# ##0.00". If I have the LO default locale set to English (USA), and the cell locale is default, then the space has no special meaning, and should be treated as textual insert. The number 1000000000 is displayed as 1000000 000.00 . If then I change the LO default locale to Russian (or send the file to a user having this locale), the space becomes standard thousands separator. LO modifies the format string representation to be "# ##0,00" (note dot changed to comma), and the format string is expected to make 1000000000 look like 1 000 000 000,00 (at least that is what I get when I enter this fmt string diretly in a Russian-locale cell). But in this previously-English cell, this format string will continue to treat the space as textual character, giving 1000000 000,00 - and that is absolutely unexpected to user looking at the format string!

It would be better if the separator characters of format string were fixed, and inserting them as text would always require quoting them. It would make the format string unambiguous.
Comment 18 Joel Madero 2014-01-17 04:32:46 UTC
Marking as NEW per comment 16

Major - loss of data/formatting (silently lost)
Medium
Comment 19 Joel Madero 2015-05-02 15:42:41 UTC Comment hidden (obsolete)
Comment 20 Mike Kaganski 2015-05-02 17:44:58 UTC
Still reproducible with Version: 4.4.3.2
Build ID: 88805f81e9fe61362df02b9941de8e38a9b5fd16
Locale: ru_RU
under Win7x64
Comment 21 QA Administrators 2016-09-20 09:38:23 UTC Comment hidden (obsolete)
Comment 22 Eike Rathke 2017-05-04 16:12:04 UTC
Does not occur anymore (at least since 5.1.5)