Bug 126342 - [Calc] Date acceptance pattern error
Summary: [Calc] Date acceptance pattern error
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.5.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.4.0 target:6.3.0.2 target:6.2.6
Keywords: regression
Depends on:
Blocks:
 
Reported: 2019-07-11 11:21 UTC by Lenge
Modified: 2019-07-18 15:22 UTC (History)
1 user (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 Lenge 2019-07-11 11:21:50 UTC
Description:
One of the recent LO releases (I suspect 6.2.5 or 6.2.4) introduced the following misbehavior when entering a date into a Calc cell. I have never observed this bug in earlier releases.

Steps to Reproduce:
1. Format a cell as
- Category = Date
- Format = 1999-12-31 (Format code "JJJJ-MM-TT", ISO 8601)
- Language = German (Germany)

2. Click the empty cell and enter a date using German style "<Day>.<Month>.<2-digit year>", e. g. "11.7.19".
The pattern is correctly recognized and displayed as "2019-07-11", as it has always been in previous versions of LO.

3. Now that the cell is no longer empty, click it again and overwrite the existing date with the exact same input as above, "11.7.19".

Actual Results:
The same input is now incorrectly interpreted as "<Year>.<Month>.<Day>" and displayed as "2011-07-19" (instead of the correct "2019-07-11").

Expected Results:
The input should have been correctly interpreted as "2019-07-11" again. Date acceptance patterns shouldn't depend on the previous content of a cell.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 6.2.5.2 (x64)
Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159
CPU threads: 8; OS: Windows 6.3; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
Comment 1 stragu 2019-07-11 13:36:59 UTC
Confirmed as described with:

Version: 6.3.0.1
Build ID: 41ac97386aba908b6db860cfb4cfe2da871886ae
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-AU (en_AU.UTF-8); UI-Language: en-US
Calc: threaded

I also tried a similar thing with language as "English (Australia)" instead, and the date entered in the cell as 11/7/19: the same thing happens. First time, it is interpreted as expected. Second time, day and year are reversed.

So it seems it is not language-specific!
Comment 2 Eike Rathke 2019-07-11 14:14:30 UTC
Life would be easier if the date was input like displayed in this case..
On one hand users want be able to do that, on the other hand complain if they don't. It works on an empty cell because there is no visible indication whatsoever for the user which date particle order is expected and then the LibreOffice locale's order is preferred. For already existing date content the display format's date particle order is preferred. And yes, this is somewhat locale specific because in these  cases the input locale's DMY or MDY order doesn't match the display format's YMD order, which leads to this effect.

Taking a stab at this.
Comment 3 Lenge 2019-07-11 15:13:38 UTC
I propose to handle it this way:

1.) In neither case shall the current cell content have any impact on how a date input is interpreted. This should only be influenced by (a) the cell format and (b) the language/locale settings.

2.) If a date is input according to the cell format (= using the same separator as the cell format), then it should be interpreted accordingly, regardless of language/locale settings.
Example: If a cell is ISO formatted (YYYY-MM-DD), then any input using "-" as separator should be interpreted as "Y-M-D".

3.) If a date is input using different separators than the cell format, the language/locale settings should be applied.
Example: In German language, any input using "." as separator should be interpreted as "D.M.Y".

I deal a lot with ISO formatted date cells in German language and have always been able to enter the dates either as "Y-M-D" or as "D.M.Y" with both being correctly accepted by Calc.
Comment 4 Eike Rathke 2019-07-11 19:51:05 UTC
(In reply to Lenge from comment #3)
> 1.) In neither case shall the current cell content have any impact on how a
> date input is interpreted. This should only be influenced by (a) the cell
> format and (b) the language/locale settings.
As said, with an empty or text content there is no indicator for the user what date input could be accepted. So preferring the current LibreOffice locale is logical, and is what always was done. Now when there is already date content, the date is expected to be edited in the edit format of the display format's locale (MM/DD/YYYY for en-US, DD/MM/YYYY for en-GB, DD.MM.YYYY for de-DE, ...)


> 2.) If a date is input according to the cell format (= using the same
> separator as the cell format), then it should be interpreted accordingly,
> regardless of language/locale settings.
> Example: If a cell is ISO formatted (YYYY-MM-DD), then any input using "-"
> as separator should be interpreted as "Y-M-D".
Only the separator is not a sufficient criterion. Consider en-US MM/DD/YY vs en-GB DD/MM/YY, for the ISO case also an underlying current locale with D-M-Y pattern. They have both to be considered and you're lucky if the year was a 4-digit input and the day >=13.


> 3.) If a date is input using different separators than the cell format, the
> language/locale settings should be applied.
> Example: In German language, any input using "." as separator should be
> interpreted as "D.M.Y".
Which actually depends also on the active date acceptance patterns, not (just) the locale. The default date acceptance patterns depend on the locale, but can be changed. Again, things get complicated if, for example, the cell is formatted for a German locale with D.M.Y pattern, but the default LibreOffice locale has Y.M.D pattern.


> I deal a lot with ISO formatted date cells in German language and have
> always been able to enter the dates either as "Y-M-D" or as "D.M.Y" with
> both being correctly accepted by Calc.
Earlier there were only two possibilities: date input either according to the current locale and date acceptance patterns, or in ISO format. The cell format was never considered. "Easy" (except when a locale pattern is D-M-Y or M-D-Y ..). Then users wanted to be able to edit a differently formatted date in the locale it is displayed. They also wanted to be able to enter a date over an existing one in the same format. And at the same time understandably everyone wants to be able to enter a date according to the current locale settings.
Comment 5 Commit Notification 2019-07-12 11:53:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/7de96e9f7b69354fd5b15e1276678000bc386568%5E%21

Resolves: tdf#126342 obtain date particle order from pattern match

It will be available in 6.4.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 6 Eike Rathke 2019-07-12 11:56:21 UTC
Pending review
https://gerrit.libreoffice.org/75490 for 6-3
https://gerrit.libreoffice.org/75491 for 6-2
Comment 7 Commit Notification 2019-07-15 11:37:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/71b7e419b705b421443981e222ab5968cab4decb%5E%21

Resolves: tdf#126342 obtain date particle order from pattern match

It will be available in 6.3.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 8 Commit Notification 2019-07-18 15:22:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/7af89de46ff4a40a8161fd629584d54cfdeadee1%5E%21

Resolves: tdf#126342 obtain date particle order from pattern match

It will be available in 6.2.6.

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.