[Libreoffice-bugs] [Bug 140691] calc: calculation: sequence of operations neglected in many tasks, only mathematical better results should be allowed if calc differs from ex$el | was: sum of range: (and similar) - no compatibility with ex$el reg. ordering of operands

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=140691

--- Comment #8 from Mike Kaganski <mikekaganski at hotmail.com> ---
(In reply to b. from comment #7)
> as far as it's a matter of 'standards' it's a clear weakness of them, and
> should be argued about to change and how to define, 
> > They may have different data layout that makes their specific order more efficient, and our data layout may make another order preferred; 
> - yeah, and it's time to get that noticed and be aware of, 

I don't see what you are trying to tell with this "it's time to get that
noticed and be aware of". You can't be aware of the undocumented data layout in
a closed-source program like Excel. You can't rely on it even if you somehow
made assumptions and reverse-engineered that.

> > also they may change it tomorrow, without notice. 
> - i can't believe you really believe that, they are! ... 'somewhat
> problematic' ... but not stupid enough to shock all their users and break
> their! backward compatibility, 

No that will not break "their! backward compatibility". It would of course need
to keep the major picture (e.g., it would be illegal to return 0 where they
returned 1), but it is OK for any spreadsheet application to change the result
in 15th significant decimal, if that is a justified change.

> > It's for purpose that various standards do not define the order of evaluation.
> - on purpose? too difficult so far? or just forgotten? - if you say 'on
> purpose': 1. evidence please, 2. what purpose? 

E.g., a purpose to allow implementers to use different, unknown in advance,
optimization strategies (like parallel access). Spreadsheets are
well-espablished, and didn't appear yesterday. They have international
standards with several revisions, each approved by international bodies of
experts. They all know what order of evaluation is (such order is always
defined e.g. in most programming languages, and people there in TCs are
perfectly aware of pros and contras of strict specification of that). It's just
not reasonable to imagine that omission of this spec in all published 
standards is "shortcoming" or "omission"; it was mentioned in many places like
[1], and is not a well-hidden secret.

> > And then, if you think that it would "improve compatibility", then you are breaking compatibility with our documents. Then, what about Excel following the order used in Calc when opening ODS?
> - imho it's not a problem of the file, but of calculation sequence at
> runtime, as of now i expect the results stored in the files to be different
> if saved by calc or ex$el, 

It's a problem of *standard*. If you assume that e.g. XLS(X) formulas need to
have some *specific* order of evaluation, then it would be normal that MS
defined it; but then the ODS spreadsheets are a separate thing, and they have
features not present in XLSX - so following the train of thought, the order of
evaluation of arguments in *that* file format would be something that *its*
author (OOO and later LO) defined, so - following it further - MS Excel should
now start to evaluate formulas differently when it opens ODS.

> > and suggestions that allow to improve calculations. 
> - i hope you won't start citing Adolf Hitler? who once spouted such nonsense
> as: 'Only the one who can solve a task better is entitled to criticism.' 
> no, we all make better progress if even the people who can not yet eliminate
> errors are allowed to point them out. 

You are attacking personally, putting words that I didn't tell into my mouth. I
never wrote that only those who can change can suggest, it's your imagination
that told you that. A suggestion "let's calculate SUM(1e20;-1;-1E20) to correct
-1" *is* a suggestion that allows to improve calculations, and suggesting it
does not require a CS degree; but "let's make LO follow unspecified order of
evaluation of a closed-source application" is not.

[1] https://www.sciencedirect.com/science/article/pii/S2590118420300204

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210421/bba8bce0/attachment.htm>