Bug 72174 - convert_add() from "at" and "atm" to "Pa" gives the same answer.
Summary: convert_add() from "at" and "atm" to "Pa" gives the same answer.
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: odf
Keywords:
Depends on:
Blocks:
 
Reported: 2013-11-30 15:49 UTC by Konrad
Modified: 2020-02-10 18:32 UTC (History)
10 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 Konrad 2013-11-30 15:49:22 UTC
Calc gives:
convert_add(1;"at";"Pa") = 101325
convert_add(1;"atm";"Pa") = 101325

but correct is:
convert_add(1;"at";"Pa") = 98066,5 technical atmosphere
convert_add(1;"atm";"Pa") = 101325 normal atmosphere
Comment 1 Julien Nabet 2013-11-30 17:17:54 UTC
On pc Debian x86-64 with master sources updated today, I can reproduce this.
Comment 2 Julien Nabet 2013-11-30 17:18:32 UTC
It must be there:
http://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysishelper.cxx#2549
I take it.
Comment 3 Commit Notification 2013-11-30 17:29:33 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c2dd0ef4b891a3d126dbe70ab5b76016b69d491c

Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 4 Julien Nabet 2013-11-30 17:57:42 UTC
For 4.2 and 4.1, commits have been submitted to gerrit.
Comment 5 Commit Notification 2013-12-01 11:07:15 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=054b74222d72a61db3c67b32d62f99a1a0a5e9d6&h=libreoffice-4-2

Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer


It will be available in LibreOffice 4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2013-12-01 11:08:33 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4139dc70da04fe3d6a86bbab1e7bf6f11d812f04&h=libreoffice-4-1

Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer


It will be available in LibreOffice 4.1.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Korrawit Pruegsanusak 2013-12-04 09:27:54 UTC
Hello all,

Sorry that I come late, but looking at OASIS specification[1], both the "atm" and "at" units are the same.

I didn't say that Konrad was wrong, but I think we should revert this for the sake of compatibility to the specs. Another choice is, IMHO, proposing an amendment to the specs.

Thanks :)

