Bug 95578 - A dispersion chart show totally wrong data after updating formulas with F9
Summary: A dispersion chart show totally wrong data after updating formulas with F9
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
5.0.3.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Chart
  Show dependency treegraph
 
Reported: 2015-11-04 18:08 UTC by Andy
Modified: 2023-01-28 03:25 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
The sheet is now Ok, but press F9 twice and the chart goes berserk (340.12 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-11-04 18:08 UTC, Andy
Details
Here the chart correctly mirrors the data (214.41 KB, image/jpeg)
2015-11-07 23:55 UTC, Andy
Details
Now, after pressing F9, data and charth are no more corresponding (210.09 KB, image/jpeg)
2015-11-07 23:57 UTC, Andy
Details
A Video showing the chart corruption in real time (2.49 MB, video/mp4)
2015-11-08 23:19 UTC, Andy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andy 2015-11-04 18:08:28 UTC
Created attachment 120266 [details]
The sheet is now Ok, but press F9 twice and the chart goes berserk

Open the attached file. It contains a simulation of random numbers extraction from a bivariate normal distribution. the simulated data are shown in a chart (dispersion type) to the right of the data columns.

Being simulated data based on pseudo-random functions, everything is changed and updated anytime you insert, delete or edit anything in the spreadsheet. Everything is well in this case, in the sense that the single points of course change but the general shape of the data cloud stays the same, as a rough increasing quasi-line as it is expected, since you have a 0.98 correlation coefficient.
Also, if you press CRTL+SHIFT*F9, everything is updated correctly as above.

Now press F9, first time the update seems Ok, but... from the 2nd time and on, the chart changes shape abruptly, and shows a different data cloud, which does NOT mirrors the data it is supposed to show! And the shape of the cloud now looks like the 2 variables were uncorrelated (points spread everywhere). You can go on as much as you like to press F9, the chart changes but the data shown never are those in the linked columns.

However, if you go back to press ctrl+shift+F9, the chart goes back to normal. But use again F9 twice, and you end up AGAIN WITH A WRONG CHART.

I had the knowledge that the difference between F9 and ctrl+shift+F9 was that the first updated the active sheet, while the second updated all sheets in the file, and updated also formulas whose inputs did not change. These have clearly nothing to do with this case, since there is just one sheet here, and the random function is rand() which is updated by F9 as well. 
So I have no way to explain the erratic behaviour you get pressing F9, and I think it is a bug.

I discovered this during a lecture to my students, and it was quite embarassing, unfortunately.... thanks for the attention
Comment 1 raal 2015-11-07 11:42:08 UTC
Could you please post a printscreen with wrong chart? I've tried to reproduce, but doesn't see the problem. I've checked random points of chart a it was like in data table.
Comment 2 Andy 2015-11-07 23:55:04 UTC
Created attachment 120375 [details]
Here the chart correctly mirrors the data
Comment 3 Andy 2015-11-07 23:57:10 UTC
Created attachment 120376 [details]
Now, after pressing F9, data and charth are no more corresponding
Comment 4 Andy 2015-11-08 00:13:18 UTC
I am showing you the situation in the most apparent case, when the correlation coefficients 1 and the variances of the two variables are equal: in this case the data cloud reduces to a single line, and the angle of it is 45°.

In the first attach, the data in columns B and C are correctly mirrored. However, now press F9 twice and the data clouds expands to what you see in the second attach: 
it is easy to check that it is wrong since, as said above, in this case X and Y should take the same values, forming a 45° steep line: this happens on the data range; but does NOT in the chart, where data shown are clearly NOT those in columns B and C: it is sufficient to check that they do not have equal horizontal and vertical coordinates.
Comment 5 raal 2015-11-08 08:05:14 UTC
I've changed G19 = 1 (Rho) and can not reproduce with Version: 5.1.0.0.alpha1+
Build ID: c5fefe46fc9dca3942b2fc33ffd1f7e041d450e6
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2015-11-04_07:04:49
LO  on win7 5.0.3.2 (x64), Version: 5.1.0.0.alpha1+ (x64)
Build ID: b35e797ca0e2c7e7ad6dbccea6b92208b209677c
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-11-02_22:21:21
Comment 6 Andy 2015-11-08 23:19:33 UTC
Created attachment 120398 [details]
A Video showing the chart corruption in real time

I hope this video clarifies what I mean.
I must add that the problem is not saved with the file, i.e. whan you close the file and reopen it the chart is ok again.
Comment 7 raal 2015-11-09 06:56:46 UTC
For the test, could you rename your LibreOffice directory profile (see https://wiki.documentfoundation.org/UserProfile) and give it a new try?
Comment 8 Buovjaga 2015-11-12 09:41:02 UTC
Set to NEEDINFO.
Change back to UNCONFIRMED, if the problem persists. Change to RESOLVED WORKSFORME, if the problem went away.
Comment 9 Andy 2015-11-16 10:16:35 UTC
"For the test, could you rename your LibreOffice directory profile (see https://wiki.documentfoundation.org/UserProfile) and give it a new try?"

I am sorry to bother you, but I just want to be sure about what you are asking me and how do I revert the change and go back to my profile which contains tons of personalizations):
I am running win 8.1, my account name is Andy Scagni: is the following the correct folder you are referring to?
C:\Users\Andy Scagni\AppData\Roaming\LibreOffice\4
If you find strange that the last folder is "4" please consider that I have LO5 installed in
C:\Program Files (x86)\LibreOffice 4
To maintain personalizations from the previous release.

So: what you do ask is that I rename the "4" folder above to any new name, then open LO and check if the problem is still there. Is this right?

Moreover: when done, what do I need to do to get back? Is it sufficient to re-rename the renamed folder back to "4"? Should I delete any other LO-related folder that has been created?

Thanks for any help you can give....
Comment 10 Buovjaga 2015-11-16 10:25:33 UTC
(In reply to Andy from comment #9)
> If you find strange that the last folder is "4" please consider that I have
> LO5 installed in
> C:\Program Files (x86)\LibreOffice 4
> To maintain personalizations from the previous release.

The profile folder name did not change from 4 to 5 with LibO 5. It was intentional to not disrupt the user's update experience.

> So: what you do ask is that I rename the "4" folder above to any new name,
> then open LO and check if the problem is still there. Is this right?

Yes.

> Moreover: when done, what do I need to do to get back? Is it sufficient to
> re-rename the renamed folder back to "4"? Should I delete any other
> LO-related folder that has been created?

Yes, it is enough to delete the newly generated "4"-folder and rename your "old4" folder back to "4".

But if it turns out that your chart problem is solved by the profile reset, then you would have to transition to a new profile.

Profile corruption is unfortunate and there has been talk of creating a profile comparison tool, so we could find out *what* exactly is wrong in such cases. It would hopefully make transitioning easier in cases like yours, where you have a lot of customizations of your own.
Comment 11 Andy 2015-12-03 09:42:03 UTC
Sorry for the long delay.
I have finally done what you suggested (rename old profile and the problem with a newly created one).

Sadly, nothing changes, and the behavior shown in the video is always there.

I suspected this, since I have three PCs with different stories and configurations, all of them showing the problem, but I tried anyway.

I understand that this could be considered a very particular problem for a generic user, still it is a totally erratic behaviour of the chart and the F9 command that should be corrected IMHO.
As you wrote, I am changing the status back to unconfirmed (even if it is totally confirmed for me.... as I said, on my 3 PCs it is always there!!)
Comment 12 raal 2015-12-03 11:25:28 UTC
(In reply to Andy from comment #11)
> As you wrote, I am changing the status back to unconfirmed (even if it is
> totally confirmed for me.... as I said, on my 3 PCs it is always there!!)

Hello Andy, the rules are that we need another person to reproduce the bug.
Comment 13 Jacques Guilleron 2015-12-03 14:30:05 UTC
Hi Andy,

It seems to me that if you uncheck "Allow use of OpenCL" in 
Tools > Options > LibreOffice > OpenCL
you keep the shape of the data you get before change by F9.
Tried with 
LO 5.0.3.2 Build ID: e5f16313668ac592c1bfb310f4390624e3dbfb75
Locale : fr-FR (fr_FR)
under Windows 7 Home.
Comment 14 Andy 2015-12-03 15:13:38 UTC
Yesss I can confirm that!
Once you uncheck that option, the problem is gone for good.

Besides, I realized that this option affects in a wrong way other functionalities too!!
In fact another quite different bug I posted this morning (Bug 96222) is gone as well when OpenCl ins disabled.

So what remains to be seen is:
1) why is this option malignantly affecting calc in different ways
2) what do you miss after disabling it that could be of value

I googled the term very rapidly, it seems to be something that should speed up numerical computation, but things are not very clear.
Comment 15 Xisco Faulí 2017-08-03 16:23:53 UTC
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Comment 16 Jacques Guilleron 2017-08-03 19:13:28 UTC
Hi Andy and all, 

All seems to work fine now.
I tried repeated F9 on 
LO 5.4.0.3 (x64) Build ID: 92c2794a7c181ba4c1c5053618179937228ed1fb
Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
Also with
LO  5.2.1.2 Build ID: 31dd62db80d4e60af04904455ec9c9219178d620
Threads CPU : 2; Version de l'OS :Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
Doesn't work with 
LO 5.2.0.4
Comment 17 Andy 2017-08-04 09:55:32 UTC
Hello everyone,
I am sorry to bring bad news, but things have not changed at all, or rather they got WORSE for me!!!
I have now installed 5.4.0.3 (which has deep problems with menu fonts display but that's another matter), downloaded my own spreadsheet, set all parameters "media" and "sigma" to be equal so that the points in the graph should lie on a single straight line like it was in the video.
Any recomputing command makes the graph totally WRONG, with the points in column B and C having EXACTLY the same values, but are shown in the graph as acloud of randomly spread points!!
What is worse, but really so, is that this now happens regardless of OpenCL being enabled or disabled!!
So at the moment I have no way to make this work reliably....
And my courses start in mid september.... MY GOD
I even tried and rebuilt the file from scratch to be sure nothing bad was inherited, but things are stuck all the same.
If you cannot reproduce it, or believe I am doing something strange or wrong, I can make another video. Or whatever.... But PLEASE do not ignore this, I can see this is not interesting to many people but having a spreadsheet app that gives such unpredictable results is reason enough to dump it if you're not a true fan.
Thank Jacques for contributing to this.
Any workaround would also be hugely appreciated, before this is actually fixed for good...
Comment 18 Andy 2017-08-04 09:56:28 UTC
with any recomputing I mean bot the F9 hotkey AND the menu command
Comment 19 Andy 2017-08-04 10:01:49 UTC
I also explored the crazy graph a bit more, after it went awry:
clicked on the data, then again so that a single data point is selected and a flyover shows its values on the x-y axis:
well the values in the flyover, which are the ones actually drawn on the graph are totally different from the values in the cells which are displayed with a blue border as the source of the data... difficult to say what is the real source of these wrong coordinates
Comment 20 Jacques Guilleron 2017-08-04 10:25:16 UTC
Sorry Andy,

I have to go for the week-end.
I saw your video. Under windows 7, I get a better answer to F9 use with the same context.

Jacques
Comment 21 Xisco Faulí 2017-11-06 20:47:18 UTC
it seems the chart changes depending where the cursor is at the time you update.
Does it happen if you select a cell in column B?
Comment 22 Andy 2017-11-07 21:35:17 UTC
OMG you're right if the active cell lies within the A3:B500 range everything goes OK!
Anywhere else, the graph fails miserably.
This is absolutely worrisome. A spreadsheet that is not reliable for data computing is worthless.
I will kneel and pray that someone will look into this ASAP.
In the meantime, I will keep things to ourselves, otherwise nobody will trust Calc anymore..
Comment 23 Jean-Baptiste Faure 2018-06-17 16:46:17 UTC
Not reproducible for me with LibreOffice 6.0.4 under Ubuntu x86-64 without OpenCL.

Note: on opening the attachment, the chart does not look the same as attachment 120375 [details]. I get a cloud of points roughly aligned. To get the same picture I need to change to formula in the column C
from 
=F$20*(G$19*A3+SQRT(1-G$19^2)*NORM.S.INV(RAND()))+E$20
to
=F$20*(G$19*A3+SQRT(1-G$19^2)*A3)+E$20
where A3 = NORM.S.INV(RAND())

Each time I hit the F9 key the columns A, B and C are uptaded, then the chart is updated too. It keeps always the same look of a cloud of points roughly aligned (an very flat ellipsoid). 

Best regards. JBF
Comment 24 Jean-Baptiste Faure 2018-09-23 20:01:18 UTC
Still not reproducible for me with LO 6.1.1 under Ubuntu 18.04 x86-64 if OpenCL is completely disabled: "Allow use of Software Interpreter (even when openCL is not available" and "Allow use of OpenCL" both unchecked.

Best regards. JBF
Comment 25 Jacques Guilleron 2018-09-27 10:25:29 UTC
Still a problem, as shown in the video with
LO  6.2.0.0.alpha0+
Build ID: 1aa37aa6bee19099b57555a6d839992b054aa405
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-09-23_10:17:54
Locale: fr-FR (fr_FR); Calc: CL
When "Allow use of OpenCL" is desactived, all works fine.
Comment 26 Andy 2018-09-27 10:56:24 UTC
Yes you're right. As a matter of fact, I've long given up using OpenCL. Seeing what it causes here, who knows what other evil it could inflict....
Comment 27 Jacques Guilleron 2018-09-28 08:07:28 UTC
Hello,

Some precisions:

Geeks3D: GPU_Caps_Viewer gives:
No GPU
OpenCL: OpenCL 1.2, CPU compute units:2@2800MHz
Comment 28 QA Administrators 2023-01-28 03:25:24 UTC
Dear Andy,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug