Bug 138393 - Calc automatically evaluates arguments of if() function
Summary: Calc automatically evaluates arguments of if() function
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-11-21 16:34 UTC by [REDACTED]
Modified: 2021-01-06 01:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Calc file with bug demonstration (19.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-11-30 15:24 UTC, [REDACTED]
IF() with LOGGER() function (8.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-01-06 01:31 UTC, Eike Rathke

Note You need to log in before you can comment on or make changes to this bug.
Description [REDACTED] 2020-11-21 16:34:03 UTC
The if() function evaluates all its arguments, regardless of the value of the first argument. This is bad.

An example of badness is if(A1,webservice(),"-"). If A1 is false, the URL should not be accessed. URL sites can and often changes state on access.

Fix: The second two arguments of if() should only be evaluated depending on the state of the first argument, rather than evaluating both and then choosing.
Comment 1 Ming Hua 2020-11-21 18:48:13 UTC
(In reply to libreoffice from comment #0)
> The if() function evaluates all its arguments, regardless of the value of
> the first argument.
Are you sure?

A simple test with formula "=IF(0, 1/0, "Good")" shows that the "1/0" argument is not evaluated, otherwise it would give a "#DIV/0!" error.  Also from my understanding (see bug 111675) Calc's IF() shortcuts the evaluation of arguments, but IFS() doesn't.

So if IF() function is evaluating both second and third argument for you, please provide a sample document for testing.
Comment 2 [REDACTED] 2020-11-23 02:01:57 UTC
After carefully constructing a test case, I was unable to reproduce what I thought was happening. Change to invalid, I guess.
Comment 3 [REDACTED] 2020-11-29 17:34:48 UTC
Here is where I see this potential bug:

The website Alpha Vantage (alphavantage.co) allows five stock quotes per minute.
I have a spreadsheet with several instances of if(<VAR>,webservice("http://alphavantage.co/....","no-quote-yet") and the <VAR> cells set to zero. As soon as I turn on "Allow Updating", the five Alpha Vantage quotes are consumed. This means that turning on "Allow Updating" evaluated the second "webservice()" argument, regardless of the value of the first argument.
Comment 4 [REDACTED] 2020-11-29 17:41:11 UTC

If <VAR> is zero when the spreadsheet is opened, then the second argument is not evaluated when "Allow Updating" is turned on. However, is <VAR> is 1 when the spreadsheet is opened, then set to zero before "Allow Updating", then the second argument is evaluated.
Comment 5 Xisco Faulí 2020-11-30 12:29:45 UTC
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Comment 6 [REDACTED] 2020-11-30 15:24:54 UTC
Created attachment 167685 [details]
Calc file with bug demonstration
Comment 7 QA Administrators 2020-12-01 03:53:46 UTC Comment hidden (obsolete)
Comment 8 Eike Rathke 2021-01-06 01:31:34 UTC
Created attachment 168714 [details]
IF() with LOGGER() function

The claim is simply not true, the paths in IF() are not evaluated unconditionally.

Attached is a document with a BASIC macro function LOGGER() that increments a static variable each call and returns the value. In cell A1 is a formula
where if B1 is 0 or empty LOGGER() is not called and "no" is returned, otherwise (numeric B1 <> 0) LOGGER() is called and the overall call count is returned by IF(). With B1 0 or empty hit F9 a few times on cell A1 or reinput 0 in B1 repeatedly, then enter 1 in B1 and see A1 is incremented by just one of the previous value; toggle and repeat.

However, a call to WEBSERVICE() accesses the URI more than once, for example once with an OPTIONS request, then with a HEAD request and finally the GET request. If Alphavantage counts each request against your quota and not only the GET request there's nothing we can do about that.