Bug 151886 - "Use English function names" changes decimal separator recognition
Summary: "Use English function names" changes decimal separator recognition
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.4.2.3 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL: https://forumooo.ru/index.php?topic=9461
Whiteboard: target:7.6.0 target:7.5.0.2 target:7.4.6
Keywords: bibisected, bisected, regression
: 151210 152674 152886 (view as bug list)
Depends on:
Blocks:
 
Reported: 2022-11-04 05:18 UTC by Mike Kaganski
Modified: 2023-01-24 10:36 UTC (History)
5 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2022-11-04 05:18:05 UTC
1. Use a locale that uses decimal comma
2. Make sure "Use English function names" is DISABLED
3. In a cell, type formula "=1,1" (with comma)
=> See that the cell shows "1,1" numeric value

4. Now ENABLE "Use English function names"
5. In another cell, type formula "=1,1" (with comma)
=> See that the cell shows #NAME? error
=> It only accepts "=1.1" now

This is a regression. It accepted "=1,1" with "Use English function names" enabled in Version: 7.4.0.3 (x64) / LibreOffice Community
Build ID: f85e47c08ddd19c015c0114a68350214f7066f5a
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL

It fails starting from commit 0a78f11fdd13d914d5f063007b2df9796e6dce9d. It was backported to 7.4.2 as part of commit 32019fae7730fc8d94f14d47ebaccbdc6775340a.
Comment 1 Mike Little 2022-11-04 06:27:09 UTC Comment hidden (noise)
Comment 2 Rafael Lima 2022-11-04 12:23:41 UTC
I can confirm this in

Version: 7.4.2.3 / LibreOffice Community
Build ID: 40(Build:3)
CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb)
Locale: pt-BR (pt_BR.UTF-8); UI: pt-BR
Ubuntu package version: 1:7.4.2~rc3-0ubuntu0.22.04.1~lo1
Calc: threaded

NOT REPRO in

Version: 7.3.1.3 (x64) / LibreOffice Community
Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951
CPU threads: 1; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: pt-BR (pt_BR); UI: pt-BR
Calc: threaded

So indeed it is a regression.
Comment 3 Eike Rathke 2022-11-07 11:34:07 UTC
That is on purpose. Using English function names now sets an English locale context as well, which enables copy&paste of English formula expressions (e.g. as found on the web or the Ask site) in any locale.
Comment 4 Mike Kaganski 2022-11-07 11:45:27 UTC
(In reply to Eike Rathke from comment #3)

This might sound reasonable. But more problematic is that using English names now requires you to use different number notation when typing formulas vs. typing numbers:

* type "1,1" in cell to get number 1,1;
* type "=A1*1.1" in cell to get a value multiplied by 1,1.

This makes the English names feature much less useful as full-time feature - basically, one would be unable to use it as one's primary mode.
I'd suggest to have two: "English names" (working as before), and "ODF syntax" or "standard syntax" or whatever other name for fully standardized behavior.
Comment 5 Eike Rathke 2022-11-08 15:05:51 UTC
Omitting

        mbEnglish = r.mbEnglish;

from mentioned commit 0a78f11fdd13d914d5f063007b2df9796e6dce9d should already switch back to the old behaviour, but that also would imply that the locale's CharClass would be used again which again could lead to problems where ASCII lower/upper is not yielding expected ASCII characters like in the Turkish locale and then function name mismatches could occur, and Add-In localization would kick back in again. So that is not what we want.

Careful examination of places using mxSymbols->isEnglish() in sc/source/core/tool/compiler.cxx and formula/source/core/api/FormulaCompiler.cxx is needed and probably we'll need yet another member variable in the symbol map to determine whether to use English or system locale for values.

Regarding UI, this is not about any standard behaviour, so "ODF syntax" doesn't make sense at all and "standard syntax" neither. I'd rather make that just a child checkbox option under "Use English function names", like "Use English locale" (default checked if English function names is checked and disabled/ignored otherwise).
Comment 6 Mike Kaganski 2022-12-25 17:42:15 UTC
*** Bug 152674 has been marked as a duplicate of this bug. ***
Comment 7 Lenge 2022-12-25 18:17:40 UTC
An additional observation from Bug 152674:
Switching "Use English function names" now does change the locale of numbers (= bug!), but does no longer change the function names itself (= 2nd bug!), e. g. between "SUM" and German "SUMME".

BTW:
Eike's idea to add an additional child checkbox "Use English locale" would be a good solution. And it should also switch the function argument separator between "," and ";" to enable pasting English formulas from the web without the need to manually edit them afterwards.
Comment 8 Eike Rathke 2022-12-25 21:19:30 UTC
(In reply to Lenge from comment #7)
> Switching "Use English function names" now does change the locale of numbers
> (= bug!), but does no longer change the function names itself (= 2nd bug!),
> e. g. between "SUM" and German "SUMME".
Can't reproduce. Switching a German UI to English formulas displays and accepts SUM() instead of SUMME().


> Eike's idea to add an additional child checkbox "Use English locale" would
> be a good solution. And it should also switch the function argument
> separator between "," and ";" to enable pasting English formulas from the
> web without the need to manually edit them afterwards.
Yeah that'd probably be good.
Comment 9 Lenge 2022-12-25 21:33:08 UTC
(In reply to Eike Rathke from comment #8)
> Can't reproduce. Switching a German UI to English formulas displays and
> accepts SUM() instead of SUMME().

Test again on three different machines (Win8.1 and Win10), all set to English UI and German locale (as in bug Bug 152674).

Result: They all accept "SUM()" only, never "SUMME()", regardless if "Use English function names" is on or off.

So it seems to refer to the UI language instead of the locale setting. This was different (and IMO better) in previous LO versions.
Comment 10 Eike Rathke 2022-12-26 00:34:01 UTC
No, that never was different. The functions were always in the UI language (if that has translated function names). The locale is irrelevant. One can only switch to English function names, not to another language's function names.
Comment 11 Commit Notification 2023-01-01 20:26:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#151886 Use default locale with English function names again

It will be available in 7.6.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 12 Commit Notification 2023-01-02 02:21:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-5":

https://git.libreoffice.org/core/commit/29d8b78c6b0bcbcf3f66e123194325008f7bc029

Resolves: tdf#151886 Use default locale with English function names again

It will be available in 7.5.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 13 Eike Rathke 2023-01-02 21:28:34 UTC
Pending review https://gerrit.libreoffice.org/c/core/+/144906 for 7-4
Comment 14 Eike Rathke 2023-01-04 18:36:03 UTC
*** Bug 151210 has been marked as a duplicate of this bug. ***
Comment 15 m.a.riosv 2023-01-05 01:57:08 UTC
*** Bug 152886 has been marked as a duplicate of this bug. ***
Comment 16 Commit Notification 2023-01-11 16:31:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-4":

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

Resolves: tdf#151886 Use default locale with English function names again

It will be available in 7.4.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 17 Commit Notification 2023-01-18 16:14:25 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/888ab228d8839ea5c52907da726e560687e257b0

tdf#151886: sc_uicalc: Add unittest

It will be available in 7.6.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 18 Xisco Faulí 2023-01-24 10:36:25 UTC
7.4.5 was a hotfix release, updating target in status-whiteboard