Calc AND function bug or design flaw?

English
#1

I noticed today that the AND function always resolves all inputs, regardless if any of them have been found to be FALSE already. I have never seen that in a programming language, so not sure if this is a spreadsheet thing or what. But in my experience with many programming languages, the boolean function “AND” always returns FALSE after it encounters the first input that is FALSE. There is no need to examine the remaining inputs as none of them can turn a former FALSE into a TRUE etc. Thus, this is both a performance issue and a logic/rational issue. It’s based on the industry acceptable definition of the boolean logic AND gate.

But here is a screenshot from Calc. You can see that Tests 3, 6, and 7 reveal that ALL inputs are examined leading to the solution being a divide by zero error when it would have returned FALSE after the first FALSE input encountered.

image
image537×533 29.3 KB

#3

Note that e.g. Basic also doesn’t have short-circuit boolean evaluation.
Even though I find no explicit mention of this in ODF, it looks OK, consistent with usual spreadsheet processing. But e.g. IF provides short-cirquiting (or maybe simply doesn’t propagate the error, unless required). Maybe @erAck can explain the reason for the behavior.

#4

I think the IF function can only test one input. Obviously, that input could be the AND function, but then we’d be right back at the start of this question/issue. Unless I am missing something in the comparison?

#6

IF could evaluate both branches, and error out when one of them is error (which is common in spreadsheet functions). Thus, I mentioned it here, as a sample where we don’t propagate errors in arguments.

#8

It is mentioned in ODF in the formula processing model, 3.2.3 Operator and Function Evaluation:

1.The value of all expression arguments are computed. Exceptions to computation of all arguments are noted in a function’s specification.

Note: The practice of computing all argument expressions is known as “eager” evaluation. The IF function is an example of a function that does not require computation of all arguments.

1 Like
#9

I’m not sure what you mean by branches. Are you referring to nesting behavior such as having another IF for the second and/or third function parameters?

#10

IF( condition ; then_branch ; else_branch )

#11

Using the AND() function simply creates an expression (formula) or a subexpression of a formula.
Expressions may or may not be evaluated “shortcircuit”. To do so may look efficient or logically pretty, but shortcircuit can only make sense if the actual order (sequence) of operations is unambiguously determined, and the user is well aware of how it is done.
In addition shortcircuit technique will always thwart side-effects of “avoided” sub-expressions which users may expect to be created anyway.
I don’t know that many programming languages as the questioner, but my impression was that -if at all- shortcicuiting is applied to subexpressions formed by chains of logical operators. In fact I don’t remember a single case where it was applied to continued products if a factor was annulling (e.g.).
Anyway we have to take it as is: Neither Calc nor Basic apply shortcicuiting.
Concerning the IF() function I need to “research”. It may be the one exception. If so, this must be specified. Otherwise it’s a bug.
This accepted, we also can’t avoid the propagation of errors.

#2

In SQL, any operation with a Null value returns Null.

#5

I don’t understand the logic of that. First, it’s contrary to the definition of the AND gate. Second, if we care about a boolean result, and the value of a former input gives us that result, why do we care what the other inputs are, even if they are Null values? If I cared whether or not any of the inputs were Null values, I would expect to use an “ISNULL” function or perhaps the existing ISNA function (and feed it an array of inputs, which I don’t think it currently supports unfortunately). And third, evaluating every input may involve an uncessary performance hit, which could be significant.

#7

Please read the docs. Could be any old Excel book from the 90ies.
ISNA tests for one special type of error (failed lookup because the looked up value is simply not there).
ISERROR tests for any error.
ISERR tests for any error except #NA.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.