Bug 112780 - Incompatibility in 5.3.1.2 for spreadsheet created with 5.1 (wrong data type)
Summary: Incompatibility in 5.3.1.2 for spreadsheet created with 5.1 (wrong data type)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.0.0.alpha1+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.0.0 target:5.3.7 target:5.4.3
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2017-09-30 16:32 UTC by Dominique Meeùs
Modified: 2017-10-22 06:33 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
test zetelverdeling (25.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-01 16:46 UTC, Xavier Van Wijmeersch
Details
the actual test case document (24.77 KB, application/vnd.oasis.opendocument.spreadsheet-template)
2017-10-02 11:55 UTC, Eike Rathke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dominique Meeùs 2017-09-30 16:32:24 UTC
Description:
Everything OK in a spreadheet under LibreOffice Calc 5.1
When opening in 5.3, I get "Erreur : type de données incorrect" (Wrong data type, I suppose, in English).
The cell is the sum of three others showing no error.
The error disappears if the other cells are filled. The error does not reappear if the content of the other cells is deleted.
It seems that 5.3 does not consider the terms of the sum as numbers when opening the file. 

Steps to Reproduce:
1. Open a sample of the template http://www.d-meeus.be/verkelec/municipal/Zetelverdel-provincie.ots in 5.1
2. Open a sample of the template http://www.d-meeus.be/verkelec/municipal/Zetelverdel-provincie.ots in 5.3

Actual Results:  
In 5.1, no errors in D6 to D25
In 5.3, wrong data type in D6 to D25

Expected Results:
5.3 should be compatible with 5.1


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:55.0) Gecko/20100101 Firefox/55.0
Comment 1 Xisco Faulí 2017-09-30 19:36:40 UTC
Regression introduced by:

author	Eike Rathke <erack@redhat.com>	2016-11-16 17:23:25 (GMT)
committer	Eike Rathke <erack@redhat.com>	2016-11-16 17:24:00 (GMT)
commit	83cbbc6d664d949f6405409713c5bda1a5be559f (patch)
tree	d7d06317429e5b22c14ebe7d7522d6afcc174a90
parent	ed5a8df72ac2d14aa2f5d1f87543fcfff9ad9d7d (diff)
tdf#96475 restore the EmptyDisplayedAsString condition during load
So also "empty" result cells with a number format applied are displayed empty,
instead of 1899-12-30 for example.

Bisected with: bibisect-linux-64-5.3

Adding Cc: to Eike Rathke
Comment 2 Xavier Van Wijmeersch 2017-10-01 10:16:33 UTC
Hi,
I did find three sheet with formatting errors. The cells A4 to T4 did have a #.### format and that caused the #value error in the results sheet. I changed the format to #.##0 and the error is gone. I am not sure but for me its not a bug, just a wrong formatting.

Best regards
Comment 3 Dominique Meeùs 2017-10-01 15:07:06 UTC
I am not convinced.

A. To LO Calc 5.1, # ### (without zero) is considered valid number format (indeed a choice proposed in the number format dialog). Why call this wrong format? (I choose # ### because I do not like a sheet full of zeros in unused cells.)

B. If (in 5.3) I change to # ###0 in cols D, G, J, M of the main sheet and in line 4 of the other sheets (Xavier’s suggestion), the error remains. In fact the error depends on line 8 in the other sheets, but changing the format or these does not do any better.

C. What does make a difference is introducing the number 0 (instead of an empty cell formatted as number) in line 4 of the other sheets, even while keeping # ### as format.

D. Very strange: if you delete these 0s, the error does not reappears. For 5.3, an empty number cell is not allowed but an emptied number cell is!

To reproduce: open a fresh sample from the template in 5.3. Select the three sheets District-something. Enter 0 in each of A4 to T4. (Because of # ###, this 0 is invisible.) The error in the first sheet disappears.

Conclusion: LO Calc 5.1 accepts an empty cell formatted as number as a valid number. The behaviour of 5.3 is different.

Follow up: I have only 5.3 here. Tomorrow, as a workaround, I will put the 0s in the template under 5.1 and in the evening see the result opening in 5.3. — And keep you informed.
Comment 4 Xavier Van Wijmeersch 2017-10-01 16:44:13 UTC
Thanks for your comments.

I did something else, deleted two latest sheets, copied District A and renamed them to B and C, needed the reenter the formula's
I did not changed the formatting. Most #value errors are gone.
The only thing i then have seen is that in U1 is when the number is to high there is a #value error. I tested with LO 5.4.3. I hope i can find a older release of LO to test it

Best regards
Comment 5 Xavier Van Wijmeersch 2017-10-01 16:46:36 UTC
Created attachment 136668 [details]
test zetelverdeling
Comment 6 Eike Rathke 2017-10-02 11:47:14 UTC
Loading the attached test case document in 5.3.6 I don't get any #VALUE! error.
Comment 7 Eike Rathke 2017-10-02 11:53:46 UTC
Ah that's not the original document in question..
Comment 8 Eike Rathke 2017-10-02 11:55:29 UTC
Created attachment 136684 [details]
the actual test case document
Comment 9 Eike Rathke 2017-10-02 13:16:21 UTC
Investigating.
Comment 10 Eike Rathke 2017-10-02 13:36:59 UTC
Fwiw, the error disappears once the document is recalculated, use Shift+Ctrl+F9 once.
Comment 11 Commit Notification 2017-10-02 14:24:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=df778416981ab02d42182e5c2e46dc09ba2e2a3c

Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString()

It will be available in 6.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Eike Rathke 2017-10-02 14:32:54 UTC
Pending review
https://gerrit.libreoffice.org/43047 for 5-4
https://gerrit.libreoffice.org/43048 for 5-3
Comment 13 Dominique Meeùs 2017-10-02 17:03:55 UTC
Indeed, contrarily to what I hoped for, as a workaround, introducing a numerical value zero in the cells waiting for data from the user does not solve the problem.

Recalculate by Shift+Ctrl+F9 does work. I will suggest this to the potential users on my website, if they happen to have a wrong version of LibreOffice.
Comment 14 Xavier Van Wijmeersch 2017-10-03 16:26:55 UTC
Confirm fixed no errors with

Version: 6.0.0.0.alpha0+
Build ID: 9572e3254bf62fb658a09577e64dc591b3a06954
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 15 Commit Notification 2017-10-04 10:18:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8d9b236dcb3fd0f7028e4d19ede04589cf85d760&h=libreoffice-5-3

Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString()

It will be available in 5.3.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2017-10-16 20:16:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=714ef78b8dd60020a70d2ffc364d737503c49992&h=libreoffice-5-4

Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString()

It will be available in 5.4.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Xavier Van Wijmeersch 2017-10-20 14:56:11 UTC
Confirm fixed no errors with

Version: 5.4.4.0.0+
Build ID: b84cd7fb7610b0ad2c74c5a672bccc77b91a82f5
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 5.3.8.0.0+
Build ID: a0fae00a2d52960eebbb14f08d2de251e0a8ff3f
CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-10-05_05:58:12
Locale: nl-BE (en_US.UTF-8); Calc: group