Bug 93101 - Reference Error #REF! Inconsistent behaviour not complying with specifications in OpenDocument-v1.2-part2 specifications
Summary: Reference Error #REF! Inconsistent behaviour not complying with specification...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: Other All
: medium major
Assignee: Eike Rathke
URL:
Whiteboard: target:5.2.0 target:5.1.4
Keywords:
Depends on:
Blocks:
 
Reported: 2015-08-03 16:15 UTC by Wolfgang Jäger
Modified: 2016-10-25 19:01 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Demonstration containing addenda (13.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-03 16:15 UTC, Wolfgang Jäger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Jäger 2015-08-03 16:15:20 UTC
Created attachment 117626 [details]
Demonstration containing addenda

A sibgle cell reference into a not accessible (or not existing e.g.) spreadsheet file (ods or Excel type e.g.) will correctly produce the error indicator #REF!
(Range references may produce a misleading error message.)

The actual bug(s): 
As soon as the missing reference is used as an operand it will be evaluated as a blank cell would be. It is evaluated to 0 or to the empty string depending on the operator, in case of comparison depending on the second operand. It even evaluates to 0 if used as a selecting parameter with OFFSET or INDEX.
The missing reference passed to one of the information functions ISTEXT, ISNUMBER, ISLOGICAL, ISERROR will result in FALSE returned. 
ISBLANK on contrary will answer TRUE.

Steps to reproduce the (basic) bug:
1. Create a new Calc document
2. Enter into A3 of the first sheet the formula 
   ='file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1
3. Verify the expected error message #REF!
4. Enter into A4 below
   =1+'file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1
5. Verify the (unexpected) result 1
6. Enter into A12
   =ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1)
7. Verify the (unexpected) result TRUE

Expected behavior:
In case of 4, #REF! or a compatible error message
In case of 6. #REF! or a compatible error message

The reported bug is demonstrated in the attached example. The attachment also contains a few addenda to this report.
Comment 1 Wolfgang Jäger 2015-08-03 17:59:52 UTC
I experienced the bug with v4.4.4 and verified it with V5.0.0.5. Meanwhile I also verified the same behaviour with V3.4.5 (portable). The "legacy" version 3.3.4. behaved in a slightly different way, but the basic bug reported was already present there.
Comment 2 m.a.riosv 2015-08-03 22:49:06 UTC
Hi @Wolfang, thanks for reporting.

And for a so detailed report.

Reproducible with
Win7x64
Version: 5.1.0.0.alpha1+ (x64) Build ID: bb697b4c7bd694c6b81ed2a4cc9471cb5573b592
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-07-31_22:24:37

verified that as commented slightly different with 3.3.4.

But doesn't seem inherited from AOo, at least in 4.1.1 seems to work fine.

I think we can change the version to 3.3.4
LibreOffice 3.3.4 
OOO330m19 (Build:401)
tag libreoffice-3.3.4.1
Comment 3 MM 2015-08-04 23:13:20 UTC
Well, with v3.3.4 and Go-oo 3.2.0 on win7 x64 the =ISBLANK issue is FALSE.
And with v3.4.4 the outcome in line 6 is still 1.
With Go-oo 3.2.0 however, the outcome from line 4 and 6 are both #REF!
Comment 4 Commit Notification 2016-05-20 10:14:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#93101 propagate external reference error from refcache token

It will be available in 5.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 5 Commit Notification 2016-05-20 10:15:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#93101 correctly resolve svExternalSingleRef token in GetMatrix()

It will be available in 5.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 6 Commit Notification 2016-05-20 10:15:10 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#93101 handle svExternalSingleRef in ISERROR()

It will be available in 5.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 7 Eike Rathke 2016-05-20 10:48:17 UTC
Note that when loading the test case document it needs to be hard recalculated (Shift+Ctrl+F9) to display the now correct values. Also, the #ref!.A$1 in cell A18 is something different, in some older versions invalidated sheet references were stored in an undefined way which so far when reloading is not resolved to an invalid reference but to an invalid symbol instead.
Comment 8 Commit Notification 2016-05-20 12:41:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

handle svExternalSingleRef in all IS*() functions, tdf#93101 related

It will be available in 5.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 9 Commit Notification 2016-05-20 13:08:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

handle svExternalDoubleRef in all IS*() functions, tdf#93101 related

It will be available in 5.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 10 Eike Rathke 2016-05-20 13:49:02 UTC
Pending review https://gerrit.libreoffice.org/25207 for 5-1
Comment 11 Commit Notification 2016-05-20 20:16:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

unit test for unresolved external references, tdf#93101

It will be available in 5.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 12 Commit Notification 2016-05-23 08:13:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

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

Resolves: tdf#93101 handle external references and propagate error

It will be available in 5.1.4.

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.