Bug 121105 - the current() function is confused if used inside or()
Summary: the current() function is confused if used inside or()
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-11-01 20:48 UTC by Jiří Boháč
Modified: 2019-02-26 10:54 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
reproducer (7.82 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-11-01 20:51 UTC, Jiří Boháč
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jiří Boháč 2018-11-01 20:48:39 UTC
Description:
The CURRENT() function seems to be broken if used twice in a formula.
Say I want to use STYLE() to apply a "red" style for numbers between 3 and 4, and "green" otherwise:

=A1+STYLE(IF(OR(CURRENT() > 4, CURRENT() <3),"green","red"))

I always get the "green" style.

I will attach a test spreadsheet.
Reproducible on 6.0.4.2 in openSUSE and 5.2.7.2 in Debian.

Steps to Reproduce:
1. open calc
2. in B2 enter =A1+STYLE(IF(OR(CURRENT() > 4, CURRENT() <3),"green","red"))
3. copy B2 to B2..B5
4. enter 1 2 3 4 5 in A1 A5 A3 A4 A5
5. define a "red" style with a red background and a "green" style with a green background


Actual Results:
B1..B5 are all green

Expected Results:
B3 and B4 should be red


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Jiří Boháč 2018-11-01 20:51:59 UTC
Created attachment 146236 [details]
reproducer

Spreadsheet created as described in the initial description.
All cells in column B are green.
A similar failing formula is in column C; instead of using STYLE() the formula should differentiate between values and add 0.1 or 0.2. It always adds 0.1, which is wrong.
Comment 2 Jiří Boháč 2018-11-01 20:56:33 UTC
hmm, the bug description was wrong; I reproduced the bug with a modified formula:
=A1+STYLE(IF(OR(FALSE(), CURRENT() <3),"green","red"))

It seems to be the CURRENT() inside OR() which is broken.
The above formula should make 1 and 2 "green" and the rest "red"
Everything is green.
Comment 3 Xavier Van Wijmeersch 2018-11-01 21:43:59 UTC
=A1+STYLE(IF(CURRENT()<>3,"green","red"))

try these one
Comment 4 Jiří Boháč 2018-11-01 22:07:32 UTC
(In reply to Xavier Van Wijmeersch from comment #3)
> try these one

Thanks for the tip, but my actual use case was something much more complicated, the above formulas are just minimalist reproducers created to help get the bug fixed.

I actually worked around the problem by replacing IF(OR(a,b),x,y) with this nested IF(a,x,IF(b,x,y)). Both conditions, a and b, use CURRENT() and it works. So it seems it's really the combination of OR() and CURRENT() that is broken.
Comment 5 m.a.riosv 2018-11-01 22:25:11 UTC
What seems a in issue I think it's not, in
=A1+STYLE(IF(OR(CURRENT() > 4, CURRENT() <3),"green","red"))
the second CURRENT() receive their value from the first CURRENT() comparison.

A formula like
=A1+STYLE(IF(O(CURRENT() > 4; CURRENT());"green";"red"))
gives red except for 5.
Comment 6 himajin100000 2018-11-02 03:19:19 UTC
I believe m.a.riosv's explanation on comment 5 is correct. This is not a bug.
Comment 7 Jiří Boháč 2018-11-02 06:52:40 UTC
I'm sorry, but it clearly *is* a bug.

See Comment #4.

If "CURRENT() < 3" gives a different result than OR(FALSE(); CURRENT() < "3") it clearly is a bug.

I think the truly minimal reproducer is in fact this single formula:
=5+OR(FALSE(), CURRENT()<3)
It returns 6 and should return 5.
Comment 8 m.a.riosv 2018-11-02 11:09:24 UTC
=5+OR(3;CURRENT())=6
=5+OR(1;1)=5+1=6

=5+OR(0;CURRENT())=5
=5+OR(0;0)=5+0=5

=5+OR(CURRENT()<3)+CURRENT()=10
=5+       0       +   5     =10

=5+OR(CURRENT()>3)+CURRENT()=12
=5+       1´      +   6     =12

CURRENT() takes the value inside the function, what looks fine at that step, until the calculation of the function is finished.

At this time change their behavior it's not an option.

VLOOKUP with an inner array I think can do it.
=A1+STYLE(VLOOKUP(CURRENT();{0|"green";3|"red";4|"green"};2;1))
Change ; and | for your array row and column separator in Menu/Tools/Options/LibreOffice calc/Formula

Please, if you are not agree reopen it again.
Comment 9 Jiří Boháč 2018-11-02 11:44:25 UTC
(In reply to m.a.riosv from comment #8)
> =5+OR(3;CURRENT())=6
> =5+OR(1;1)=5+1=6

this is correct

> =5+OR(0;CURRENT())=5
> =5+OR(0;0)=5+0=5

yes, this is the bug. There cannot be any sane explanation for this:

=5+OR(0;CURRENT()) = 5
=5+OR(CURRENT(),0) = 6

> CURRENT() takes the value inside the function, what looks fine at that step,
> until the calculation of the function is finished.

Seem CURRENT() in fact returns the value of the first function argument:

=5+POWER(3,CURRENT()) = 5+3^3 = 5+27 = 32
=5+POWER(CURRENT(), 3) = 5 + 5^3  = 130

Parentheses also totally screw it up:

=5+POWER((CURRENT()+1), 3) = 5+(5+1)^3 = 221 (correct)
=5+POWER((1+CURRENT()), 3) = 5+(1+1)^3 = 13 (WTF!)
Comment 10 Xisco Faulí 2018-11-05 12:16:39 UTC
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone
else confirms it.
Comment 11 Eike Rathke 2019-02-26 10:28:53 UTC
(In reply to Jiří Boháč from comment #9)
> yes, this is the bug. There cannot be any sane explanation for this:
> 
> =5+OR(0;CURRENT()) = 5
> =5+OR(CURRENT(),0) = 6
That is completely logical.
In the first case, CURRENT() evaluates to 0 because the 0 was pushed last onto the evaluation stack and OR(0,0) yields 0.
In the second case, CURRENT() evaluates to 5 because the 5 was pushed last onto the evaluation stack and OR(5,0) yields 1.
Comment 12 Eike Rathke 2019-02-26 10:54:36 UTC
Just for explanation of the original description case:
=A1+STYLE(IF(OR(CURRENT() > 4, CURRENT() <3),"green","red"))
The first CURRENT() is the value of A1 pushed last onto the evaluation stack, the second CURRENT() is the result of the comparison CURRENT() > 4 hence A1 > 4 which yields either 1 or 0 and as such is always <3 therefore the "green" style is always applied.

As a general advice, do not use the STYLE() function but conditional formatting instead.