Description: A large file gives four different results for column data range values and corresponding x,y scatter diagrams. Options for Java runtime produce different values. Closing (without saving) and reopening the file produces different results (after User Profile has been modified automatically). Reset User Profile changes the results also, without improvement. "Delete then Undo:delete" behaves differently than "delete then re-enter" or "paste new values". For Windows,Option OpenCL disallowed evades/avoids the symptoms. The provocation is found to be one cell syntax ==IF (without operands, without dependents, without error label for the user, showing a value not used elsewhere). Finding (difficult) and correcting (easy) this cell to =IF resolves the derangement. Investigation continues to check for file damage (if saved) or if the User Profile changes need to be purged or both. Steps to Reproduce: 1. Close actual work with Libreoffice (Writer or Calc)or restart computer. 2. Windows, OpenCL allowed. Start Calc but do not open any other file. 3. Open the attachment file syntax == 4. Use mouse to Select H19 to L19. Those cells are now highlighted. 5. Use keyboard delete key to empty those cell values. 6. Charts in red and orange update correctly. 7. Use mouse (important) toolbar "back arrow" to "Undo:delete". 8. The red chart x values are restored, but the y values not (incorrect) 9. Mouse click Data>Calculate>Recalculate hard, the chart updates x values. 10. Edit cell L23 (blue) from ==IF to =IF. Save file under new name. 11. Restart Calc, open new file 12. Repeat steps 4 to 7. "Undo:delete" now causes correct chart update. Actual Results: Cell L23 is without dependents but Calc interprets ==IF (with many more consequences than those described above using only a reduced version of the original file). A User Profile change occurs also that may be a new corruption (to be confirmed). Seeing or not seeing the problem is configuration dependent. Expected Results: Boolean Equality == is a valid operator (Javascript also) yet requires two operands. Expected result were Calc sets Err:502 invalid argument. Reproducible: Always User Profile Reset: Yes Additional Info: Only Windows tested so far (Ubuntu version work in progress). The red graph in step 8 error state shows most points value -27. This PC here has Options>Advanced>Java Runtime enabled by default, JRE empty. If Java runtime is disabled, the red graph error state shows points at value 0. Disabling OpenCL, repeating all steps, the fault is hidden (manipulations run, data ranges update, charts update) even though the == syntax is still there. The sample file is a small extraction from a very large file. The syntax problem was very difficult to find (only one cell and only checking by eye) and repositionned at L23 deliberately for simplicity. Although this Calc vulnerability may seem simple to close (therefore minor), the consequences for the file/autocalculate were very difficult and a severe challenge. I find (so far) that re-using the original file (used in Bug 86321) there are at least three different states at Open, depending on the User Profile situation and work flow. Copy the values and paste behaves differently to Undo:delete in some cases. Before Chart updates fixes this month, this was nearly impossible to diagnose. I'll file a different Bug report about the sporadic fail aspect (where and how). Was the file damaged in xml in any way? Seems not; work in progress. How does Ubuntu+Calc react? Not sure; my OpenCL plus Linux fails test, so off. Are there any other operators causing same type of bug? Work in progress. Earliest affected version? Unknown, so reported using the first version known to generate the file that was master file for this new reduced sample. Always reproduceable on: Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 1675a68526c43c6c6e4dc850ee911f0c1de75c88 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 16; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: nl-NL (nl_NL); UI: nl-NL Calc: CL Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win Locale: da-DK (da_DK); UI: da-DK Calc: CL
Created attachment 172433 [details] Simple sample file for the step by step (with syntax error).
Please don set up your own bugs as new, except you are going to assign yourself to solve if. == has a special meaning to convert the cell calculation in volatile. Find in https://ask.libreoffice.org/en/question/268263/why-two-equal-signs-in-formula-dec2hexl34/ some information from @Erak (calc development)
Not a bug. In spreadsheet syntax there is no == boolean equality operator, the equality operator is one = with two operands. Starting a formula expression with two == equal signs instead of one = means that formula will be interpreted on every change of the spreadsheet, not just if a predecessor it depends on changes. Meant for side effects of macro functions that otherwise have no indication whether they are volatile or not. See also OpenDocument Format OpenFormula specification 5.2 Basic Expressions https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part4-formula/OpenDocument-v1.3-cs02-part4-formula.html#__RefHeading__1017934_715980110 Note the explanatory text for "forced recalculation" is wrong, instead of "should be recalculated whenever one of its predecessors it depends on changes" (which of course is the normal behaviour) it should be "should be recalculated whenever recalculation occurs, not only when one of its predecessors it depends on changes". And no, I highly doubt that would corrupt a user profile by any means.. Btw, please don't confirm your own bugs by setting them to NEW, leave them at UNCONFIRMED so they can be triaged.
(In reply to m.a.riosv from comment #2) > > == has a special meaning to convert the cell calculation in volatile. > Thankyou. I need to slow down and will raise a new report with a simpler "Expected result" as "Charts display the same as the data range whether OpenCL is allowed or disallowed". I set NEW because it was asked-for (for a different Bug report). Got it this time, to not do that when originating the Bug.
(In reply to Eike Rathke from comment #3) >"should be recalculated whenever recalculation occurs, not only when one of its > predecessors it depends on changes". > > And no, I highly doubt that would corrupt a user profile by any means.. > I'm a fan of your "yeoldestuff"; thankyou for the precise paragraph as the spec puts examples of == , but not in 5.2. Much appreciated. Added to the work list here is to create a test file to attempt to test the == implementation. I may even start using it (later). In any case, I'll write a new bug report with a more open suggestion of expected result (if OpenCL has limitations with == or if OpenCL is revealing something else or if Undo:delete is difficult to support in some circumstances). Just "Chart is expected to plot the Data Range". After a Reset User Profile and close re-open Calc (still without changing nor saving the sample file) there's more chart/range discrepancy and different, including whole column with wrong result for A=B. Reproduceable OpenCL on/off also. The sample file is someone else's yet useful to sort (separate) and investigate why. I'll write that step by step in a new (not NEW) report. For VLB @VLB. I still see the problem; there is a bug, just not that function ==. Bug report closed because my suggestion Err:502 is in error. I understand. I'll be using -- as a trigger for diagnosis (I'm looking for more operators too; all welcome). I only found one instance in your file, in one cell without dependents. If our projects have more == I conclude to have to work it out (deliberate, keyboard bounce, redundant, still wanted for something else or any other reason?) because it does trigger the bug, does alter the charts (the Step by Step is valid). Reading the link in comment 2, Calc can even automatically put == back in (for valid reasons) while we edit.
Thank you all for the new information and at least in 1 cell I changed the unintended == to =. I will keep an eye on the behavior in the new situation of the sheets. I was also interested in the intended function == and was not aware of this.
(In reply to Eike Rathke from comment #3) [...] > Note the explanatory text for "forced recalculation" is wrong, instead of > "should be recalculated whenever one of its predecessors it depends on > changes" (which of course is the normal behaviour) it should be "should be > recalculated whenever recalculation occurs, not only when one of its > predecessors it depends on changes". Some thoughts on undo: Should recalculation occur or not at "undo"? If undo is implemented as restoring the previous values, then no recalculation is needed. But in that case, a formula with == should be right in any case (as I understand the explanation). Well, a small test seems to show that undo does cause at least some recalculation. A cell with the formula "=RANDOM()" or "==RANDOM()" gets a random number in the interval [0,1[, and the number is renewed on every recalculation. For instance, it changes when "del" is pressed in any cell. Now, Ctrl-Z does NOT change the contents back to the previous value but to a new random number (for both = and ==). So it seems that recalculation (rather than storing) occurs. Well, I don't know if this brings in anything new, but the original description was tied to undo.