Issue 120976

Summary: External cell ranges including empty rows break LBound/UBound
Product: Calc Reporter: Andrea Pescetti <pescetti>
Component: uiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3    
Version: 3.4.0   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Original attachment as discussed by Jørn Pedersen on ooo-dev (don't use)
none
Data file for simplified analysis
none
File bug_bounds.ods for the simplified analysis none

Description Andrea Pescetti 2012-09-15 14:03:53 UTC
Created attachment 79513 [details]
Original attachment as discussed by Jørn Pedersen on ooo-dev (don't use)

Cell ranges including empty rows have different size (as reported by LBound/UBound) depending on whether they refer to the current document or to an external document (where apparently the empty rows get trimmed during the range->array conversion). This means that referring to a 3x3 range (say, A1:C3) in an external document and assuming that the returned "object" is a 3x3 array may lead to an error: "Illegal value or data type. Index outside area".

If this is considered a bug, it is a regression with respect to 3.2.1.

(I include, with permission, the complete bug report by Jørn Pedersen here after a post on ooo-dev a few months ago and a private follow-up; but I will shortly post a much more compact way to reproduce and diagnose the same problem, so I recommend that those documents are used instead)


A Calc macro with file refs. fails in v3.3 but works in v3.2





Overview:

A macro in Calc operating on numerical values in a matrix works well in OpenOffice v3.2.0 (Ubuntu 10.04) and v3.2.1 (Win XP SP3).



The macro fails in OpenOffice v3.3.0 (Win XP SP3) when the macro refers to data in other files and when the matrix is filled in in a certain way.



The function of the macro is to interpolate linearly between columns in a matrix.



Three types of matrix filling has been provided in the attached examples: A normally filled matrix, a matrix with interleaved rows (only data on every second line), and matrices with empty areas (positions where no data has been entered).





Steps to reproduce:

First of all we are talking about Danish versions of OpenOffice in all cases.



1.

Start-up OpenOffice Calc v 3.2 (Danish)



2.

Copy the macro code from the attached file "Macro_source_code.txt" to the Standard library.



3.

Allow macros



4.

Open the attached file "Data.ods" and allow macros.



Now the result should look as in attached file "Data.ods_v3.2.bmp".

Below each matrix 3 interpolations between column 1 and 2 has been done in 3 different rows, and the results nicely shows 3, 13 and 23 as expected.



5.

Open the attached file "Data_a.ods" and allow macros.



Now the result should look as in attached file "Data_a.ods_v3.2.bmp".

Here the same interpolations have been done for each matrix but with the macroparameters now referring to the file "Data.ods".

The results are the same, just as expected.







6.

Start-up OpenOffice Calc v 3.3 (Danish)



7.

Copy the macro code from the attached file "Macro_source_code.txt" to the Standard library.



8.

Allow macros



9.

Open the attached file "Data.ods" and allow macros.

Now the result should look as in attached file "Data.ods_v3.3.bmp".

Same result as with v3.2. Correct as expected.



9.

Open the attached file "Data_a.ods" and allow macros. 



If answering "No" to update links:

The results is as in attached file "Data_a_v3.3_x.bmp".

Now the answers are not correct when the matrix has blank areas.



If answering "Yes" to update links:

First 6 error messages as shown in attached file "ErrorMsg_1.bmp". The text says "Illegal value or data type. Index outside area".

The results is as in attached file "Data_a_v3.3_y.bmp".

Now again the answers are not correct when the matrix has blank areas, but the pattern is different. The result can vary depending on which device the data is stored on. I got different results when stored on HD and USB dongle.



10.

Open the attached file "Data_b.ods" and allow macros.

Now the result should look as in attached file "Data_b.ods_v3.3.bmp".

This is a simple cell-to-cell reference to check the referencing to external files. These answers are correct.





Subconclusion:

It seems not to be the referencing to external files itself that creates the error. Otherwise the error pattern seems unlogical.

Inspecting the response when not updating links ("Data_a_v3.3_x.bmp") it could look as the correct answers are shifted 1 line downwards or 2 lines when having 1 empty line in the matrix respectably 2 lines.
Comment 1 Andrea Pescetti 2012-09-15 14:06:52 UTC
Created attachment 79514 [details]
Data file for simplified analysis
Comment 2 Andrea Pescetti 2012-09-15 14:13:29 UTC
Created attachment 79515 [details]
File bug_bounds.ods for the simplified analysis

To reproduce and study the bug it's enough to:

1) Save the data file numbers.ods and the file bug_bounds.ods in the same folder
2) Open bug_bounds.ods (allow macros and update links when asked).

The same range (B5:D11) is seen as a 7x3 array when in the same document, as a 5x3 array (i.e., empty rows are trimmed) when imported from an external document.

For reference, there might be some similarity to
https://issues.apache.org/ooo/show_bug.cgi?id=117220
(but the description does not really match)
and the same bug can be seen at
https://bugs.freedesktop.org/show_bug.cgi?id=51990
but is untouched there.