database/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-07 00:04:31 +03:00
Code Activity
Files
main
mariadb/mysql-test/suite/compat/oracle/t/func_to_char.test
Alexander Barkov 2dc9b8b78e MDEV-36216 TO_CHAR FM format not recognized in SQL_MODE=Oracle 
Adding support for the "FM" format in function TO_CHAR(date_time, fmt).

"FM" in the format string disables padding of all components following it.

So now TO_CHAR() works as follows:

- By default string format components DAY (weekday name) and
  MONTH (month name) are right-padded with spaces to the maximum
  possible DAY and MONTH name lengths respectively,
  according to the current locale specified in @@lc_time_names.
  So for example, with lc_time_names='en_US' all month names are
  right-padded with spaces up to 9 characters ('September' is the longest).

    SET lc_time_names='en_US';
    SELECT TO_CHAR('0001-02-03', 'MONTH');   -> 'February '  (padded to 9 chars)

  NEW: When typed after FM, DAY and MONTH names are not right-padded
  with trailing spaces any more:

   SET lc_time_names='en_US';
   SELECT TO_CHAR('0001-02-03', 'FMMONTH'); -> 'February' (not padded)

- By default numeric components YYYY, YYY, YY, Y, DD, H12, H24, MI, SS
  are left-padded with leading digits '0' up to the maximum possible
  number of digits in the component (e.g. 4 for YYYY):

    SELECT TO_CHAR('0001-02-03', 'YYYY'); -> '0001' (padded to 4 chars)

  NEW: When typed after FM, these numeric components are not left-padded
  with leading zeros any more:

    SELECT TO_CHAR('0001-02-03', 'FMYYYY'); -> '1' (not padded)

- If FM is specified multiple times in a format string,
  every FM negates the previous padding state:
  * an odd FM disables padding
  * an even FM enables padding

Implementation details:
- Adding a helper class Date_time_format_oracle.
- Adding a helper method Date_time_format_oracle::append_lex_cstring()
- Moving the function append_val() to Date_time_format_oracle as a method.
- Moving the function make_date_time_oracle() to Date_time_format_oracle
  as a method format().
- Adding helper methods month_name() and day_name() in class MY_LOCALE,
  to return the corresponding components as LEX_CSTRINGs.
2025-04-16 12:55:49 +04:00

308 lines
14 KiB
Plaintext
Raw Permalink Blame History

