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.
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.
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'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.
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.
(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).
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
(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).
(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.
(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. :-)
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.
(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.
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.
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)
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.
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.
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?
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
(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
(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.
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.
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?
(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.
Dennis may want to look at this.
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
(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.
I meant 'scare', not 'share'. I wish I could edit comments!
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
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.
(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
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