Bug 75627

Summary: General number format change leads to inconsistent/lost formatting
Product: LibreOffice Reporter: tmacalp <tmacalp>
Component: SpreadsheetAssignee: Not Assigned <libreoffice-bugs>
Status: NEW --- QA Contact:
Severity: major    
Priority: high CC: fdbugs, mariosv, markus.mohrhard, todventtu
Version: 4.1.0.4 releaseKeywords: bisected, regression
Hardware: Other   
OS: All   
Whiteboard: bibisected
i915 platform: i915 features:
Attachments: Example spreadsheet

Description tmacalp 2014-02-28 21:17:02 UTC 
Description:
Cells with formulas referencing other cells that have special number formats (like currency) will attempt to inherit those number formats too.  LibreOffice 4.1 changed the behavior for the “General” number format when used with formula format code inheritance.  Unfortunately, "clear direct formating" or changing format back to "General" on these formula cells causes inconsistent behavior.  The format code does, indeed, change back to "General," and the cell format does change, as you would expect.  But when you save and reload the spreadsheet, the formula cells will revert to mimicking their referenced cells.

According to the bug priority flowchart, I could argue that it does cause a loss of data in a component that affects many users.  This would categorize it as Critical-High.  It is also a regression, so that too would justify a higher priority.  Since it's rather easy to work around, I'll set to Major High.

https://wiki.documentfoundation.org/images/0/06/Prioritizing_Bugs_Flowchart.jpg

I've tested a number of versions under different versions of Linux and MS Windows.  This regression appears to have been introduced in version 4.1.0.

Tested Affected Inconsistent Versions:
LO 4.1.0.4 using 32bit Fedora 17
LO 4.1.4.2 using 64bit RHEL6
LO 4.2.1.1 using 32bit Fedora 17
LO 4.1.4.2 using 32bit Windows 7

Tested Versions with previous (consistent) behavior:
OO 3.2.1 using 64bit RHEL6
LO 3.5.7.2 using 32bit Fedora 17
LO 4.0.6.2 using 32bit Fedora 17

Steps to reproduce:
1. Open new spreadsheet
2. In cell A1, insert a dollar amount ($100)
3. In cell B1, insert a formula pointing to A1 (=A1)
4. Note that B1 changed format to match A1 (should be currency)
5. Right click B1 and clear formatting
6. Note that B1 changed format from currency to a standard number format (100) 
7. Save document
8. File -> Reload

Expected:
However your formatting looks when you save a document is how it should look when you load it.  B1 should still have a standard number format “100” if that is how you saved it.

Actual:
B1 has changed format back to currency, ignoring our last formatting change to clear direct formatting.  It looks very different than how we saved it.

I have also included an example spreadsheet.  Simply clear formatting on column B, save, and reload the document to see the error.

Notes:
This behavior seems rather complicated.  I might be assuming too much about LO Dev's intentions, but it seems that LO changed the behavior of the “number-general” number format in version 4.1.  Prior versions of OO/LO seem to use the “number-general” format as an “undefined format.”  In our example, when you viewed B1's formatting, it wouldn't actually change from “number-general” to “currency” as it does now.  It would remain number-general, but just APPEAR to be formatted as currency.  Since there was no actual change of formats, only appearance, the change would be immediate and remain consistent between saves.  Everything was at least everything was consistent, but rather confusing.

In LO 4.1+, if you check the formula cells(B column) in the example, they are no longer formatted as number-general.  They have now actually changed format code to mimic the cells they reference.  

It also seems that LO devs attempted to change the behavior for “number-general” to actually be a defined number format.  When you clear formatting it now changes back to a normal number (integer).  But the devs forgot to change the behavior for when you load the saved document.  When the document is loaded, LO still processes “number-general” as undefined and applies the referenced cell's format.

I agree with LO's decision to change this behavior.  If I have currency cells, I would expect that formatting them back to number-general would format them the way they would appear if I just typed that value into a blank cell.  Unfortunately, it's currently half implemented and leads to some horrible quality-of-work cases.  One of my users recently sent a spreadsheet to another user and an entire derived column changed formats to currency.

Work Around:
An easy work-around is to change your formula's number format to a SPECIFIC number format code.  For instance, change it to “0” instead of “General”.

Unfortunately, I think it will be rather complicated to fix this bug without breaking compatibility with other LO/OO versions/possibly MS formats.  But it is something that needs to be addressed.
Comment 1 tmacalp 2014-02-28 21:22:07 UTC 
Created attachment 94921 [details]
Example spreadsheet
Comment 2 m.a.riosv 2014-03-01 03:10:23 UTC 
Hi tmacalp, thanks for reporting.

Clearing the format, when reopening the inherited format is restored.
But if you set up a format (not clear), the new format is preserved after reload.

The issue is still there with:
Win7x64Ultimate
Version: 4.2.2.1 Build ID: 3be8cda0bddd8e430d8cda1ebfd581265cca5a0f
Comment 3 tmacalp 2014-11-04 16:17:29 UTC 
Apparently, LO is saving the cleared format state, but ignoring that change on document load.

