Debian Codegraph @

mysql-test/suite/compat/oracle/t/func_to_char.test

raw
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;