| 1 |
############################################################## |
| 2 |
# testcase for TO_CHAR() function for oracle |
| 3 |
# Part of MDEV-20017 Implement TO_CHAR() Oracle compatible function |
| 4 |
############################################################## |
| 5 |
|
| 6 |
# Save sql_mode |
| 7 |
set @save_sql_mode=@@sql_mode; |
| 8 |
|
| 9 |
--echo # |
| 10 |
--echo # test for datetime |
| 11 |
--echo # |
| 12 |
|
| 13 |
CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime); |
| 14 |
|
| 15 |
INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00'); |
| 16 |
INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59'); |
| 17 |
INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00'); |
| 18 |
INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00'); |
| 19 |
|
| 20 |
CREATE TABLE t_to_char2(c1 timestamp); |
| 21 |
INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39'); |
| 22 |
INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00'); |
| 23 |
INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10'); |
| 24 |
|
| 25 |
# test for timestamp |
| 26 |
SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2; |
| 27 |
SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2; |
| 28 |
|
| 29 |
# test full output format |
| 30 |
--echo # |
| 31 |
--echo # test YYYY/YY/MM/DD/HH/HH24/MI/SS |
| 32 |
--echo # |
| 33 |
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; |
| 34 |
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; |
| 35 |
|
| 36 |
--echo # |
| 37 |
--echo # test YYY/Y/MON/DD/DY/HH/HH12/MI/SS |
| 38 |
--echo # |
| 39 |
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; |
| 40 |
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; |
| 41 |
|
| 42 |
--echo # |
| 43 |
--echo # test RRRR/RR/DAY |
| 44 |
--echo # |
| 45 |
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; |
| 46 |
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; |
| 47 |
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; |
| 48 |
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; |
| 49 |
|
| 50 |
--echo # |
| 51 |
--echo # test AD/A.D./BC/B.C./AM/A.M./PM/P.M. |
| 52 |
--echo # |
| 53 |
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; |
| 54 |
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; |
| 55 |
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; |
| 56 |
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; |
| 57 |
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; |
| 58 |
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; |
| 59 |
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; |
| 60 |
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; |
| 61 |
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; |
| 62 |
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; |
| 63 |
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; |
| 64 |
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; |
| 65 |
|
| 66 |
--echo # |
| 67 |
--echo # test format without order |
| 68 |
--echo # |
| 69 |
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; |
| 70 |
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; |
| 71 |
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; |
| 72 |
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; |
| 73 |
|
| 74 |
--echo # |
| 75 |
--echo # test for special characters |
| 76 |
--echo # |
| 77 |
SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1; |
| 78 |
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; |
| 79 |
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; |
| 80 |
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; |
| 81 |
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; |
| 82 |
--error ER_STD_INVALID_ARGUMENT |
| 83 |
SELECT TO_CHAR(c1, 'YYYY&MM-DD') FROM t_to_char1 where c0=1; |
| 84 |
SELECT TO_CHAR(c1, 'YYYY"abx"MM"bsz"DD') AS C1 FROM t_to_char1; |
| 85 |
|
| 86 |
--echo # |
| 87 |
--echo # test for other locale |
| 88 |
--echo # |
| 89 |
SET character_set_client='utf8'; |
| 90 |
SET character_set_connection='utf8'; |
| 91 |
SET character_set_results='utf8'; |
| 92 |
SET lc_time_names='zh_TW'; |
| 93 |
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1; |
| 94 |
SET lc_time_names='de_DE'; |
| 95 |
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1; |
| 96 |
SET lc_time_names='en_US'; |
| 97 |
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1; |
| 98 |
SET lc_time_names='zh_CN'; |
| 99 |
SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1; |
| 100 |
|
| 101 |
--echo # |
| 102 |
--echo # test for invalid format |
| 103 |
--echo # |
| 104 |
|
| 105 |
--error ER_STD_INVALID_ARGUMENT |
| 106 |
SELECT TO_CHAR(c1, 'YYYYaxMON-DAY') FROM t_to_char1 where c0 = 1; |
| 107 |
--error ER_STD_INVALID_ARGUMENT |
| 108 |
SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1; |
| 109 |
--error ER_STD_INVALID_ARGUMENT |
| 110 |
SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1; |
| 111 |
--error ER_STD_INVALID_ARGUMENT |
| 112 |
SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1; |
| 113 |
--error ER_STD_INVALID_ARGUMENT |
| 114 |
SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1; |
| 115 |
--error ER_STD_INVALID_ARGUMENT |
| 116 |
select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1; |
| 117 |
--error ER_STD_INVALID_ARGUMENT |
| 118 |
select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1; |
| 119 |
--error ER_STD_INVALID_ARGUMENT |
| 120 |
select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1; |
| 121 |
|
| 122 |
SET character_set_client='latin1'; |
| 123 |
SET character_set_connection='latin1'; |
| 124 |
SET character_set_results='latin1'; |
| 125 |
--echo # |
| 126 |
--echo # test for unusual format |
| 127 |
--echo # |
| 128 |
select to_char(c3, 'YYYYYYYYYYYYYYY') from t_to_char1; |
| 129 |
select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1; |
| 130 |
|
| 131 |
--echo # |
| 132 |
--echo # oracle max length is 144 |
| 133 |
--echo # |
| 134 |
|
| 135 |
--error ER_STD_INVALID_ARGUMENT |
| 136 |
select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY') from t_to_char1 where c0 = 1; |
| 137 |
CREATE TABLE t_f(c1 varchar(150)); |
| 138 |
insert into t_f values('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD'); |
| 139 |
select to_char('2000-11-11', c1) from t_f; |
| 140 |
DROP TABLE t_f; |
| 141 |
select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM') from t_to_char1 where c0 = 1; |
| 142 |
|
| 143 |
--echo # |
| 144 |
--echo # now only support two parameter. |
| 145 |
--echo # |
| 146 |
select to_char(c3) from t_to_char1 where c0 =1; |
| 147 |
select to_char(c3, "YYYY-MM-DD HH:MI:SS") from t_to_char1 where c0 =1; |
| 148 |
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT |
| 149 |
select to_char(c3, "YYYY-MM-DD HH:MI:SS", "zh_CN") from t_to_char1 where c0 = 1; |
| 150 |
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT |
| 151 |
select to_char(c3, "YYYY-MM-DD HH:MI:SS", "NLS_DATE_LANGUAGE = zh_CN") from t_to_char1 where c0 = 1; |
| 152 |
|
| 153 |
--echo # |
| 154 |
--echo # oracle support format but mariadb does not support |
| 155 |
--echo # |
| 156 |
--error ER_STD_INVALID_ARGUMENT |
| 157 |
select to_char(c3, 'DDD') from t_to_char1 where c0 = 1; |
| 158 |
--error ER_STD_INVALID_ARGUMENT |
| 159 |
select to_char(c3, 'D') from t_to_char1 where c0 = 1; |
| 160 |
--error ER_STD_INVALID_ARGUMENT |
| 161 |
select to_char(c3, 'DS') from t_to_char1 where c0 = 1; |
| 162 |
--error ER_STD_INVALID_ARGUMENT |
| 163 |
select to_char(c3, 'IY') from t_to_char1 where c0 = 1; |
| 164 |
--error ER_STD_INVALID_ARGUMENT |
| 165 |
select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1; |
| 166 |
|
| 167 |
--echo # |
| 168 |
--echo # test for first argument data type |
| 169 |
--echo # |
| 170 |
--error ER_STD_INVALID_ARGUMENT |
| 171 |
select to_char(1, 'yyyy'); |
| 172 |
--error ER_STD_INVALID_ARGUMENT |
| 173 |
select to_char(1.1, 'yyyy'); |
| 174 |
CREATE TABLE t_a(c1 int, c2 float, c3 decimal, c4 char(20), c5 varchar(20), c6 nchar(20), c7 nvarchar(20)); |
| 175 |
insert into t_a VALUES (1, 3.2, 2002.02, '2000-11-11', '2000-11-11', '2000-11-11', '2000-11-11'); |
| 176 |
--error ER_STD_INVALID_ARGUMENT |
| 177 |
SELECT TO_CHAR(c1, 'YYYY') from t_a; |
| 178 |
--error ER_STD_INVALID_ARGUMENT |
| 179 |
SELECT TO_CHAR(c2, 'YYYY') from t_a; |
| 180 |
--error ER_STD_INVALID_ARGUMENT |
| 181 |
SELECT TO_CHAR(c3, 'YYYY') from t_a; |
| 182 |
SELECT TO_CHAR(c4, 'YYYY') from t_a; |
| 183 |
SELECT TO_CHAR(c5, 'YYYY') from t_a; |
| 184 |
SELECT TO_CHAR(c6, 'YYYY') from t_a; |
| 185 |
SELECT TO_CHAR(c7, 'YYYY') from t_a; |
| 186 |
DROP TABLE t_a; |
| 187 |
|
| 188 |
CREATE TABLE t_b(c0 int, c1 char(20), c2 varchar(20), c3 nchar(20), c4 nvarchar(20)); |
| 189 |
INSERT INTO t_b VALUES (1111, 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD'); |
| 190 |
SELECT TO_CHAR('2000-11-11', c0) FROM t_b; |
| 191 |
SELECT TO_CHAR('2000-11-11', c1) FROM t_b; |
| 192 |
SELECT TO_CHAR('2000-11-11', c2) FROM t_b; |
| 193 |
SELECT TO_CHAR('2000-11-11', c3) FROM t_b; |
| 194 |
SELECT TO_CHAR('2000-11-11', c4) FROM t_b; |
| 195 |
DROP TABLE t_b; |
| 196 |
|
| 197 |
EXPLAIN EXTENDED SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char1; |
| 198 |
|
| 199 |
--echo # |
| 200 |
--echo # test for time type with date format string |
| 201 |
--echo # |
| 202 |
SELECT TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS') from t_to_char1; |
| 203 |
SELECT TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS') from t_to_char1; |
| 204 |
SELECT TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS') from t_to_char1; |
| 205 |
SELECT TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS') from t_to_char1; |
| 206 |
|
| 207 |
DROP TABLE t_to_char1; |
| 208 |
DROP TABLE t_to_char2; |
| 209 |
|
| 210 |
|
| 211 |
--echo # |
| 212 |
--echo # Test strict mode |
| 213 |
--echo # |
| 214 |
|
| 215 |
create table t1 (a datetime, b int, f varchar(30)) engine=myisam; |
| 216 |
insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MM-DD"); |
| 217 |
insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MQ-DD"); |
| 218 |
create table t2 (a varchar(30)) engine=myisam; |
| 219 |
insert into t2 select to_char(a,f) from t1; |
| 220 |
set @@sql_mode="STRICT_ALL_TABLES"; |
| 221 |
--error ER_STD_INVALID_ARGUMENT |
| 222 |
insert into t2 select to_char(a,f) from t1; |
| 223 |
select * from t2; |
| 224 |
drop table t1,t2; |
| 225 |
set @local.sql_mode=@sql_mode; |
| 226 |
|
| 227 |
--echo # |
| 228 |
--echo # MDEV-29152: Assertion failed ... upon TO_CHAR with wrong argument |
| 229 |
--echo # |
| 230 |
|
| 231 |
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION |
| 232 |
SELECT TO_CHAR((VALUES('2022-12-12','2020-10-10'))); |
| 233 |
|
| 234 |
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION |
| 235 |
SELECT TO_CHAR((STR_TO_DATE('2023-01-01', '%d-%m-%Y'), 'YYYY-MM-DD') ); |
| 236 |
|
| 237 |
--echo # |
| 238 |
--echo # MDEV-28136 MariaDB ASAN Unknown Crash in Item_func_to_char::parse_format_string |
| 239 |
--echo # |
| 240 |
|
| 241 |
CREATE TABLE t1( a varchar(1)) ; |
| 242 |
INSERT INTO t1 VALUES ('p'); |
| 243 |
select to_char('2000-11-11', a) from t1; |
| 244 |
drop table t1; |
| 245 |
|
| 246 |
--echo # |
| 247 |
--echo # MDEV-36216 TO_CHAR FM format not recognized in SQL_MODE=Oracle |
| 248 |
--echo # |
| 249 |
|
| 250 |
SET NAMES utf8mb3; |
| 251 |
CREATE TABLE t1 (fmt VARCHAR(256)); |
| 252 |
INSERT INTO t1 VALUES |
| 253 |
|
| 254 |
/* Add the slash character before FM to see the position of FM in the results */ |
| 255 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 256 |
('/FMYYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 257 |
('YYYY-/FMMM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 258 |
('YYYY-MM-/FMDD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 259 |
('YYYY-MM-DD /FMHH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 260 |
('YYYY-MM-DD HH24:/FMMI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 261 |
('YYYY-MM-DD HH24:MI:/FMSS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 262 |
('YYYY-MM-DD HH24:MI:SS /FMDAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 263 |
('YYYY-MM-DD HH24:MI:SS DAY /FMMONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 264 |
|
| 265 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; /FMYYYY-MM-DD HH24:MI:SS DAY MONTH;'), |
| 266 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-/FMMM-DD HH24:MI:SS DAY MONTH;'), |
| 267 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-/FMDD HH24:MI:SS DAY MONTH;'), |
| 268 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD /FMHH24:MI:SS DAY MONTH;'), |
| 269 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:/FMMI:SS DAY MONTH;'), |
| 270 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:/FMSS DAY MONTH;'), |
| 271 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS /FMDAY MONTH;'), |
| 272 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY /FMMONTH;'), |
| 273 |
('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH/FM;'), |
| 274 |
|
| 275 |
/*Formats not covered above */ |
| 276 |
('YYY YY Y HH12; /FMYYY YY Y HH12;'), |
| 277 |
|
| 278 |
/*FM specified multiple times*/ |
| 279 |
('FMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'), |
| 280 |
('FMFMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'), |
| 281 |
( |
| 282 |
'YYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; ' |
| 283 |
'FMYYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; ' |
| 284 |
'FMYYYY-MM-DD [DAY] [MONTH];' |
| 285 |
), |
| 286 |
|
| 287 |
/*Corner cases*/ |
| 288 |
('FX') /*Unknown format starting with 'F'*/, |
| 289 |
('F') /*Unexpected end of the format string*/; |
| 290 |
|
| 291 |
SET lc_time_names='en_US'; |
| 292 |
SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1; |
| 293 |
SET sql_mode=ORACLE; |
| 294 |
CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1; |
| 295 |
SHOW CREATE VIEW v1; |
| 296 |
SELECT * FROM v1; |
| 297 |
SET sql_mode=DEFAULT; |
| 298 |
SHOW CREATE VIEW v1; |
| 299 |
SELECT * FROM v1; |
| 300 |
DROP VIEW v1; |
| 301 |
|
| 302 |
SET lc_time_names='zh_CN'; |
| 303 |
SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1; |
| 304 |
SET sql_mode=ORACLE; |
| 305 |
CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1; |
| 306 |
SHOW CREATE VIEW v1; |
| 307 |
SELECT * FROM v1; |
| 308 |
SET sql_mode=DEFAULT; |
| 309 |
SHOW CREATE VIEW v1; |
| 310 |
SELECT * FROM v1; |
| 311 |
DROP VIEW v1; |
| 312 |
|
| 313 |
SET lc_time_names=DEFAULT; |
| 314 |
|
| 315 |
DROP TABLE t1; |