Bug 121398 - Powers of negative numbers highly inconsistent
Summary: Powers of negative numbers highly inconsistent
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
Reported: 2018-11-13 16:55 UTC by mwelinder
Modified: 2022-01-13 23:16 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:

example excel 2016 (9.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-13 17:19 UTC, Oliver Brinzing

Note You need to log in before you can comment on or make changes to this bug.
Description mwelinder 2018-11-13 16:55:03 UTC
(-1)^(1/3)   --> -1
(-1)^(2/3)   --> Error
(-1)^(1/5)   --> -1
(-1)^(3/5)   --> Error
(-1)^(6004799503160661/2^54) --> -1

From a purely mathematical (ie., using the mathematical concept of Reals) the correct results are -1, +1, -1, -1, and Error.

From a floating-point view (i.e, using ieee-854 base-2 floating-point numbers, round-to-nearest and operations in the order dictated by parentheses) the correct results are Error, Error, Error, Error, and Error.

In other words, the results are inconsistent with both mathematical and floating-point views.  Looking at the code, this comes from a na\"ive attempt at identifying certain floating-point numbers as odd-denominator fractions.

Suggestion: implement Gnumeric's 3-argument POWER, e.g. POWER(-1,1,3).  That avoids trying to interpret floating-point numbers as fractions, a highly error-prone business.

Steps to Reproduce:
Enter this into A1:A5


Actual Results:
Actual: -1, #NUM!, -1, #NUM!, and -1.

Expected Results:
Expected: 5 x #NUM!
Expected: -1, 1, -1, -1, #NUM!

Reproducible: Always

User Profile Reset: No

Additional Info:
The constants in (-1)^(6004799503160661/2^54) are carefully chosen such that there is no floating-point rounding errors at any point in the calculation.  The exponent is what you get from 1/3 after rounding to nearest double.

The code is at https://docs.libreoffice.org/sc/html/interpr5_8cxx_source.html near line 1588.  As an aside, the static_cast<int> is undefined behaviour for cases like (-1)^(2^-64).
Comment 1 Oliver Brinzing 2018-11-13 17:18:04 UTC
confirming with 

Version: (x64)
Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: 

but excel 2016 calculates same...
Comment 2 Oliver Brinzing 2018-11-13 17:19:02 UTC
Created attachment 146601 [details]
example excel 2016
Comment 3 Julien Nabet 2018-11-13 19:18:46 UTC

*** This bug has been marked as a duplicate of bug 44076 ***
Comment 4 Mike Kaganski 2018-11-15 14:55:46 UTC
This is not a duplicate of bug 44076. The latter was the same as bug 69293, while this one is about inconsistent result of fixing them both.
Comment 5 mwelinder 2018-12-26 13:19:10 UTC
Unmarking duplicate.

44076 was about odd roots only and was just closed based on evaluating
(-8)^(1/3) as -2.  That ignores the whole inconsistency raised here.
Comment 6 Xisco Faulí 2019-06-26 08:36:23 UTC
A new major release of LibreOffice is available since this bug was reported.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 7 QA Administrators 2019-12-24 03:25:53 UTC Comment hidden (obsolete)
Comment 8 b. 2019-12-30 10:19:12 UTC
for the bug hunting session: 

still behaviour as mentioned in OP with below ver., 

regardless which file format (ods, xls, xlsx), resistent against F9 and ctrl-shift-F9, indpendent on recalc on load or not, unaffected by threaded or CL ... 

just a math-error, sorry, inconsistency, not anything in handling, 

Version: (x64)
Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: CL
Comment 9 Jean-Baptiste Faure 2021-08-12 15:23:09 UTC
Expected should be 5 x #NUM!

See https://bugs.documentfoundation.org/show_bug.cgi?id=44076#c21
and https://bugs.documentfoundation.org/show_bug.cgi?id=69293#c3

For me the correct solution is to restore initial behavior of a^b when a < 0.

Best regards. JBF
Comment 10 Roberto 2022-01-13 21:00:20 UTC
I agree with a revision of code.

Another bug is:

=0^0  -->  1

but really must be an error.

With the same formula Ecxel give an error..
I know many calc apps give 1 as result but not all!
Comment 11 Jean-Baptiste Faure 2022-01-13 23:16:52 UTC
(In reply to Roberto from comment #10)
> [...]
> =0^0  -->  1
> but really must be an error.

0^0 = 1 by definition. So not a bug.

> With the same formula Ecxel give an error..
> I know many calc apps give 1 as result but not all!

It is not necessary to mimic all MS-Excel's bugs!

Best regards. JBF