Bug 139173 - Incorrect bounds on formula DEC2HEX
Summary: Incorrect bounds on formula DEC2HEX
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium minor
Assignee: Eike Rathke
URL:
Whiteboard: target:7.2.0 target:7.1.0.2 target:7.0.5
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-12-23 00:50 UTC by cameron.pinnegar
Modified: 2021-01-08 20:29 UTC (History)
3 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description cameron.pinnegar 2020-12-23 00:50:34 UTC
Description:
In MS Excel the formulae DEC2BIN, DEC2OCT, and DEC2HEX have valid inputs as integers m in the range -2^n <= m < 2^n, where n is given by

DEC2BIN: n=9
DEC2OCT: n=29
DEC2HEX: n=39

LibreOffice Calc gets this right for DEC2BIN and DEC2OCT, but not DEC2HEX. There, the valid range is erroneously: -2^39 - 1 <= m < 2^39 + 1.

In other words, in Excel we have:

=DEC2HEX(2^39) == #NUM!
=DEC2HEX(-2^39 - 1) == #NUM!

But in LOCalc:

=DEC2HEX(2^39) == "8000000000"
=DEC2HEX(-2^39 - 1) == "7FFFFFFFFF"

This is surprising and does not seem like intended behaviour. I have not yet investigated whether the other base conversion functions have inconsistent bounds as well.

Steps to Reproduce:
In a cell do =DEC2HEX(2^39) or DEC2HEX(-2^39 - 1)

Actual Results:
Answers are "8000000000" and "7FFFFFFFFF" respectively.

Expected Results:
Answers should be #NUM! in both cases, in accordance with Excel.


Reproducible: Always


User Profile Reset: No