##############################################################
# testcase for TO_CHAR() function for oracle
# Part of MDEV-20017 Implement TO_CHAR() Oracle compatible function
##############################################################
# Save sql_mode
set @save_sql_mode=@@sql_mode;
--echo #
--echo # test for datetime
--echo #
CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime);
INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00');
INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59');
INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00');
INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00');
CREATE TABLE t_to_char2(c1 timestamp);
INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39');
INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00');
INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10');
# test for timestamp
SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2;
SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2;
# test full output format
--echo #
--echo # test YYYY/YY/MM/DD/HH/HH24/MI/SS
--echo #
SELECT TO_CHAR(c1, 'YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:mi:ss') AS C3 FROM t_to_char1;
--echo #
--echo # test YYY/Y/MON/DD/DY/HH/HH12/MI/SS
--echo #
SELECT TO_CHAR(c1, 'YYY-MON-DD') AS C1, TO_CHAR(c2, 'HH12:MI:SS') AS C2, TO_CHAR(c3, 'Y-MONTH-DY HH:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'yyy-Mon-Dd') AS C1, TO_CHAR(c2, 'Hh12:mi:Ss') AS C2, TO_CHAR(c3, 'y-Month-Dy Hh:Mi:Ss') AS C3 FROM t_to_char1;
--echo #
--echo # test RRRR/RR/DAY
--echo #
SELECT TO_CHAR(c1, 'RRRR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'RRRR-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'RR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'Rrrr-Mm-Dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'Rrrr-mm-dd Hh24:mi:ss') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'rr-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:Mi:ss') AS C3 FROM t_to_char1;
--echo #
--echo # test AD/A.D./BC/B.C./AM/A.M./PM/P.M.
--echo #
SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'BCYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'BCYYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'B.C.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'B.C.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'bcyyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'BcYYyy-MM-DD Hh24:mi:sS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'b.c.yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mI:Ss') AS C2, TO_CHAR(c3, 'b.C.Yyyy-Mm-dd hH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'PMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD P.M.HH24:MI:SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'pmHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD p.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;
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 format without order
--echo #
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;
SELECT TO_CHAR(c1, 'yyy-Dd-Mon') AS C1, TO_CHAR(c2, 'mi:Hh12:Ss') AS C2, TO_CHAR(c3, 'Ss:Hh:Mi Dy-y-Month') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'Dd-Mm-Rrrr') AS C1, TO_CHAR(c2, 'ss:hh:mi') AS C2, TO_CHAR(c3, 'ss:Rrrr-hh24-dd mon:mi') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'YYYYA.D.-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
--echo #
--echo # test for special characters
--echo #
SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'YYYY!!MM@DD') AS C1, TO_CHAR(c2, 'HH#MI$SS') AS C2, TO_CHAR(c3, 'YY%MM^DD*HH24(MI)SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'YYYY_MM+DD') AS C1, TO_CHAR(c2, 'HH=MI{SS') AS C2, TO_CHAR(c3, 'YY}MMDDHH24MISS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'YYYY,MM.DD') AS C1, TO_CHAR(c2, 'HH/MI;SS') AS C2, TO_CHAR(c3, 'YY>MM<DD]HH24[MI\SS') AS C3 FROM t_to_char1;
SELECT TO_CHAR(c1, 'YYYY||||MM|DD') AS C1, TO_CHAR(c2, 'HH&|MI|&|SS') AS C2, TO_CHAR(c3, 'YY&&&\\MM|&&|DD HH24|| MI&||"abx"|SS') AS C3 FROM t_to_char1;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY&MM-DD') FROM t_to_char1 where c0=1;
SELECT TO_CHAR(c1, 'YYYY"abx"MM"bsz"DD') AS C1 FROM t_to_char1;
--echo #
--echo # test for other locale
--echo #
SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';
SET lc_time_names='zh_TW';
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
SET lc_time_names='de_DE';
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
SET lc_time_names='en_US';
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
SET lc_time_names='zh_CN';
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
--echo #
--echo # test for invalid format
--echo #
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYYaxMON-DAY') FROM t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1;
--error ER_STD_INVALID_ARGUMENT
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;
SET character_set_client='latin1';
SET character_set_connection='latin1';
SET character_set_results='latin1';
--echo #
--echo # test for unusual format
--echo #
select to_char(c3, 'YYYYYYYYYYYYYYY') from t_to_char1;
select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1;
--echo #
--echo # oracle max length is 144
--echo #
--error ER_STD_INVALID_ARGUMENT
select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY') from t_to_char1 where c0 = 1;
CREATE TABLE t_f(c1 varchar(150));
insert into t_f values('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD');
select to_char('2000-11-11', c1) from t_f;
DROP TABLE t_f;
select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM') from t_to_char1 where c0 = 1;
--echo #
--echo # now only support two parameter.
--echo #
select to_char(c3) from t_to_char1 where c0 =1;
select to_char(c3, "YYYY-MM-DD HH:MI:SS") from t_to_char1 where c0 =1;
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select to_char(c3, "YYYY-MM-DD HH:MI:SS", "zh_CN") from t_to_char1 where c0 = 1;
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select to_char(c3, "YYYY-MM-DD HH:MI:SS", "NLS_DATE_LANGUAGE = zh_CN") from t_to_char1 where c0 = 1;
--echo #
--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
--echo #
--error ER_STD_INVALID_ARGUMENT
select to_char(1, 'yyyy');
--error ER_STD_INVALID_ARGUMENT
select to_char(1.1, 'yyyy');
CREATE TABLE t_a(c1 int, c2 float, c3 decimal, c4 char(20), c5 varchar(20), c6 nchar(20), c7 nvarchar(20));
insert into t_a VALUES (1, 3.2, 2002.02, '2000-11-11', '2000-11-11', '2000-11-11', '2000-11-11');
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c1, 'YYYY') from t_a;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c2, 'YYYY') from t_a;
--error ER_STD_INVALID_ARGUMENT
SELECT TO_CHAR(c3, 'YYYY') from t_a;
SELECT TO_CHAR(c4, 'YYYY') from t_a;
SELECT TO_CHAR(c5, 'YYYY') from t_a;
SELECT TO_CHAR(c6, 'YYYY') from t_a;
SELECT TO_CHAR(c7, 'YYYY') from t_a;
DROP TABLE t_a;
CREATE TABLE t_b(c0 int, c1 char(20), c2 varchar(20), c3 nchar(20), c4 nvarchar(20));
INSERT INTO t_b VALUES (1111, 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD');
SELECT TO_CHAR('2000-11-11', c0) FROM t_b;
SELECT TO_CHAR('2000-11-11', c1) FROM t_b;
SELECT TO_CHAR('2000-11-11', c2) FROM t_b;
SELECT TO_CHAR('2000-11-11', c3) FROM t_b;
SELECT TO_CHAR('2000-11-11', c4) FROM t_b;
DROP TABLE t_b;
EXPLAIN EXTENDED SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char1;
--echo #
--echo # test for time type with date format string
--echo #
SELECT TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS') from t_to_char1;
SELECT TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS') from t_to_char1;
SELECT TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS') from t_to_char1;
SELECT TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS') from t_to_char1;
DROP TABLE t_to_char1;
DROP TABLE t_to_char2;
--echo #
--echo # Test strict mode
--echo #
create table t1 (a datetime, b int, f varchar(30)) engine=myisam;
insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MM-DD");
insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MQ-DD");
create table t2 (a varchar(30)) engine=myisam;
insert into t2 select to_char(a,f) from t1;
set @@sql_mode="STRICT_ALL_TABLES";
--error ER_STD_INVALID_ARGUMENT
insert into t2 select to_char(a,f) from t1;
select * from t2;
drop table t1,t2;
set @local.sql_mode=@sql_mode;
--echo #
--echo # MDEV-29152: Assertion failed ... upon TO_CHAR with wrong argument
--echo #
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TO_CHAR((VALUES('2022-12-12','2020-10-10')));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TO_CHAR((STR_TO_DATE('2023-01-01', '%d-%m-%Y'), 'YYYY-MM-DD') );
--echo #
--echo # MDEV-36216 TO_CHAR FM format not recognized in SQL_MODE=Oracle
--echo #
SET NAMES utf8mb3;
CREATE TABLE t1 (fmt VARCHAR(256));
INSERT INTO t1 VALUES
/* Add the slash character before FM to see the position of FM in the results */
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('/FMYYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-/FMMM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-/FMDD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD /FMHH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:/FMMI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:/FMSS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS /FMDAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY /FMMONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; /FMYYYY-MM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-/FMMM-DD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-/FMDD HH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD /FMHH24:MI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:/FMMI:SS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:/FMSS DAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS /FMDAY MONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY /FMMONTH;'),
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH/FM;'),
/*Formats not covered above */
('YYY YY Y HH12; /FMYYY YY Y HH12;'),
/*FM specified multiple times*/
('FMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'),
('FMFMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'),
(
'YYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; '
'FMYYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; '
'FMYYYY-MM-DD [DAY] [MONTH];'
),
/*Corner cases*/
('FX') /*Unknown format starting with 'F'*/,
('F') /*Unexpected end of the format string*/;
SET lc_time_names='en_US';
SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
SET sql_mode=ORACLE;
CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
SET sql_mode=DEFAULT;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;
SET lc_time_names='zh_CN';
SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
SET sql_mode=ORACLE;
CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
SET sql_mode=DEFAULT;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;
SET lc_time_names=DEFAULT;
DROP TABLE t1;
View Git Blame Copy Permalink