Bug 123878 - Numeric format inconsistency: Macros vs Writer fields, formula calculation fails
Summary: Numeric format inconsistency: Macros vs Writer fields, formula calculation fails
Status: RESOLVED DUPLICATE of bug 73242
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Extensions (show other bugs)
Version:
(earliest affected)
6.2.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-UNOAPI
  Show dependency treegraph
 
Reported: 2019-03-05 15:50 UTC by jsd.libreoffice
Modified: 2020-05-14 17:09 UTC (History)
3 users (show)

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


Attachments
Minimal demonstration of decimal point bug (328.03 KB, application/octet-stream)
2019-12-02 18:23 UTC, jsd.libreoffice
Details
My macOS locale settings (268.95 KB, image/png)
2020-05-12 14:03 UTC, jsd.libreoffice
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jsd.libreoffice 2019-03-05 15:50:40 UTC
[Sorry if Extensions component is wrong, wasn't sure what to pick]

Running LO 6.2.0.3 on MacOS 10.13.6 in South Africa, where system regional setting uses comma rather than period for decimal separator.

Writer treats strings with period, eg "1.5" as non-numeric and evaluates that as 0.  With a comma instead, eg "1,5" the value is one-and-a half. OK, fair enough.

Now I have a macro that extracts a value from a spreadsheet cell, and sets that value into a Writer variable field which is then used elsewhere in a Writer formula.

The latter formula does not compute correctly - result is 0 - because the extracted spreadsheet value (a floating point number, type Variant/Double returned by RangeTLCellValue) gets turned into a string with a period for decimal point in the Writer variable field (DependentTextField.Content = myNumericValue)

Either the implicit double-to-string conversion should be using the same punctuation that's expected elsewhere (so comma not period) or else everything should accept a period as decimal separator in number strings, and then do the local-dependent presentation only at the formatting stage (which is what OpenOffice used to do: this macro began life there).

FWIW I tried changing Language settings in the document, made no difference.  Presumably the system locale trumps that for number parsing.  This also affected static "set variable" fields in the template: I had to change decimal periods to commas in those too, to make the formulas work.
Comment 1 Xisco Faulí 2019-06-10 16:25:59 UTC
Thank you for reporting the bug.
it seems you're using an old version of LibreOffice.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 2 jsd.libreoffice 2019-06-10 18:18:30 UTC
Thanks for the response.  I just downloaded and installed LO 6.4.2, and the bug is still present, as described.

I will try to compose a minimal demonstration, but for now let me describe what I am doing, and what is happening:

Note that in my system regional settings (ZA) the decimal point character is a comma, 
not a period.  This is central to the problem, I believe.

(1) I have a Writer template with 3 "Set variable" fields...
      Field 1 ("Rate")  value is 1 cent (0.01) actually entered as 0,01
      Field 2 ("Count") value is initially 0
      Field 3 ("Total") value is Count*Rate and so initially displays as 0

    Note that I have to enter the fractional 1 cent value with a comma, not
    a period as the decimal.  If I type the value as 0.01 then formulas
    based on the field do not work (the value displayed for the formula
    field does not change)

(2) The macro opens a spreadsheet and reads a cell value like so:
      tCount = RangeTLCellValue(oSpreadsheet, "TotalCount")
    from a named range spanning one cell. The value is a calculation result
    that is not necessarily an integer.  In the debugger watch window after
    reading it, tCount is displayed as Type Variant/Double, Value 1234.5
    (Note the period in there, not a comma)

(3) The macro then sets tCount into the Writer document "Count" field
    with the equivalent of this line:
       TextFieldMaster(sName).DependentTextFields(iIndex).Content = tCount
    (it actually loops through all the dependent text fields, I can't remember why)

(4) Finally the macro does this:
       ThisComponent.GetTextFields.Refresh

Now I would expect the Total field in the document to display "12.34" (or "12,34")
(i.e. the result of calculating 1234.5 * 0.01) but it still shows 0.

If I inspect the document Count field, its value is 1234.5 exactly as it came
from the spreadsheet.

Now if I change the Count field value to 1234,5 (with a decimal comma) then
the Total field changes to the expected calculation result value 12,34

Phew.

To summarise:
  * Writer seems to insist that numeric field values use the decimal character
    defined in the system regional settings, in my case a comma.  If there
    is a period instead of a comma as the decimal, the value is treated as
    non-numeric.  That's arguably justifiable, BUT...

  * A non-integer value extracted from a spreadsheet is rendered using a
    period as the decimal, not a comma, when it is stored in a Writer field.
    Thus Writer treats it as non-numeric is it is used in a formula.

So these two behaviours conflict with each other.

The older behaviour of Open Office (where my macro was initially developed)
was to always use a decimal period in field values, but to _show_ values in
the document using the regional characters.  This is what I would recommend.

Hope that all makes sense, and thanks for reading this far :-)
Comment 3 Andreas Heinisch 2019-12-02 17:14:09 UTC
Could you provide a minimal working document as an attachement? I recently fixed a bug where this behaviour could be fixed ...
Comment 4 jsd.libreoffice 2019-12-02 18:23:13 UTC
Created attachment 156251 [details]
Minimal demonstration of decimal point bug

Hi Andreas.  The attached .OTT file demonstrates the issue.  
It includes an explanation as well as screenshots since YMMV, as I suspect the behaviour depends on the system locale.  Mine is South Africa, where the decimal separator is a comma, not a period.

For me this problem manifests in 2 ways: (a) with constant fields in the template itself, as demonstrated here and (b) with values pulled from a spreadsheet and inserted into the document by a macro.  The second case is hard to demo in a single file, but I'd say it boils down to the same root cause: the number will be inserted into the document with a period (not a comma) as the separator, and consequently calculations that depend on the value will fail.
Comment 5 Andreas Heinisch 2019-12-02 20:49:55 UTC
I think the bug lies in the handling of numeric types in the macro libraries. I recently fixed a bug, where Macros did not use any locale: https://bugs.documentfoundation.org/show_bug.cgi?id=97983#c10

Could you test your macro using a daily build of LO?
Comment 6 Buovjaga 2020-04-27 13:21:29 UTC
(In reply to Andreas Heinisch from comment #5)
> I think the bug lies in the handling of numeric types in the macro
> libraries. I recently fixed a bug, where Macros did not use any locale:
> https://bugs.documentfoundation.org/show_bug.cgi?id=97983#c10
> 
> Could you test your macro using a daily build of LO?

jsd: you will find daily builds here: https://dev-builds.libreoffice.org/daily/master/current.html
https://wiki.documentfoundation.org/Installing_in_parallel/OS_X
Comment 7 jsd.libreoffice 2020-05-12 13:59:58 UTC
(In reply to Buovjaga from comment #6)
> > Could you test your macro using a daily build of LO?
> 
> jsd: you will find daily builds here:
> https://dev-builds.libreoffice.org/daily/master/current.html

Sorry for delayed response.  I'm afraid I see no change in behaviour, using this build https://git.libreoffice.org/core/+log/4118610df8a78b00e5369bad1d9ce21cf1fbafeb
(downloaded today, 12 May)

I'll attach a screenshot of my regional settings, where you can see (if you look very closely :-) that the decimal separator is a period, not a comma.

Using the demo file I attached here on 2019-12-02, I get the same result, which is that calculations fail for numbers with a period as the decimal separator.

Something that just occurred to me is this: for my locale, ZA, the _default_ decimal separator is a comma.  I changed that to a period (and changed the thousands grouping character from period to comma).

If LO is using the default locale settings instead of the getting the actual ones from the OS, this would explain my results.
Comment 8 jsd.libreoffice 2020-05-12 14:03:40 UTC
Created attachment 160728 [details]
My macOS locale settings

Current locale settings, mentioned in my previous post.
Decimal separator is a period, which is not the default (comma)
Comment 9 Buovjaga 2020-05-12 14:04:35 UTC
(In reply to jsd.libreoffice from comment #7)
> Something that just occurred to me is this: for my locale, ZA, the _default_
> decimal separator is a comma.  I changed that to a period (and changed the
> thousands grouping character from period to comma).
> 
> If LO is using the default locale settings instead of the getting the actual
> ones from the OS, this would explain my results.

So bug 73242 ?
Comment 10 jsd.libreoffice 2020-05-12 15:41:39 UTC
(In reply to Buovjaga from comment #9)
> > If LO is using the default locale settings instead of the getting the actual
> > ones from the OS, this would explain my results.
> 
> So bug 73242 ?

Quite possibly, yes.  If I understand that discussion correctly, the gist is that LO isn't picking up the OS locale settings.  If it were to do so, I agree that would probably resolve this issue.

But here's an interesting thing: in the LO Preferences under Language Settings/Languages, the settings I have are:

Language Of:
  User interface: Default - English (USA)
Formats:
  Locale setting: Default - English (UK)
  Decimal separator key: (ticked) Same as local setting (.)

What's interesting to me is that AFAIK, the decimal separator for both English (USA) and English (UK) is a period, not a comma.  Yet LO is nevertheless expecting a comma, not a period, in numbers.

Not sure why that would be.  I considered the possibility that it might be using the _document's_ language setting, which was originally English (South Africa).  But changing that to English (USA) did not make a difference.  Calculations involving numbers with a decimal comma worked, those involving numbers with a decimal period did not.

Curiouser and curiouser.
Comment 11 Buovjaga 2020-05-14 17:09:15 UTC
Let's close as duplicate of bug 73242.

*** This bug has been marked as a duplicate of bug 73242 ***