Download it now!
Bug 36933 - Array formula operations on external references does return 504 error
Summary: Array formula operations on external references does return 504 error
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.1 release
Hardware: x86 (IA32) Linux (All)
: medium critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:3.5
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-07 05:47 UTC by Robert
Modified: 2012-01-06 21:33 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
data.ods and test.ods files (15.84 KB, application/zip)
2011-05-07 05:47 UTC, Robert
Details
generating err502 for referenced array (10.81 KB, application/zip)
2011-05-14 08:58 UTC, Robert
Details
No err502 in referrenced data however first empty cells are referenced as zero and size of result is wrong (10.84 KB, application/zip)
2011-05-14 08:59 UTC, Robert
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert 2011-05-07 05:47:23 UTC
Created attachment 46420 [details]
data.ods and test.ods files

We have to files 

/home/<user>/data.ods
/home/<user>/test.ods

in data.ods is array (a2:a8) of values (1-7)

in B12 is condition '4'

if I do locally in data.ods

=SUMPRODUCT(A2:A8<B12,A2:A8) (cells B13 or B14 - array variant)

result is '6' as expected 

however if I refer A2:A8 array from another file there is 504 error

in test.ods the same formula (B4):

=SUMPRODUCT('file:///home/<user>/data.ods'#$Sheet1.A2:A8<B3,'file:///home/<user>/data.ods'#$Sheet1.A2:A8)

Results with Err:504 error
Comment 1 Robert 2011-05-07 06:21:56 UTC
Bug is reported for:

LibreOffice 3.3.2 
OOO330m19 (Build:202)
tag libreoffice-3.3.2.2, Ubuntu package 1:3.3.2-1ubuntu5

The same behavior is for 3.4 beta4 release for linux (LibO_3.4.0beta4_Linux_x86_install-deb_en-US.tar.gz)
Comment 2 Kohei Yoshida 2011-05-07 09:59:21 UTC
Please test this in 3.4.  The external reference has been re-worked for 3.4.
Comment 3 Kohei Yoshida 2011-05-07 10:03:00 UTC
Nevermind you already did.

I'll take a look.
Comment 4 Kohei Yoshida 2011-05-09 12:58:37 UTC
BTW, Robert, is this behavior documented somewhere?  I'm starting to doubt that this is actually an intended behavior, and if it ever worked it was just by accident.  If you know of any documentation that mentions this behavior, please point me to it.
Comment 5 Kohei Yoshida 2011-05-09 14:30:53 UTC
I'll put this on hold.  For instance, Excel calculates SUMPRODUCT([range]<[number]) differently to Calc.  Regardless of Calc's previous behavior was intentional or not, this would create an interesting interop problem.

However, I'd like to at least fix a standalone ={[range]<[number]} case which should create an array of boolean values.  This is current broken with external references.
Comment 6 Robert 2011-05-09 15:52:24 UTC
This is not issue with SUMPRODUCT function. 
But explicitly with array operations on external references.
i.e 

In test.ods you can use SUM function on range from data.ods.
=SUM('file:///home/<user>/Documents/data.ods'#$Sheet1.A2:A8)

and result is ok 28

but if I use do array operation on the range before sum it gives error 504 again.

i.e.

{=SUM('file:///home/<user>/Documents/libreoffice-external-ref-bug/data.ods'#$Sheet1.A2:A8<4)}

expected result is: 3 

More generic problem replication test.ods:

in cell put '=' mark range A2:A8 in data.ods hit CTRL+SHIFT+ENTER
you will see whole range in test.ods - no array operation

now after selecting range put <4 and CTRL+SHIFT+ENTER
{='file:///home/<user>/Documents/data.ods'#$Sheet1.A2:A8<4}

Result is Err: 504

but if you do the same locally in data.ods
result is:

TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE

It hope that that mechanism for calculating 'standalone ={[range]<[number]}' is used and common for each function. (I've just read you comment again). Error behavior would indicate this - so once this fixed, would fix them all! :)

I'm heavily using array operations on external references (golden sources), if I would have to copy required data to each spreadsheet it would me a crazy :)

BTW: 

Is there any automated regression test suite for libreoffice?
Comment 7 Kohei Yoshida 2011-05-09 16:21:18 UTC
Yes, I understand that.  But the fact that this behaves differently from Excel makes it difficult going forward, since we are trying to standardize on calculation behaviors across different spreadsheet apps.  For example, in Excel, {=SUM(A1:A4<4)} when A1:A4 has 1, 2, 3, 4 (from top down) generates {0,0,0,0} as the result (same result with SUMPRODUCT, null array).  If the same formula calculates different result in Calc, I'd like to know the rationale behind it.  This is why I asked for where it was documented.
Comment 8 Kohei Yoshida 2011-05-09 16:22:33 UTC
And if it is undocumented behavior, I don't want the users to rely on this behavior, because it might change in the future.
Comment 9 Kohei Yoshida 2011-05-09 19:31:28 UTC
(In reply to comment #6)

> BTW: 
> 
> Is there any automated regression test suite for libreoffice?

We use the same smoke test that OOo used for years.  We are also working on writing a separate unit test that's more fine-tuned for Calc's individual behaviors, but that one is brand-new by us, and doesn't include all use cases, and certainly not this one.

If you are a developer and are interested in helping us write more test coverage, please drop us a note on our developer's list.
Comment 10 Kohei Yoshida 2011-05-09 19:50:33 UTC
Ok.  Fixing the standalone ={[range]<[number]} cases ended up fixing the SUMPRODUCT case as well....  But like I said, I'm not sure if that's an intended feature, or unintended accidental behavior that happens to be useful to someone (like Robert)....
Comment 11 Kohei Yoshida 2011-05-09 20:13:15 UTC
Pending on someone to sign off on my fix, on the mailing list, before the fix gets committed to the 3.4 branch.
Comment 12 Michael Meeks 2011-05-10 02:47:32 UTC
tested, and cherry picked the fix to libreoffice-3-4 - nice work Kohei :-)
Comment 13 Robert 2011-05-12 16:14:17 UTC
how to test it? I would have to fetch sources from CVS and compile?
Comment 14 Kohei Yoshida 2011-05-12 17:05:55 UTC
You can download the next beta of 3.4.0.
Comment 15 Robert 2011-05-14 08:57:21 UTC
Works! 

Tested version

LibreOffice 3.4.0 
DEV300m103 (Build:5)

First line is not true:)

I've discovered that it works as only referenced external range contains values.
If beginning of external referenced range is empty - than returned array:
size - is number of non empty cells
first cell corresponding to empty in source are zero

Replication Procedure:

- New document
- save as data.ods (in home)
- in A5:A10 put numbers 1 to 5
- save
- New Document
- save as test.ods (in home)
- in A1 put '=' and mark range A1:A10 from data.ods - CTRL+SHIFT+ENTER (='file:///home/<user>/data.ods'#$Sheet1.A1:A9)

As a result in test.ods is
A1-A4 - zeros '0'
A5 -> '1'
A6 is empty



Next odd behavior - (This could be different bug)
- Save test.ods (in home - where data.ods is)
- Open again.
- Window pop up for data update
Before update I click yes = I can see spreadsheet empty with one value in A5 -> '0'

After 'yes'

A1:A5 contains Err:502
Looking into array definition, there is:
='file:///home/data.ods'#$Sheet1.A1:A9

So there is no <user> in path - so result is obviously wrong

No if I fix formula and ad <user> there, and save document, close, and reopen
reference is good!


So this behavior is only for fresh document.
I've added two ZIPS. one giving Err502 and one without giving just wrong array result.
Comment 16 Robert 2011-05-14 08:58:19 UTC
Created attachment 46718 [details]
generating err502 for referenced array
Comment 17 Robert 2011-05-14 08:59:26 UTC
Created attachment 46719 [details]
No err502 in referrenced data however first empty cells are referenced as zero and size of result is wrong
Comment 18 Robert 2011-05-14 09:34:31 UTC
[..]
BTW, Robert, is this behavior documented somewhere?  I'm starting to doubt that
this is actually an intended behavior, and if it ever worked it was just by
accident.  If you know of any documentation that mentions this behavior, please
point me to it.
[..]

Regarding documentation.
In http://help.libreoffice.org/Calc/Array_Functions

is

[..]
Comparison operators in an array formula treat empty cells in the same way as
in a normal formula, that is, either as zero or as an empty string. For
example, if cells A1 and A2 are empty the array formulas {=A1:A2=""} and
{=A1:A2=0} will both return a 1 column 2 row array of cells containing TRUE.
[..]

I would be unpleasantly if this functionality would disappear.

in excel (2007) it works in the same way, however before entering referenced
range it is required to select output range.

Openoffice/libreoffice does not require this. Array result is being output as
array.
Comment 19 Robert 2011-07-09 05:37:03 UTC
Bug still is in: 
LO 3.3.2 Build 202
LO 3.4.1 Build 103

and..

Oracle's Open Office 3.3.0 Build 9567
Comment 20 Eike Rathke 2011-08-22 18:16:49 UTC
(In reply to comment #7)
> Yes, I understand that.  But the fact that this behaves differently from Excel
> makes it difficult going forward, since we are trying to standardize on
> calculation behaviors across different spreadsheet apps.  For example, in
> Excel, {=SUM(A1:A4<4)} when A1:A4 has 1, 2, 3, 4 (from top down) generates
> {0,0,0,0} as the result (same result with SUMPRODUCT, null array).  If the same
> formula calculates different result in Calc, I'd like to know the rationale
> behind it.  This is why I asked for where it was documented.

The difference is that Excel does not calculate with logical values whereas Calc does.
Comment 21 Kohei Yoshida 2012-01-06 21:33:31 UTC
I no longer see this bug in the latest 3.5 build.  I'll mark this as fixed.  We've fixed several external reference related bugs since last we spoke, so I guess one of them must have fixed this.