Bug 143165 - [EDITING] When I type "5-d" into cell, it is autoconverted into date 2021/07/05
Summary: [EDITING] When I type "5-d" into cell, it is autoconverted into date 2021/07/05
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.0.4 release
Hardware: All All
: medium minor
Assignee: Eike Rathke
URL:
Whiteboard: target:7.3.0 target:7.2.0.0.beta2 tar...
Keywords:
Depends on:
Blocks:
 
Reported: 2021-07-02 19:34 UTC by zzz
Modified: 2021-07-22 18:27 UTC (History)
2 users (show)

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


Attachments
5-D_bug.ods (example data. filesize 13kB) (12.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-07-02 19:34 UTC, zzz
Details
5-D_bug_snap1.png (screenshot of 5-D_bug.ods) (15.17 KB, image/png)
2021-07-02 19:35 UTC, zzz
Details
LibreCalc_Options_Language.png (my option settings) (45.75 KB, image/png)
2021-07-02 19:36 UTC, zzz
Details
LibreCale_AutoCorrect_Options.png (my option settings) (21.31 KB, image/png)
2021-07-02 19:38 UTC, zzz
Details
LibreCale_AutoCorrect_Localized.png (my option settings) (24.01 KB, image/png)
2021-07-02 19:38 UTC, zzz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description zzz 2021-07-02 19:34:15 UTC
Created attachment 173317 [details]
5-D_bug.ods (example data. filesize 13kB)

Reproduction procedure:
  In any unformatted (General) empty cell, type "5-d" directly without the doublequotes.
  (five hyphen dee)

Expected result:
  Cell value will become implicit text type '5-d, displayed as 5-d

Actual result:
  Cell value becomes date type 2021/07/05 (5th of July, 2021),
  displayed "7月5日" on my instance.

My environment:
  The date of experiment: 2nd of July, 2021
  Windows 10 64bit in Japanese mode
  Libre Calc: UI:English(USA), locale:Japanese
  I Think I have turned off all the autochange switches, as much as possible.
  (If there already is a user switch to turn this feature off, it is too difficult to find.)

Reproducibility:
  100% (tested over 30 times)

Notes:
  * The range of numbers where this happens is only 1 to 12. "13-d" gives "13-d".
    1-d gives 2021/07/01 and so on.
  * The "d" part can be either small "d" or capital "D".
  * "/" (slash) in the place of "-" (hyphen) gives the same date.
  * Among the several other alphabets I tried, none behaved similarly.
  * Can be either direct typed into the cell, or typed into the edit box.
  * Can be either typed in by keyboard or pasted from clipboard.
  * Actually, the cell doesn't have to be empty. Just type in.
  * Behavior differs by cell format:
      Date mode: Same date
      E+, Currency mode: Some number (probably date code)
      % mode: "5-d%"
  * Microsoft Excel 2000 and 365 doesn't show this behaviour. "5-d" is just "5-d".

Very strange.
Comment 1 zzz 2021-07-02 19:35:52 UTC
Created attachment 173318 [details]
5-D_bug_snap1.png (screenshot of 5-D_bug.ods)
Comment 2 zzz 2021-07-02 19:36:57 UTC
Created attachment 173319 [details]
LibreCalc_Options_Language.png (my option settings)
Comment 3 zzz 2021-07-02 19:38:00 UTC
Created attachment 173320 [details]
LibreCale_AutoCorrect_Options.png (my option settings)
Comment 4 zzz 2021-07-02 19:38:45 UTC
Created attachment 173321 [details]
LibreCale_AutoCorrect_Localized.png (my option settings)
Comment 5 Mike Kaganski 2021-07-02 19:50:43 UTC
I am reasonably sure this is not a bug, and 5-d likely is a valid date pattern in your locale, which means that for any cell formatted as number, the text is automatically recognized and converted to the date value.

If this is the case, you should not play with autocorrect options (because what you see is *not* autocorrection, but recognition of numeric data), and instead simply pre-format your cells where you put textual data as Text (Format Cells->Numbers; select Text category). This makes sure that the data you enter is not attempted to be converted to numbers.

There's some chance that I'm wrong, and 5-d is *not* a valid date pattern for your locale; maybe Eike can clarify?
Comment 6 Ming Hua 2021-07-02 23:58:20 UTC
Reproduced with 7.0.6 and simplified Chinese (zh-CN) locale:
Version: 7.0.6.2 (x64)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 2; OS: Windows 10.0 Build 19041; UI render: default; VCL: win
Locale: zh-CN (zh_CN); UI: en-US
Calc: threaded
...with both the attached sample ODS file and a fresh new spreadsheet.

I agree with Mike's diagnosis in comment #5, that this is not AutoCorrection, but instead Calc converting inputted strings as dates according to locale-specific settings.  However I also consider such conversion defective, as the patterns for converting to dates in zh-CN locale is (Tools > Options > Language Settings > Languages > Formats > Date acceptance patterns):
"Y/M/D;M-D;M/D;M/D;Y.M.D;Y/M/D;Y年M月D日;M月D日"
and it doesn't make sense that "5-D" would be treated as matching this pattern, using the literal "D" in the input to match the "D" format code (and converting it to the current month July anyway).
Comment 7 Ming Hua 2021-07-03 00:09:21 UTC
FWIW, when switched to en-US locale, and the date acceptance pattern is just "M/D/Y;M/D", input "5-D" doesn't trigger this bug anymore, but "5/D" still does.  Similarly, input "5/D/Y" is also converted, to "7/5/21", which means "July 5th, 2021".  Input "5/7/Y" is properly converted to "5/7/21", which means "May 7th, 2021".  Input "M/5" or "M/5/Y" is not converted.

Something is definitely wrong here.
Comment 8 zzz 2021-07-03 02:05:09 UTC
Thanks guys for clues.
I found there is indeed "M-D" and "D/M" in the preset date acceptance patterns, as you can see in my uploaded screenshot LibreCalc_Options_Language.png.
I will make it clear that, for Japanese locale too, treating the literals "-d","-D","/d","D" as part of a date is unnecessary and unwanted.
Comment 9 Commit Notification 2021-07-05 11:58:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/89a3caee73c476133af804d9a0a650e72e711d95

Resolves: tdf#143165 Date input must match separator, D,M,Y not part of

It will be available in 7.3.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 Commit Notification 2021-07-05 13:15:26 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

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

Resolves: tdf#143165 Date input must match separator, D,M,Y not part of

It will be available in 7.2.0.0.beta2.

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 2021-07-05 13:37:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

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

Resolves: tdf#143165 Date input must match separator, D,M,Y not part of

It will be available in 7.1.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.
Comment 13 Commit Notification 2021-07-05 15:32:33 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#143165: svl_qa_cppunit: Add unittest

It will be available in 7.3.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 14 Commit Notification 2021-07-06 09:04:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1-5":

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

Resolves: tdf#143165 Date input must match separator, D,M,Y not part of

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