Additional Info:
OS: Manjaro Linux
LibreOffice Calc version: 6.4.7.2
Comment 1 Julien Nabet 2020-12-23 09:01:41 UTC
Why LO should be in accordance with Excel or more precisally, with the same limitations? Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS functions? (but I don't see doc about this).
Comment 2 Ming Hua 2020-12-23 11:55:51 UTC
I can reproduce the reported inconsistency with 7.1.0 Beta1, i.e.:

DEC2BIN(2^9) and DEC2BIN(-(2^9)-1) gives Err:502
DEC2HEX(2^39) gives "8000000000" and DEC2HEX(-(2^39)-1) gives "7FFFFFFFFF", not errors

Version: 7.1.0.0.beta1 (x64)
Build ID: 828a45a14a0b954e0e539f5a9a10ca31c81d8f53
CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win
Locale: zh-CN (zh_CN); UI: zh-CN
Calc: threaded

Also both DEC2HEX(2^39) and DEC2HEX(-(2^39)) gives "8000000000", so this is definitely a bug.

(In reply to Julien Nabet from comment #1)
> Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS
> functions? (but I don't see doc about this).
MS has support page for DEC2HEX: https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5-4c6a-a672-f64666704619

I believe this is Eike's area of expertise, adding him to CC.
Comment 3 Julien Nabet 2020-12-23 12:03:38 UTC
(In reply to Ming Hua from comment #2)
> ...
> (In reply to Julien Nabet from comment #1)
> > Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS
> > functions? (but I don't see doc about this).
> MS has support page for DEC2HEX:
> https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5-
> 4c6a-a672-f64666704619
> ...
I don't understand, is "DEC2HEX" a kind of "proprietary" function of MS?
Why couldn't LO use these function names and expand what Excel can do? Is LO bound to mimic Excel even in its limitations?
Comment 4 cameron.pinnegar 2020-12-23 12:13:50 UTC
(In reply to Julien Nabet from comment #3)
> (In reply to Ming Hua from comment #2)
> > ...
> > (In reply to Julien Nabet from comment #1)
> > > Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS
> > > functions? (but I don't see doc about this).
> > MS has support page for DEC2HEX:
> > https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5-
> > 4c6a-a672-f64666704619
> > ...
> I don't understand, is "DEC2HEX" a kind of "proprietary" function of MS?
> Why couldn't LO use these function names and expand what Excel can do? Is LO
> bound to mimic Excel even in its limitations?

Well, LO Calc is mimicking Excel's limitations already. The bounds of the formulae seem to be so that the outputs are constrained to be 10 characters long, but that's just an arbitrary thing Microsoft chose. LibreOffice doesn't have to abide by that -- it could choose to allow much bigger inputs, yet it doesn't, for what I assume are compatibility reasons. So if compatibility and consistency are goals, the bounds should match Excel's.
Comment 5 Julien Nabet 2020-12-23 12:28:19 UTC
(In reply to cameron.pinnegar from comment #4)
>... 
> Well, LO Calc is mimicking Excel's limitations already. The bounds of the
> formulae seem to be so that the outputs are constrained to be 10 characters
> long, but that's just an arbitrary thing Microsoft chose. LibreOffice
> doesn't have to abide by that -- it could choose to allow much bigger
> inputs, yet it doesn't, for what I assume are compatibility reasons. So if
> compatibility and consistency are goals, the bounds should match Excel's.
So you prefer LO sticks to Excel limitations instead of LO being able to do better just for compatibility sake?
It seems most people prefer Excel compatibility as a top priority for Calc, I find it crazy. Anyway, don't worry, I must be the only one (at least never saw another one) to think like this, so I suppose this "bug" will be "fixed" :-)
=> uncc myself
Comment 6 himajin100000 2020-12-24 13:17:32 UTC
personally I prefer Julien's opinion. 
but putting aside that, I can provide a souce code pointer.

https://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysishelper.cxx?r=93c64a61&fi=ConvertFromDec#704

This is not
fNum <= fMin || fNum >= fMax
Comment 7 himajin100000 2020-12-24 13:20:05 UTC
oops,typo

souce => source
Comment 8 himajin100000 2020-12-24 13:23:37 UTC
https://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysis.cxx?r=107399d6#662

more note:

SCA_MAX2 and SCA_MAX8 are odd numbers, but SCA_MAX16 is an even number.
Comment 9 Eike Rathke 2021-01-05 21:58:03 UTC
All three functions are Excel functions. It is futile to discuss whether Calc could do better than Excel, the definition of DEC2HEX is there
https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part4-formula/OpenDocument-v1.3-cs02-part4-formula.html#DEC2HEX
and the current implementation has one-off errors in its interval.
Comment 10 Commit Notification 2021-01-06 16:38:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/70ea6b36df9ede18b135876d9b9da9945f6c129b

Resolves: tdf#139173 One-off error in limits for DEC2HEX()

It will be available in 7.2.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 11 Eike Rathke 2021-01-06 16:40:09 UTC
Pending review
https://gerrit.libreoffice.org/c/core/+/108896 for 7-1
https://gerrit.libreoffice.org/c/core/+/108897 for 7-0
Comment 12 Xisco Faulí 2021-01-07 09:30:52 UTC
Verified in

Version: 7.2.0.0.alpha0+
Build ID: 6bb6eb1692d7dd432103d0e7278534390084caf6
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 13 Commit Notification 2021-01-07 09:32:50 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/072b43a4bdf8392c9a6a8cb1ad5e8a3e61c60117

Resolves: tdf#139173 One-off error in limits for DEC2HEX()

It will be available in 7.1.0.2.

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 2021-01-07 09:33:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/8c7266b084fe95460c1e8b5e27bb4464ca1cc20a

Resolves: tdf#139173 One-off error in limits for DEC2HEX()

It will be available in 7.0.5.

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 2021-01-07 22:07:57 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#139173: sc_addin_functions_test: Add function

It will be available in 7.2.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 Commit Notification 2021-01-08 20:29:53 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#139173: sc_addin_functions_test: Add dec2bin and dec2oct functions

It will be available in 7.2.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.