Bug 60977 - Recalculation on load: Usage of cached values conflicting with XVolatliteResults of UNO plugins
Summary: Recalculation on load: Usage of cached values conflicting with XVolatliteResu...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.1.0 target:4.0.1
Keywords:
Depends on:
Blocks:
 
Reported: 2013-02-16 22:05 UTC by Christian Fries
Modified: 2013-12-15 23:26 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Test sheet (requires Obba.oxt from http://www.obba.info ) (7.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-18 17:55 UTC, Christian Fries
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Christian Fries 2013-02-16 22:05:24 UTC
LibreOffice 4.0.0.3 offers a setting at Preferences->LibreOffice Calc->Formula called "Recalculation on file load".

If this option is enabled (which it is by default) cached values (saved into the file) are used. This does not apply for functional like "NOW()" - of course.

However, UNO plugin functions which return XVolatileResults are not called either, hence it is not possible to initialize XVolatilieResults.

This will likely render all plugins with XVolatilieResults-Functions unusable since it effect a core property of XVolatileResults (the ability to force recalculation of a cell).

The problem does not occur if the above option is set to "Always recalculate".

See also bug 60964, 60973, 60974.
Comment 1 Christian Fries 2013-02-17 16:49:26 UTC
Edit: By "If this option is enabled", I meant "If this option is set to "Never recalculate" (which is the default setting).
Comment 2 Markus Mohrhard 2013-02-18 00:05:11 UTC
The default option is "Prompt user".

Can you please attach a test document showing the issue.
Comment 3 Christian Fries 2013-02-18 14:39:59 UTC
"Prompt user" is advertised as the default option on the LibreOffice web page discussing the new feature. However:

- Performing a clean install of LibreOffice on Mac OS X and Windows showed "Never recalculate" as the setted option (note: "Never recalculate is a big problem since XVolatlityResults Add-In Functions are currently not handled correctly).

- If you like to test it: On Windows: Delete the config directory %APPDATA&\LibreOffice and restart LibreOffice: What you will see: If you open a sheet the user is not prompted and the setting in the preferences is "Never recalculate".

PS: In addition: On Mac OS X I had the problem that the user is not prompted even if the option was set to "Prompt user".
Comment 4 Christian Fries 2013-02-18 17:55:27 UTC
Created attachment 75064 [details]
Test sheet (requires Obba.oxt from http://www.obba.info )


With respect to XVolatileResult here is a setup on how to reproduce the problem:

1. Download and install the extension Obba from http://www.obba.info (download Obba.zip and install Obba.oxt from "Obba for OpenOffice"). This extension offers a UDF called obAddClasses which loads Java classes from disk and provides them dynamically to other UDFs. This function is implemented with an XVolatileResult since it needs to notify the sheet of an update of the sheet path (actually this is a workaround for another LibreOffice/OpenOffice/UNO bug). (Note: XVolatileResult is a result that may change upon recalculation or any time later even if the arguments of the function are the same. Examples of such functions are, e.g., NOW()).

2. Open the attached sheet "LoadClassesTest.ods". This sheet has just a single function call to obAddClasses with the local spreadsheet path "." as argument. As a result the function will (should) show the current directory of the sheet.

Result:

A) If you open the sheet on LibreOffice 3.6 the sheet will be recalculated, initializing the Java virtual machine and showing the correct current directory of the sheet (retry with the sheet copied to another directory).

B) If you open the sheet on LibreOffice 4 the sheet will not show the current directory of the sheet. Instead it will show the directory of the sheet upon last calculation, this is /Users/fries/Documents 

Note that this bug has nothing to do with Obba (I just use it for convenience). For example: IT IS NOW IMPOSSIBLE TO CREATE AN UNO PLUGIN WHICH PROVIDES A SPREADSHEET FUNCTION "GetSheetDirectory" and have the current directory of the sheet displayed in the sheet, since this function won't be called after load.

On the other hand, if you use the build-in volatile function "NOW()" in a spreadsheet it get's relcalculated. Hence the developers who implemented the use of cached values does consider volatile functions (the sheet is not displayed in its last saved state, it update the NOW function!).

I believe the use of cached values has to be changed in the several ways. That is:

- Provide an option to load a sheet in "View only" using cached values, even for volatile functions (NOW()). Notify the user that this sheet contains volatile functions (like a notification in the case where a sheet contains an external reference - a volatile function is similar to an external reference).

- Upon first recalculation of any cell of such a sheet, recalculate all (!) volatile functions, including all functions provided by UNO plugins (as it is done for the NOW() function). (Even better: default to a conservative model, where all plugin functions are recalculated until UNO developers adopt to the requirement to use XVolatileResults where necessary. Give UNO developers time to cope with the paradigm change that sheets are not recalculated on load).
Comment 5 Christian Fries 2013-02-19 21:28:20 UTC
I have not tested this, but I am quite sure that LibreOffice 4 will also break the functionality of the api demo "ExampleAddIn.java" which uses an XVolatileResult

http://api.libreoffice.org/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java
Comment 6 Markus Mohrhard 2013-02-21 02:14:04 UTC
@Eike: Any objections against making ocMacro also a RECALC_ONLOAD case. From what I have seen here and thinking generally about it, it looks like the best approach to me.
Comment 7 Eike Rathke 2013-02-21 11:43:25 UTC
@Markus:
Sounds good. Additionally to ocMacro that should be done also for ocExternal to actually cover the Add-In case described here.
Comment 8 Christian Fries 2013-02-22 22:53:20 UTC
Thank you for considering this. From a plugin-developers perspective I would suggest:
- Have RECALC_ONLOAD on load for all functions which are not build-in (Macros and Externals).
- In a later version: Introduce some way such that macros and UNO add-ins can declare being "nonvolatile", that is, compatible with RECALC_ONLOAD. This could be a keyword in Basic or some special way of registering the function.

I thought that one might classify functions provided via UNO just by their return value, e.g., to have RECALC_ONLOAD only for those who declare an XVolatileResult as a return value. However, I would suggest not to do this. This would mean that all functions have to return XVolatileResult if they need a RECALC_ONLOAD, even if their later behavior is non-volatile (the "getDocumentPath" could be an example for such a function). From my experience XVolatileResult introduces a bigger overhead (caching, listeners, etc.) and has some negative impact on the performance, see bug 53060 for example.
Comment 9 Not Assigned 2013-02-25 07:20:13 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a11f944b4c3d268e0f3b36bbccba3a50d2db066a

ocMacro and ocExternal need to be recalculated on load, fdo#60977



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 10 Not Assigned 2013-02-26 07:26:51 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c4322062929815445f9f3f2b3a5c232a43d4abd3&h=libreoffice-4-0

ocMacro and ocExternal need to be recalculated on load, fdo#60977


It will be available in LibreOffice 4.0.2.

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 11 Not Assigned 2013-02-26 12:12:30 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-0-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d8ae8130759119fe5e1a184bab5a06d469a8689a&h=libreoffice-4-0-1

ocMacro and ocExternal need to be recalculated on load, fdo#60977


It will be available already in LibreOffice 4.0.1.

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 12 Christian Fries 2013-03-05 18:58:04 UTC
I have downloaded a daily build of 4.0.2 and can confirm that the problem is fixed. Thank you very much!