Bug 113898 - Cross-Document reference in array formula using FIND/ISERROR does not work
Summary: Cross-Document reference in array formula using FIND/ISERROR does not work
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.3.0 target:6.2.0.1 target:6....
Keywords:
Depends on:
Blocks:
 
Reported: 2017-11-17 10:36 UTC by Tilman Vogel
Modified: 2022-03-24 16:18 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
First sheet showing how the formula works correctly within a single sheet. Second sheet referencing the first and exhibiting the problem follows. (8.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-11-17 10:36 UTC, Tilman Vogel
Details
Second sheet referencing sheet1.ods and exhibiting that the result does not agree. (10.39 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-11-17 10:37 UTC, Tilman Vogel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tilman Vogel 2017-11-17 10:36:41 UTC
Created attachment 137824 [details]
First sheet showing how the formula works correctly within a single sheet. Second sheet referencing the first and exhibiting the problem follows.

Version: 5.3.3.2
Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448

When I use a SUMPRODUCT() formula referencing a range in a separate document and using FIND() in conjunction with ISERROR(), I don't get correct results - sometimes wrong results, sometimes #VALUE.

I'll attach two example sheet documents
Comment 1 Tilman Vogel 2017-11-17 10:37:33 UTC
Created attachment 137825 [details]
Second sheet referencing sheet1.ods and exhibiting that the result does not agree.
Comment 2 Buovjaga 2017-11-18 15:45:47 UTC
Confirmed.

Arch Linux 64-bit
Version: 6.0.0.0.alpha1+
Build ID: 121303615054568c204def97872343d2014af4a0
CPU threads: 8; OS: Linux 4.13; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on November 17th 2017

Arch Linux 64-bit
Version 3.6.7.2 (Build ID: e183d5b)
Comment 3 QA Administrators 2018-11-19 03:39:57 UTC Comment hidden (obsolete)
Comment 4 Xavier Van Wijmeersch 2018-11-19 18:20:04 UTC
can still reproduce with

Version: 6.2.0.0.beta1+
Build ID: 268364e35100b559f42d8c02b930c5cca1c84be7
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); UI-Language: en-US
Calc: threaded

But with AOO 4.2.0alpha i have the correct value off 2
Comment 5 Buovjaga 2018-11-21 15:30:20 UTC
I get the wrong result in 3.3.0 already (Win 10) so maybe AOO fixed this along the way?
Comment 6 Eike Rathke 2018-11-21 15:50:18 UTC
The old OOo code resolves external references differently; apparently FIND() (or something along the line) lacks handling the new way.
Comment 7 Commit Notification 2018-11-22 11:33:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/266aab73a88ae5b62fcddaf383107d8e7edc51f2%5E%21

Resolves: tdf#113898 create JumpMatrix for svExternalDoubleRef scalar values

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2018-11-22 15:15:50 UTC
This actually affected every function that expects a single scalar value parameter and gets an external reference range passed as argument in array evaluation mode and don't handle it on their own.

Pending review
https://gerrit.libreoffice.org/63794 for 6-2
https://gerrit.libreoffice.org/63797 for 6-1
Comment 9 Commit Notification 2018-11-22 18:05:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/358d7c5630e0a3b5c1afe5a2aef4c0ec0f54fd34%5E%21

Resolves: tdf#113898 create JumpMatrix for svExternalDoubleRef scalar values

It will be available in 6.2.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2018-11-22 21:01:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

https://git.libreoffice.org/core/+/8bc69c4a45d1fc4fe73dd579bc5dc828aaa9b1e7%5E%21

Resolves: tdf#113898 create JumpMatrix for svExternalDoubleRef scalar values

It will be available in 6.1.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Xavier Van Wijmeersch 2018-11-23 09:08:42 UTC
its working with

Version: 6.3.0.0.alpha0+
Build ID: ccae6c0faa6f99b4dd84fb05c5cc8efefb3b4d4f
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); UI-Language: en-US
Calc: threaded

will test later with Version 6.2.0.1beta
Comment 12 Xavier Van Wijmeersch 2018-11-23 17:33:32 UTC
also working with

Version: 6.2.0.0.beta1+
Build ID: 19805f580fcbcf890790f8c6ab736aab80c08de7
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 13 Xavier Van Wijmeersch 2018-11-24 06:51:02 UTC
thanks @Eike its working also with

Version: 6.1.4.0.0+
Build ID: b3af23568f9b9b444ae09f2a1ab4a61d212b422a
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-11-23_09:38:29
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 14 Commit Notification 2019-12-16 12:52:43 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/ba2be3187e59758ed31ceeb522f58d5796bcb97c

crashtesting: null deref on load of tdf113898-2.ods

It will be available in 6.5.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2022-03-24 16:18:35 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/400bfd03a97a9af75a55230bffd53fca45a0f3a6

tdf#113898: sc_uicalc: Add unittest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.