Apache OpenOffice (AOO) Bugzilla – Issue 125743

INDEX() function in array context returns #VALUE! cells instead of full rows

Last modified: 2014-10-17 17:50:10 UTC

How to reproduce: * Open a blank spreadsheet * In an arbitrary cell, enter this formula: <tt>=INDEX({1;3;5|7;9;10};{2|1};0;1)</tt> * Press Command-Shift-Enter to make this formula an array formula. * The array formula fills three columns by two rows of cells. Observed behaviour: Cell contents are <tt>{7; #VALUE!; #VALUE! | 1; #VALUE!; #VALUE}</tt> . Expected behaviour: Cell contents are <tt>{7; 9; 10 | 1; 3; 5}</tt> . Discussion: The "column" parameter of the INDEX() function is 0. This means that, outside of an array context, the INDEX function returns all columns of the selected row of an array. The "row" parameter of the function is <tt>{2|1}</tt>. I expect that in the array context, each row of this vector would be applied to the array. If the formula is modified to have only a scalar value for the "row" parameter, as in <tt>=INDEX({1;3;5|7;9;10};2;0;1)</tt>, as an array formula, then the result array is one row by three columns, with the value <tt>{7; 9; 10}</tt>, as expected. If the formula is modified to have a nonzero value for the "column" parameter, as in <tt>=INDEX({1;3;5|7;9;10};{2|1};1;1)</tt>, as an array formula, then the result array is two rows by one columns, with the value <tt>{7|1}</tt>, as expected. If the formula is modified to delete the "column" and "range" parameters, the observed behaviour is unchanged. Observed only on OO.o 4.0.1 on Mac OS X 10.6.8. I can't run OO.o 4.1.x on OS X 10.6.8, so I haven't tried it on OO.o 4.1. Thus I think that this is an interaction between the array formula context, and the array parameter for "row" where a scalar is expected, and the 0 value for the parameter "column" asking for an array rather than a scalar result. One possible response is, this computation model for INDEX() in an array context calls for this. Explain the computation model, and I'll be happy to write it up for the documentation at https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_INDEX_function . Or, this might be a bug. In which case, that is also good to know.

Added heads-up note in documentation at https://wiki.openoffice.org/wiki/Documentation/How_Tos/Using_Arrays#Issues and https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_INDEX_function#INDEX_function_in_array_formula_context .

I don't think it is a bug. Please compare =INDEX({1;3;5|7;9;10};{2;1};0;1) and =INDEX({1;3;5|7;9;10};0;{2|1};1) as array formulas. Consider the difference between the vector directions! (vertical {a|b} for column value and horizontal {a;b} for row value)

@mroe: thanks for looking at this. You may be right, perhaps it's not a bug. But then, can we come up with words that describe the present behaviour? When an array formula expands the array in a scalar parameter like "row" or "column", in what way does it treat a 1-row vector differently from a 1-column vector? Is the behaviour when this vector is in the "row" parameter different from when it is in the "column" parameter? If so, why? And why does having a value 0 for the "row" or "column" parameter not give a complete column or row in the result? My expectation is that setting a parameter to 0 causes INDEX() to return a complete row or column. This is not happening in the array formula context. Maybe my expectation is wrong. But I don't see any written specification to tell me what I should expect.

Microsoft Excel 2008 for Mac (version 12.3.6 (130206) has different behaviour than OO.o Calc 4.0.1. I tried permutations of (vector parameter for row, 0 for column) and (0 for row, vector parameter for column) with (2-row 1-column) and (1-row 2-column) vector parameters. In each case the value in the upper-left cell is the same between Excel and OO.o. However, the values in the remaining cells differ. Also, when I define the array formula, Excel doesn't replicate it to adjoining cells, the way OO.o Calc does when it has a multi-cell array to return. Wrapping the INDEX() expression in ROWS() and COLUMNS() functions seems to indicate that Excel does return an array, not a single cell. My next step is to make a test spreadsheet which tries the permutations and shows the results. One can open this spreadshset in both OO.o and Excel to compare.

Because I don't have Excel I can't compare it. I'm not a developer and don't know how the function is implemented. Issuezilla is the Bugtracking System for AOO. Better places for discussions are user forums, the user mailing list or the developer mailing list, if there implementation related questions. So I close this as NOT AN ISSUE.

MRoe: I am confused. I am filing a *bug* report. I think the behaviour of OpenOffice 4.0.1 does not meet my expectations. Also, I see no documentation describing what the correct behaviour is. Either there is an issue with the software (it behaves incorrectly), or with the software's specification (it does not specify how the software should behave in this case). Why close this as "not an issue", when I am reporting a bug, with reproducible behaviour? You say, "Issuezilla is the Bugtracking System for AOO." I believe that this site <https://issues.apache.org/ooo/> is what you refer to as Issuezilla. Am I mistaken? Also, why change the "version" field from "4.0.1" to "OO.o 3.3 or older"? Perhaps that was unintentional? I changed it back. I apologise if that is impertinent.

> why change the "version" field Look at the help for the field Version: https://issues.apache.org/ooo/page.cgi?id=fields.html#version "The oldest version of the software the issue can be found in." As I know the function was never implemented in an other way, so it is "OOo 3.3 or older" (OpenOffice.org before Apache OpenOffice). > I think the behaviour of OpenOffice 4.0.1 does not meet my expectations. Expectations are not a bug of AOO. A bug occurs if a function does not what it should do by the given implementation. Developers look at IssueZilla to find bugs and features on which they are want to work. Maybe there is a good reason to change the implementation of the function. But please discuss it on https://forum.openoffice.org/en/forum/viewforum.php?f=9 or subscribe to the users mailing list. Because you are writing a documentation for AOO you may want discuss implementation details by subscribing to the developers mailing list. If such a discussion results in a agreement that - the function gives a wrong result - there is a wrong documentation (help) of the function - the function should be enhanced for some reasons then you are welcome back, reopen the issue as DEFECT with your description why the result is wrong or as ENHANCEMENT with a description of your feature wish. Please have a look at some other issues which are refers to discussions in forums or mailing lists; and look also at the archive of mailing list where are discussions about special issues.

Thank you for the clarifications. >> why change the "version" field > >Look at the help for the field Version: >https://issues.apache.org/ooo/page.cgi?id=fields.html#version >"The oldest version of the software the issue can be found in." >As I know the function was never implemented in an other way, so it is "OOo 3.3 >or older" (OpenOffice.org before Apache OpenOffice). I had not read this definition. Thank you for showing me. Based on your understanding that the function was never implemented in another way, the correct setting is indeed "OOo 3.3 >or older". I have changed it back. > Expectations are not a bug of AOO. > A bug occurs if a function does not what it should do by the given > implementation. I agree. So what is important in this discussion is the specified correct behaviour for the function. I opened a forum post on this topic, "What is correct INDEX() behaviour in array context?" <https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=72871>. A valuable pointer there was to the specification for Apache OpenOffice's functions, <http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.html> (actually the related .odt file is authoritative). Specifically, the relevant sections seem to be: * 6.14.6INDEX <http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018424_715980110> * 3.3Non-Scalar Evaluation (aka 'Array expressions') <http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017866_715980110> There is also a helpful comment from forum ocntributor 'acknak', " think the problem with the bug report is that there is no clearly correct behavior. If you find a situation where Calc adds 2 and 3 to get 6, then obviously there is a problem, and the correct behavior is clear as well. I'm not seeing where that's the case here. Calc's behavior may not make sense but there's no way to say that it's wrong." I'm interpreting the situation as, Calc does not have a specification for correct behaviour of INDEX() returning a non-scalar value in an array expression context, and therefore the reported behaviour is not wrong. It may be unexpected or unsatisfactory, but it is not presently 'wrong'.

Thanks to Lupp, in the forum thread, for pointing me to <http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.html>, the "OpenDocument specification", v1.2, Part 2, which is apparently the authority on how Calc *should* behave. This is the sort of specification for which I was looking. My case, with some array values for normally scalar arguments, and a "0" parameter for "column", seems to be covered by <http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017866_715980110>, section 3.3 "Non-Scalar Evaluation (aka 'Array expressions')", section 2.2.1: > 2.2.1) Functions returning arrays are not eligible for implicit iteration. When evaluated in 'matrix' mode the {0;0}th element is used. We can quibble about what section 3.3 says about how Calc should behave, and how this differs from the way Calc 4.0.1 actually behaves. But the behaviour I was expecting in this issue was that functions returning arrays would be eligible for implicit iteration. The spec isn't giving me that. In that case, I think the specification for <http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018424_715980110>, 6.14.6 "INDEX", and the "https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_INDEX_function]documentation wiki for INDEX", and the OpenOffice Help for INDEX, are lacking. They should have some mention that a parameter value of 0 is not permitted in some circumstances. And, documentation for any other "function returning arrays" should have a similar mention. I would like to see a note like: > Note: when this function is used in an array formula context, and when one of the [i]row[/i], [i]column[/i], or [i]area[/i] parameters are arrays instead of scalar values, then neither the [i]row[/i] nor the [i]column[/i] parameter is permitted to have the value 0. The function will not return the expected array in this situation. And a similar note should be in the specification and documentation for any other function returning arrays. However, that is beyond the scope of this issue.

I think, your interpretation isn't correct (if I understand it right). > They should have some mention that a parameter value of 0 is not permitted in some circumstances. The specific problem of understanding is IMO the fact, that the parameters of INDEX() are not operands of a mathematical term rather than positional parameters *for* an operand.

Oops, Bugzilla formatted two paragraphs from my comment #9 as very long lines. Here they are again, with line breaks. First, ...section 3.3 "Non-Scalar Evaluation (aka 'Array expressions')", section 2.2.1: > 2.2.1) Functions returning arrays are not eligible for implicit iteration. > When evaluated in 'matrix' mode the {0;0}th element is used. Second, ...I would like to see a note like: > Note: when this function is used in an array formula context, and > when one of the [i]row[/i], [i]column[/i], or [i]area[/i] parameters > are arrays instead of scalar values, > then neither the [i]row[/i] nor the [i]column[/i] parameter > is permitted to have the value 0. The function will not > return the expected array in this situation. And a similar note should be in the specification and documentation for any other function returning arrays. Hopefully, this formatting of those parts of #9 will be easier to read.

Sorry, I don't understand what you mean with your comment #10: >... the parameters of INDEX() are not operands of a mathematical term > rather than positional parameters *for* an operand. There is a distinction I learned in programming class between formal parameters (names which are place-holders in the function definition) and actual parameters (the specific values which the software uses when executing a specific call to the function). Maybe that's what you are trying to express by "operands" and "positional parameters *for* and operand". But I'm afraid I don't see how to apply that interpretation to subsection 2.2.1 of the specification. Could you clarify, please? And if you don't want to have a discussion in the issue comments, perhaps bring your comment to the forum topic? "What is correct INDEX() behaviour in array context?" <https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=72871>. Thank you.