[1] http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018496_715980110
Comment 8 Julien Nabet 2013-12-04 11:16:49 UTC
Korrawit: I understand the point but I don't know at all the process to amend Oasis specs. Any idea who may help here?
Comment 9 Mateusz 2013-12-04 19:41:23 UTC
(In reply to comment #8)
> Korrawit: I understand the point but I don't know at all the process to
> amend Oasis specs. Any idea who may help here?

From what I remembered, Miklos Vajna and Michael Meeks are involved in work at ODF-Next at OASIS.
Comment 10 Julien Nabet 2013-12-04 20:04:22 UTC
Miklos/Michael: any idea about the process to amend Oasis specs? Does it worth it or should we revert the patch?
Comment 11 Mateusz 2013-12-04 20:29:46 UTC
I added Kohei Yoshida because he is an expert from spreadsheet.
Comment 12 Michael Meeks 2013-12-04 21:07:18 UTC
IIRC Michael Stahl is a good guy for tweaks to ODF.

We keep our implementor notes here - prolly worth adding something to that so it is tracked:

https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes

if it is not there already.
Comment 13 Eike Rathke 2013-12-05 11:12:16 UTC
Argh.. sigh.. the OpenFormula CONVERT function was specified after Excel, plus some extensions, and also Excel treats "atm" as an equivalent of "at", latest checked with Excel 2013. We should not come up with different factors on our own in this case.

I'll revert the corresponding commits.
Comment 14 Commit Notification 2013-12-05 14:48:23 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=63153f10e0a7d16511878bbfcd97e2c8781a5564

Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer"



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2013-12-05 14:55:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5491192f98a9b50e1b8d2ecd9d711ee373246d15&h=libreoffice-4-2

Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer"


It will be available in LibreOffice 4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2013-12-05 14:59:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=152ee33b6c70315e1103b12de745b3f9c675c0b2&h=libreoffice-4-1

Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer"


It will be available in LibreOffice 4.1.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 17 Ady 2013-12-05 16:35:21 UTC
Users involved with scientific matters might assume that LO and ODF "Standards" would correctly follow scientific standards too. Such assumption might lead to wrong conclusions from users, for example when analyzing data obtained from Calc.

Perhaps some note(s) in LibreOffice's documentation / help / wiki / Calc guide(s) should be added regarding this issue (for example for the help regarding the involved Calc functions)?

Regards,
Ady.
Comment 18 Julien Nabet 2013-12-05 18:59:57 UTC
I "unassign" myself since I can't do more and am quite disappointed about this.
IMHO, ODF and even less Excel do not define standards about measures (it's not their role). Of course odf files must follow odf standards but in this case, we know that the standard includes a mistake. Therefore we should follow real standards + ask to amend ODF standard or disable this conversion but not validate an error. Now, it's just my humble opinion.
Comment 19 Michael Meeks 2013-12-05 19:24:14 UTC
Grief - wheat a vexed problem ! In general, doing what people expect - ie. the same as Excel is really a good idea. Of course, when that is wrong it's hard to know what to do. Thanks for unwinding this Julien - and raising the profile of it. Of course, we could add some libreoffice specific function that does what we think is right, and then a very large amount of filter logic to convert to/from Excel/old-ODF ? - but that seems like a lot of work (?)
Comment 20 Julien Nabet 2013-12-10 19:51:35 UTC
Just for information, I opened a thread here to know if it's possible to amend OpenDocument format:
https://lists.oasis-open.org/archives/office-comment/201312/threads.html
Comment 21 Regina Henschel 2013-12-10 20:45:18 UTC
I've read the comment on the mentioned ODF office-comment list. I think Julien is right, that the units "atm" and "at" are different and that they need two separate rows in the spec.

Gnumeric circumvents the problem. It does not allow unit "at". Perhaps LibreOffice should do the same for ods format. That way the user notices that the result had been false, and newer documents will not be based on a wrong "at" conversion. Then later on, when the ODF specification is corrected, LibreOffice can us the correct conversion with less conflicts.
Comment 22 Lionel Elie Mamane 2015-02-05 08:30:43 UTC
Apparently the ODF TC has decided to fix this for ODF 1.3, and also "ODF 1.2 Errata 01"???

So we will have to implement both behaviours, one for importing xls/xlsx files and ODF 1.2 and before, and one for ODF 1.2 errata 01 and later? I also wonder about users being very confused why the behaviour is different in different ods files... depending on the version of ODF they are tagged with, which is AFAIK not clearly displayed in the UI.

Frankly I'm a bit surprised one would change that in an errata, especially since it seems it was done very much on purpose. Are errata even considered different versions, meaning they get a different version tag in the files?
Comment 23 Michael Stahl (allotropia) 2015-02-05 16:17:33 UTC
There is no way to check the "errata level" of an ODF document because errata are not allowed to make substantive changes.

i don't know anything about this, most likely didn't attend the meeting where it was discussed, so no idea if there is actually the intent to fix this in ODF 1.2 errata; the "Resolution" says "Will fix in ODF 1.3" but the "Fix for" also lists 1.2 errata...
Comment 24 Eike Rathke 2018-02-12 18:33:28 UTC
Just in case someone comes across this again (I was asked by an OASIS-TC member about my opinion), for clarification:

http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#CONVERT
*already* deprecates the use of "at" as an equivalent of "atm", listed as
"atm" ("at")

Nevertheless, the misleading use of unit is in the wild since ages. The ODFF specification states at the bottom of the unit table:
"The unit symbols in parentheses are deprecated unit symbols; evaluators shall support these unit symbols."

The correct way is to specify a new unit symbol to calculate the "at-correct" conversion.
Comment 25 Julien Nabet 2018-02-12 20:55:06 UTC
(In reply to Eike Rathke from comment #24)
>...
> The correct way is to specify a new unit symbol to calculate the
> "at-correct" conversion.

I'm not sure to understand. Would it be an LO-internal new unit symbol? If yes, how LO would distinguish in a file the use of "real at" and the "wrong at"?
At least, I hope they don't mean a new "at-correct"/"at-fixed"/... symbol available for users since it would be a bit cumbersome.
Comment 26 Eike Rathke 2018-02-12 23:43:46 UTC
No, I meant an additional unit symbol to be specified by ODFF. Sorry for confusion. And yes, that would mean an "at-correct" symbol because "at" is spoiled forever and either calculated as Excel does and specified, or not calculated at all as Gnumeric does.
Comment 27 Michael Stahl (allotropia) 2020-02-10 18:32:55 UTC
update: this was resolved now at OASIS, quoting from https://issues.oasis-open.org/browse/OFFICE-3851

    Resolution: 
"at" Deprecated. Note: Interpreted inconsistently by existing implementations.

"atm" Standard atmosphere = 1.0132510+5 Pa

"SI_at" Technical atmosphere = 9.8066510+4 Pa