[MDEV-38774] TO_DATE: Incompatibilities between TO_CHAR and TO_DATE - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: N/A
Fix Version/s: 12.3
Component/s: Server
Labels:
None

Description

I would expect that double conversion date value => TO_CHAR => TO_DATE using the identical format string should always work, regardless possible differences with the standard and/or Oracle. However, it is not always the case.

The test case is below. It uses all languages declared as supported, and dates which represent each month and each weekday. for each combination, it runs 8 queries, checking separately MONTH, MON, DAY, and DY (returning the value after double conversion and additionally boolean to confirm that it's the same as the original date).

Whenever the double conversion succeeds, the values are correct.
However, in some cases TO_DATE fails to parse the result of TO_CHAR:

Czech: long June;
Japanese: short Jan-Sep;
Korean: short Jan-Sep;
Slovak: short Wed, Thu;
Tamil: short Tue, Thu, Fri, Sat;
Thai: All short month names, short Tue, Wed;
Turkish: long Friday, Sunday;
Ukrainian: long Friday;
Vietnamese: long March, long October-December, short Jan, long Tuesday;
Traditional/simplified Chinese: short Jan-Sep;

SET NAMES utf8mb4;

CREATE TABLE dates (dt DATE, purpose VARCHAR(32));

INSERT INTO dates VALUES

  ('2026-01-01', 'Jan, Thu'),

  ('2026-02-01', 'Feb, Sun'),

  ('2026-03-01', 'Mar, Sun'),

  ('2026-04-01', 'Apr, Wed'),

  ('2026-05-01', 'May, Fri'),

  ('2026-06-01', 'Jun, Mon'),

  ('2026-07-01', 'Jul, Wed'),

  ('2026-08-01', 'Aug, Sat'),

  ('2026-09-01', 'Sep, Tue'),

  ('2026-10-01', 'Oct, Thu'),

  ('2026-11-01', 'Nov, Sun'),

  ('2026-12-01', 'Dec, Tue')

CREATE TABLE lc_nls (lc CHAR(5), nls CHAR(32));

INSERT INTO lc_nls VALUES

  ('sq_AL', 'ALBANIAN'),

  ('en_US', 'AMERICAN'),

  ('ar_YE', 'ARABIC'),

  ('eu_ES', 'BASQUE'),

  ('be_BY', 'BELARUSIAN'),

  ('pt_BR', 'BRAZILIAN PORTUGUESE'),

  ('bg_BG', 'BULGARIAN'),

  ('fr_FR', 'CANADIAN FRENCH'),

  ('ca_ES', 'CATALAN'),

  ('hr_HR', 'CROATIAN'),

  ('sr_RS', 'CYRILLIC SERBIAN'),

  ('cs_CZ', 'CZECH'),

  ('da_DK', 'DANISH'),

  ('nl_BE', 'DUTCH'),

  ('en_GB', 'ENGLISH'),

  ('et_EE', 'ESTONIAN'),

  ('fi_FI', 'FINNISH'),

  ('fr_BE', 'FRENCH'),

  ('de_DE', 'GERMAN'),

  ('el_GR', 'GREEK'),

  ('he_IL', 'HEBREW'),

  ('hi_IN', 'HINDI'),

  ('hu_HU', 'HUNGARIAN'),

  ('is_IS', 'ICELANDIC'),

  ('id_ID', 'INDONESIAN'),

  ('it_IT', 'ITALIAN'),

  ('ja_JP', 'JAPANESE'),

  ('en_CA', 'KANNADA'),

  ('ko_KR', 'KOREAN'),

  ('es_ES', 'LATIN AMERICAN SPANISH'),

  ('lv_LV', 'LATVIAN'),

  ('lt_LT', 'LITHUANIAN'),

  ('mk_MK', 'MACEDONIAN'),

  ('ms_MY', 'MALAY'),

  ('es_MX', 'MEXICAN SPANISH'),

  ('nb_NO', 'NORWEGIAN'),

  ('pl_PL', 'POLISH'),

  ('pt_PT', 'PORTUGUESE'),

  ('ro_RO', 'ROMANIAN'),

  ('ru_RU', 'RUSSIAN'),

  ('zh_CN', 'SIMPLIFIED CHINESE'),

  ('sk_SK', 'SLOVAK'),

  ('sl_SI', 'SLOVENIAN'),

  ('es_ES', 'SPANISH'),

  ('sw_KE', 'SWAHILI'),

  ('sv_SE', 'SWEDISH'),

  ('ta_IN', 'TAMIL'),

  ('th_TH', 'THAI'),

  ('zh_CN', 'TRADITIONAL CHINESE'),

  ('tr_TR', 'TURKISH'),

  ('uk_UA', 'UKRAINIAN'),

  ('vi_VN', 'VIETNAMESE')

--delimiter $

CREATE PROCEDURE names()

BEGIN

  DECLARE loc CHAR(5);

  DECLARE lang VARCHAR(32);

  DECLARE no_data BOOL DEFAULT FALSE;

  DECLARE stmt TEXT;

  DECLARE fmt_long_month VARCHAR(32) DEFAULT 'MONTH DD, YYYY';

  DECLARE fmt_short_month VARCHAR(32) DEFAULT 'MON DD, YYYY';

  DECLARE fmt_long_day VARCHAR(32) DEFAULT 'YYYY-MM-DD, DAY';

  DECLARE fmt_short_day VARCHAR(32) DEFAULT 'YYYY-MM-DD, DY';

  DECLARE ln CURSOR FOR SELECT lc, nls FROM lc_nls ORDER BY nls;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = TRUE;

  OPEN ln;

  fetch_loop: LOOP

    FETCH ln INTO loc, lang;

    IF no_data THEN

      LEAVE fetch_loop;

    END IF;

    SELECT CONCAT("SET STATEMENT lc_time_names = ",loc," FOR ",

      "SELECT dt, purpose, "

        "TO_DATE(TO_CHAR(dt,'",fmt_long_month,"'),'",fmt_long_month,

          "','NLS_DATE_LANGUAGE=''",lang,"''') AS long_month, ",

        "DATE(TO_DATE(TO_CHAR(dt,'",fmt_long_month,"'),'",fmt_long_month,

          "','NLS_DATE_LANGUAGE=''",lang,"''')) = dt AS long_month_correct, ",

        "TO_DATE(TO_CHAR(dt,'",fmt_short_month,"'),'",fmt_short_month,

          "','NLS_DATE_LANGUAGE=''",lang,"''') AS short_month, ",

        "DATE(TO_DATE(TO_CHAR(dt,'",fmt_short_month,"'),'",fmt_short_month,

          "','NLS_DATE_LANGUAGE=''",lang,"''')) = dt AS short_month_correct, ",

        "TO_DATE(TO_CHAR(dt,'",fmt_long_day,"'),'",fmt_long_day,

          "','NLS_DATE_LANGUAGE=''",lang,"''') AS long_day, ",

        "DATE(TO_DATE(TO_CHAR(dt,'",fmt_long_day,"'),'",fmt_long_day,

          "','NLS_DATE_LANGUAGE=''",lang,"''')) = dt AS long_day_correct, ",

        "TO_DATE(TO_CHAR(dt,'",fmt_short_day,"'),'",fmt_short_day,

          "','NLS_DATE_LANGUAGE=''",lang,"''') AS short_day_correct, ",

        "DATE(TO_DATE(TO_CHAR(dt,'",fmt_short_day,"'),'",fmt_short_day,

          "','NLS_DATE_LANGUAGE=''",lang,"''')) = dt AS short_day_correct ",

        "FROM dates") INTO stmt;

    SELECT stmt;

    EXECUTE IMMEDIATE stmt;

  END LOOP;

  CLOSE ln;

END $

--delimiter ;

CALL names();

DROP PROCEDURE names;

DROP TABLE lc_nls, dates;

Example of the results (the whole result doesn't fit in the JIRA field and is attached as a file, for the reference:

bb-12.3-MDEV-19683-to_date d5fb3dccd72d816c7181a069b91eedb3c76b2715
+SET STATEMENT lc_time_names = vi_VN FOR SELECT dt, purpose, TO_DATE(TO_CHAR(dt,'MONTH DD, YYYY'),'MONTH DD, YYYY','NLS_DATE_LANGUAGE=''VIETNAMESE''') AS long_month, DATE(TO_DATE(TO_CHAR(dt,'MONTH DD, YYYY'),'MONTH DD, YYYY','NLS_DATE_LANGUAGE=''VIETNAMESE''')) = dt AS long_month_correct, TO_DATE(TO_CHAR(dt,'MON DD, YYYY'),'MON DD, YYYY','NLS_DATE_LANGUAGE=''VIETNAMESE''') AS short_month, DATE(TO_DATE(TO_CHAR(dt,'MON DD, YYYY'),'MON DD, YYYY','NLS_DATE_LANGUAGE=''VIETNAMESE''')) = dt AS short_month_correct, TO_DATE(TO_CHAR(dt,'YYYY-MM-DD, DAY'),'YYYY-MM-DD, DAY','NLS_DATE_LANGUAGE=''VIETNAMESE''') AS long_day, DATE(TO_DATE(TO_CHAR(dt,'YYYY-MM-DD, DAY'),'YYYY-MM-DD, DAY','NLS_DATE_LANGUAGE=''VIETNAMESE''')) = dt AS long_day_correct, TO_DATE(TO_CHAR(dt,'YYYY-MM-DD, DY'),'YYYY-MM-DD, DY','NLS_DATE_LANGUAGE=''VIETNAMESE''') AS short_day_correct, DATE(TO_DATE(TO_CHAR(dt,'YYYY-MM-DD, DY'),'YYYY-MM-DD, DY','NLS_DATE_LANGUAGE=''VIETNAMESE''')) = dt AS short_day_correct FROM dates
+dt purpose long_month long_month_correct short_month short_month_correct long_day long_day_correct short_day_correct short_day_correct
+2026-01-01 Jan, Thu 2026-01-01 00:00:00 1 NULL NULL 2026-01-01 00:00:00 1 2026-01-01 00:00:00 1
+2026-02-01 Feb, Sun 2026-02-01 00:00:00 1 2026-02-01 00:00:00 1 2026-02-01 00:00:00 1 2026-02-01 00:00:00 1
+2026-03-01 Mar, Sun NULL NULL 2026-03-01 00:00:00 1 2026-03-01 00:00:00 1 2026-03-01 00:00:00 1
+2026-04-01 Apr, Wed 2026-04-01 00:00:00 1 2026-04-01 00:00:00 1 2026-04-01 00:00:00 1 2026-04-01 00:00:00 1
+2026-05-01 May, Fri 2026-05-01 00:00:00 1 2026-05-01 00:00:00 1 2026-05-01 00:00:00 1 2026-05-01 00:00:00 1
+2026-06-01 Jun, Mon 2026-06-01 00:00:00 1 2026-06-01 00:00:00 1 2026-06-01 00:00:00 1 2026-06-01 00:00:00 1
+2026-07-01 Jul, Wed 2026-07-01 00:00:00 1 2026-07-01 00:00:00 1 2026-07-01 00:00:00 1 2026-07-01 00:00:00 1
+2026-08-01 Aug, Sat 2026-08-01 00:00:00 1 2026-08-01 00:00:00 1 2026-08-01 00:00:00 1 2026-08-01 00:00:00 1
+2026-09-01 Sep, Tue 2026-09-01 00:00:00 1 2026-09-01 00:00:00 1 NULL NULL 2026-09-01 00:00:00 1
+2026-10-01 Oct, Thu NULL NULL 2026-10-01 00:00:00 1 2026-10-01 00:00:00 1 2026-10-01 00:00:00 1
+2026-11-01 Nov, Sun NULL NULL 2026-11-01 00:00:00 1 2026-11-01 00:00:00 1 2026-11-01 00:00:00 1
+2026-12-01 Dec, Tue NULL NULL 2026-12-01 00:00:00 1 NULL NULL 2026-12-01 00:00:00 1

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

stdout.log.txt
128 kB
2026-02-07 13:30

Issue Links

is caused by

MDEV-19683 Add support for Oracle TO_DATE()

Approved

TO_DATE: Incompatibilities between TO_CHAR and TO_DATE

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration