Bug 44076 - =(-8)^(1/3) should return -2
Summary: =(-8)^(1/3) should return -2
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0 target:7.1.0
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2011-12-22 13:55 UTC by Björn Michaelsen
Modified: 2020-07-25 16:38 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Björn Michaelsen 2011-12-22 13:55:53 UTC
1) lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.3-1ubuntu2
  Candidate: 1:3.3.3-1ubuntu2
  Version table:
 *** 1:3.3.3-1ubuntu2 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages
        100 /var/lib/dpkg/status
     1:3.3.2-1ubuntu4 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages

3) What is expected to happen is when one types in a cell:

=(-8)^(1/3)

one gets -2 as the answer.

4) What happens instead is one receives #VALUE! as the answer.

WORKAROUND: Use Excel in WINE.

Microsoft Office Excel 2003 (11.5612.6505)

apt-cache policy wine1.3
wine1.3:
  Installed: 1.3.28-0ubuntu1~ppa1~natty1
  Candidate: 1.3.28-0ubuntu1~ppa1~natty1
  Version table:
 *** 1.3.28-0ubuntu1~ppa1~natty1 0
        500 http://ppa.launchpad.net/ubuntu-wine/ppa/ubuntu/ natty/main i386 Packages
        100 /var/lib/dpkg/status
     1.3.15-0ubuntu5 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages
Comment 1 Markus Mohrhard 2011-12-22 18:10:51 UTC
Setting this to enhancement. We need to write our own Power function for this. At the moment we are using the c++ standard library functionn which results in an error for negative base and a non integral exponent.

Code pointers:

http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr5.cxx#1687
Comment 2 Markus Mohrhard 2011-12-23 03:36:05 UTC
Hm after an additional thought I'm not sure if it is really that simple to implement. At the point where we calculate the power we no longer have the information that we had a fraction as exponent.

I wonder how excel solves this problem. I suspect that we would have the same problem with ixion.
Comment 3 Regina Henschel 2011-12-29 12:26:10 UTC
The problem has been discussed in OOo too; https://issues.apache.org/ooo/show_bug.cgi?id=37129

If you will keep rule a^(r*s) = (a^r)^s you cannot allow negative basis a.

Excel does not allow arbitrary fractions but tests only for unit fraction. That is not difficult. The highest such converted number is 1/(2^31-3) in Excel; 1/(2^31-1) fails. 

So the question is, whether LO should implement Excels behavior for easier converting from Excel. ODF1.2 says "POWER(a,b), where a<=0 and INT(b)!=b, is implementation-defined." in chapter 6.16.46. So the standard allows such solution.

If you want to use arbitrary fractions, the class TOOLS_DLLPUBLIC Fraction from fract.hxx in tools might be useful.
Comment 4 Christopher M. Penalver 2011-12-31 15:35:48 UTC
WORKAROUND: SIGN(X)*ABS(X)^(1/3)
Comment 5 Markus Mohrhard 2012-06-18 09:09:31 UTC
*** Bug 50596 has been marked as a duplicate of this bug. ***
Comment 6 Eike Rathke 2015-09-28 11:46:38 UTC
*** Bug 94568 has been marked as a duplicate of this bug. ***
Comment 7 Julien Nabet 2018-11-13 19:18:46 UTC
*** Bug 121398 has been marked as a duplicate of this bug. ***
Comment 8 Xavier Van Wijmeersch 2018-11-14 09:26:16 UTC
tested with 

Version: 6.1.4.0.0+
Build ID: 17c87566e84ac433645f264a9bee4cb5ddbd23a5
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded

Version: 6.2.0.0.alpha1+
Build ID: eb9a223151a00d11ed89a42465663b6e5cd75bd9
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); UI-Language: en-US
Calc: threaded

"What is expected to happen is when one types in a cell: =(-8)^(1/3)
one gets -2 as the answer."

i have the correct answer, no error message
Comment 9 mwelinder 2018-11-14 14:35:43 UTC
If you have (-8)^(1/3) --> -2
then you also have (-8)^(6004799503160661/2^54) --> -2

And that's clearly wrong.

Note: there are no rounding errors in evaluating 6004799503160661/2^54.
Comment 10 Eike Rathke 2018-11-15 14:37:20 UTC
Fwiw, this was implemented with https://gerrit.libreoffice.org/plugins/gitiles/core/+/b0fdf6cf11ee46d46925e9cd9fa2768a1b49bb64%5E!/ for bug 69293.

The same result -2 for (-8)^(6004799503160661/2^54)
There may be no rounding error but an accuracy limit here, see
https://www.wolframalpha.com/input/?i=6004799503160661%2F2%5E54

Perhaps reformulating (or even omitting?) the rtl::math::approxEqual() would be an option? Didn't dig into it.
Comment 11 mwelinder 2018-11-15 15:05:48 UTC
No accuracy problem either.  You can enter 6004799503160661 in A1 and widen
the column enough that you can see the whole, odd number.  You can then add or
subtract 1 and see ...662 and ...660 as expected.  Scaling by a power of 2 doesn't change that for "double" which is a base-2 representation.


x=6004799503160661/2^54 didn't come out of thin air.  It is what you get if you
round 1/3 to the nearest ieee-854 double number.  Once you have x, you have no
way of knowing whether it came from 1/3 [plus rounding error] or from
6004799503160661/2^54.

Note, that the code you linked to exhibits undefined behaviour if the cast to
"int" overflows.  That will happen for, for example, (-1)^(2^-64).  IIRC, sparc
and x86 produce different results for such a cast.
Comment 12 Christopher M. Penalver 2018-12-23 22:39:53 UTC
Björn Michaelsen, confirmed result is now -2 in:
Version: 6.1.3.2 (x64)
Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb
CPU threads: 8; OS: Windows 10.0; UI render: default; 
Locale: en-US (en_US); Calc: group threaded
Comment 13 Commit Notification 2019-01-10 10:56:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/559758a35216c0cb852de65d129154947a4d91e8%5E%21

Related: tdf#44076 do not leave cast to int to undefined behaviour

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2019-01-11 00:09:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/f4844297c9cdbbf341e3e7d5e2e0c0c18cb40553%5E%21

Related: tdf#44076 use sc::power() in ScMatrix::PowOp()

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2020-06-28 19:32:01 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/f07a56498875e67fa2637fd0a73890629024efa8

tdf#44076: sc: Add unittest

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 b. 2020-07-25 16:38:28 UTC
here is still some air upward, while '=(-8)^(1/3)^(2)' results in 4, just like '=(-8)^(2)^(1/3)', '=(-8)^(2/3)' produces #NUM! error, at least in ver. 

Version: 7.1.0.0.alpha0+ (x64)
Build ID: 0d45380c99c7200075d01860a2315d0ddb450f1c
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: 

reopen? new bug? leave for future generations? or am i barking up the wrong tree? 

btw. the workaroung from @Christopher M. Penalver doesn't hold for this case, at least not with above mentioned ver. 

@Mike Kaganski has worked out (some of) the logical limitations of such transformations there: https://bugs.documentfoundation.org/show_bug.cgi?id=69293

the annotation 'brackets specify the order' imho doesn't hold, my memories of school math say that there are! rules to deal with and legally change calculation ordering, that way the three terms from the first sentence are equivalent and should give identical results? i have to think about multiple or losing roots or getting additional ones, but at least one result would be nice ...  

@mwelinder: imho calc stops using exact representations at 2^53-1, already 2^53 isn't exact, and calc does 'more aggresive' rounding than neccessary (down to 14 instead of 15,95 *lol* significant digits), and at 2^54 it's in a range where it doesn't care about 34 less or 66 plus, thus all it's producing consists of approximations and assumptions which approximation best fits user expectations ... 

i'm in doubt if calc uses real integers anywhere - which could hold up to 2^64 as you pointed out - but uses floats as standard and integer precision is limited to the range where fragment and mantissa balance each other out, 

given that it would be as legal to calculate -2 for your sample as it's to calculate 2^54 minus 34 to the same 1,8014398509482E+016 representation as 2^54 plus 66, 

i'd read somewhere that internal values and calculations have better precision and the rounding is only for the final result shown in the sheet, couldn't yet test or prove that ...