Bug 111675 - IFS() and SWITCH() should short-cut evaluation like IF() and CHOOSE()
Summary: IFS() and SWITCH() should short-cut evaluation like IF() and CHOOSE()
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 113595 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2017-08-10 23:57 UTC by Michael
Modified: 2020-01-06 17:11 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
SampleSTYLE.ods, sample ODS explaining the IFS bug (9.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-08-10 23:58 UTC, Michael
Details
Variant in position of CURRENT() in the IFS (10.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-08-11 17:39 UTC, Regina Henschel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Michael 2017-08-10 23:57:01 UTC
Description:
Using Function IFS and CURRENT within Test expressions leads to not comprehensible results.

Target/background: A cell (referencing whatever, e.g. COUNTIF etc.) should have a STYLE() applied, depending on another cell's value, e.g. a treshold in a real life example

Works with nested if, does NOT WORK with IFS. Example uploaded.

Steps to Reproduce:
All in uploaded "SampleSTYLE.ods":

Both ref value B2 and Treshold value B3 are the same, e.g. 4


Actual Results:  
(B5) =B2+STYLE(IFS(CURRENT()<B3;"Grün";CURRENT()=B3;"Gelb";CURRENT()>B3;"Rot")) 
leads to 4 with "Rot" = RED, wrong as CURRENT()=B3 is TRUE!

(B6) =B2+STYLE(IF(CURRENT()<B3;"Grün";IF(CURRENT()=B3;"Gelb";IF(CURRENT()>B3;"Rot"))))
leads to 4 with "Gelb" = YELLOW, correct.

Expected Results:
IFS should be able to work in combination with CURRENT


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Comment 1 Michael 2017-08-10 23:58:33 UTC
Created attachment 135441 [details]
SampleSTYLE.ods, sample ODS explaining the IFS bug
Comment 2 Michael 2017-08-10 23:59:26 UTC
LO calc version is officially shipped version with OpenSuse Leap 42.3
Comment 3 Michael 2017-08-11 00:02:39 UTC
Asked this question before opening this bug: https://ask.libreoffice.org/en/question/120323/calc-bug-inbecause-of-function-style-german-vorlage/
Comment 4 m.a.riosv 2017-08-11 07:46:32 UTC
Please read the help 
https://help.libreoffice.org/Calc/Information_Functions#CURRENT
"This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got)."
What should be for you the value of CURRENT() in any place.
Comment 5 Michael 2017-08-11 16:07:17 UTC
I've read the help and I'm not sure if I understand your remark. 

In my example ODS both B5 and B6 are getting the same value B2 + STYLE(...), so in fact B2. Only difference is, that the same conditions 

CURRENT()<B3,
CURRENT()=B3 and
CURRENT()>B3

are resolved differently if they're resolved for one IFS() call or nested IF() calls.

not sure if I've got your comment "What should be for you the value of CURRENT() in any place." - Isn't "any place" both IFS and nested IF?
Comment 6 Regina Henschel 2017-08-11 17:39:35 UTC
Created attachment 135474 [details]
Variant in position of CURRENT() in the IFS

I expect, that the result of CURRENT() is only changed, after the IFS was completely evaluated. It seems, that CURRENT() gets an internal values of the condition, that was not met.
Comment 7 TBeholder 2017-11-02 08:39:21 UTC
Same here, in Version: 5.4.1.2 

----
=0+IFS( […],CURRENT(),[…],CURRENT(),[…],CURRENT(), CURRENT()=1,"[yes!]",[…],CURRENT())
----
shows 1 no matter how the pairs are shuffled, unless you put "CURRENT()=1" first, in which case it's "Err:518"

Conditions using CURRENT() don't work properly... and if the comparison gets to the pair 
----
…CURRENT()>2,CURRENT()&">2"…
----
it returns "1>2", so it changes.
IFS tries to return TRUE unless some type is given to it:
----
=IFS(0=0,CURRENT(), […]
----
Thus most likely CURRENT() is set to TRUE (which turns into 1 if used in a number type formula) when the condition is met.

Inside the chosen branch it resets (!):
----
="before"&IFS(0=0,CURRENT()&"z", […]
="before"&IFS(0=0,"z"&CURRENT(), […]
----
the former returns "before1z", the latter "beforezz".

Also, if no condition is met, the returned default value is "N/A"? Not sure it's a good idea.

Condition "ISNA(CURRENT())" doesn't catch anything either, BTW.
Comment 8 Eike Rathke 2018-03-09 21:31:29 UTC
Well, what do you expect? CURRENT() returns the current internal value at the position of the evaluation chain where it occurs. When used multiple times within  arguments to the same function the result depends on the order of evaluation. IFS() evaluates conditions until it finds a matching one, sprinkling CURRENT() all over makes it depend on internal behaviour what arguments will be evaluated first, which currently may be all before IFS() is called and in future may be only the ones until one is matched.

CURRENT()>2,CURRENT()&">2"
depends on whether the parameters are evaluated from left to right (usual but not necessarily) or from right to left. If from left to right then the second CURRENT() returns the result of the comparison CURRENT()>2, whatever CURRENT() might had been there, so 0 or 1.

Also this
="before"&IFS(0=0,CURRENT()&"z", […]
="before"&IFS(0=0,"z"&CURRENT(), […]
is logical behaviour. In the first example CURRENT() returns the result of the condition 0=0, which is 1. In the second example it returns the "z" that was just pushed as operand for the & operator.

Using CURRENT() multiple times at the same function level within one expression means relying on internal implementation details. One should not. It may change without notice.

=ISNA(CURRENT()) returns FALSE because CURRENT() as first to be evaluated function does not return NA(), it's Err:518, =ISERROR(CURRENT()) returns TRUE.
Comment 9 Michael 2018-03-10 01:54:18 UTC
(In reply to Eike Rathke from comment #8)
> IFS() evaluates conditions until it finds a matching one,
> sprinkling CURRENT() all over makes it depend on internal behaviour what
> arguments will be evaluated first, which currently may be all before IFS()
> is called and in future may be only the ones until one is matched.
> 

As I opened this bug, I would be thankful if this bug is not going to be closed because someone else's examples or additions might be misleading or independent of my bug. 

If you look on my example attached (https://bugs.documentfoundation.org/attachment.cgi?id=135441), the IFS formula in B5 is: 

=B2+STYLE(IFS(CURRENT()<B3;"Grün";CURRENT()=B3;"Gelb";CURRENT()>B3;"Rot"))

And my question is: For B3 constantly being e.g. "4", how could a value of "4" for B2 lead to the condition "CURRENT()>B3" being selected by IFS() ??? 

--> As ONLY the condition "CURRENT()=B3" is applicable, IMHO it does not have any impact if IFS() evaluates sub-formulas from left to right, or vice versa.

In the attached example LO shows a value of "4" for B5, so for whatever reason CURRENT might return any other value than "4", and as B3 is also "4", how could ever the condition "CURRENT()>B3" evaluated as true ??
Comment 10 Eike Rathke 2018-03-12 11:15:25 UTC
I'm repeating myself:
"IFS() evaluates conditions until it finds a matching one, sprinkling CURRENT() all over makes it depend on internal behaviour what arguments will be evaluated first, which currently may be all before IFS() is called and in future may be only the ones until one is matched."
and
"Using CURRENT() multiple times at the same function level within one expression means relying on internal implementation details. One should not. It may change without notice."

> =B2+STYLE(IFS(CURRENT()<B3;"Grün";CURRENT()=B3;"Gelb";CURRENT()>B3;"Rot"))
> 
> And my question is: For B3 constantly being e.g. "4", how could a value of
> "4" for B2 lead to the condition "CURRENT()>B3" being selected by IFS() ??? 
Because here the state of CURRENT is not what you think it is. For the first CURRENT()<B3 it is B2 that was previously pushed to the stack, for CURRENT()=B3 it is "Grün" that was just pushed on the stack, for CURRENT()>B3 it is "Gelb" that was just pushed on the stack, and a string is greater than a numeric value.

Anyway, I'll keep this and close bug 113595 instead, they have the same reason.

IFS() and SWITCH() currently evaluate all parameters' arguments in order encountered from left to right for IFS() and right to left for SWITCH() and return the result matching for the condition(s). They do not short-cut evaluation like IF() and CHOOSE() do. Hence for a function that has side effects like STYLE() all styles are applied and the last one evaluated wins.
Comment 11 Eike Rathke 2018-03-12 11:15:46 UTC
*** Bug 113595 has been marked as a duplicate of this bug. ***
Comment 12 QA Administrators 2019-03-13 03:46:19 UTC Comment hidden (obsolete)
Comment 13 So 2019-12-23 02:17:31 UTC
Still in 6.4.0 RC1. Both
=IFS(0,1/0,1,TRUE())
=SWITCH(1,0,1/0,1,TRUE())
return #DIV/0!
Comment 14 b. 2019-12-28 01:29:20 UTC
@So: u're right, bug, evaluating things that shouldn't ... 
while that's wrong OP question not solveable ...