Hello Monty,
The patch looks good. I noticed a few things though.
- Oracle's TO_DATE() always returns Oracle's DATE data type.
Which in MariaDB is DATETIME(0).
So in MariaDB, TO_DATE() should never return MariaDB's DATE
for compatibility purposes:
Oracle does not have MariaDB style DATE data type.
- Oracle's TO_DATE() does not support the FF format specifier.
Oracle returns an error on attempts to use it.
To be strictly compatible, MariaDB could also return an error.
Otherwise, if we want to support FF, it should be clearly documented.
- The function uses session variables @@timezone, @@timestamp,
@@lc_time_names.
Therefore it's not deterministic. It should not be allowed as a
vcol virtual or vcol indexed expression.
This statement is allowed, which is wrong:
create or replace table t1 (
c1 varchar(10),
c2 datetime as (to_date(c1,'MM')) virtual,
key(c2));
- The same reason (the use of @@timezone, @@timestamp, @lc_time_names)
makes it not deterministic for replication purposes.
It should a have a five-argument version so when put into
the binlog it prints itself in a deterministic way:
TO_DATE(subject, format, on_error_value, nlsformat, default_date)
For example:
TO_DATE('10','MM', '', 'NLS_DATE_LANGUAGE = German', '2001-02-03')
The extra arguments are:
- nlsformat - to transfer @@lc_time_names (the locale) in Oracle
compatible way
- default_date - to get the YYYY-MM-DD components when they are not
specified in the format.
- Please add binlog and replication tests
I realized that after addressing the above suggestions, the patch can
change.
So I haven't reviewed the patch very thoroughly. It'll need another review
round.
Please also see comments inline though:
commit 9deae06144f712821a45ae0e23f41d8789c97dfb
> Author: Michael Widenius <[email protected]>
> Date: Thu Oct 30 20:24:17 2025 +0200
>
> MDEV-19683 Add support for Oracle TO_DATE()
>
> Syntax:
> TO_DATE(string_expression [DEFAULT string_expression ON
CONVERSION ERROR],
> format_string)
> The format_string has the same format elements as TO_CHAR(), except a
> few elements that are not supported.
> TO_DATE() returns a datetime or date value, depending on the format
> elements used.
>
> Allowed separators, same as tochar():
> space, tab and any of !#%'()*+,-./:;<=>
>
> '&' can also be used if next character is not a character a-z or A-Z
> "text' indicates a text string that is verbatim in the format.
One cannot
> use " as a separator.
>
> Format elements supported by TO_DATE():
> AD Anno Domini ("in the year of the Lord")
> AD_DOT Anno Domini ("in the year of the Lord")
> AM Meridian indicator (Before midday)
> AM_DOT Meridian indicator (Before midday)
> DAY Name of day
> DD Day (1-31)
> DDD Day of year (1-336)
> DY Abbreviated name of day
> FF[1-6] Fractional seconds
Supporting FF is not compatible with Oracle.
> HH Hour (1-12)
> HH12 Hour (1-12)
> HH24 Hour (0-23)
> IW Week of year (1-53). Used with I, IY...IYYY. ISO 8601
> MI Minutes (0-59)
> MM Month (1-12)
> MON Abbreviated name of month
> MONTH Name of Month
> PM Meridian indicator (After midday)
> PM_DOT Meridian indicator (After midday)
> RR 20th century dates in the 21st century. 2 digits
> 50-99 is assumed from 2000, 0-49 is assumed from 1900.
> RRRR 20th century dates in the 21st century. 4 digits
> SS Seconds
> Y 1 digit year
> YY 2 digits year
> YYY 3 digits year
> YYYY 4 digits year
>
> Note that if there is a missing part of the date, the current
date is used!
> For example if 'MM-DD HH-MM-SS' then the current year will be used.
>
> Not supported:
> BC, D, DL, DS, E, EE, FM, FX, RM, SSSSS, TS, TZD, TZH, TZR, X,SY
> BC is not supported by MariaDB datetime.
> Most of the other are exotic formats, format modifies other
things that
> does not make in MariaDB as we return a date or a datetime, not
string.
> D (day-of-week) is not supported as it is not clear exactly how
it would
> map to MariaDB. This element depends on the NLS territory of the
session.
>
> New formats handled by TO_CHAR():
> FF[1-6] Fractional seconds
> DDD Daynumber 1-366
> IW Week 1-53 according to ISO 8601
> I 1 digit year according to ISO 8601
> IY 2 digit year according to ISO 8601
> IYY 3 digit year according to ISO 8601
> IYYY 4 digit year according to ISO 8601
>
> diff --git a/mysql-test/suite/compat/oracle/r/func_to_char.result
b/mysql-test/suite/compat/oracle/r/func_to_char.result
> index d10c6265f26..d974014dbc5 100644
> --- a/mysql-test/suite/compat/oracle/r/func_to_char.result
> +++ b/mysql-test/suite/compat/oracle/r/func_to_char.result
> @@ -154,6 +154,43 @@ A.D.9999-12-31 PM11:59:59 A.D..9999-12-31
P.M.23:59:59
> A.D.2021-01-03 AM08:30:00 A.D..2021-01-03 A.M.08:30:00
> A.D.2021-07-03 PM06:30:00 A.D..2021-07-03 P.M.18:30:00
> #
> +# Test FF
> +#
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF');
> +TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF')
> +2025-10-11 12:10:14.123456
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF6');
> +TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF6')
> +2025-10-11 12:10:14.123456
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF5');
> +TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF5')
> +2025-10-11 12:10:14.12345
> +#
> +# Test DDD, I*
> +#
> +select c3,to_char(c3, 'DDD') from t_to_char1 where c0 = 3;
> +c3 to_char(c3, 'DDD')
> +2021-01-03 08:30:00 003
> +select c3,to_char(c3, 'I') from t_to_char1 where c0 = 3;
> +c3 to_char(c3, 'I')
> +2021-01-03 08:30:00 0
> +select c3,to_char(c3, 'I-MM') from t_to_char1 where c0 = 3;
> +c3 to_char(c3, 'I-MM')
> +2021-01-03 08:30:00 0-01
> +select c3,to_char(c3, 'IY') from t_to_char1 where c0 = 3;
> +c3 to_char(c3, 'IY')
> +2021-01-03 08:30:00 20
> +select c3,to_char(c3, 'IYY-MM') from t_to_char1 where c0 = 3;
> +c3 to_char(c3, 'IYY-MM')
> +2021-01-03 08:30:00 020-01
> +select c3, to_char(c3, 'IYYY-IW') from t_to_char1 where c0 between 3
and 4;
> +c3 to_char(c3, 'IYYY-IW')
> +2021-01-03 08:30:00 2020-53
> +2021-07-03 18:30:00 2021-26
> +select to_char("2001-01-01", "IYYY-IW");
> +to_char("2001-01-01", "IYYY-IW")
> +2001-1
> +#
> # test format without order
> #
> SELECT TO_CHAR(c1, 'MM-YYYY-DD') AS C1, TO_CHAR(c2, 'HH:SS:MI') AS
C2, TO_CHAR(c3, 'DD-YY-MM MI:SS:HH24') AS C3 FROM t_to_char1;
> @@ -275,6 +312,10 @@ select to_char(c3, 'YYYY&DDD') from t_to_char1
where c0 = 1;
> ERROR HY000: Invalid argument error: date format not recognized at
&DDD in function to_char.
> select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
> ERROR HY000: Invalid argument error: date format not recognized at
xxYYYY-D in function to_char.
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF7');
> +ERROR HY000: Invalid argument error: date format not recognized at 7
in function to_char.
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF0');
> +ERROR HY000: Invalid argument error: date format not recognized at 0
in function to_char.
> SET character_set_client='latin1';
> SET character_set_connection='latin1';
> SET character_set_results='latin1';
> @@ -289,10 +330,10 @@ to_char(c3, 'YYYYYYYYYYYYYYY')
> 202120212021021
> select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1;
> to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD')
> -100010001000000010101
> -999999999999999313131
> -202120212021021030303
> -202120212021021030303
> +100010001000000001001
> +999999999999999365365
> +202120212021021003003
> +202120212021021184184
> #
> # oracle max length is 144
> #
> @@ -325,16 +366,10 @@ ERROR 42000: Incorrect parameter count in the
call to native function 'to_char'
> #
> # oracle support format but mariadb does not support
> #
> -select to_char(c3, 'DDD') from t_to_char1 where c0 = 1;
> -ERROR HY000: Invalid argument error: date format not recognized at D
in function to_char.
> select to_char(c3, 'D') from t_to_char1 where c0 = 1;
> ERROR HY000: Invalid argument error: date format not recognized at D
in function to_char.
> select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
> ERROR HY000: Invalid argument error: date format not recognized at
DS in function to_char.
> -select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
> -ERROR HY000: Invalid argument error: date format not recognized at
IY in function to_char.
> -select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
> -ERROR HY000: Invalid argument error: date format not recognized at
IYYY in function to_char.
> #
> # test for first argument data type
> #
> diff --git a/mysql-test/suite/compat/oracle/r/func_to_date.result
b/mysql-test/suite/compat/oracle/r/func_to_date.result
> new file mode 100644
> index 00000000000..b6b82389444
> --- /dev/null
> +++ b/mysql-test/suite/compat/oracle/r/func_to_date.result
> @@ -0,0 +1,167 @@
> +SET time_zone=_latin1'+03:00';
> +SET timestamp=1100000000;
> +SELECT NOW();
> +NOW()
> +2004-11-09 14:33:20
> +SELECT TO_DATE('2025/10/26', 'YYYY/MM/DD') AS result;
> +result
> +2025-10-26
> +SELECT TO_DATE('26-OCT-25', 'DD-MON-RR') AS result;
> +result
> +2025-10-26
> +SELECT TO_DATE('October 26, 2025', 'MONTH DD, YYYY') AS result;
> +result
> +2025-10-26
> +# Two-Digit Year (RR)
> +SELECT TO_DATE('26-OCT-60', 'DD-MON-RR') AS result;
> +result
> +1960-10-26
> +SELECT TO_DATE('26-OCT-25', 'DD-MON-RR') AS result;
> +result
> +2025-10-26
> +# Day of Year (DDD)
> +SELECT TO_DATE('2025-300', 'YYYY-DDD') AS result;
> +result
> +2025-10-27
> +# Time Formats (HH24, MI, SS)
> +SELECT TO_DATE('14:30:25', 'HH24:MI:SS') AS result;
> +result
> +2004-11-09 14:30:25
> +SELECT TO_DATE('08:30:25', 'HH:MI:SS') AS result;
> +result
> +2004-11-09 08:30:25
> +SELECT TO_DATE('08:30:25 PM', 'HH:MI:SS AM') AS result;
> +result
> +2004-11-09 20:30:25
> +# Day of Week (DAY, DY). Uses current day
> +SELECT TO_DATE('Tuesday', 'DAY') AS result;
> +result
> +2004-11-09
> +SELECT TO_DATE('26-OCT-25 Sunday', 'DD-MON-YY DAY') AS result;
> +result
> +2025-10-26
> +# Month of Year (MONTH, MON)
> +SELECT TO_DATE('October', 'MONTH') AS result;
> +result
> +2004-10-09
> +SELECT TO_DATE('Oct', 'MON') AS result;
> +result
> +2004-10-09
> +# Fractional Seconds (FF)
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF') AS result
> +;
> +result
> +2025-10-26 10:30:25.123456
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF6') AS result;
> +result
> +2025-10-26 10:30:25.123456
> +SELECT TO_DATE('2025-10-26 10:30:25.123', 'YYYY-MM-DD HH:MI:SS.FF')
AS result;
> +result
> +2025-10-26 10:30:25.123000
> +SELECT TO_DATE('2025-10-26 10:30:25.12', 'YYYY-MM-DD HH:MI:SS.FF2')
AS result;
> +result
> +2025-10-26 10:30:25.120000
> +SELECT TO_DATE('2025-10-26 10:30:25.129', 'YYYY-MM-DD HH:MI:SS.FF2')
AS result;
> +result
> +2025-10-26 10:30:25.120000
> +Warnings:
> +Warning 1292 Truncated incorrect datetime value: '2025-10-26
10:30:25.129'
> +SELECT TO_DATE('2025-10-26 10:30:123:34', 'YYYY-MM-DD HH:MI:FF3:SS')
AS result;
> +result
> +2025-10-26 10:30:34.123000
> +#AD
> +SELECT TO_DATE('26-10-1000 AD', 'DD-MM-YYYY AD') AS result;
> +result
> +1000-10-26
> +NULL
> +SELECT TO_DATE(NULL, 'YYYY-MM-DD') AS result;
> +result
> +NULL
> +#
> +# Fuzzy string
> +
> +SELECT TO_DATE('0207','MM/YY');
> +TO_DATE('0207','MM/YY')
> +2007-02-09
> +SELECT TO_DATE('02#07','MM/YY');
> +TO_DATE('02#07','MM/YY')
> +2007-02-09
> +# Tab and space
> +SELECT TO_DATE('25 05 3','YY MM DD');
> +TO_DATE('25 05 3','YY MM DD')
> +2025-05-03
> +# Quotes
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD "hello"
HH24:MI:SS');
> +TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD "hello" HH24:MI:SS')
> +2023-01-02 03:04:05
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD HH24:MI:SS');
> +TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD HH24:MI:SS')
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: '2023-01-02 hello 03:04:05'
for function str_to_date
> +select TO_DATE('2023-01-02 03:04:05', 'YYYY-MM-DD "hello" HH24:MI:SS');
> +TO_DATE('2023-01-02 03:04:05', 'YYYY-MM-DD "hello" HH24:MI:SS')
> +2023-01-02 03:04:05
> +select TO_DATE('2023-01-02 "03:04:05"', 'YYYY-MM-DD
""""HH24:MI:SS""""');
> +TO_DATE('2023-01-02 "03:04:05"', 'YYYY-MM-DD """"HH24:MI:SS""""')
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: '2023-01-02 "03:04:05"' for
function str_to_date
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD hello
HH24:MI:SS');
> +ERROR HY000: Invalid argument error: date format not recognized at
hello HH in function to_date.
> +#
> +# Error Handling (Invalid/not supported formats)
> +
> +SELECT TO_DATE('Not a date', 'YYYY-MM-DD') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: 'Not a date' for function
str_to_date
> +SELECT TO_DATE('2025-02-30', 'YYYY-MM-DD') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: '2025-02-30' for function
str_to_date
> +SELECT TO_DATE('26-10-1000 BC', 'DD-MM-YYYY BC') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: '26-10-1000 BC' for function
str_to_date
> +SELECT TO_DATE('Monday', 'DAY') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: 'Monday' for function str_to_date
> +SET timestamp=unix_timestamp("2025-01-31");
> +SELECT TO_DATE('January', 'MONTH') AS result;
> +result
> +2025-01-31
> +SELECT TO_DATE('February', 'MONTH') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: 'February' for function
str_to_date
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF0') AS result;
> +ERROR HY000: Invalid argument error: date format not recognized at 0
in function to_date.
> +SELECT TO_DATE('2025-10-26 26', 'YYYY-MM-DD DD') AS result;
> +ERROR HY000: Invalid argument error: date format not recognized at
DD in function to_date.
> +#
> +# Default handling
> +#
> +SELECT TO_DATE(NULL DEFAULT '2024-01-02' ON CONVERSION ERROR,
'YYYY-MM-DD') AS result;
> +result
> +2024-01-02
> +SELECT TO_DATE('not a date' DEFAULT '2024-01-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> +result
> +2024-01-02
> +SELECT TO_DATE('2025' DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD') AS result;
> +result
> +NULL
> +SELECT TO_DATE('2025-13-02' DEFAULT '2025-12-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> +result
> +2025-12-02
> +SELECT TO_DATE('2025-13-02' DEFAULT '2025-14-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> +result
> +NULL
> +Warnings:
> +Warning 1411 Incorrect datetime value: '2025-14-02' for function
str_to_date
> diff --git a/mysql-test/suite/compat/oracle/t/func_to_char.test
b/mysql-test/suite/compat/oracle/t/func_to_char.test
> index df5d0065f50..58cfab45191 100644
> --- a/mysql-test/suite/compat/oracle/t/func_to_char.test
> +++ b/mysql-test/suite/compat/oracle/t/func_to_char.test
> @@ -63,6 +63,27 @@ SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1,
TO_CHAR(c2, 'pmHH:MI:SS') AS C2, TO_
> SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2,
'AMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD A.m.HH24:MI:SS') AS C3
FROM t_to_char1;
> SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2,
'amHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD a.M.HH24:MI:SS') AS C3
FROM t_to_char1;
>
> +
> +--echo #
> +--echo # Test FF
> +--echo #
> +
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF');
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF6');
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF5');
> +
> +--echo #
> +--echo # Test DDD, I*
> +--echo #
> +
> +select c3,to_char(c3, 'DDD') from t_to_char1 where c0 = 3;
> +select c3,to_char(c3, 'I') from t_to_char1 where c0 = 3;
> +select c3,to_char(c3, 'I-MM') from t_to_char1 where c0 = 3;
> +select c3,to_char(c3, 'IY') from t_to_char1 where c0 = 3;
> +select c3,to_char(c3, 'IYY-MM') from t_to_char1 where c0 = 3;
> +select c3, to_char(c3, 'IYYY-IW') from t_to_char1 where c0 between 3
and 4;
> +select to_char("2001-01-01", "IYYY-IW");
> +
> --echo #
> --echo # test format without order
> --echo #
> @@ -118,6 +139,10 @@ select to_char(c3, 'YYYYxDDD') from t_to_char1
where c0 = 1;
> select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1;
> --error ER_STD_INVALID_ARGUMENT
> select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
> +--error ER_STD_INVALID_ARGUMENT
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF7');
> +--error ER_STD_INVALID_ARGUMENT
> +SELECT TO_CHAR('2025-10-11 12:13:14.123456', 'YYYY-MM-DD HH:MM:SS.FF0');
>
> SET character_set_client='latin1';
> SET character_set_connection='latin1';
> @@ -154,15 +179,9 @@ select to_char(c3, "YYYY-MM-DD HH:MI:SS",
"NLS_DATE_LANGUAGE = zh_CN") from t_to
> --echo # oracle support format but mariadb does not support
> --echo #
> --error ER_STD_INVALID_ARGUMENT
> -select to_char(c3, 'DDD') from t_to_char1 where c0 = 1;
> ---error ER_STD_INVALID_ARGUMENT
> select to_char(c3, 'D') from t_to_char1 where c0 = 1;
> --error ER_STD_INVALID_ARGUMENT
> select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
> ---error ER_STD_INVALID_ARGUMENT
> -select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
> ---error ER_STD_INVALID_ARGUMENT
> -select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
>
> --echo #
> --echo # test for first argument data type
> diff --git a/mysql-test/suite/compat/oracle/t/func_to_date.test
b/mysql-test/suite/compat/oracle/t/func_to_date.test
> new file mode 100644
> index 00000000000..5620c803c52
> --- /dev/null
> +++ b/mysql-test/suite/compat/oracle/t/func_to_date.test
> @@ -0,0 +1,94 @@
> +# Test script for an Oracle TO_DATE() function
> +# Testing of all format elements and various edge cases.
> +
> +SET time_zone=_latin1'+03:00';
> +# Ensure all date parts are consistent and different
> +SET timestamp=1100000000;
> +SELECT NOW();
> +
> +SELECT TO_DATE('2025/10/26', 'YYYY/MM/DD') AS result;
> +SELECT TO_DATE('26-OCT-25', 'DD-MON-RR') AS result;
> +SELECT TO_DATE('October 26, 2025', 'MONTH DD, YYYY') AS result;
> +
> +--echo # Two-Digit Year (RR)
> +SELECT TO_DATE('26-OCT-60', 'DD-MON-RR') AS result;
> +SELECT TO_DATE('26-OCT-25', 'DD-MON-RR') AS result;
> +
> +--echo # Day of Year (DDD)
> +SELECT TO_DATE('2025-300', 'YYYY-DDD') AS result;
> +
> +--echo # Time Formats (HH24, MI, SS)
> +SELECT TO_DATE('14:30:25', 'HH24:MI:SS') AS result;
> +SELECT TO_DATE('08:30:25', 'HH:MI:SS') AS result;
> +SELECT TO_DATE('08:30:25 PM', 'HH:MI:SS AM') AS result;
> +
> +--echo # Day of Week (DAY, DY). Uses current day
> +SELECT TO_DATE('Tuesday', 'DAY') AS result;
> +SELECT TO_DATE('26-OCT-25 Sunday', 'DD-MON-YY DAY') AS result;
> +
> +--echo # Month of Year (MONTH, MON)
> +SELECT TO_DATE('October', 'MONTH') AS result;
> +SELECT TO_DATE('Oct', 'MON') AS result;
> +
> +--echo # Fractional Seconds (FF)
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF') AS result
> +;
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF6') AS result;
> +SELECT TO_DATE('2025-10-26 10:30:25.123', 'YYYY-MM-DD HH:MI:SS.FF')
AS result;
> +SELECT TO_DATE('2025-10-26 10:30:25.12', 'YYYY-MM-DD HH:MI:SS.FF2')
AS result;
> +SELECT TO_DATE('2025-10-26 10:30:25.129', 'YYYY-MM-DD HH:MI:SS.FF2')
AS result;
> +SELECT TO_DATE('2025-10-26 10:30:123:34', 'YYYY-MM-DD HH:MI:FF3:SS')
AS result;
> +
> +--echo #AD
> +SELECT TO_DATE('26-10-1000 AD', 'DD-MM-YYYY AD') AS result;
> +
> +--echo NULL
> +SELECT TO_DATE(NULL, 'YYYY-MM-DD') AS result;
> +
> +--echo #
> +--echo # Fuzzy string
> +--echo
> +
> +SELECT TO_DATE('0207','MM/YY');
> +SELECT TO_DATE('02#07','MM/YY');
> +
> +--echo # Tab and space
> +SELECT TO_DATE('25 05 3','YY MM DD');
> +
> +--echo # Quotes
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD "hello"
HH24:MI:SS');
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD HH24:MI:SS');
> +select TO_DATE('2023-01-02 03:04:05', 'YYYY-MM-DD "hello" HH24:MI:SS');
> +select TO_DATE('2023-01-02 "03:04:05"', 'YYYY-MM-DD
""""HH24:MI:SS""""');
> +--error ER_STD_INVALID_ARGUMENT
> +select TO_DATE('2023-01-02 hello 03:04:05', 'YYYY-MM-DD hello
HH24:MI:SS');
> +
> +--echo #
> +--echo # Error Handling (Invalid/not supported formats)
> +--echo
> +
> +SELECT TO_DATE('Not a date', 'YYYY-MM-DD') AS result;
> +SELECT TO_DATE('2025-02-30', 'YYYY-MM-DD') AS result;
> +SELECT TO_DATE('26-10-1000 BC', 'DD-MM-YYYY BC') AS result;
> +SELECT TO_DATE('Monday', 'DAY') AS result;
> +
> +SET timestamp=unix_timestamp("2025-01-31");
> +SELECT TO_DATE('January', 'MONTH') AS result;
> +SELECT TO_DATE('February', 'MONTH') AS result;
> +
> +--error ER_STD_INVALID_ARGUMENT
> +SELECT TO_DATE('2025-10-26 10:30:25.123456', 'YYYY-MM-DD
HH:MI:SS.FF0') AS result;
> +--error ER_STD_INVALID_ARGUMENT
> +SELECT TO_DATE('2025-10-26 26', 'YYYY-MM-DD DD') AS result;
> +
> +--echo #
> +--echo # Default handling
> +--echo #
> +
> +SELECT TO_DATE(NULL DEFAULT '2024-01-02' ON CONVERSION ERROR,
'YYYY-MM-DD') AS result;
> +SELECT TO_DATE('not a date' DEFAULT '2024-01-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> +
> +SELECT TO_DATE('2025' DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD') AS result;
> +
> +SELECT TO_DATE('2025-13-02' DEFAULT '2025-12-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> +SELECT TO_DATE('2025-13-02' DEFAULT '2025-14-02' ON CONVERSION
ERROR, 'YYYY-MM-DD') AS result;
> diff --git a/sql/item_func.h b/sql/item_func.h
> index 0be8cf61591..1fe2fb8ee59 100644
> --- a/sql/item_func.h
> +++ b/sql/item_func.h
> @@ -874,6 +874,8 @@ class Item_handled_func: public Item_func
> :Item_func(thd, a), m_func_handler(NULL) { }
> Item_handled_func(THD *thd, Item *a, Item *b)
> :Item_func(thd, a, b), m_func_handler(NULL) { }
> + Item_handled_func(THD *thd, Item *a, Item *b, Item *c)
> + :Item_func(thd, a, b, c), m_func_handler(NULL) { }
> void set_func_handler(const Handler *handler)
> {
> m_func_handler= handler;
> @@ -918,6 +920,12 @@ class Item_handled_func: public Item_func
> {
> return m_func_handler->val_native(thd, this, to);
> }
> + virtual bool get_date_common(THD *thd, MYSQL_TIME *ltime,
> + date_mode_t fuzzydate, timestamp_type)
> + {
> + DBUG_ASSERT(0);
> + return 0;
> + }
Please don't add this virtual method.
Item_handled_func is a generic class, for functions returning any data type,
not only DATE/TIME. It must stay as clean as possible and should not contain
extra data type specific methods.
Adding get_date_common() breaks this idea.
I will think of another ways to avoid the cast in the future.
For now, please don't use "virtual" with this method.
You need to make a new class Func_handler_to_date which can
derive from Func_handler_str_to_date_datetime_sec.
It should override get_date() to call the non-virtual get_date_common()
of the owner.
> };
>
>
> diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
> index b9795d3c713..6d3caa8c979 100644
> --- a/sql/item_timefunc.cc
> +++ b/sql/item_timefunc.cc
> @@ -465,6 +465,405 @@ static bool extract_date_time(THD *thd,
DATE_TIME_FORMAT *format,
> }
>
>
> +/*
> + Oracle has many formatting models, we list all but only part of them
> + are implemented, because some models depend on oracle functions
> + which mariadb is not supported.
> +
> + Models for datetime, used by TO_CHAR/TO_DATE. Normal format
characters are
> + stored as short integer < 128, while format characters are stored as a
> + integer > 128
> +*/
> +
> +enum enum_tochar_formats
> +{
> + FMT_BASE= 128,
> + FMT_AD, /* Handled: Anno Domini ("in the year of the Lord") */
> + FMT_AD_DOT, /* Handled: Anno Domini ("in the year of the Lord") */
> + FMT_AM, /* Handled: Meridian indicator (Before midday) */
> + FMT_AM_DOT, /* Handled Meridian indicator (Before midday) */
> + FMT_BC, /* Handled: Before Christ */
> + FMT_BC_DOT, /* Handled: Before Christ */
> + FMT_CC,
> + FMT_SCC,
> + FMT_D,
> + FMT_DAY, /* Handled: Name of day */
> + FMT_DD, /* Handled: Day (1-31) */
> + FMT_DDD, /* Handled: Day of year (1-336) */
> + FMT_DL,
> + FMT_DS,
> + FMT_DY, /* Handled: Abbreviated name of day */
> + FMT_E,
> + FMT_EE,
> + FMT_FF, /* Handled: Fractional seconds */
> + FMT_FM, /* Handled: Value with no leading or trailing blanks */
> + FMT_FX,
> + FMT_HH, /* Handled: Hour (1-12) */
> + FMT_HH12, /* Handled: Hour (1-12) */
> + FMT_HH24, /* Handled: Hour (0-23) */
> + FMT_IW, /* Handled: Week of year (1-53). Used with FMT_I*.
ISO 8601 */
> + /* FMT_I, FMT_IY...FMT_IYYY must be in sequence */
> + FMT_I, /* Handled: Year, 1 digit. Used with IW. ISO 8601 */
> + FMT_IY, /* Handled: Year, 2 digits. Used with IW. ISO 8601 */
> + FMT_IYY, /* Handled: Year, 3 digits. Used with IW. ISO 8601 */
> + FMT_IYYY, /* Handled: Year, 4 digits. Used with IW. ISO 8601 */
> + FMT_J,
> + FMT_MI, /* Handled: Minutes (0-59) */
> + FMT_MM, /* Handled: Month (1-12) */
> + FMT_MON, /* Handled: Abbreviated name of month */
> + FMT_MONTH, /* Handled: Name of Month */
> + FMT_PM, /* Handled: Handled: Meridian indicator (After
midday) */
> + FMT_PM_DOT, /* Handled: Handled: Meridian indicator (After
midday) */
> + FMT_RM,
> + FMT_RR, /* Handled: 20th century dates in the 21st century.
2 digits */
> + FMT_RRRR, /* Handled: 20th century dates in the 21st century.
4 digits */
> + FMT_SS, /* Handled: Seconds */
> + FMT_SSSSSS,
> + FMT_TS,
> + FMT_TZD,
> + FMT_TZH,
> + FMT_TZM,
> + FMT_TZR,
> + FMT_W,
> + FMT_WW,
> + FMT_X,
> + FMT_Y, /* Handled: 1 digit year */
> + FMT_YY, /* Handled: 2 digits year */
> + FMT_YYY, /* Handled: 3 digits year */
> + FMT_YYYY, /* Handled: 4 digits year */
> + FMT_YYYY_COMMA,
> + FMT_YEAR,
> + FMT_SYYYY,
> + FMT_SYEAR
> +};
> +
> +static const char *ad_bc_names[]=
> +{
> + "AD", "A.D.", "BC", "B.C.", NullS
> +};
> +
> +static TYPELIB ad_bc_typelib=
> +{
> + array_elements(ad_bc_names)-1,"", ad_bc_names, NULL, NULL
> +};
> +
> +#define INVALID_CHARACTER(x) (((x) >= 'A' && (x) <= 'Z') ||((x) >=
'0' && (x) <= '9') || (x) >= 127 || ((x) < 32))
> +
> +/*
> + A value between 0-49 will return a 20xx year.
> + A value between 50-99 will return a 19xx year.
> +*/
> +
> +uint oracle_year_2000_handling(uint year)
> +{
> + DBUG_ASSERT(year < 100);
> + if ((year=year+1900) < 1950)
> + year+=100;
> + return year;
> +}
> +
> +/**
> + Extract datetime value to MYSQL_TIME struct from string value
> + according to Oracle format string.
> +
> + @param format date/time format specification
> + @param val String to decode
> + @param length Length of string
> + @param l_time Store result here.
> + This value should be prefilled with the
> + current date in case format does not
> + have all date parts.
> + @parrm locale Used to get day and month names
> + @param data_time_type Type of string. Used for error messages
> + @param fuzzy_date If partial dates are allowed
> + @param give_error Generate a warning/error
> + @par
> + @retval
> + 0 ok
> + @retval
> + 1 error
> +*/
> +
> +static bool
> +extract_oracle_date_time(THD *thd, uint16 *format,
Please add "const" to "format".
> + const char *val, uint length, MYSQL_TIME
*l_time,
> + const MY_LOCALE *locale,
> + const char *date_time_type,
> + date_conv_mode_t fuzzydate,
> + bool give_error)
> +{
> + int weekday= 0, yearday= 0, daypart= 0;
> + int frac_part;
> + bool usa_time= 0;
> + bool before_christ= 0;
> + const char *val_begin= val;
> + const char *val_end= val + length;
> + char *tmp;
> + CHARSET_INFO *cs= &my_charset_bin;
> + DBUG_ENTER("extract_oracle_date_time");
> +
> + for ( ; *format ; format++)
> + {
> + uint val_len;
> + int error;
> +
> + while (my_isspace(cs, *val))
> + val++;
> + if (*format < 128)
> + {
> + char format_char= (char) (*format & 255);
> + bool control_character;
> + if (my_isspace(cs, format_char))
> + continue;
> + if (format_char == *val ||
> + ((control_character= !INVALID_CHARACTER(format_char)) &&
> + !INVALID_CHARACTER(*val)))
> + {
> + /* Perfect match or both are non-alphanumeric characters */
> + val++;
> + continue;
> + }
> + if (control_character && my_isdigit(cs, *val))
> + {
> + /*
> + Oracle tochar allows values without punctuation characters for
> + numercial values
> + */
> + continue;
> + }
> + }
> +
> + error= 0;
> + val_len= (uint) (val_end - val);
> +
> + switch (*format) {
> + /* Year */
> + case FMT_YYYY:
> + tmp= (char*) val + MY_MIN(4, val_len);
> + l_time->year= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> + case FMT_YYY:
> + tmp= (char*) val + MY_MIN(3, val_len);
> + l_time->year= ((int) my_strtoll10(val, &tmp, &error) +
> + l_time->year/1000*1000);
> + val= tmp;
> + break;
> + case FMT_YY:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->year= ((int) my_strtoll10(val, &tmp, &error) +
> + l_time->year/100*100);
> + val= tmp;
> + break;
> + case FMT_Y:
> + tmp= (char*) val + MY_MIN(1, val_len);
> + l_time->year= ((int) my_strtoll10(val, &tmp, &error) +
> + l_time->year/10*10);
> + val= tmp;
> + break;
> + case FMT_RR:
> + {
> + uint year;
> + tmp= (char*) val + MY_MIN(2, val_len);
> + year= my_strtoll10(val, &tmp, &error);
> + l_time->year= oracle_year_2000_handling(year);
> + val= tmp;
> + break;
> + }
> + case FMT_RRRR:
> + {
> + uint year;
> + tmp= (char*) val + MY_MIN(4, val_len);
> + year= my_strtoll10(val, &tmp, &error);
> + l_time->year= year < 100 ? year : oracle_year_2000_handling(year);
> + val= tmp;
> + break;
> + }
> +
> + case FMT_AD:
> + case FMT_AD_DOT:
> + case FMT_BC:
> + case FMT_BC_DOT:
> + {
> + int period;
> + if ((period= check_word(&ad_bc_typelib, val, val_end, &val)) <= 0)
> + goto error;
> + before_christ= period > 2;
> + break;
> + }
> +
> + /* Month */
> + case FMT_MM:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->month= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> + case FMT_MONTH:
> + if ((l_time->month= check_word(locale->month_names,
> + val, val_end, &val)) <= 0)
> + goto error;
> + break;
> + case FMT_MON:
> + if ((l_time->month= check_word(locale->ab_month_names,
> + val, val_end, &val)) <= 0)
> + goto error;
> + break;
> +
> + /* Day */
> + case FMT_DD:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->day= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> +
> + /* Hour */
> + case FMT_HH:
> + case FMT_HH12:
> + usa_time= 1;
> + /* fall through */
> + case FMT_HH24:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->hour= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> +
> + /* Minute */
> + case FMT_MI:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->minute= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> +
> + /* Second */
> + case FMT_SS:
> + tmp= (char*) val + MY_MIN(2, val_len);
> + l_time->second= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> +
> + /* Second part */
> + case FMT_FF:
> + {
> + uint length= 6;
> + if (format[1] >= '1' && format[1] <= '6')
> + length= *++format - (uint) '0';
> + tmp= (char*) val + MY_MIN(length, val_len);
> + l_time->second_part= (int) my_strtoll10(val, &tmp, &error);
> + frac_part= 6 - (int) (tmp - val);
> + if (frac_part > 0)
> + l_time->second_part*= (ulong) log_10_int[frac_part];
> + val= tmp;
> + break;
> + }
> + /* AM / PM */
> + case FMT_AM:
> + case FMT_AM_DOT:
> + case FMT_PM:
> + case FMT_PM_DOT:
> + if (val_len < 2 || ! usa_time)
> + goto error;
> + if (!my_charset_latin1.strnncoll(val, 2, "PM", 2))
> + {
> + daypart= 12;
> + val+= 2;
> + }
> + else if (!my_charset_latin1.strnncoll(val, 2, "AM", 2))
> + val+= 2;
> + else if (!my_charset_latin1.strnncoll(val, 2, "P.M", 4))
> + {
> + daypart= 12;
> + val+= 4;
> + }
> + else if (!my_charset_latin1.strnncoll(val, 2, "A.M.", 4))
> + val+= 4;
> + else
> + goto error;
> + break;
> +
> + /* Exotic things. Weekdays are only use validation of date */
> + case FMT_DAY:
> + if ((weekday= check_word(locale->day_names, val, val_end,
&val)) <= 0)
> + goto error;
> + break;
> + case FMT_DY:
> + if ((weekday= check_word(locale->ab_day_names, val, val_end,
&val)) <= 0)
> + goto error;
> + break;
> +
> + case FMT_DDD: // Day of year
> + tmp= (char*) val + MY_MIN(val_len, 3);
> + yearday= (int) my_strtoll10(val, &tmp, &error);
> + val= tmp;
> + break;
> +
> + default:
> + goto error;
> + }
> + if (unlikely(error)) // Error from my_strtoll10
> + goto error;
> + }
> +
> + /* Generate the datetime */
> + if (usa_time)
> + {
> + if (l_time->hour > 12 || l_time->hour < 1)
> + goto error;
> + l_time->hour= l_time->hour%12+daypart;
> + }
> +
> + if (yearday > 0)
> + {
> + uint days;
> + days= calc_daynr(l_time->year,1,1) + yearday - 1;
> + if
(get_date_from_daynr(days,&l_time->year,&l_time->month,&l_time->day))
> + goto error;
> + }
> +
> + if (before_christ) // Cannot handle
negative dates
> + goto error;
> +
> + if (weekday && calc_weekday(calc_daynr(l_time->year,l_time->month,
> + l_time->day),0) != weekday -1)
> + goto error;
> +
> + if (l_time->month > 12 || l_time->day > 31 || l_time->hour > 23 ||
> + l_time->minute > 59 || l_time->second > 59)
> + goto error;
> +
> + int was_cut;
> + if (check_date(l_time, fuzzydate, &was_cut))
> + goto error;
> +
> + if (val != val_end)
> + {
> + do
> + {
> + if (!my_isspace(&my_charset_latin1,*val))
> + {
> + ErrConvString err(val_begin, length, &my_charset_bin);
> + make_truncated_value_warning(thd,
Sql_condition::WARN_LEVEL_WARN,
> + &err, l_time->time_type,
> + nullptr, nullptr, nullptr);
> + break;
> + }
> + } while (++val != val_end);
> + }
> + DBUG_RETURN(0);
> +
> +error:
> + if (give_error)
> + {
> + char buff[128];
> + strmake(buff, val_begin, MY_MIN(length, sizeof(buff)-1));
> + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
> + ER_WRONG_VALUE_FOR_TYPE,
> + ER_THD(thd, ER_WRONG_VALUE_FOR_TYPE),
> + date_time_type, buff, "str_to_date");
> + }
> + DBUG_RETURN(1);
> +}
> +
> +
> /**
> Create a formatted date/time value in a string.
> */
> @@ -2019,77 +2418,6 @@ class Date_time_format_oracle
> };
>
>
> -/*
> - Oracle has many formatting models, we list all but only part of them
> - are implemented, because some models depend on oracle functions
> - which mariadb is not supported.
> -
> - Models for datetime, used by TO_CHAR/TO_DATE. Normal format
characters are
> - stored as short integer < 128, while format characters are stored as a
> - integer > 128
> -*/
> -
> -enum enum_tochar_formats
> -{
> - FMT_BASE= 128,
> - FMT_AD,
> - FMT_AD_DOT,
> - FMT_AM,
> - FMT_AM_DOT,
> - FMT_BC,
> - FMT_BC_DOT,
> - FMT_CC,
> - FMT_SCC,
> - FMT_D,
> - FMT_DAY,
> - FMT_DD,
> - FMT_DDD,
> - FMT_DL,
> - FMT_DS,
> - FMT_DY,
> - FMT_E,
> - FMT_EE,
> - FMT_FF,
> - FMT_FM,
> - FMT_FX,
> - FMT_HH,
> - FMT_HH12,
> - FMT_HH24,
> - FMT_IW,
> - FMT_I,
> - FMT_IY,
> - FMT_IYY,
> - FMT_IYYY,
> - FMT_J,
> - FMT_MI,
> - FMT_MM,
> - FMT_MON,
> - FMT_MONTH,
> - FMT_PM,
> - FMT_PM_DOT,
> - FMT_RM,
> - FMT_RR,
> - FMT_RRRR,
> - FMT_SS,
> - FMT_SSSSSS,
> - FMT_TS,
> - FMT_TZD,
> - FMT_TZH,
> - FMT_TZM,
> - FMT_TZR,
> - FMT_W,
> - FMT_WW,
> - FMT_X,
> - FMT_Y,
> - FMT_YY,
> - FMT_YYY,
> - FMT_YYYY,
> - FMT_YYYY_COMMA,
> - FMT_YEAR,
> - FMT_SYYYY,
> - FMT_SYEAR
> -};
> -
> /**
> Flip 'quotation_flag' if we found a quote (") character.
>
> @@ -2110,9 +2438,6 @@ static inline bool check_quotation(uint16 cfmt,
bool *quotation_flag)
> return *quotation_flag;
> }
>
> -#define INVALID_CHARACTER(x) (((x) >= 'A' && (x) <= 'Z') ||((x) >=
'0' && (x) <= '9') || (x) >= 127 || ((x) < 32))
> -
> -
> /**
> Special characters are directly output in the result
>
> @@ -2156,32 +2481,55 @@ static uint parse_special(char cfmt, const
char *ptr, const char *end,
> }
>
>
> +static inline bool formats_used(uint64 *used, int src)
> +{
> + uint64 bit= 1ULL << (src-128);
> + if (*used & bit)
> + return 1; // Duplicate item
> + (*used)|= bit;
> + return 0;
> +}
> +
> +
> /**
> Parse the format string, convert it to an compact array and
calculate the
> length of output string
>
> - @param format Format string
> - @param fmt_len Function will store max length of formated date
string here
> + @param format Format string
> + @param fmt_array Packed format
> + @param fmt_len Max length of formated date string is
stored here
> + @param locale Locale
> + @param for_to_date If the format is for the to_date() oracle
function
> + @param warning_message Error message is stored here.
> + @param flags Bits of PARSE_TYPE_FLAGS of formats used
>
> - @return 0 ok. fmt_len is updated
> - @return 1 error. In this case 'warning_string' is set to error
message
> + @return 0 ok. fmt_len and flags are updated
> + @return 1 error warnings_massage set and flags=0
> */
>
> -bool Item_func_tochar::parse_format_string(const String *format,
uint *fmt_len)
> +
> +static bool parse_format_string(const String *format, uint16 *fmt_array,
> + uint *fmt_len, const MY_LOCALE *locale,
> + bool for_to_date,
> + String *warning_message,
> + PARSE_TYPE_FLAGS *flags)
> {
> const char *ptr, *end;
> uint16 *tmp_fmt= fmt_array;
> uint tmp_len= 0;
> int offset= 0;
> bool quotation_flag= false;
> + uint64 used= 0; // Formats used
> + uint type_flags= PARSE_TYPE_NONE;
>
> + *flags= PARSE_TYPE_NONE;
> ptr= format->ptr();
> end= ptr + format->length();
>
> if (format->length() > MAX_DATETIME_FORMAT_MODEL_LEN)
> {
> - warning_message.append(STRING_WITH_LEN("datetime format string
is too "
> - "long"));
> + warning_message->append(STRING_WITH_LEN("datetime format string
is too "
> + "long"));
> return 1;
> }
>
> @@ -2212,15 +2560,20 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> case 'A': // AD/A.D./AM/A.M.
> if (ptr+1 >= end)
> goto error;
> +
> next_char= my_toupper(system_charset_info, *(ptr+1));
> if (next_char == 'D')
> {
> + if (for_to_date && formats_used(&used, FMT_AD))
> + goto error;
> *tmp_fmt= FMT_AD;
> ptr+= 1;
> tmp_len+= 2;
> }
> else if (next_char == 'M')
> {
> + if (for_to_date && formats_used(&used, FMT_AM))
> + goto error;
> *tmp_fmt= FMT_AM;
> ptr+= 1;
> tmp_len+= 2;
> @@ -2229,12 +2582,16 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> {
> if (my_toupper(system_charset_info, *(ptr+2)) == 'D')
> {
> + if (for_to_date && formats_used(&used, FMT_AD))
> + goto error;
> *tmp_fmt= FMT_AD_DOT;
> ptr+= 3;
> tmp_len+= 4;
> }
> else if (my_toupper(system_charset_info, *(ptr+2)) == 'M')
> {
> + if (for_to_date && formats_used(&used, FMT_AM))
> + goto error;
> *tmp_fmt= FMT_AM_DOT;
> ptr+= 3;
> tmp_len+= 4;
> @@ -2248,6 +2605,9 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> case 'B': // BC and B.C
> if (ptr+1 >= end)
> goto error;
> + if (for_to_date && formats_used(&used, FMT_BC))
> + goto error;
> +
> next_char= my_toupper(system_charset_info, *(ptr+1));
> if (next_char == 'C')
> {
> @@ -2266,7 +2626,42 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> else
> goto error;
> break;
> + case 'I':
> + if (for_to_date)
> + goto error;
> + if (ptr+1 >= end)
> + goto found_I;
> + next_char= my_toupper(system_charset_info, *(ptr+1));
> + if (next_char == 'W')
> + {
> + *tmp_fmt= FMT_IW;
> + tmp_len+=2;
> + ptr++;
> + }
> + else if (next_char == 'Y')
> + {
> + uint i;
> + for (i= 0 ; i < 3 && ++ptr < end; i++)
> + if (my_toupper(system_charset_info, *(ptr+1)) != 'Y')
> + break;
> +
> + *tmp_fmt= FMT_IY+i;
> + tmp_len+= 2 + i;
> + }
> + else
> + {
> + found_I:
> + *tmp_fmt= FMT_I;
> + tmp_len+= 1;
> + }
> + break;
> case 'P': // PM or P.M.
> + if (ptr + 1 == end)
> + goto error;
> +
> + if (for_to_date && formats_used(&used, FMT_AM))
> + goto error;
> +
> next_char= my_toupper(system_charset_info, *(ptr+1));
> if (next_char == 'M')
> {
> @@ -2285,17 +2680,22 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> else
> goto error;
> break;
> - case 'Y': // Y, YY, YYY o YYYYY
> + case 'Y': // Y, YY, YYY or YYYYY
> + if (for_to_date && formats_used(&used, FMT_Y))
> + goto error;
> +
> if (ptr + 1 == end || my_toupper(system_charset_info,
*(ptr+1)) != 'Y')
> {
> *tmp_fmt= FMT_Y;
> tmp_len+= 1;
> + type_flags|= PARSE_TYPE_PART_YEAR;
> break;
> }
> if (ptr + 2 == end ||
> my_toupper(system_charset_info, *(ptr+2)) != 'Y') /* YY */
> {
> *tmp_fmt= FMT_YY;
> + type_flags|= PARSE_TYPE_PART_YEAR;
> ulen= 2;
> }
> else
> @@ -2304,18 +2704,22 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> {
> *tmp_fmt= FMT_YYYY;
> ulen= 4;
> + type_flags|= PARSE_TYPE_YEAR;
> }
> else
> {
> *tmp_fmt= FMT_YYY;
> + type_flags|= PARSE_TYPE_PART_YEAR;
> ulen= 3;
> }
> }
> ptr+= ulen-1;
> tmp_len+= ulen;
> break;
> -
> case 'R': // RR or RRRR
> + if (for_to_date && formats_used(&used, FMT_Y))
> + goto error;
> +
> if (ptr + 1 == end || my_toupper(system_charset_info,
*(ptr+1)) != 'R')
> goto error;
>
> @@ -2333,6 +2737,7 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> }
> ptr+= ulen-1;
> tmp_len+= ulen;
> + type_flags|= PARSE_TYPE_YEAR;
> break;
> case 'M':
> {
> @@ -2343,20 +2748,29 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> tmp1= my_toupper(system_charset_info, *(ptr+1));
> if (tmp1 == 'M')
> {
> + if (for_to_date && formats_used(&used, FMT_MM))
> + goto error;
> *tmp_fmt= FMT_MM;
> tmp_len+= 2;
> + type_flags|= PARSE_TYPE_MONTH;
> ptr+= 1;
> }
> else if (tmp1 == 'I')
> {
> + if (for_to_date && formats_used(&used, FMT_MI))
> + goto error;
> *tmp_fmt= FMT_MI;
> tmp_len+= 2;
> + type_flags|= PARSE_TYPE_TIME;
> ptr+= 1;
> }
> else if (tmp1 == 'O')
> {
> if (ptr + 2 >= end)
> goto error;
> + if (for_to_date && formats_used(&used, FMT_MM))
> + goto error;
> +
> char tmp2= my_toupper(system_charset_info, *(ptr+2));
> if (tmp2 != 'N')
> goto error;
> @@ -2376,37 +2790,57 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> my_charset_utf8mb3_bin.mbmaxlen);
> ptr+= 4;
> }
> + type_flags|= PARSE_TYPE_MONTH;
> }
> else
> goto error;
> }
> break;
> - case 'D': // DD, DY, or DAY
> + case 'D': // D, DD, DY, or DAY
> {
> + char tmp1;
> if (ptr + 1 >= end)
> goto error;
> - char tmp1= my_toupper(system_charset_info, *(ptr+1));
>
> + tmp1= my_toupper(system_charset_info, *(ptr+1));
> if (tmp1 == 'D')
> {
> - *tmp_fmt= FMT_DD;
> + if (ptr + 2 != end &&
> + my_toupper(system_charset_info, *(ptr+2)) == 'D')
> + {
> + if (for_to_date &&
> + (formats_used(&used, FMT_DDD) ||
> + formats_used(&used, FMT_DD) ||
> + formats_used(&used, FMT_MM)))
> + goto error;
> + *tmp_fmt= FMT_DDD; // Day of year
> + tmp_len+= 3;
> + ptr+= 2;
> + break;
> + }
> + if (for_to_date && formats_used(&used, FMT_DD))
> + goto error;
> + *tmp_fmt= FMT_DD; // Day of month 1-31
> tmp_len+= 2;
> + type_flags|= PARSE_TYPE_DAY;
> }
> else if (tmp1 == 'Y')
> {
> - *tmp_fmt= FMT_DY;
> + *tmp_fmt= FMT_DY; // Day name
> tmp_len+= 3;
> + type_flags|= PARSE_TYPE_WEEKDAY;
> }
> - else if (tmp1 == 'A') // DAY
> + else if (tmp1 == 'A')
> {
> if (ptr + 2 == end || my_toupper(system_charset_info,
*(ptr+2)) != 'Y')
> goto error;
> - *tmp_fmt= FMT_DAY;
> + *tmp_fmt= FMT_DAY; // Day name
> tmp_len+= locale->max_day_name_length *
my_charset_utf8mb3_bin.mbmaxlen;
> + type_flags|= PARSE_TYPE_WEEKDAY;
> ptr+= 1;
> }
> else
> - goto error;
> + goto error; // 'D', weekday 1-7,
territory
> ptr+= 1;
> }
> break;
> @@ -2415,8 +2849,10 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> char tmp1, tmp2, tmp3;
> if (ptr + 1 >= end)
> goto error;
> - tmp1= my_toupper(system_charset_info, *(ptr+1));
> + if (for_to_date && formats_used(&used, FMT_HH))
> + goto error;
>
> + tmp1= my_toupper(system_charset_info, *(ptr+1));
> if (tmp1 != 'H')
> goto error;
>
> @@ -2447,14 +2883,17 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> }
> }
> tmp_len+= 2;
> + type_flags|= PARSE_TYPE_TIME;
> break;
> }
> case 'S': // SS
> if (ptr + 1 == end || my_toupper(system_charset_info,
*(ptr+1)) != 'S')
> goto error;
> -
> + if (for_to_date && formats_used(&used, FMT_SS))
> + goto error;
> *tmp_fmt= FMT_SS;
> tmp_len+= 2;
> + type_flags|= PARSE_TYPE_TIME;
> ptr+= 1;
> break;
> case '|':
> @@ -2476,17 +2915,30 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> ptr--; // Fix ptr for above
for loop
> tmp_fmt--;
> break;
> -
> case 'F':
> if (ptr + 1 == end)
> goto error;
> if (my_toupper(system_charset_info, ptr[1]) == 'M')
> {
> + if (for_to_date)
> + goto error;
> *tmp_fmt= FMT_FM;
> ptr+= 1;
> - continue;
> }
> - goto error;
> + else if (my_toupper(system_charset_info, ptr[1]) == 'F')
> + {
> + if (for_to_date && formats_used(&used, FMT_FF))
> + goto error;
> + *tmp_fmt= FMT_FF;
> + type_flags|= PARSE_TYPE_SUBSECONDS;
> + ptr+= 1;
> + if (ptr + 1 <= end && ptr[1] >= '1' && ptr[1] <= '6')
> + *++tmp_fmt= *++ptr; // Copy format length
> + tmp_len+= 6;
> + }
> + else
> + goto error;
> + break;
>
> default:
> offset= parse_special(cfmt, ptr, end, tmp_fmt);
> @@ -2501,14 +2953,34 @@ bool
Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
> }
> *fmt_len= tmp_len;
> *tmp_fmt= 0;
> + *flags= (PARSE_TYPE_FLAGS) type_flags;
> return 0;
>
> error:
> - warning_message.append(STRING_WITH_LEN("date format not recognized
at "));
> - warning_message.append(ptr, MY_MIN(8, end- ptr));
> + warning_message->append(STRING_WITH_LEN("date format not
recognized at "));
> + warning_message->append(ptr, MY_MIN(8, end- ptr));
> return 1;
> }
>
> +/**
> + Get the result datetime type for parsed string
> +
> + Same logic used as in get_date_time_result_type()
> +*/
> +
> +static const Item_handled_func::Handler *
> +get_parsed_result_type(PARSE_TYPE_FLAGS type)
> +{
> + /* If YEAR, MONTH or DAY is used, set all date bits */
> + if (type & PARSE_TYPE_DATE)
> + type= (PARSE_TYPE_FLAGS) (type | PARSE_TYPE_DATE);
> + if (type & PARSE_TYPE_SUBSECONDS)
> + return &func_handler_str_to_date_datetime_usec;
> + if (type & PARSE_TYPE_TIME)
> + return &func_handler_str_to_date_datetime_sec;
> + return &func_handler_str_to_date_date;
> +}
> +
The above is not Oracle compatible.
It should never use &func_handler_str_to_date_date.
>
> bool Date_time_format_oracle::format(const uint16 *fmt_array,
> const MYSQL_TIME *l_time,
> @@ -2519,6 +2991,7 @@ bool Date_time_format_oracle::format(const
uint16 *fmt_array,
> const uint16 *ptr= fmt_array;
> uint hours_i;
> uint weekday;
> + uint year= 0, week= 0;
>
> str->length(0);
>
> @@ -2537,7 +3010,6 @@ bool Date_time_format_oracle::format(const
uint16 *fmt_array,
> }
>
> switch (*ptr) {
> -
> case FMT_AM:
> case FMT_PM:
> if (l_time->hour > 11)
> @@ -2684,11 +3156,50 @@ bool Date_time_format_oracle::format(const
uint16 *fmt_array,
> goto err_exit;
> break;
>
> + case FMT_FF:
> + {
> + uint length= 6;
> + if (ptr[1] >= '1' && ptr[1] <= '6')
> + length= *++ptr - (uint) '0';
> + if (append_val(l_time->second_part / log_10_int[6-length],
length, str))
> + goto err_exit;
> + break;
> + }
> case FMT_FM:
> m_fm= !m_fm;
> break;
>
> + case FMT_DDD:
> + {
> + uint day= (calc_daynr(l_time->year, l_time->month, l_time->day) -
> + calc_daynr(l_time->year, 1, 1) + 1);
> + if (append_val(day, 3, str))
> + goto err_exit;
> + break;
> + }
> + case FMT_IW:
> + case FMT_I:
> + case FMT_IY:
> + case FMT_IYY:
> + case FMT_IYYY:
> + if (!week)
> + week= calc_week(l_time, WEEK_MONDAY_FIRST | WEEK_YEAR, &year);
> + if (*ptr == FMT_IW)
> + {
> + if (str->append_longlong(week))
> + goto err_exit;
> + }
> + else
> + {
> + /* Handling FMT_I, FMT_IY, FMT_IYY and FMT_IYYY */
> + int length= (*ptr - FMT_I) + 1;
> + if (append_val(year % log_10_int[length], length, str))
> + goto err_exit;
> + }
> + break;
> default:
> + if (*ptr > 127)
> + goto err_exit;
> str->append((char) *ptr);
> }
>
> @@ -2740,8 +3251,10 @@ bool Item_func_tochar::fix_length_and_dec(THD
*thd)
> if (args[1]->basic_const_item() && (str= args[1]->val_str(&buffer)))
> {
> uint ulen;
> + PARSE_TYPE_FLAGS flags;
> fixed_length= 1;
> - if (parse_format_string(str, &ulen))
> + if (parse_format_string(str, fmt_array, &ulen, locale, 0,
&warning_message,
> + &flags))
> {
> my_printf_error(ER_STD_INVALID_ARGUMENT,
> ER(ER_STD_INVALID_ARGUMENT),
> @@ -2784,8 +3297,10 @@ String *Item_func_tochar::val_str(String* str)
> if (!fixed_length)
> {
> uint ulen;
> + PARSE_TYPE_FLAGS flags;
> if (!(format= args[1]->val_str(&format_buffer)) ||
!format->length() ||
> - parse_format_string(format, &ulen))
> + parse_format_string(format, fmt_array, &ulen, locale, 0,
> + &warning_message, &flags))
> goto null_date;
> max_result_length= ((size_t) ulen) * collation.collation->mbmaxlen;
> }
> @@ -4036,6 +4551,137 @@ bool
Item_func_str_to_date::get_date_common(THD *thd, MYSQL_TIME *ltime,
> }
>
>
> +/**
> + Compile the format string to fmt_array
> +
> + @return The type flags found. PARSE_TYPE_NONE in case of error
> +*/
> +
> +PARSE_TYPE_FLAGS Item_func_to_date::get_format()
> +{
> + StringBuffer<64> format_str;
> + String *format= args[1]->val_str(&format_str, &format_converter,
> + internal_charset);
> + PARSE_TYPE_FLAGS result_type;
> +
> + if (!args[1]->null_value)
> + {
> + uint ulen;
> + warning_message.length(0);
> + if (parse_format_string(format, fmt_array, &ulen, locale, 1,
> + &warning_message, &result_type))
> + {
> + my_printf_error(ER_STD_INVALID_ARGUMENT,
> + ER(ER_STD_INVALID_ARGUMENT),
> + MYF(0),
> + warning_message.c_ptr(),
> + func_name());
> + return PARSE_TYPE_NONE;
> + }
> + }
> + DBUG_ASSERT(result_type != 0);
> + return result_type;
> +}
> +
> +
> +bool Item_func_to_date::fix_length_and_dec(THD *thd)
> +{
> + PARSE_TYPE_FLAGS format_flags= PARSE_TYPE_NONE;
> +
> + locale= thd->variables.lc_time_names;
> +
> + if (!args[0]->type_handler()->is_traditional_scalar_type() ||
> + !args[1]->type_handler()->is_traditional_scalar_type() ||
> + (arg_count == 33 &&
Why 33? I guess it should be:
(arg_count > 2 &&
Please fix and add a test case covering arg_count>2.
> + !args[2]->type_handler()->is_traditional_scalar_type()))
> + {
> + my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0),
> + args[0]->type_handler()->name().ptr(),
> + args[2]->type_handler()->name().ptr(), func_name());
> + return TRUE;
> + }
> + if (agg_arg_charsets(collation, args, arg_count,
MY_COLL_ALLOW_CONV, 1))
> + return TRUE;
> + if (collation.collation->mbminlen > 1)
> + internal_charset= &my_charset_utf8mb4_general_ci;
I think internal_charset should always be utf8, because locale data is utf8.
I have not tested, but from my understanding it won't be able to compare
month names with non-ascii characters properly, say latin1 month name
coming from the subject string in args[0] versus the month name in the
locale data.
> +
> + set_maybe_null();
> + set_func_handler(&func_handler_str_to_date_datetime_usec);
> +
> + if ((const_item= args[1]->const_item()))
> + {
Can you please rename the member const_item to const_format?
const_item looks confusing.
> + if (!(format_flags= get_format()))
> + return true;
The above looks misleading. I suggest:
if ((format_flags= get_format()) == PARSE_TYPE_NONE)
return true;
> + set_func_handler(get_parsed_result_type(format_flags));
> + }
> + if (!test_all_bits(format_flags, PARSE_TYPE_DATE))
> + {
> + /* Some time part is missing, get current time to fill them in */
> + thd->variables.time_zone->gmt_sec_to_TIME(&now_time,
thd->query_start());
> + now_time.hour= now_time.minute= now_time.second= 0;
> + now_time.second_part= 0;
> + now_time.neg= 0;
> + }
> + else
> + bzero(&now_time, sizeof(now_time));
> + now_time.time_type= MYSQL_TIMESTAMP_NONE;
> +
> + return m_func_handler->fix_length_and_dec(this);
> +}
> +
> +
> +bool Item_func_to_date::get_date_common(THD *thd, MYSQL_TIME *ltime,
> + date_mode_t fuzzydate,
> + timestamp_type tstype)
> +{
> + StringBuffer<64> val_string;
> + String *val;
> +
> + val= args[0]->val_str(&val_string, &subject_converter,
internal_charset);
> + if (args[1]->null_value)
> + goto error;
Why args[1]->null_value it tested without calling args[1]->val_str() ?
> + if (args[0]->null_value)
> + {
> + if (arg_count == 2)
> + goto error;
> + val= args[2]->val_str(&val_string, &subject_converter,
internal_charset);
> + if (args[2]->null_value)
> + goto error;
> + }
Looks good. According to Oracle's docs, args[2], which is return_value
from the `DEFAULT return_value ON CONVERSION ERROR` clause, is converted
to DATE using the same method it uses for args[0] (the subject parameter).
So it should be exactly args[2]->val_str() rather than args[2]->get_date().
Thanks for noticing this.
Can you please add a comment here?
Oracle's behavior is strange.
> +
> + if (!const_item && !get_format())
> + goto error;
Again, !get_format() looks misleading. I suggest:
if (!const_item && (get_format() == PARSE_TYPE_NONE))
goto error;
> +
> + /* Set default year, month and day */
> + memcpy(ltime, &now_time, sizeof(*ltime));
> + ltime->time_type= tstype;
> +
> + if (!extract_oracle_date_time(thd, fmt_array,
> + val->ptr(), val->length(),
> + ltime, locale, "datetime",
> + (date_conv_mode_t(fuzzydate) |
> + sql_mode_for_dates(thd)),
> + arg_count == 2))
> + return (null_value= 0);
> +
> + if (arg_count == 3)
> + {
> + /* Try to use default value */
> + val= args[2]->val_str(&val_string, &subject_converter,
internal_charset);
> + if (args[2]->null_value)
> + goto error;
> + if (!extract_oracle_date_time(thd, fmt_array,
> + val->ptr(), val->length(),
> + ltime, locale, "datetime",
> + (date_conv_mode_t(fuzzydate) |
> + sql_mode_for_dates(thd)), 1))
> + return (null_value= 0);
> + }
> + error:
> + return (null_value=1);
> +}
> +
> +
> bool Item_func_last_day::get_date(THD *thd, MYSQL_TIME *ltime,
date_mode_t fuzzydate)
> {
> Datetime::Options opt(date_conv_mode_t(fuzzydate & ~TIME_TIME_ONLY),
> diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
> index 762288fade8..fbefdcc2fc5 100644
> --- a/sql/item_timefunc.h
> +++ b/sql/item_timefunc.h
> @@ -1125,7 +1125,6 @@ class Item_func_tochar :public Item_str_func
> return name;
> }
> bool fix_length_and_dec(THD *thd) override;
> - bool parse_format_string(const String *format, uint *fmt_len);
>
> bool check_vcol_func_processor(void *arg) override
> {
> @@ -1756,7 +1755,7 @@ class Item_func_str_to_date :public
Item_handled_func
> internal_charset(NULL)
> {}
> bool get_date_common(THD *thd, MYSQL_TIME *ltime, date_mode_t
fuzzydate,
> - timestamp_type);
> + timestamp_type) override;
> LEX_CSTRING func_name_cstring() const override
> {
> static LEX_CSTRING name= {STRING_WITH_LEN("str_to_date") };
> @@ -1768,6 +1767,75 @@ class Item_func_str_to_date :public
Item_handled_func
> };
>
>
> +/* Oracle TO_DATE() function */
> +
> +
> +/* Flags used by parse_format_string() */
> +
> +enum PARSE_TYPE_FLAGS
> +{
> + PARSE_TYPE_NONE= 0,
> + PARSE_TYPE_YEAR= 1,
> + PARSE_TYPE_MONTH= 2,
> + PARSE_TYPE_DAY= 4,
> + PARSE_TYPE_TIME= 8,
> + PARSE_TYPE_SUBSECONDS= 16,
> + PARSE_TYPE_WEEKDAY= 32,
> + PARSE_TYPE_PART_YEAR= 64
> +};
> +
> +#define PARSE_TYPE_DATE (PARSE_TYPE_YEAR | PARSE_TYPE_MONTH |
PARSE_TYPE_DAY)
> +
> +class Item_func_to_date :public Item_handled_func
> +{
> + bool const_item;
Please rename it to const_format.
> + String subject_converter;
> + String format_converter;
> + CHARSET_INFO *internal_charset;
> + MYSQL_TIME now_time; // Used for incomplete dates
> +
> + THD *thd;
> + const MY_LOCALE *locale;
> + String warning_message;
> + PARSE_TYPE_FLAGS formats_used;
> +
> + /*
> + When datetime format models is parsed, use uint16 integers to
> + represent the format models and store in fmt_array.
> + */
> + uint16 fmt_array[MAX_DATETIME_FORMAT_MODEL_LEN+1];
> +
> +public:
> + Item_func_to_date(THD *thd, Item *a, Item *b):
> + Item_handled_func(thd, a, b), const_item(false),
> + internal_charset(NULL)
> + {
> + /* NOTE: max length of warning message is 64 */
> + warning_message.alloc(64);
> + warning_message.length(0);
> + }
> + Item_func_to_date(THD *thd, Item *a, Item *b, Item *c):
> + Item_handled_func(thd, a, b, c), const_item(false),
> + internal_charset(NULL)
> + {
> + /* NOTE: max length of warning message is 64 */
> + warning_message.alloc(64);
> + warning_message.length(0);
> + }
> + bool get_date_common(THD *thd, MYSQL_TIME *ltime, date_mode_t
fuzzydate,
> + timestamp_type) override;
> + LEX_CSTRING func_name_cstring() const override
> + {
> + static LEX_CSTRING name= {STRING_WITH_LEN("to_date") };
> + return name;
> + }
> + bool fix_length_and_dec(THD *thd) override;
> + Item *do_get_copy(THD *thd) const override
> + { return get_item_copy<Item_func_to_date>(thd, this); }
> + PARSE_TYPE_FLAGS get_format();
> +};
> +
> +
> class Item_func_last_day :public Item_datefunc
> {
> bool check_arguments() const override
> @@ -2067,8 +2135,7 @@ class Func_handler_str_to_date_datetime_sec:
> bool get_date(THD *thd, Item_handled_func *item,
> MYSQL_TIME *to, date_mode_t fuzzy) const override
> {
> - return static_cast<Item_func_str_to_date*>(item)->
> - get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_DATETIME);
> + return item->get_date_common(thd, to, fuzzy,
MYSQL_TIMESTAMP_DATETIME);
> }
> };
>
> @@ -2085,8 +2152,7 @@ class Func_handler_str_to_date_datetime_usec:
> bool get_date(THD *thd, Item_handled_func *item,
> MYSQL_TIME *to, date_mode_t fuzzy) const override
> {
> - return static_cast<Item_func_str_to_date*>(item)->
> - get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_DATETIME);
> + return item->get_date_common(thd, to, fuzzy,
MYSQL_TIMESTAMP_DATETIME);
> }
> };
>
> @@ -2097,8 +2163,7 @@ class Func_handler_str_to_date_date: public
Item_handled_func::Handler_date
> bool get_date(THD *thd, Item_handled_func *item,
> MYSQL_TIME *to, date_mode_t fuzzy) const override
> {
> - return static_cast<Item_func_str_to_date*>(item)->
> - get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_DATE);
> + return item->get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_DATE);
> }
> };
>
> @@ -2109,8 +2174,7 @@ class Func_handler_str_to_date_time: public
Item_handled_func::Handler_time
> bool get_date(THD *thd, Item_handled_func *item,
> MYSQL_TIME *to, date_mode_t fuzzy) const override
> {
> - if (static_cast<Item_func_str_to_date*>(item)->
> - get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_TIME))
> + if (item->get_date_common(thd, to, fuzzy, MYSQL_TIMESTAMP_TIME))
> return true;
> if (to->day)
> {
> diff --git a/sql/lex.h b/sql/lex.h
> index 41a34ef7389..3efeb807b15 100644
> --- a/sql/lex.h
> +++ b/sql/lex.h
> @@ -150,8 +150,9 @@ SYMBOL symbols[] = {
> { "CONTEXT", SYM(CONTEXT_SYM)},
> { "CONTINUE", SYM(CONTINUE_MARIADB_SYM)},
> { "CONTRIBUTORS", SYM(CONTRIBUTORS_SYM)},
> + { "CONVERSION", SYM(CONVERSION_SYM)},
> { "CONVERT", SYM(CONVERT_SYM)},
> - { "CPU", SYM(CPU_SYM)},
> + { "CPU", SYM(CPU_SYM)},
> { "CREATE", SYM(CREATE)},
> { "CROSS", SYM(CROSS)},
> { "CUBE", SYM(CUBE_SYM)},
> @@ -672,6 +673,7 @@ SYMBOL symbols[] = {
> { "TINYINT", SYM(TINYINT)},
> { "TINYTEXT", SYM(TINYTEXT)},
> { "TO", SYM(TO_SYM)},
> + { "TO_DATE", SYM(TO_DATE)},
> { "TRAILING", SYM(TRAILING)},
> { "TRANSACTION", SYM(TRANSACTION_SYM)},
> { "TRANSACTIONAL", SYM(TRANSACTIONAL_SYM)},
> diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
> index 6fc288c0551..b52c4ef5336 100644
> --- a/sql/sql_yacc.yy
> +++ b/sql/sql_yacc.yy
> @@ -827,6 +826,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t
*yystacksize);
> %token <kwd> CONTAINS_SYM /* SQL-2003-N */
> %token <kwd> CONTEXT_SYM
> %token <kwd> CONTRIBUTORS_SYM
> +%token <kwd> CONVERSION_SYM
> %token <kwd> CPU_SYM
> %token <kwd> CUBE_SYM /* SQL-2003-R */
> %token <kwd> CURRENT_SYM /* SQL-2003-R */
> @@ -1142,6 +1142,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,
size_t *yystacksize);
> %token <kwd> TRANSACTION_SYM
> %token <kwd> TRANSACTIONAL_SYM
> %token <kwd> THREADS_SYM
> +%token <kwd> TO_DATE /* PLSQL function */
> %token <kwd> TRIGGERS_SYM
> %token <kwd> TRIM_ORACLE
> %token <kwd> TRUNCATE_SYM
Don't forget to add the two new keywords into the rule
"keyword_func_sp_var_and_label", so they are still allowed as
table, column names etc, for backward compatibility.
> @@ -10413,6 +10414,19 @@ column_default_non_parenthesized_expr:
> $7))))
> MYSQL_YYABORT;
> }
> + | TO_DATE '(' expr ',' expr ')'
> + {
> + $$= new (thd->mem_root) Item_func_to_date(thd, $3, $5);
> + if (unlikely($$ == NULL))
> + MYSQL_YYABORT;
> + }
> + | TO_DATE '(' expr DEFAULT expr ON CONVERSION_SYM ERROR_SYM
> + ',' expr ')'
> + {
> + $$= new (thd->mem_root) Item_func_to_date(thd, $3, $10, $5);
> + if (unlikely($$ == NULL))
> + MYSQL_YYABORT;
> + }
> ;
>
> primary_expr:
_______________________________________________
developers mailing list -- [email protected]
To unsubscribe send an email to [email protected]
