Bug 37271 - -A1^2 is interpreted as (-A1)^2 instead of -(A1^2), violating the order of operations as defined in mathematics
Summary: -A1^2 is interpreted as (-A1)^2 instead of -(A1^2), violating the order of op...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
: 38824 93983 94568 94580 98248 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-05-16 14:26 UTC by Frédéric Buclin
Modified: 2016-04-06 19:21 UTC (History)
9 users (show)

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Frédéric Buclin 2011-05-16 14:26:45 UTC
The order of operations in mathematics clearly states that exponents take precedence over +, -, *, :, but this order is clearly violated when typing =-A1^2 in a cell as it's interpreted as (-A1)^2 instead of -(A1^2). This can trigger major troubles.
Comment 1 Rainer Bielefeld Retired 2011-05-17 01:49:31 UTC
Imho LibO "intuitively" correctly recognizes the difference between a "Sign of a number" ant the subtraction operator. 

I disagree with reporter's interpretation.

Discussion might become interesting, please see discussion in related OOo issues:

Spreadsheet should not implement all of excels order of precedence for worksheet operators. For example, in Excel "=-X^2 + B" is not equal to "=b - X^2"
 <http://openoffice.org/bugzilla/show_bug.cgi?id=26755>

Mathematical error in Calc, -4^2
<http://openoffice.org/bugzilla/show_bug.cgi?id=24271>

@Frédéric Buclin:
Please provide information concerning public available mathematical sources supporting your thesis.
Comment 2 Frédéric Buclin 2011-05-17 10:14:18 UTC
(In reply to comment #1)
> Please provide information concerning public available mathematical sources
> supporting your thesis.

Oh, I don't need to provide anything. I teach maths and physics, and that's one of the first things we teach to students. 3 correct examples below:

Scientific tools like "gnuplot" and Mathematica:

gnuplot> print -4**2
-16

(same output when executed within Mathematica)

Perl:

perl -E 'say -4**2'
-16

Python:

python -c 'print -4**2'
-16


It's wrong to make a distinction between a "sign of a number" and the subtraction operator. LibreOffice developers are not free to decide how maths should work.
Comment 3 vitriol 2011-05-17 10:23:01 UTC
MS Excel works like OOo/LibO. I think that a change in LibO will break Excel compatibility.
Comment 4 Don't use this account, use tml@iki.fi 2011-05-17 10:33:56 UTC
How "maths work" is surely a much deeper thing than the notational conventions used to express mathematics. Conventions are just that. Excel and LibreOffice apparently happen to use a different lexical and/or syntactic convention here than most other programming languages and conventional notation. Sure, it is a pity. But changing it would be a regression. Just get over it.

Would you also say that HP's scientific calculators with their postfix notation (does HP still make those?) also break "how maths work"?

What about the APL programming language, with its lack of precedence hierarchy, did that also break "how maths works"?
Comment 5 Frédéric Buclin 2011-05-17 10:40:06 UTC
There are mathematical rules which everyone has to obey. -X^2 means -(X^2). Period. Everything/everyone not following these rules is wrong. If that's how your examples behave, then yes, there are wrong.
Comment 6 vitriol 2011-05-17 10:48:43 UTC
@Frédéric Buclin
Changing this convention now means breaking compatibility with previous OOo/LibO documents and with others major spreadsheet software. Do you really want this? I don't think so...
Comment 7 Rainer Bielefeld Retired 2011-05-17 11:07:09 UTC
I wrote such a detailed comment, but Tor said it "to the point". Full ACK, nothing to add.
Comment 8 Frédéric Buclin 2011-05-17 11:15:53 UTC
Being myself also a programmer, I perfectly understand the compatibility problem, and know how hard such problems are to fix. Where I totally disagree is about comments like comment 1 in this bug or http://openoffice.org/bugzilla/show_bug.cgi?id=24271#c1 or http://openoffice.org/bugzilla/show_bug.cgi?id=24271#c27 where the commenters just cannot remember the basic mathematical rules and prefer to blame the reporters to be dumb when they are right. Resolving such bugs as INVALID just demonstrate that the developer in question should stop programming. Resolving the bug as WONTFIX + a comment which explains "sorry, but we are forced to keep the wrong behavior for compatibility with this broken Excel" is much more respectful.

To avoid or at least reduce such trouble, I think it would be great to have the help assistant to pop up when it detects -A1^2 in a cell and warn the user that it will be (incorrectly) interpreted as (-A1)^2.
Comment 9 Björn Michaelsen 2011-05-20 17:04:42 UTC
"NOTABUG" is also valid resolution of this because:
a) The order of precedence as commonly used in math has never been formally standardized. It is just a convention.
b) LibreOffice never stated to conform to the convention. Actually it cant, because the convention was never formalized. If it would, it would need to formalize the convention itself first, which is out of scope for the project.

