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
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)
Please test this in 3.4. The external reference has been re-worked for 3.4.
Nevermind you already did. I'll take a look.
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.
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.
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?
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.
And if it is undocumented behavior, I don't want the users to rely on this behavior, because it might change in the future.
(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.
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)....
Pending on someone to sign off on my fix, on the mailing list, before the fix gets committed to the 3.4 branch.
tested, and cherry picked the fix to libreoffice-3-4 - nice work Kohei :-)
how to test it? I would have to fetch sources from CVS and compile?
You can download the next beta of 3.4.0.
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.
Created attachment 46718 [details] generating err502 for referenced array
Created attachment 46719 [details] No err502 in referrenced data however first empty cells are referenced as zero and size of result is wrong
[..] 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.
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
(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.
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.