Bug 130961 - LibreOffice Calc - Calculations frozen after solver and lost named ranges
Summary: LibreOffice Calc - Calculations frozen after solver and lost named ranges
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Solver
  Show dependency treegraph
 
Reported: 2020-02-26 15:02 UTC by John Murrell
Modified: 2021-03-15 20:08 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Spreadsheet that freezes after Solver Run (56.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-02-27 14:07 UTC, John Murrell
Details
Manage names after solver but before failure - top of list (91.80 KB, image/png)
2021-03-15 19:57 UTC, John Murrell
Details
Manage names after solver but before failure - bottom of list (105.37 KB, image/png)
2021-03-15 19:58 UTC, John Murrell
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John Murrell 2020-02-26 15:02:58 UTC
Description:
using Calc Solver - SCO Evolutionary Algorithm.
Allow to solve then save the solution when the ''Solving successfully finished' pop-up appears using the 'Keep Result' button
Then save the spreadsheet.
The calculations in the spreadsheet involved in the Solver now freeze.
If you try to run the solver again the input cell changes but nothing happens and the solver stops immediately on 'Stagnation'
If you change the number in the input cell manually nothing happens even after a manual recalc.

If you turn off automatic recalculation and then do a manual calculation the calculations that involve named ranges all fail with a NAME? error. However the names are still defined when you check.

There does not appear to be any way to recover from this other than reload a backup copy of the spreadsheet before the solver was used. 

This may also apply to other versions of the solver ?

This failure can be confusing as it is easy to think it has found another solution with the same result rather than the entire sheet having frozen.

Calculations that are not involved in the solver still seem to work for instance I have a cell that shows the current value of the input cell in another part of the spreadsheet so I can see the input and the result - this still works even though all the other calculations have frozen.

If the developers want a copy of the (working) or frozen spreadsheet please email me and I will PM it to them.

Could not find any similar bugs reported or in internet search. 

Steps to Reproduce:
1.As above
2.
3.

Actual Results:
Spreadsheet calculations freeze
Named Ranges Lost

Expected Results:
Calculations should still work so alternative solutions or solvers can be tried
Named ranges should still work correctly.


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
The spreadsheet should still have working calculations so the solver can be run again.

The named ranges should not be lost when switching to manual calculation - I assume this bug is related but could be separate ?
Comment 1 m.a.riosv 2020-02-27 02:52:28 UTC
Please copy here the information in Menu/Help/About LibreOffice.

Can you attach a sample file for test?
Comment 2 John Murrell 2020-02-27 14:07:05 UTC
Created attachment 158229 [details]
Spreadsheet that freezes after Solver Run

As supplied changing the value of Cell D1 causes the sheet to recalculate and the result in J131 to change, the graph also changes but that is not material.

To reproduce fault
Set up the solver to minimise J131 (Result)
By varying Cell D1 (Period)
Constraint Period < 1000 (otherwise it will find a minimum at max value possible)
Use option in solver to select SCO Evolutionary Algorithm
Tick select non-negative variables
Run solver
When it solves that may take some time save the result and then save the file.

The values should have frozen. If you change D1(Period) manually The result in J131 does not change however the value in L130 that just shows the period in a more convenient place still changes.

If you try to run the solver again it stops (almost ?) immediately due to stagnation - I presume this is due to the calculations not working however the value shown in the period cell will be updated. 

Carrying out a manual recalculation (with a different value in period) has no effect.

If you turn off automatic recalculation and then turn it on again all the calculations fail with errors in the named ranges even though they are still defined.
Comment 3 John Murrell 2020-02-27 16:05:45 UTC
Here is the version information requested:

Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group
Comment 4 John Murrell 2020-02-28 14:08:03 UTC
I have been doing a bit more testing and now believe it may not be related to the solver.

I have 'cleaned up' a version of the spreadsheet and checked it was working by manually changing the input period and all was well. I saved the result using the save as button in the toolbar to keep the cleaned up version and it still worked.The spreadsheet still worked and I saved it and closed it. When I tried to reload it later it no longer worked.

This also applied to the spreadsheet I used for the source for cleaning up the version above. Last time I loaded it and used it as the source for the cleaned version above it worked - now if I load the same spreadsheet which has not been changed or saved it is 'frozen'.

Very confusing - I would say that while it happens frequently it is not clear what the exact sequenct is that causes it to freeze.
Comment 5 John Murrell 2020-02-28 15:17:02 UTC
Thought I would try to get a frozen spreadsheet working again to see if this gives a clue to what is happening:

1: On a frozen version of the spreadsheet turn off auto calc and then turn it back on

This results in the named ranges with the calculations all reporting a #NAME? error

2: Checked the named ranges using Sheet - Named Ranges and Expressions - Manage one obvious thing is that the name 'period' for cell D1 no longer exists

3: replaced the name in cell D1 as 'period' using Sheet - Named Ranges and Expressions - Define. Sheet still does not calculate

4: redefined the range 'Phase' using Sheet - Named Ranges and Expressions - Manage I just selected the 'Phase' range then clicked after the expression in the 'Range or formula expression' box and then clicked Ok which redefined the range without any other changes.

This resulted in the spreadsheet starting to work again. So it appears it freezes as the result of:

a: Loss of the named cell 'period'
b: loss of the definition of the range 'Phase'

Does this help ?

John Murrell
Comment 6 Roman Kuznetsov 2020-05-01 16:29:16 UTC
(In reply to John Murrell from comment #3)
> Here is the version information requested:
> 
> Version: 6.0.7.3
> Build ID: 1:6.0.7-0ubuntu0.18.04.10
> CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; 
> Locale: en-GB (en_GB.UTF-8); Calc: group

can you update your LibreOffice from PPA or from libreoffice.org/download and try retest your problem.

Current LibreOffice versions are 6.3.6 or 6.4.3
Comment 7 QA Administrators 2021-02-10 04:11:58 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2021-03-13 04:09:31 UTC Comment hidden (obsolete)
Comment 9 John Murrell 2021-03-15 19:57:24 UTC
Created attachment 170496 [details]
Manage names after solver but before failure - top of list

There will be a second attachment with the bottom of the list
Comment 10 John Murrell 2021-03-15 19:58:35 UTC
Created attachment 170497 [details]
Manage names after solver but before failure - bottom of list

2nd part of manage names list
Comment 11 John Murrell 2021-03-15 20:08:48 UTC
I have updated LibreOffice as requested to:

Version: 7.1.1.2 / LibreOffice Community
Build ID: fe0b08f4af1bacafe4c7ecc87ce55bb426164676
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Flatpak
Calc: threaded

The bug still exists

The attached files shows names added by the solver, some of these refer to the same cells / ranges as my original named ranges for instance the cell Period is also referenced as solver_adj, this shows up in the name box when that cell is selected rather than period.

Some action that I have not been able to determine triggers the removal of most of the named cell / ranges in the spreadsheet both the solver ones and my named ranges. The only one that remains is the Chart_Title that is on a different sheet and contains text rather than a number.

It looks as though the erasure of the names may be triggered by a sequence of actions but I have not been able to reproduce it consistently.

Any information on when the solver solution / names are removed and what triggers this ?