Issue 117843 - CALC - MATCH function doesn't work on external file
Summary: CALC - MATCH function doesn't work on external file
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: 3.4.0 Beta (OOo)
Hardware: All All
: P3 Normal (vote)
Target Milestone: 4.2.0
Assignee: ooo
QA Contact:
Keywords: regression
Depends on:
Reported: 2011-04-14 09:44 UTC by daniel.weidmann
Modified: 2015-11-06 13:24 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---
kschenk: 4.1.2_release_blocker?

Evidences to reproduce match bug (198.97 KB, application/zip)
2011-04-14 09:44 UTC, daniel.weidmann
no flags Details
Patch to avoid shrinking the matrix for uncached range (882 bytes, patch)
2015-01-11 10:14 UTC, hanya
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description daniel.weidmann 2011-04-14 09:44:00 UTC
Created attachment 76379 [details]
Evidences to reproduce match bug

In CALC, when using the MATCH to locate a value in an external file (link) doesn't work if the search criterion is an alphabetical value (similar usage with a numerical search criterion works fine)

Evidence in the 2 attached file :
MATCH-Bug-OO-3.4.ods : the main file where the bug arises
MATCH-Bug-OO-Bareme.ods : the external file used to do the lookup

on rows 6 & 9 the lookups are performed on the external file
on rows 7 & 10 the lookups are performed on a internal copy (calc-sheet) of the external file content

on row 6 - the lookup on an alphabetical value fails
on row 9 - similar lookup on a numerical value is ok
Comment 1 daniel.weidmann 2011-04-14 09:48:57 UTC
Was working correctly in OO 3.2.1 and before

Similar defect reproduced on OO 3.3
Comment 2 Oliver Brinzing 2011-04-14 10:50:14 UTC
Comment 3 ooo 2011-04-18 09:53:49 UTC
Note: the external references point to EQUIV-Bug-OO-Bareme.ods that doesn't exist and when updating links gives #REF errors, change links to MATCH-Bug-OO-Bareme.ods
Comment 4 Oliver-Rainer Wittmann 2012-06-13 12:18:49 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.
Comment 5 hanya 2015-01-09 15:03:13 UTC
For the match against external document, matching scan is done against the data matrix 
in ScInterpreter::ScMatch method. 
The matrix data is constructed from the copied data into content.xml file from 
the external file.

It seems the problem is caused by the copied data into the file. The data contains 
different values from the original.

- Target.ods document contains some data in Sheet1.C1:E1 (A1 and B1 are empty).
<table:table table:name="Sheet1" table:style-name="ta1" table:print="false">
  <table:table-column table:style-name="co1" table:number-columns-repeated="5" table:default-cell-style-name="Default"/>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="2"/>
    <table:table-cell office:value-type="string">
    <table:table-cell office:value-type="string">
    <table:table-cell office:value-type="string">

- I got the following data that copied into the file that contains MATCH function like: 

<table:table table:name="'file:///home/asuka/Documents/TestDocs/Target.ods'#Sheet1" table:print="false" table:style-name="ta_extref">
  <table:table-source xlink:type="simple" xlink:href="../TestDocs/Target.ods" table:table-name="Sheet1" table:filter-name="calc8" table:mode="copy-results-only"/>
  <table:table-column table:number-columns-repeated="5"/>
    <table:table-cell table:number-columns-repeated="2"/>
    <table:table-cell table:style-name="ce1" office:value-type="float" office:value="6.9532872652317E-310">
    <table:table-cell table:style-name="ce1" office:value-type="float" office:value="6.95328726508467E-310">
    <table:table-cell table:style-name="ce1" office:value-type="string">
Before the cell that contains "A", strange two cells are placed.
Comment 6 hanya 2015-01-10 18:34:43 UTC
ScMatrix instance is filled in lcl_convertToTokenArray function called from 
ScExternalRefManager::getDoubleRefTokens method when the source document is loaded. 
The matrix is prepared for the shrunk data area. If the range contains empty cells 
at left or top side of the range, the matrix has smaller number of columns or rows. 
The contents of the matrix is assigned by PutXXX methods and invalid position 
of column or row is ignored to be assigned. 
In the case of Comment 5, the matrix is initialized with single row and three columns ("A", "B", "C"). 
Indexes for each columns are 2, 3 and 4. But the matrix allows 0, 1 and 2 for 
column index. Therefore only "A" was assigned to the matrix.
Strange two cells having zero value (can be taken from empty cells) in Comment 5
 was inserted in ScExternalRefCache::setCellRangeData method because 
the 0 and 1 elements are not empty (SC_MATVAL_EMPTY is not assigned).

nC and nR should be fixed like the following in lcl_convertToTokenArray function: 
SCSIZE nC = nCol - nDataCol1, nR = nRow - nDataRow1;
But this is not enough. The result of MATCH function is shifted by number of 
empty cells with the above fix.
Comment 7 hanya 2015-01-11 09:27:34 UTC
The match is correct for the matrix generated from the data stored in the file with 
copy-results-only mode. Because the matrix is filled with non-shrunken data in 
ScExternalRefCache::getCellRangeData method. 
But the range of the document not cached yet is shrunken. It gives different result.

Until sparsely-filled matrix is implemented that described in the comment of getCellRangeData method, 
shrinking the matrix data gives wrong match without shifting information of the 
matrix inside the original range. But there is no such way on the matrix implementation.
Comment 8 hanya 2015-01-11 10:14:37 UTC
Created attachment 84402 [details]
Patch to avoid shrinking the matrix for uncached range

Without shrinking the matrix, generated matrix matches the original range and 
no zero data is inserted in setCellRangeData method. This gives correct matrix 
for ranges from external files.
Comment 9 SVN Robot 2015-01-15 18:14:42 UTC
"hanya" committed SVN revision 1652207 into trunk:
#i117843# avoid shrinking the range from external file until the matrix suppo...
Comment 10 hanya 2015-01-15 18:22:57 UTC
Fixed on trunk.
To confirm this, download the attached zip archive by the reporter, 
- unzip the archive
- renamce from MATCH-BUG-OO-Bareme.ods to EQUIV-BUG-OO-Bareme.ods to match the file name with the name input as the argument of MATCH function call
- open MATCH-Bug-OO-3.4.ods file
- push yes to update the external link when notice message box is opened
-> correct position for external link is shown

The reporter says the reference to textile is broken. But the real problem is 
all external reference having empty cells at left side or top side in their 
cell range are broken. Therefore, all references to external link should work 
with this fix.