Assuming that LibreOffice would give a unformalized convention precedence over user expectations (which are formed by spreadsheet applications and an not by scripting languages) is an axiom. And it is wrong. The reasoning behind this can easily be deducted from the mission of the project -- which is the only relevant base here. see: http://www.libreoffice.org/features/
Comment 10 Regina Henschel 2011-06-14 09:58:56 UTC
LibreOffice should follow ODF1.2 standard. And in this standard =-3^2 has to be calculated with the minus as sign and not as operator and therefore results in 9.
But in schools it is taught, that -3^2 results in -9.

I suggest to handle it as Gnumeric, which automatically inserts brackets, when someone enters =-3^2. So entering =-3^2, it is immediately changed to =(-3)^2. So the formula does neither violate the taught precedence nor ODF1.2 standard.
Comment 11 Björn Michaelsen 2011-07-08 11:20:19 UTC
*** Bug 38824 has been marked as a duplicate of this bug. ***
Comment 12 Julien Nabet 2015-09-07 19:56:06 UTC
*** Bug 93983 has been marked as a duplicate of this bug. ***
Comment 13 donotspam 2015-09-07 21:25:15 UTC
Not being an Excel user I hadn't realised that Excel also deviates from mathematical convention, but I agree that compatibility should be maintained as far as possible.

My question is, would a gnumeric-like solution of background-bracketing -x^y to (-x)^y would at least give users a clue that the result may not be what they intended, if they understand the maths behind what they are perhaps trying to achieve.

Would such an approach break anything?

I have difficulty in accepting that an inaccurate result in a spreadsheet from a formula observing conventions of operator precedence should not be considered potentially a major problem.  Also, in maths, for something so fundamental, surely this "convention" is a standard in all but name?  Other approaches (postfix etc) might be considered somewhat non-mainstream and I think are likely to be used by those who are mindful of such issues and know what to check for and against (eg. scientists, mathematicians) rather than, say, a doctor or pharmacist calculating a dosage and expecting the spreadsheet to work like maths does...
Comment 14 GerardF 2015-09-28 17:01:24 UTC
*** Bug 94568 has been marked as a duplicate of this bug. ***
Comment 15 Adrian Maire 2015-09-28 18:32:59 UTC
So as Microsoft developers has made a mistake 20 years ago, LibreOffice also copied this mistake without even checking. And now, to maintain retro-compatibility, we are going to have this wrong behaviour until the end of the time?.

Today, I lost 2h until I discovered why the hell my function with around 30 parameters was wrong to discover that Libre-office was wrong. 

I asked a Mathematician community to be sure before and I end-up against these comments.

Possible solution:
Change the behaviour to the correct, and add parentheses while opening old files from before the update so they still work.
Comment 16 Julien Nabet 2015-09-28 19:11:14 UTC
*** Bug 94580 has been marked as a duplicate of this bug. ***
Comment 17 Julien Nabet 2016-02-28 12:12:20 UTC
*** Bug 98248 has been marked as a duplicate of this bug. ***