Download it now!
Bug 115493 - matrix formula wrong calculation from names, also wrong saving of names
Summary: matrix formula wrong calculation from names, also wrong saving of names
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3 all versions
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:6.1.0 target:6.0.2 target:5.4.6
Keywords:
Depends on:
Blocks:
 
Reported: 2018-02-06 17:37 UTC by y1360555
Modified: 2018-02-28 10:03 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
testing file with bug shown (15.57 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-06 17:38 UTC, y1360555
Details

Note You need to log in before you can comment on or make changes to this bug.
Description y1360555 2018-02-06 17:37:38 UTC
Description:
open attached spreadsheet and use steps there:
fill cells with constants
name start is defined as A6
name end is defined as A10
name count is defined as ROW(end)-ROW(start)-1
name aaa is defined as OFFSET(start,1,0):OFFSET(start,count,0)
name bbb is defined as OFFSET(start,1,1):OFFSET(start,count,1)
cell B1 is defined as matrix formula =SUM(IF(EXACT(aaa,A$1),bbb,0))
-------> wrong also B2 is used to create matrix formula
-------> wrong result in B1: is 6, should be 4
-------> wrong result in B2: is 6, should be 2
-------> wrong saving of file - names are damaged
FILESAVE, CALCULATION

Steps to Reproduce:
1. open attached spreadsheet
2. fill cells with constants
3. name start is defined as A6
4. name end is defined as A10
5. name count is defined as ROW(end)-ROW(start)-1
6. name aaa is defined as OFFSET(start,1,0):OFFSET(start,count,0)
7. name bbb is defined as OFFSET(start,1,1):OFFSET(start,count,1)
8. cell B1 is defined as matrix formula =SUM(IF(EXACT(aaa,A$1),bbb,0))


Actual Results:  
B2 is used to create matrix formula
result in B1: is 6
result in B2: is 6
names are damaged when saving and reopening file


Expected Results:
only B1 is used to create matrix formula
result in B1 should be 4
result in B2 should be 2
names are preserved when saving and reopening file



Reproducible: Always


User Profile Reset: No



Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:57.0) Gecko/20100101 Firefox/57.0
Comment 1 y1360555 2018-02-06 17:38:46 UTC
Created attachment 139643 [details]
testing file with bug shown
Comment 2 Eike Rathke 2018-02-07 14:51:14 UTC
Investigating.
Comment 3 Eike Rathke 2018-02-07 20:21:25 UTC
These are actually two issues.

1. The names are not damaged during save, but when loading the document and compiling a named expression a contained name is not found if not already loaded, which is resolved in a second step later, but here the name is identical to a cell content (e.g. "start" and "end") and "Automatically find column and row labels" is switched on for this document (Tools->Options->Calc->Calculate, General calculations). These labels then lead to errors in the named expression context they are used.

2. The unexpected calculation of both array results as 6 when names are intact.
Comment 4 Commit Notification 2018-02-07 21:25:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#115493 postpone name resolution after unsuccessful IsNamedRange()

It will be available in 6.1.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 Eike Rathke 2018-02-07 21:32:59 UTC
Pending review of this part
https://gerrit.libreoffice.org/49396 for 6-0
https://gerrit.libreoffice.org/49397 for 5-4
Comment 6 Eike Rathke 2018-02-08 20:12:22 UTC
Effectively the result in this example should be the same as
=SUM(IF(EXACT(A7:A9,A$1),B7:B9,0))
entered as array formula, delivering one element of 4.
Expecting 2 as a second result element is not correct.
Comment 7 Commit Notification 2018-02-08 23:20:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.1.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 8 Eike Rathke 2018-02-08 23:26:28 UTC
Pending review https://gerrit.libreoffice.org/49464 for 6-0
Comment 9 Commit Notification 2018-02-09 10:18:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=04835fd953d28e44843e9473be4f4d99a24eddf0&h=libreoffice-6-0

tdf#115493 postpone name resolution after unsuccessful IsNamedRange()

It will be available in 6.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 10 Commit Notification 2018-02-09 10:18:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

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

tdf#115493 postpone name resolution after unsuccessful IsNamedRange()

It will be available in 5.4.6.

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 Commit Notification 2018-02-09 12:01:11 UTC Comment hidden (obsolete)
Comment 12 Eike Rathke 2018-02-09 12:50:56 UTC
Ignore last commit, wrong bug number.
Comment 13 Commit Notification 2018-02-14 15:34:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Unit test tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.1.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 14 Commit Notification 2018-02-26 10:43:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

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

Resolves: tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.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.