Bug 123158 - isNumeric returns false if number is passed as String
Summary: isNumeric returns false if number is passed as String
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: x86-64 (AMD64) macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-StarBasic 136801
  Show dependency treegraph
 
Reported: 2019-02-04 13:34 UTC by Leos Pohl
Modified: 2023-07-27 22:40 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Testcase in LO 7.5 (777.55 KB, image/png)
2023-03-19 18:35 UTC, eisa01
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Leos Pohl 2019-02-04 13:34:32 UTC
The Basic function isNumeric() returns False if a number is passed as a String under OS X. This behaviour is different from Linux version. Tested on versions 6.0.7.3 and 6.1.4.2 both are affected. As per documentation (https://help.libreoffice.org/Basic/IsNumeric_Function_Runtime), the behaviour under Linux is correct, the one under OS X is not.


MWE:

Sub test
Dim a As String
Dim b As Boolean
a = "0.2"
b = isNumeric(a)
' b will be false on OS X and true on Linux
End Sub
Comment 1 Julien Nabet 2019-02-04 15:08:38 UTC
https://help.libreoffice.org/Basic/IsNumeric_Function_Runtime indicates a variant not a string.
Now, I think all envs should have the same behavior.
Comment 2 Oliver Brinzing 2019-02-05 18:09:32 UTC
with 

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

result is True
Comment 3 Alex Thurgood 2019-02-06 09:00:23 UTC
Confirming with

Version: 6.3.0.0.alpha0+
Build ID: e9db8eceff48290be72591f7422b4fc45e5752fc
CPU threads: 4; OS: Mac OS X 10.14.2; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded
Comment 4 Alex Thurgood 2019-02-06 09:04:14 UTC
I get the same result even if I set variable a as Any...
Comment 5 Alex Thurgood 2019-02-06 09:22:49 UTC
Tested with

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

I can reproduce the same behaviour here.
Comment 6 Alex Thurgood 2019-02-06 09:26:23 UTC
Also reproduced in 

Version: 5.2.6.2
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
Threads CPU : 4; Version de l'OS :Mac OS X 10.14.2; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group

so, it would seem that the bug has always been present.
Comment 7 Takeshi Abe 2019-02-18 03:43:37 UTC
IsNumeric(a) is true with:

Version: 6.2.0.3
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; VCL: osx; 
Locale: ja-JP (en_JP.UTF-8); UI-Language: en-US
Calc: threaded

but false with:

Version: 6.2.0.3
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; VCL: osx; 
Locale: fr-FR (en_JP.UTF-8); UI-Language: en-US
Calc: threaded

i.e. on macOS it depends on your locale setting, which determines "." or "," as the decimal separator.
Comment 8 Leos Pohl 2019-02-22 00:34:11 UTC
To complement my full results:
Version: 6.1.5.2
Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805
CPU threads: 8; OS: Mac OS X 10.14.3; UI render: default; 
Locale: cs-CZ (cs_CZ.UTF-8); Calc: group threaded
(locale decimal separator is ",")

IsNumeric(1,2) = True
IsNumeric(1.2) = True
IsNumeric("1.2") = False
IsNumeric("1,2") = True

So maybe if the input is a String, it does depend on the Locale but regardless of the input type, it needs to provide consistent results which it does not. Whether it is correct to take into account the locale or not is a good question because, if a the table has numbers stored as text (e.g. '1.2) then when such a workbook is transported onto a system with such locale that the decimal separator is "," these numbers are not automatically transformed to have the correct decimal separator but it is still a number (at least it was supposed as such on the other system), regardless of the fact that it is stored as text and since the point of isNumeric is to determine whether a Variant type (i.e. even a string) is a correct number, from this point of view both isNumber should be insensitive to the locale.
Comment 9 Leos Pohl 2019-02-22 00:34:36 UTC Comment hidden (obsolete)
Comment 10 Mike Kaganski 2020-03-14 12:08:35 UTC
But the help for IsNumeric [1] links to glossagy [2] for definition of Number; and the latter tells:

> Decimal Point
> When converting numbers, LibreOffice Basic uses the locale settings of the
> system for determining the type of decimal and thousand separator.
>
> The behavior has an effect on both the implicit conversion ( 1 + "2.3" = 3.3 )
> as well as the function IsNumeric.


Close NOTABUG?

[1] https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/03102700.html
[2] https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/00000002.html#dezimal
Comment 11 Mike Kaganski 2020-03-14 12:51:18 UTC
(In reply to Mike Kaganski from comment #10)
> > The behavior has an effect on both the implicit conversion ( 1 + "2.3" = 3.3 )
> > as well as the function IsNumeric.

The *different* problem here is that the implicit conversion seems to not behave like mentioned in the cited help topic (worth own bug).
Comment 12 joesch04 2020-05-10 12:03:50 UTC
it does not exactly belong to the topic of the bug, but may I add the following example:

IsNumeric(" ") returns True
IsNumeric("") returns False

I think in both cases the result should be False.
Comment 13 QA Administrators 2022-09-18 04:08:50 UTC Comment hidden (obsolete)
Comment 14 eisa01 2023-03-19 18:35:48 UTC
Created attachment 186081 [details]
Testcase in LO 7.5
Comment 15 eisa01 2023-03-19 18:36:22 UTC
Seems to work now?

Version: 7.5.1.2 (AARCH64) / LibreOffice Community
Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129
CPU threads: 10; OS: Mac OS X 13.2.1; UI render: default; VCL: osx
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 16 Xisco Faulí 2023-03-20 11:11:55 UTC
(In reply to joesch04 from comment #12)
> it does not exactly belong to the topic of the bug, but may I add the
> following example:
> 
> IsNumeric(" ") returns True
> IsNumeric("") returns False
> 
> I think in both cases the result should be False.

This is still reproducble in

Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 06ebcfd5af22ae722bf2dd9918a6f6a83fe23b6e
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: en-US (es_ES.UTF-8); UI: en-US
Calc: threaded

@Mike, should it be reported as a separated bug ?
Comment 17 Mike Kaganski 2023-03-20 11:38:56 UTC
(In reply to Leos Pohl from comment #8)
> (locale decimal separator is ",")
> 
> IsNumeric(1,2) = True
> IsNumeric(1.2) = True
> IsNumeric("1.2") = False
> IsNumeric("1,2") = True

(In reply to eisa01 from comment #14)
> Created attachment 186081 [details]

(In reply to eisa01 from comment #15)
> Seems to work now?

First of all, let us not mix things and do the nonsensical testing.

1. IsNumeric(1,2) = True

This is an own bug, unrelated to this. The IsNumeric *here* is called with *two* parameters, while it accepts only a single argument. Leos Pohl and eisa01 likely confused the (1,2) syntax for an alternative floating-point notation using comma - but it's not: it's the same as IsNumeric(1, 2) (note the space between the two arguments, which is simply missing in the confusing sample). The bug is that functions must throw an error when the caller passes more arguments than allowed. The resulting "True" is for the integer 1 that is passed as the first parameter - in is indeed numeric.

2. IsNumeric(1.2) = True

This is a correct check is a floating-point number is numeric. The only correct way of writing floating-point number literals in Basic is using decimal dot.

3. IsNumeric("1.2")
   IsNumeric("1,2")

This is the whole essence of the issue discussed here. And it definitely depends on the *locale* (as explained in comment 10), and so, when you test it using a different locale, it doesn't tell you anything about reproducibility of the original problem. Namely, the initial report used a locale with decimal comma, and the test in comment 14 used a locale with decimal dot (and comma for thousand separator). In Basic, the thousand separator can appear at any place, not only separating groups of three digits -> hence the "1,2" is considered an integer "12" with a thousand separator in unusual place. And "1.2" is considered numeric, because it naturally matches the locale's floating-point notation.

(In reply to Xisco Faulí from comment #16)
> > IsNumeric(" ") returns True
> @Mike, should it be reported as a separated bug ?

Yes, indeed.
Comment 18 Xisco Faulí 2023-03-20 11:44:39 UTC
(In reply to Mike Kaganski from comment #17)
> (In reply to Xisco Faulí from comment #16)
> > > IsNumeric(" ") returns True
> > @Mike, should it be reported as a separated bug ?
> 
> Yes, indeed.

Done -> bug 154284