Bibisected:
7a454addef42971c41393dd4f668123884973601 is the first bad commit
commit 7a454addef42971c41393dd4f668123884973601
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Thu Oct 17 09:21:19 2013 +0000

    source-hash-23583553d1a9951eaa33dfb598606cdf55d3f01a
    
    commit 23583553d1a9951eaa33dfb598606cdf55d3f01a
    Author:     Michael Stahl <mstahl@redhat.com>
    AuthorDate: Sun Jun 2 13:26:30 2013 +0200
    Commit:     Michael Stahl <mstahl@redhat.com>
    CommitDate: Sun Jun 2 20:37:57 2013 +0200
    
        mysqlcppconn: MSVC 2010 finally has grown a stdint.h
    
        Change-Id: I5b8d948aad94ba492075245c18c8ed781baa469e

:100644 100644 27848ba16c148657f41ac7b1df02e091a44dd29f 7beb466ac333cd27a5756f959c24f4514c12f47b M      ccache.log
:100644 100644 601e47632607a385493e43c480061748c2ca4c7b 57fbcee71fd8e9eb24ab4c293f9023f03784d884 M      commitmsg
:100644 100644 21e4be7670edb7af70f3d7bf4f3a21a45c2e09bd 35f68343cf886768eaa96263b6e4a8164eb92a05 M      dev-install.log
:100644 100644 fc4c2507cb79b70a4e64b27a328ce2b4507374f9 43d9c0d800f3e49309b6b8e5393c27810a50c177 M      make.log
:040000 040000 787f0b310a532028f6ef2a3b46651a6000091148 827d6a3eb295e2aabe319c0683bab9adc4e2e26e M      opt

$ git bisect log
# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 
git bisect start 'latest' 'oldest'                                              
# bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574                         
# good: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15
git bisect good 8f4aeaad2f65d656328a451154142bb82efa4327
# good: [9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02] source-hash-8600bc24bbc9029e92bea6102bff2921bc10b33e
git bisect good 9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02
# good: [8ad82bc1416a07501651e8d96fe268e47d3931d3] source-hash-13821254f88d2c5488fba9fe6393dcf4ae810db4
git bisect good 8ad82bc1416a07501651e8d96fe268e47d3931d3
# good: [d084d250b04446535ca1d7c29cf2062e6bd042b3] source-hash-688f72e3a2c3ef923389bbd21f6aea3afe1114db
git bisect good d084d250b04446535ca1d7c29cf2062e6bd042b3
# good: [c2069a369d738078124812312d51f21ea1ce2421] source-hash-f160e4935c474a5293b3d3c11b3d538efb4767a0
git bisect good c2069a369d738078124812312d51f21ea1ce2421
# good: [a0f20bc04a32a7791ba765d2de2f44f1b74033d1] source-hash-1de66ba440855050a794b3b2a8647c1b02c210b8
git bisect good a0f20bc04a32a7791ba765d2de2f44f1b74033d1
# bad: [a48fbf799e4d4d555fe383b7233c804f573eca4e] source-hash-bb6ecd8b40313b7cc83d4e619029f4e001334a52
git bisect bad a48fbf799e4d4d555fe383b7233c804f573eca4e
# bad: [7a454addef42971c41393dd4f668123884973601] source-hash-23583553d1a9951eaa33dfb598606cdf55d3f01a
git bisect bad 7a454addef42971c41393dd4f668123884973601
# good: [bb1ef709fce943598a8bcab0234b9a4ba1b2e69a] source-hash-c4cca49f49408bc4094bdfcf782de2f7cd16ce6a
git bisect good bb1ef709fce943598a8bcab0234b9a4ba1b2e69a
# first bad commit: [7a454addef42971c41393dd4f668123884973601] source-hash-23583553d1a9951eaa33dfb598606cdf55d3f01a
Comment 4 Matthew Francis 2015-01-07 23:50:28 UTC 
This behaviour changed over the course of a couple of commits. Before 57efd69c22e2c6f5cb4d057345644b6e07a62d48, the =A1 reference doesn't have an independent format that can be reset or altered. From e18f5306714e6ca4103dd236bcf3187d492cbb91 onwards (modulo a merge error fixing commit which follows), the current situation where the format can be reset but doesn't roundtrip correctly occurs.

Adding Cc: to markus.mohrhard@googlemail.com. Could you possibly take a look at this? Thanks


commit 57efd69c22e2c6f5cb4d057345644b6e07a62d48
Author: Markus Mohrhard <markus.mohrhard@googlemail.com>
Date:   Fri May 24 22:41:44 2013 +0200

    remove inherited number formats, related fdo#60215
    
    Change-Id: I23d5e1b3baeb1499ada1fba1665027bdbe3fbb87

commit e18f5306714e6ca4103dd236bcf3187d492cbb91
Author: Markus Mohrhard <markus.mohrhard@googlemail.com>
Date:   Sat May 25 15:12:24 2013 +0200

    all formula cells have now an explicit number format
    
    Remove ScFormulaCell::GetStandardFormat
    
    Change-Id: I0b19f572cfcf5c08c81009b0f10e7cc44a1aa2f4

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.