Bug 107724 - Inpredictable behaviour in a more complex lookup and address processing formula
Summary: Inpredictable behaviour in a more complex lookup and address processing formula
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.5.2 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0
Keywords: regression
Depends on:
Blocks:
 
Reported: 2017-05-09 12:46 UTC by Thijs
Modified: 2017-05-10 16:09 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Test spreadsheet showing the bug (14.47 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-05-09 12:49 UTC, Thijs
Details
Screenshot showing result (266.81 KB, image/jpeg)
2017-05-09 12:49 UTC, Thijs
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Thijs 2017-05-09 12:46:52 UTC
Description:
Combination of various nested MATCH, INDIRECT, ADDRESS functions behaves inpredictably, whilst working correctly when splitting the formula over two cells.
Only malbehaves in LibreOffice and under various OS'ses. Behaves *correctly* in MS-Office using xslx exports, and also behaves *correctly* in OpenOffice using the original .ods file.

Steps to Reproduce:
I did prepare a sheet showing the problem and a correct working workaround using two cells with split functionality (URL: https://www.mpxf.nl/data/bugs/LibreOffice%205.3.2.2%20Bug.zip)

Basically the looks up the two occurences (assumed to be present) in a list named RoleAssignment for a given entry in a table named Role. Occ#1 is lookedup using MATCH(), whereas Occ#2 is also searched for using MATCH, but starting just after Occ#1 by restricting the address range within the table RoleAssignment

formula (bad behaving):
=MATCH($E4;INDIRECT(ADDRESS(ROW(RoleAssignment)+$G4;COLUMN(RoleAssignment))&":"&ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;COLUMN(RoleAssignment)));0)+$G4

split formula (well behaving):
(1st step)
=ADDRESS(ROW(RoleAssignment)+G4;COLUMN(RoleAssignment)) & ":" & ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;COLUMN(RoleAssignment))
(2nd step)
=MATCH($E4;INDIRECT($I4);0)+$G4

Actual Results:  
#N/A
#N/A
8
8
6

Expected Results:
3
6
10
8
9


Reproducible: Always

User Profile Reset: No, but I used various computers and OS'ses (WINXP-SP3, WIN10, Linux SUSE Leap 42.2)

Additional Info:
Note the link above (under "Steps to reproduce") to the prepared test spreadsheet showing the malbehaviour and a screenshot of the result on my systems


User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0
Comment 1 Thijs 2017-05-09 12:49:00 UTC
Created attachment 133191 [details]
Test spreadsheet showing the bug
Comment 2 Thijs 2017-05-09 12:49:51 UTC
Created attachment 133192 [details]
Screenshot showing result
Comment 3 Eike Rathke 2017-05-09 13:07:55 UTC
Already in 5.1.5 at least, don't have earlier versions at hand. Investigating.
Comment 4 Eike Rathke 2017-05-09 16:49:29 UTC
The problem is that for MATCH (and LOOKUP, HLLOKUP and VLOOKUP) the second parameter forces its argument and sub-arguments to Array if not of type Reference, which happens during compile time, but INDIRECT during runtime returns Reference so should not be treated that way. Here arguments to INDIRECT (namely ADDRESS and ROW and COLUMN) are forced into Array context hence make INDIRECT return different ranges than expected.
Comment 5 Commit Notification 2017-05-09 20:37:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Move ScParameterClassification::Type to formula::ParamClass, tdf#107724 prep

It will be available in 5.4.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 2017-05-09 22:40:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Disambiguate Reference -> uno::Reference (tdf#107724 prep rel)

It will be available in 5.4.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 Commit Notification 2017-05-09 22:43:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Change IsForceArrayParameter() to GetForceArrayParameter(), tdf#107724 prep

It will be available in 5.4.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 Commit Notification 2017-05-09 22:44:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Disambiguate Reference -> uno::Reference (tdf#107724 prep rel)

It will be available in 5.4.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 2017-05-09 22:44:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Hold ParamClass at FormulaByteToken instead of bool, tdf#107724 prep

It will be available in 5.4.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 Commit Notification 2017-05-10 12:03:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Add ParamClass ScParameterClassification::CommonData::eReturn, tdf#107724 prep

It will be available in 5.4.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 11 Commit Notification 2017-05-10 12:04:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

GetParameterType: SAL_MAX_UINT16 for return ParamClass, tdf#107724 prep

It will be available in 5.4.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 2017-05-10 12:04:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

inherit ReferenceOrForceArray only if nested not Reference, tdf#107724 prep

It will be available in 5.4.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 13 Commit Notification 2017-05-10 12:05:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#107724 INDIRECT and OFFSET return ParamClass::Reference

It will be available in 5.4.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 2017-05-10 13:41:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

ParamClass::Reference return for range creating OpCodes, tdf#107724 related

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

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

Add unit test for non-propagation of array context, tdf#107724

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