Issue 117220 - Columns and Rows functions on external referenced ranges return wrong values
Summary: Columns and Rows functions on external referenced ranges return wrong values
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 3.4.0 Beta (OOo)
Hardware: PC All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2011-03-04 16:47 UTC by marco.nicolazzo
Modified: 2013-01-29 21:44 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Example spreadsheets (13.33 KB, application/octet-stream)
2011-03-04 16:47 UTC, marco.nicolazzo 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description marco.nicolazzo 2011-03-04 16:47:55 UTC 
Created attachment 76004 [details]
Example spreadsheets

The following functions

=ROWS('file:///C:/Users/marco/Desktop/TestB.ods'#$Sheet1.$A$1:$Z$1048576)
=COLUMNS('file:///C:/Users/marco/Desktop/TestB.ods'#$Sheet1.$A$1:$Z$1048576)

return wrong value (i.e. 1) when the range specified is very large and user answer not to refresh referenced data on opening.

Example (in TestA.ods)

Range		Rows	Columns
A1:C10		10	3
A1:C1000	1000	3
A1:C1000000	1000000	3
A1:C1048576	1048576	3
A1:F1048576	1048576	6
A1:I1048576	1048576	9
A1:L1048576	1048576	12
A1:M1048576	1048576	13
A1:N1048576	1048576	14
A1:O1048578	1	1

wher rows by cols exceed a value arount 14 millions, then both functions return the value 1, if referred data is not refreshed on opening.

Implication: vlookup or hlookup functions are unusable when selecting large ranges in external references (for example the entire theoretical sheet, as it's often done), because the wrong number of rows/columns reported cause them to report error 502.
Comment 1 Regina Henschel 2011-03-07 13:04:03 UTC 
I see the problem with the attached pair of documents. The wrong result 1 does not only appear with very large number of rows but also with more than 7 columns in the example documents. But I cannot set up a new pair of documents with exact the same error.

Trying it with new documents I get the error, that not the number of rows or columns of the reference is returned. But the number of rows and columns depends on the content of the referenced cells. This behavior does not correspond to the ODF definition. The wrong result does only occur, if a range in an external file is referenced, but not when the range is inside the own document.

So there is surely something wrong with external references.

I have tested it with DEV300m101 on WinXP.

(Remove target milestone OOo3.3, because it is released already.)
Comment 2 Oliver Brinzing 2011-03-09 08:38:44 UTC 
.
Comment 3 ooo 2011-03-09 15:41:30 UTC 
Grabbing issue.
External references need to be treated differently for ROWS() and COLUMNS().
Comment 4 Oliver-Rainer Wittmann 2012-06-13 12:32:45 UTC 
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.