Bug 118735 - Opening a spreadsheet with saved values of URI functions shows #VALUE! instead of cached values (see comment 7)
Summary: Opening a spreadsheet with saved values of URI functions shows #VALUE! instea...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0
Keywords: bibisected, bisected
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-07-12 23:12 UTC by Fitch
Modified: 2019-02-24 19:59 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
The original document from comment 0 to avoid dependency on external storage (18.69 MB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-13 05:37 UTC, Mike Kaganski
Details
The original supplementary document from comment 0 to avoid dependency on external storage (1.25 MB, application/vnd.ms-excel)
2018-07-13 05:38 UTC, Mike Kaganski
Details
Minimal reproducer (9.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-16 09:23 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Fitch 2018-07-12 23:12:40 UTC
I used to have Lo 4.4.7 until yesterday.
It worked great, but I was advised to upgrade to 5.1.
I instantly found that it demanded I save the file even though I had not amended anything.

It also gave #VALUE! errors for all the xmlfliter(webservice cells.
But the formula wizard shows correct values for all cells.

I threw that version in the bin and did another upgrade to 6.0.5.2 as it advertised somewhere it had fixed a shedload of bugs.
Well not this one.

The sheet is about 7MB, and it needs a "latest.xls" in the same directory.

https://www.dropbox.com/s/puhkre5lqsdlbpf/FakeMembers.ods?dl=1

https://www.dropbox.com/s/tfejjho86z8laqw/latest.xls?dl=1

All names & addresses are fakes.

It used to be that you could open the spreadsheet, and it would not need to update its link, as it would remember from the previous session.
No longer.

I been using this spreadsheet for a couple of years now, with no problem up until the upgrade.
Comment 1 Mike Kaganski 2018-07-13 05:33:05 UTC
Thank you for filing this.

First of all: please always file one bug/request per issue. Here, you seem to mention two different issues:

1. A document being marked as modified on load;
2. Formulas with URI functions [1] incorrectly returning #VALUE.

I cannot reproduce #2 using Version: 6.1.0.1 (x64)
Build ID: 378e26bd4f22a135cef5fa17afd5d4171d8da21a
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: ru-RU (ru_RU); Calc: CL.

Or, rather, kind of reproduce: when opening the document, I do see those "VALUE"s in columns AW-AZ; but also I see the infobanner telling "Automatic update of external links has been disabled."; and pressing "Enable Content" there, I get the formulas recalculated and #VALUEs becoming normal results. I suspect that the behaviour change here is related to the recent fixes of [2], and this being the correct behaviour, not a bug.

I do reproduce issue #1 opening the document. I don't confirm this yet, though, until you decide how to proceed here: either you let it handle the one reproducible; or split it to two; or something else.

I set it to NEEDINFO until it is clear which issue to track here. Please set back to UNCONFIRMED when made requested changes. Thanks!

[1] https://help.libreoffice.org/latest/en-US/text/scalc/01/func_webservice.html
[2] https://www.libreoffice.org/about-us/security/advisories/cve-2018-6871/
Comment 2 Mike Kaganski 2018-07-13 05:37:48 UTC
Created attachment 143527 [details]
The original document from comment 0 to avoid dependency on external storage
Comment 3 Mike Kaganski 2018-07-13 05:38:27 UTC
Created attachment 143528 [details]
The original supplementary document from comment 0 to avoid dependency on external storage
Comment 4 Fitch 2018-07-13 11:58:24 UTC
I believe the two errors to be linked to the same bug.

Now open it a second time.
The info is already in the spreadsheet, so it should not come up value again, and the spreadsheet should not ask for save.
Comment 5 Mike Kaganski 2018-07-13 12:09:33 UTC
(In reply to Fitch from comment #4)
> I believe the two errors to be linked to the same bug.

It doesn't matter what your beliefs are. There is a strict rule: one bug per issue. It should be followed, or there won't be possible to track what is to fix, what is fixed, etc. If you don't want to comply, it's your right to ignore whatever rules you want. Just it's unlikely that such a bug will be handled.

> Now open it a second time.

It looks like you didn't read what I asked you. I asked you to set it to UNCONFIRMED. You "confirmed" it by setting to improper "REOPENED" (as if it was closed sometime!). It is not confirmed until someone of QA decides it's in a good shape to be NEW.

> The info is already in the spreadsheet, so it should not come up value
> again, and the spreadsheet should not ask for save.

You are welcome to structure the two issues separately. You could not know that they are different; but now you have been told they are, so no matter how you feel about that, they are different, and must be tracked separately.

It is still not in a shape to be confirmed, and still needs to be split. Setting to NEEDINFO again. Please don't set it to arbitrary statuses, however unclear to you could you deem the rules here. They exist for a reason. Thank you.
Comment 6 Fitch 2018-07-13 12:16:02 UTC
O.K. 
Forget the save bug.
The #VALUE! bug is more concerning.
Don't know how to edit this.
Comment 7 Mike Kaganski 2018-07-13 12:35:55 UTC
(In reply to Fitch from comment #4)
> The info is already in the spreadsheet, so it should not come up value
> again

(In reply to Fitch from comment #6)
> The #VALUE! bug is more concerning.

The info is not already in the *original* spreadsheet. The XML inside the ODS has these records:

> <table:table-cell table:style-name="ce202" table:formula="of:=IF([.AR2]=&quot;&quot;;&quot;&quot;;COM.MICROSOFT.FILTERXML(COM.MICROSOFT.WEBSERVICE(&quot;http://www.geopostcode.org.uk/api/&quot;&amp;[.AR2]&amp;&quot;&quot;);&quot;//osgb36/north&quot;))" office:value-type="string" office:string-value="" calcext:value-type="error"><text:p>#VALUE!</text:p></table:table-cell>

But I *do* reproduce if I enable content (allowing to fetch from WEB), and re-save the normal values. Then the file has this:

> <table:table-cell table:style-name="ce202" table:formula="of:=IF([.AR2]=&quot;&quot;;&quot;&quot;;COM.MICROSOFT.FILTERXML(COM.MICROSOFT.WEBSERVICE(&quot;http://www.geopostcode.org.uk/api/&quot;&amp;[.AR2]&amp;&quot;&quot;);&quot;//osgb36/east&quot;))" office:value-type="string" office:string-value="437871" calcext:value-type="string"><text:p>437871</text:p></table:table-cell>

but still opening it in Calc brings the #VALUE!.

So the problem is that Calc should show the cached values for the WEBSERVICE formulas, but shows #VALUE! instead.

Setting to NEW.
Comment 8 Mike Kaganski 2018-07-16 09:23:57 UTC
Created attachment 143569 [details]
Minimal reproducer

The file contains a single formula in A1:

> =WEBSERVICE("http://www.geopostcode.org.uk/api/")

It contains the cached value in the content.xml, but opens with #VALUE! in the cell.

By the way, this doesn't make the document modified on open.
Comment 9 Fitch 2018-07-16 10:17:01 UTC
Comment on attachment 143569 [details]
Minimal reproducer

Don't you need to put in a postcode?
e.g. =WEBSERVICE("http://www.geopostcode.org.uk/api/EH9 1QG")
Comment 10 Mike Kaganski 2018-07-16 11:31:06 UTC
(In reply to Fitch from comment #9)
> Comment on attachment 143569 [details]
> Minimal reproducer
> 
> Don't you need to put in a postcode?
> e.g. =WEBSERVICE("http://www.geopostcode.org.uk/api/EH9 1QG")

For the reproducer, it's irrelevant. The function just returns an HTML; and the address without the code returns some HTML as well. Both variants will serve good reproducers (just a side note: the URL with spaces is illegal, and it's pity the service is taking that; fortunately, it also takes properly encoded URL like
> =WEBSERVICE("http://www.geopostcode.org.uk/api/"&ENCODEURL("EH9 1QG"))
Comment 11 Mike Kaganski 2018-07-16 12:13:25 UTC
Another note: saving the reproducer as DOCX does not gives #VALUE!, it correctly restores the cached value.
Comment 12 Xisco Faulí 2018-07-17 18:37:10 UTC
As expected, the behaviour changed in https://cgit.freedesktop.org/libreoffice/core/commit/?id=93ea7cb6b5ab3c9b964b2b38e8f4a3bde71dbadf

@Eike, @Caolán, should Calc use the cached data if the user doesn't enable the content from external source ?
Comment 13 Eike Rathke 2018-07-18 09:23:35 UTC
Whatever sets the formula cell dirty, maybe it shouldn't..
Comment 14 Eike Rathke 2018-07-18 13:06:14 UTC
Actually this now works as it should..
Just hit the Enable Content button on the Info Bar when asked (after having inspected the links under Edit -> Links to External File... for documents of unknown source).

To have WEBSERVICE() recalculated without #VALUE! error as it was before the version that started to question the origin, put the document in a trusted location, see Tools -> Options -> LibreOffice -> Security, Macro Security, Trusted Sources (you may have to add a directory if you didn't before); and then set Tools -> Options -> Calc -> General, Updating, Update links when opening, to "Always (from trusted locations)".

However, I agree that the cached result could be used at least in some cases, where WEBSERVICE() and a literal argument is the only formula expression in a cell.

Not dirty/recalculating is one thing, but then the URI isn't propagated to the link manager and thus can't be inspected either for the "Automatic update of external links has been disabled." message, so this will need more work.
Comment 15 Eike Rathke 2018-07-18 16:18:33 UTC
(In reply to Eike Rathke from comment #14)
> However, I agree that the cached result could be used at least in some
> cases, where WEBSERVICE() and a literal argument is the only formula
> expression in a cell.
Doing so would not help in the attached sample document because the WEBSERVICE() calls there are embedded in a formula expression. So this specific case will not benefit from a change.

Btw, duplicating the actual query for each record in columns AW and AX is a waste of resources, that should be done in one cell and then the different FILTERXML() calls reference that cell instead.
Comment 16 Commit Notification 2018-07-18 19:42:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Related: tdf#118735 WEBSERVICE preserve the hybrid string result, if possible

It will be available in 6.2.0.

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 17 Commit Notification 2018-07-19 18:37:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Related: tdf#118735 introduce FormulaError::LinkFormulaNeedingCheck (Err:540)

It will be available in 6.2.0.

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 18 Fitch 2018-07-24 12:33:14 UTC
As already implied this fix was never going to work for an embedded formula, and, of course, it didn't.

I deleted all the postcodes that came up in the Edit > Links window, pressed F9, and the data came up as it should, very quickly.
The problem is, the postcodes reappeared in the Links window, set to manual, and on closing the file and re-opening, they had set themselves back to unavailable.

Surely on the links window, only the file should be there, and not every postcode qeried?
Comment 19 Eike Rathke 2018-07-24 16:36:01 UTC
(In reply to Fitch from comment #18)
> The problem is, the postcodes reappeared in the Links window, set to manual,
> and on closing the file and re-opening, they had set themselves back to
> unavailable.
If you want them to be recalculated without the error: see comment 14 and the
trusted location, or hit Yes (6.0.6) to update or (6.1 and later) Enable Content button.

> Surely on the links window, only the file should be there, and not every
> postcode qeried?
Every external resource or URI queried is expected to be listed there.
Comment 20 Dorian B. 2018-09-09 17:36:46 UTC
Hello!
I used Libreoffice Calc 6.1.0.3 Portable (in Romanian language) for Windows.
I confirm bug with menu Edit -> Links to External Files ...-> window Edit links -> Address External Data -> the list tables/range is EMPTY.
Don't update the cells with the external data with URLs and don't work "Enable Content" for "Automatic update of external links has been disabled".
 
In Libreoffice Calc 6.0.3.2 for Linux (in Romanian language), it's work External date with URLs.

Please fix this bug.

Thank you for understand.

Best regards,
Dorian Baciu
Comment 21 Fitch 2018-09-09 17:50:18 UTC
I tried the patch, but it was useless, as, as advertised, it would not work on  calls embedded in a formula expression (whereas it previously did).

I have now dropped LibreOffice, as it is no longer fit for purpose.
Comment 22 Eike Rathke 2018-09-17 17:43:45 UTC
@Fitch:
Read comment 19 and comment 14 about putting the document in a trusted location.
Comment 23 Fitch 2018-09-17 17:49:13 UTC
I did everything suggested in comments 14 & 19.
I stopped the project because the people I send the spreadsheet to, for their own lists do not have the wherewithal, or the wish to delve into Libre Calc that deeply, and create "safe" folders.
Comment 24 Fitch 2018-09-17 17:50:47 UTC
And it still didn't work....
Comment 25 Xisco Faulí 2018-12-18 15:10:01 UTC
Dear Eike Rathke,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.