Download it now!
Bug 119904 - Spreadsheet formula corrupt with 6.1.1.2
Summary: Spreadsheet formula corrupt with 6.1.1.2
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.1.2 release
Hardware: All All
: medium normal
Assignee: Dennis Francis
URL:
Whiteboard: target:6.1.3
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2018-09-16 09:47 UTC by tim
Modified: 2018-10-09 16:21 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Example corrupted formula in sheet (73.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-16 09:47 UTC, tim
Details
opencl software interpreter enabled (126.44 KB, image/jpeg)
2018-09-16 16:31 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2018-09-16 09:47:31 UTC
Created attachment 144906 [details]
Example corrupted formula in sheet

I've been running this sheet, and another similar one, for several years with no problems.

On opening it for the first time with 6.1.1.2 (previously on 6.0) many of the formulae are producing the wrong result.  I managed to copy and paste data to another sheet and get a working sheet, but then opening it again later the new sheet was also corrupt.

Open the attached example, look at the formulae in columns H to M.  They are all returning the data from row 540.

Very strange, and very worrying.

If it was just this one ods I'd just shrug and say the ods has been corrupted recently somehow.  However, I have a second version which was copied from this one several years ago and used with separate data since then, and that has also shown the same symptom.
Comment 1 tim 2018-09-16 09:56:01 UTC
This was on Version: 6.1.1.2
Build ID: 1:6.1.1~rc2-0ubuntu0.18.04.1~lo3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

I copied the ods to a Windows system, and got the same problem.
Comment 2 Jean-Baptiste Faure 2018-09-16 10:27:31 UTC
Reproducible with LO 6.1.1 from Ubuntu PPA but not with LO 6.1.2.0.0+ and master both built at home under Ubuntu 18.04.

Please could you try a nightly build of 6.1.2 to check if you still have this bug?

Status set to NEEDINFO, please set it back to UNCONFIRMED once requested
informations are provided.

Best regards. JBF
Comment 3 tim 2018-09-16 10:28:58 UTC
I've tried amending the (trivial) formula like:

=IF(G2,C2,"")

in several ways.

Omitting the "" entirely doesn't work (or it did for a moment but then failed when copying the formula to other cells).

I tried " " instead.  This seemed to work until I replaced all the similar cells and then it went wrong again.

I tried "NA" and that seemed to work, so the formulae are now like:

=IF(G2,C2,"NA")

and so on.  Having done this, I then tried " " again, which failed, so went back to "NA", and that failed as well.  Luckily I kept a copy.

This is very weird.
Comment 4 Mike Kaganski 2018-09-16 10:32:45 UTC
Cannot reproduce with Version: 6.1.1.2 (x64)
Build ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU threads: 12; OS: Windows 10.0; UI render: default; 
Locale: ru-RU (ru_RU); Calc: CL

E.g., I see "54" in H8, as expected (formula is `=IF(G8;D8;"")`; G8 is TRUE; D8 is 54).

Possibly I need to look at some specific cells to see the problem. Please mention some cells explicitly where you see the problem; write what do you actually see there; what you expect there, and why.
Comment 5 Mike Kaganski 2018-09-16 10:46:59 UTC
(In reply to tim from comment #1)
> Calc: group threaded

(In reply to Jean-Baptiste Faure from comment #2)
> Reproducible with LO 6.1.1 from Ubuntu PPA but not with LO 6.1.2.0.0+ and
> master both built at home under Ubuntu 18.04.

(In reply to Mike Kaganski from comment #4)
> Cannot reproduce with Version: 6.1.1.2 (x64)
> Calc: CL

Jean-Baptiste Faure: do you reproduce using Calc: group threaded?

I tried to disable OpenCL, and then I also can't reproduce using Calc: threaded (I wonder how to enable *group* threaded here).
Comment 6 tim 2018-09-16 10:50:03 UTC
Every single forumla in columns H to M produces the same data from the very last row.

So my row 8 shows:

30/12/2011 08:34:34	129	74	54	6966	92	TRUE	50		79		136	
	

50, 79 and 136 are the data from rows 540.

It should be:

30/12/2011 08:34:34	129	74	54	6966	92	TRUE	54		74		129
Comment 7 Mike Kaganski 2018-09-16 10:54:53 UTC
(In reply to tim from comment #6)

To check if this is related to threaded calculation, please disable Options→LibreOffice Calc→Calculate→Enable multi-threaded calculation (bottommost).
Comment 8 tim 2018-09-16 11:15:51 UTC
(In reply to Mike Kaganski from comment #7)
> (In reply to tim from comment #6)
> 
> To check if this is related to threaded calculation, please disable
> Options→LibreOffice Calc→Calculate→Enable multi-threaded calculation
> (bottommost).

That didn't work.  The version now shows

Version: 6.1.1.2
Build ID: 1:6.1.1~rc2-0ubuntu0.18.04.1~lo3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group

is that correct?



I'm about to install 6.1.2 nightly and try that.
Comment 9 tim 2018-09-16 11:24:58 UTC
(In reply to Jean-Baptiste Faure from comment #2)
> Reproducible with LO 6.1.1 from Ubuntu PPA but not with LO 6.1.2.0.0+ and
> master both built at home under Ubuntu 18.04.
> 
> Please could you try a nightly build of 6.1.2 to check if you still have
> this bug?
> 
> Status set to NEEDINFO, please set it back to UNCONFIRMED once requested
> informations are provided.
> 
> Best regards. JBF

I just tried the nightly:
Version: 6.1.2.0.0+
Build ID: c371ecf837f047c3ab3ead9ee923b0a17c466f55
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-09-15_00:07:40
Locale: en-GB (en_GB.UTF-8); Calc: group

And that doesn't work either.  I tried with multi-threading, then without (as shown above).

Building libreoffice myself would be a challenge, so I am unlikely to try that any time soon. :-)
Comment 10 tim 2018-09-16 11:33:21 UTC
If, using 6.1.2, I edit the first row's formulae to replace "" with " " in each column, and copy to all the other rows, the data is correct.

If I then close the form, and re-open with 6.1.2, the data is wrong again.

This happens with and without multi-threading.
Comment 11 tim 2018-09-16 11:39:54 UTC
(In reply to tim from comment #3)
> I've tried amending the (trivial) formula like:
> 
> =IF(G2,C2,"")
> 
> in several ways.
> 
> Omitting the "" entirely doesn't work (or it did for a moment but then
> failed when copying the formula to other cells).
> 
> I tried " " instead.  This seemed to work until I replaced all the similar
> cells and then it went wrong again.
> 
> I tried "NA" and that seemed to work, so the formulae are now like:
> 
> =IF(G2,C2,"NA")
> 
> and so on.  Having done this, I then tried " " again, which failed, so went
> back to "NA", and that failed as well.  Luckily I kept a copy.
> 
> This is very weird.
I tried this same change on the other version of this sheet, and it didn't work.  The number of rows in that version is many fewer

This is even weirder.
Comment 12 tim 2018-09-16 11:43:51 UTC
To make sure I am not imagining things, I just tried an earlier version of LO that I'd left on my system:

Version: 6.0.5.2
Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

That works fine.
Comment 13 Oliver Brinzing 2018-09-16 12:27:40 UTC
i can *not* confirm this issue with:

Version: 6.1.1.2 (x64)
Build-ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

(OpenGL, OpenCL and multi-threaded calculation are disabled)
Comment 14 tim 2018-09-16 14:15:44 UTC
Given the different behaviour of similar spreadsheets (I now have the example posted here, and two versions of the real ods files on my system), something very strange indeed is happening.  One person has seen the same thing with the example I posted, but others have not.

It looks like some form of timing-related issue.  One might have assumed that multi-threading was the culprit, but changing that setting made no difference on my system.

I have no idea what other processing might have this sort of effect.

I have tried pressing F9, and Ctrl/Shift/F9, to no effect.  Is there anything else I could try in an effort to help diagnosis?

I have one very complex (unrelated) ods with 90 or so sheets, cross-referencing between many sheets, graphs and the rest, and it seems fine.
Comment 15 tim 2018-09-16 14:35:15 UTC
On Windows, 6.1.1.2, Turning on OpenCL fixed it.

On Ubuntu, 6.1.1.2, Turning off OpenCL and Software support for Open CL fixed it on one sheet but not another.  (Turning OpenCL never 'sticks' between restarts so I assume it's not supported on ubuntu).

I did try checking that the cell value (when wrong) really was wrong (not just displayed wrong) by setting another cell to the value of the incorrect one, and it definitely is the wrong value, not some sort of weird display issue.
Comment 16 tim 2018-09-16 14:56:37 UTC
I seem now to have a stable solution on ubuntu.

Turning off the 'Allow use of Software Interpreter (even when OpenCL is not available), closing Calc, and re-opening it, brings back the correct data.

I tried this in 6.1.1.2, 6.1.2, with multi-threading on, and I think it's OK for all variants of this ods file.

Does that make any sense?
Comment 17 Oliver Brinzing 2018-09-16 16:31:16 UTC
Created attachment 144914 [details]
opencl software interpreter enabled

confirming with lo 6.1.1.2

reproducible with enabled option:
[x] Allow use of Software Interpreter 

even when OpenCL is not available
Comment 18 Jean-Baptiste Faure 2018-09-16 16:53:34 UTC
(In reply to Oliver Brinzing from comment #17)
> Created attachment 144914 [details]
> opencl software interpreter enabled
> 
> confirming with lo 6.1.1.2
> 
> reproducible with enabled option:
> [x] Allow use of Software Interpreter 
> 
> even when OpenCL is not available

I confirm your diagnostic for 6.1.1 from PPA and my own builds of 6.1.2.0.0+
For the master I do not have this option.

Best regards. JBF
Comment 19 tim 2018-09-16 17:03:17 UTC
(In reply to Oliver Brinzing from comment #17)
> Created attachment 144914 [details]
> opencl software interpreter enabled
> 
> confirming with lo 6.1.1.2
> 
> reproducible with enabled option:
> [x] Allow use of Software Interpreter 
> 
> even when OpenCL is not available
That's a relief (for me at least!).  I spent quite some time hunting for something that would make the behaviour stable and reproducible, including several ods files and 3 different computers.  Why it varied for different ods sheets I have no idea.

I have also double-checked my Windows 10 version.  If I turn on the full OpenCL the issue goes away (even with the Software Interpeter option on).  If I turn off full OpenCL and leave the other option on, I get the problem.  If I have both options off, the problem is not present.

I think that pins the problem down to the OpenCL Software Interpreter option in 6.1.1.2 and 6.1.2.  With that set on, clearly some calculations are unstable at best, and sometimes badly wrong, as in the case of my test sheet.
Comment 20 tim 2018-09-16 17:05:19 UTC
I think this is at least a major problem, for some at least, if not critical.  Bad spreadsheet calculations are not good news at all.  Some are used for important stuff.
Comment 21 Mike Kaganski 2018-09-16 17:29:10 UTC
Confirmed on Windows, with Software interpreter enabled, and OpenCL disabled (that is how "group" is turned on); with or without threaded calculations:

Version: 6.1.1.2 (x64)
Build ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU threads: 12; OS: Windows 10.0; UI render: default; 
Locale: ru-RU (ru_RU); Calc: group

Starting master with the same profile (in the hope that it will enable the setting even in the absence of UI) and enabling threading also reproduces the problem:

Version: 6.1.1.2 (x64)
Build ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU threads: 12; OS: Windows 10.0; UI render: default; 
Locale: ru-RU (ru_RU); Calc: group threaded

Miklos: is this something you would be interested in?
Comment 22 Mike Kaganski 2018-09-16 17:31:12 UTC
(In reply to Mike Kaganski from comment #21)
> Starting master with the same profile (in the hope that it will enable the
> setting even in the absence of UI) and enabling threading also reproduces
> the problem:
> 
> Version: 6.1.1.2 (x64)
> Build ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
> CPU threads: 12; OS: Windows 10.0; UI render: default; 
> Locale: ru-RU (ru_RU); Calc: group threaded

Sorry for this wrong statement. In master, this isn't reproducible; I confused myself with different versions started repeatedly from the same profile.
Comment 23 Miklos Vajna 2018-09-17 10:31:30 UTC
Dennis may want to look at this.
Comment 24 Xavier Van Wijmeersch 2018-09-18 06:53:03 UTC
As a workaround changing the formula's i can reproduce the correct value's

H2 =IF(OR($G2=TRUE();$D2=FALSE());$D2;"")
I2 =IF(OR($G2=FALSE();$D2=TRUE());$D2;"")
J2 =IF(OR($G2=TRUE();$C2=FALSE());$C2;"")
K2 =IF(OR($G2=FALSE();$C2=TRUE());$C2;"")
L2 =IF(OR($G2=TRUE();$B2=FALSE());$B2;"")
M2 =IF(OR($G2=FALSE();$B2=TRUE());$B2;"")

Version: 6.1.2.0.0+
Build ID: d76b64ad538f05afc5f18041e9d8c375f22be356
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded

But with LO master opening the file calcerror.ods without changing the formula's then the results are correct

Version: 6.2.0.0.alpha0+
Build ID: 38aae53a1004bb7393c81a98c7b370344613244e
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-09-17_05:01:09
Locale: nl-BE (en_US.UTF-8); Calc: threaded
Comment 25 tim 2018-09-18 07:49:09 UTC
(In reply to Xavier Van Wijmeersch from comment #24)
> As a workaround changing the formula's i can reproduce the correct value's
> 
> H2 =IF(OR($G2=TRUE();$D2=FALSE());$D2;"")
> I2 =IF(OR($G2=FALSE();$D2=TRUE());$D2;"")
> J2 =IF(OR($G2=TRUE();$C2=FALSE());$C2;"")
> K2 =IF(OR($G2=FALSE();$C2=TRUE());$C2;"")
> L2 =IF(OR($G2=TRUE();$B2=FALSE());$B2;"")
> M2 =IF(OR($G2=FALSE();$B2=TRUE());$B2;"")
> 
> Version: 6.1.2.0.0+
> Build ID: d76b64ad538f05afc5f18041e9d8c375f22be356
> CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
> Locale: nl-BE (en_US.UTF-8); Calc: group threaded
> 
> But with LO master opening the file calcerror.ods without changing the
> formula's then the results are correct
> 
> Version: 6.2.0.0.alpha0+
> Build ID: 38aae53a1004bb7393c81a98c7b370344613244e
> CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
> TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time:
> 2018-09-17_05:01:09
> Locale: nl-BE (en_US.UTF-8); Calc: threaded
I modified the formulae several times, and thought I had a stable solution each time, but on saving then restarting Calc, for my tests the results were still wrong.  However, with your modified formula it does seem to work and be stable.

What I don't know is how many less obvious errors I may be getting in other spreadsheets, so the Software Interpreter MUST stay off on my systems, and, I would have thought, every other system used by anyone using Calc. Unpredictable and inaccurate results frankly share the wotsit out of me.
Comment 26 tim 2018-09-18 07:49:57 UTC
I meant 'scare', not 'share'.

I wish I could edit comments!
Comment 27 tim 2018-10-06 07:51:25 UTC
The problem still exists in 

Version: 6.1.2.1
Build ID: 1:6.1.2~rc1-0ubuntu0.18.04.1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: threaded
Comment 28 Commit Notification 2018-10-08 08:31:19 UTC
Dennis Francis committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7bafe2441480e2b88d999b30b7f117f05e72c3b3&h=libreoffice-6-1

tdf#119904 : Generalize the fix for tdf#115093

It will be available in 6.1.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 29 tim 2018-10-08 15:28:06 UTC
(In reply to Commit Notification from comment #28)
> Dennis Francis committed a patch related to this issue.
> It has been pushed to "libreoffice-6-1":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=7bafe2441480e2b88d999b30b7f117f05e72c3b3&h=libreoffice-6-1
> 
> tdf#119904 : Generalize the fix for tdf#115093
> 
> It will be available in 6.1.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.
Hi.  I tried my luck on 6.1.3 as of today (wasn't sure the fix would be in yet), and the problem has been fixed.  Thanks very much indeed.

This was on:

Version: 6.1.3.0.0+
Build ID: 7bafe2441480e2b88d999b30b7f117f05e72c3b3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-10-08_08:54:10
Locale: en-GB (en_GB.UTF-8); Calc: group threaded
Comment 30 Xavier Van Wijmeersch 2018-10-09 16:21:13 UTC
problem fixed

Version: 6.1.3.0.0+
Build ID: c523d9556a354e1afac3203b6f1c8b75a2f7d2f0
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded