Bug 90433 - HYPERLINK() function, if present but not evaluated, wrongly forces cell to pretend being linked.
Summary: HYPERLINK() function, if present but not evaluated, wrongly forces cell to pr...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: low minor
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2015-04-03 12:55 UTC by Wolfgang Jäger
Modified: 2018-07-11 16:51 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Demonstrations (12.71 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-04-03 12:55 UTC, Wolfgang Jäger

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Jäger 2015-04-03 12:55:22 UTC
Created attachment 114583 [details]

A cell containing a formula including a syntactically roughly correct HYPERLINK expression will be forced to pretend being linked regardless of whether or not the HYPERLINK expression was evaluated under the given conditions. Whatever result was produced (if not an error) by the formula will be taken as the linked URL.
This even holds if the result is numeric. 

V3.6.5.2 is the oldest version I tested with.
The behaviour is unchanged in V4.4.2.2.

This is NOT the same bug as the following similar one. It is, however, related, of course.
A similar bug report was made here: https://bugs.documentfoundation.org/show_bug.cgi?id=75820 . 
It was set there to status "RESOLVED NOTOURBUG". (I want to object; see attached.)
Comment 1 m.a.riosv 2015-04-03 15:07:18 UTC
Hi @Walfgang, thanks for reporting.

I think this issue is part of the https://bugs.documentfoundation.org/show_bug.cgi?id=89633

Closed as dup, please if you are not agree reopen it.

*** This bug has been marked as a duplicate of bug 89633 ***
Comment 2 Wolfgang Jäger 2015-04-03 15:48:02 UTC
Hello @m.a.riosv, sorry! I cannot find a clear relation between this bug report and the one you suspected it to be a duplicate of. There was no import from Excel in my case e.g. I came about the problem trying to find a workaround for the requester of this forum thread: https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=76361 That one was with respect to OpenOffice 4.1 but as I tested we have the same behaviour in both branches and unchanged for a long time now. I only mentioned Excel because another bug (https://bugs.documentfoundation.org/show_bug.cgi?id=75820) was - in my opinion wrongly - closed assuming the behaviour complained about should be as intended with respect to Excel compatibility. My demonstrations therefore try to also show that this cannot be the true background. Comment on my demonstrations if not convinced, please.
Comment 3 m.a.riosv 2015-04-03 18:14:38 UTC
As you like, but in any case is not proper the reporter set up the status as NEW, that must be done for other person confirming the bug.
Comment 4 Joel Madero 2015-10-17 17:04:32 UTC
Version field is older affected. Updated per description.

As for the bug itself - let me say a couple things:

1. So sorry for the long delay, QA is working tirelessly trying to triage these issues but with <50 people on the team and tens of millions of users...we can fall behind :)

2. I have to throw this into NEEDINFO - the demonstration document is really really hard to follow. I suspect part of the reason why no one has touched this bug in almost 7 months is because the demonstration document is nearly impossible to tell what's going on. 

Here's what you can do to help:

Please provide a VERY SIMPLE test document that ONLY demonstrates the issue (get rid of all extraneous issues);
Provide clear enumerated (1. Do x, 2. do y, 3. do z) steps on how to reproduce.

Then set the bug back to UNCONFIRMED and I will try my best to reproduce the issue.

Looking at the bug it seems like a minor issue so setting to:
Minor - won't prevent high quality work;
Low - default for minor issues.
Comment 5 Wolfgang Jäger 2015-10-17 20:08:48 UTC
I will try again:

1. What is wrong? See subject!
2. Steps to reproduce the bug:
- Create a new Calc doument.
- Go to B3 and Enter there (B3 refers to the respecive cell in the demo):
=IF(1>0;"No Link Here!";HYPERLINK("http://libreoffice.org";"Linked Now!"))
3. Expected Result (since 1>0 is TRUE):
- Cell will show "No Link Here!" with no URL being assigned.
- MouseOver the text or Ctrl+MouseOver will not offer to go to an URL. 
4. Factual Result 
4.a Setting. 'Ctrl-click required...' TICKED:
- MouseOver triggers the hint "Ctrl-Click to follow:No Link Here!"
4.a Setting. 'Ctrl-click required...' NOT TICKED:
- MouseOver triggers the hint "click to open hyperlink:No Link Here!"
4.c Both cases
- Click or Ctrl-Click respectivley brings up the error message: 
"No Link Here!" is not an absolute URL that can be passed to an external application to open it.

5. Analogous, demontsrated in cell B4:
- CHOOSE with at least one of the numbered result parameters containing a syntactically accepatbel HYPERLINK expression will show the analogous bug if a result was selected by the first parameter NOT returned by HYPERLINK.

6. Even a deeply wraped-in usage of the HYPERLINK function NOT EVALUATED because of the actual conditions will make the cell wrongly "linked" to a non existing URL. See cell B6 of the demo.

7. Evaluating to an error value (and only this) will break the wrong link.
- Demonstrated in cells B15, B16 of the demo sheet.

8 Special oddity (with a sophisticated example):
- See cell B12 of the attached sheet where the correct result 11 is shown.
- The hint given is "Ctrl-Click to follow link:22" Yes, 22!

I demontsrted the issue as best I could with the attached Calc doc. It may look a bit to colourful to someone. 
Of course, the bug does not block real work, but it brings to the surface that there is something wrong, possibly with the tokenising of expressions containing HYPERLINK but only having to evaluate it under conditions.

It is really hard to report a bug here.

Hi Joel! head up! I read your "Reality Essay" and I can feel with you.
Comment 6 Wolfgang Jäger 2015-10-17 20:25:39 UTC
Hoping this it not confusing again, an additional remark:

The first formula given in my Comment #5 above, the constant condition replaced by a reference now, is (it may again be placed in B3):

=IF(A3;"No Link Here!";HYPERLINK("http://libreoffice.org";"Linked Now!"))

The results and action are, however, as if the formula was:

=HYPERLINK(IF(A3;"No Link Here!";"http://libreoffice.org");IF(A3;"No Link Here!";"Linked Now!"))
Comment 7 Joel Madero 2015-10-18 18:05:38 UTC
Great repro steps in Comment 5 - thanks for that!

I can now confirm:

Tested on:
Ubuntu 15.04 x64
LibreOffice 3.3 (inherited from OOo)
LibreOffice 5.1 (current master)

Both show the same issue - updating version which is the oldest version demonstrating the issue.

Marking as:
Minor - will slow down professional quality work but will not prevent it;
Low - default seems fine here, no regression, no proof of widespread user impact
Comment 8 QA Administrators 2016-11-08 11:28:49 UTC Comment hidden (obsolete)
Comment 9 Eike Rathke 2018-07-11 12:52:33 UTC
The behaviour forcing a hyperlink to all results no matter whether the expression was actually executed is odd, but that's how Excel does it and how it was explicitly specified for OpenOffice.org already as well, see https://www.openoffice.org/specs/calc/compatibility/HyperLinkFunction.sxw

Won't fix.
Comment 10 Wolfgang Jäger 2018-07-11 16:51:27 UTC
(In reply to Eike Rathke from comment #9)
> The behaviour forcing a hyperlink to all results no matter whether the
> expression was actually executed is odd, but that's how Excel does it and
> how it was explicitly specified for OpenOffice.org already as well, see
> https://www.openoffice.org/specs/calc/compatibility/HyperLinkFunction.sxw
> Won't fix.

Sorry. I can accept this in the sense of not insisting on reopening the bug. 
I can NOT accept it in the sense of apprehending the argumentation.

Excel (What about recent versions?) may be a nightmare even more than I expected. To nonetheless regarding it a standard on the level of an actual specification based on a document more than 14 years old (of 2004-04-16) is absurd.  

We had the case about TEXTJOIN() a while ago where you and others claimed an unacceptable behaviour of Excel and praised it nonetheless a "standard" without actally knowing how Excel behaved in reality. Finally you fixed the bug. 

I will not get any influence on the document foundation concerning the lines of development. 

I WILL remain the one to decide if I continue wasting time with my most likely hopeless attempts to sopport open and free software by offering help in formus and by reporting bugs or hinting an algorithm. 

Lots of points against already on the account.