summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/func_extract.result
blob: bebb8c717f60fb35783f4d48b37e5dec5f2ad288 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
#
# MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument
#
CREATE TABLE t1 (v VARCHAR(64), ll BIGINT, t TIME, dt DATETIME, d DATE);
CREATE TABLE t2 AS SELECT
EXTRACT(DAY FROM t),
EXTRACT(DAY_HOUR FROM t),
EXTRACT(DAY_MINUTE FROM t),
EXTRACT(DAY_SECOND FROM t),
EXTRACT(DAY_MICROSECOND FROM t),
EXTRACT(DAY FROM d),
EXTRACT(DAY_HOUR FROM d),
EXTRACT(DAY_MINUTE FROM d),
EXTRACT(DAY_SECOND FROM d),
EXTRACT(DAY_MICROSECOND FROM d),
EXTRACT(DAY FROM v),
EXTRACT(DAY_HOUR FROM v),
EXTRACT(DAY_MINUTE FROM v),
EXTRACT(DAY_SECOND FROM v),
EXTRACT(DAY_MICROSECOND FROM v),
EXTRACT(DAY FROM ll),
EXTRACT(DAY_HOUR FROM ll),
EXTRACT(DAY_MINUTE FROM ll),
EXTRACT(DAY_SECOND FROM ll),
EXTRACT(DAY_MICROSECOND FROM ll)
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `EXTRACT(DAY FROM t)` int(3) DEFAULT NULL,
  `EXTRACT(DAY_HOUR FROM t)` int(5) DEFAULT NULL,
  `EXTRACT(DAY_MINUTE FROM t)` int(7) DEFAULT NULL,
  `EXTRACT(DAY_SECOND FROM t)` int(9) DEFAULT NULL,
  `EXTRACT(DAY_MICROSECOND FROM t)` bigint(15) DEFAULT NULL,
  `EXTRACT(DAY FROM d)` int(3) DEFAULT NULL,
  `EXTRACT(DAY_HOUR FROM d)` int(5) DEFAULT NULL,
  `EXTRACT(DAY_MINUTE FROM d)` int(7) DEFAULT NULL,
  `EXTRACT(DAY_SECOND FROM d)` int(9) DEFAULT NULL,
  `EXTRACT(DAY_MICROSECOND FROM d)` bigint(15) DEFAULT NULL,
  `EXTRACT(DAY FROM v)` int(8) DEFAULT NULL,
  `EXTRACT(DAY_HOUR FROM v)` int(10) DEFAULT NULL,
  `EXTRACT(DAY_MINUTE FROM v)` bigint(12) DEFAULT NULL,
  `EXTRACT(DAY_SECOND FROM v)` bigint(14) DEFAULT NULL,
  `EXTRACT(DAY_MICROSECOND FROM v)` bigint(20) DEFAULT NULL,
  `EXTRACT(DAY FROM ll)` int(8) DEFAULT NULL,
  `EXTRACT(DAY_HOUR FROM ll)` int(10) DEFAULT NULL,
  `EXTRACT(DAY_MINUTE FROM ll)` bigint(12) DEFAULT NULL,
  `EXTRACT(DAY_SECOND FROM ll)` bigint(14) DEFAULT NULL,
  `EXTRACT(DAY_MICROSECOND FROM ll)` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64), b DECIMAL(32,9));
INSERT INTO t1 VALUES
('9999-12-31 23:59:59.123456', 99991231235959.123456),
('2001-01-01 10:20:30.123456', 20010101102030.123456),
('4294967296:59:59.123456', 42949672965959.123456),
('4294967295:59:59.123456', 42949672955959.123456),
('87649416:59:59.123456', 876494165959.123456),
('87649415:59:59.123456', 876494155959.123456),
('87649414:59:59.123456', 876494145959.123456),
('9999:59:59.123456', 99995959.123456),
('9999:01:01.123456', 99990101.123456),
('9999:01:01', 99990101),
('0.999999', 0.999999),
('0.99999', 0.99999),
('0.9999', 0.9999),
('0.999', 0.999),
('0.99', 0.99),
('0.9', 0.9),
('000000',0);
# Summary:
# Check that FUNC(varchar) and FUNC(decimal) give equal results
# Expect empty sets
SELECT a, b, EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(DAY_HOUR FROM a)<=>EXTRACT(DAY_HOUR FROM b));
a	b	EXTRACT(DAY_HOUR FROM a)	EXTRACT(DAY_HOUR FROM b)
SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (EXTRACT(DAY FROM a)<=>EXTRACT(DAY FROM b));
a	b	EXTRACT(DAY FROM a)	EXTRACT(DAY FROM b)
SELECT a, b, EXTRACT(HOUR FROM a), EXTRACT(HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(HOUR FROM a)<=>EXTRACT(HOUR FROM b));
a	b	EXTRACT(HOUR FROM a)	EXTRACT(HOUR FROM b)
SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (EXTRACT(MINUTE FROM a)<=>EXTRACT(MINUTE FROM b));
a	b	EXTRACT(MINUTE FROM a)	EXTRACT(MINUTE FROM b)
SELECT a, b, EXTRACT(SECOND FROM a), EXTRACT(SECOND FROM b) FROM t1 WHERE NOT (EXTRACT(SECOND FROM a)<=>EXTRACT(SECOND FROM b));
a	b	EXTRACT(SECOND FROM a)	EXTRACT(SECOND FROM b)
SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b));
a	b	EXTRACT(MICROSECOND FROM a)	EXTRACT(MICROSECOND FROM b)
# Detailed results
SELECT
a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM a) * 24 + EXTRACT(HOUR FROM a) AS dh,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a	cidm	dh	EXTRACT(DAY_HOUR FROM a)	EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)
9999-12-31 23:59:59.123456	NULL	767	3123	31	23	59	59	123456
2001-01-01 10:20:30.123456	NULL	34	110	1	10	20	30	123456
4294967296:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4294967295:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
87649416:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
87649415:59:59.123456	3652058 23:59:59.123456	87649415	365205823	3652058	23	59	59	123456
87649414:59:59.123456	3652058 22:59:59.123456	87649414	365205822	3652058	22	59	59	123456
9999:59:59.123456	416 15:59:59.123456	9999	41615	416	15	59	59	123456
9999:01:01.123456	416 15:01:01.123456	9999	41615	416	15	1	1	123456
9999:01:01	416 15:01:01.000000	9999	41615	416	15	1	1	0
0.999999	00:00:00.999999	0	0	0	0	0	0	999999
0.99999	00:00:00.999990	0	0	0	0	0	0	999990
0.9999	00:00:00.999900	0	0	0	0	0	0	999900
0.999	00:00:00.999000	0	0	0	0	0	0	999000
0.99	00:00:00.990000	0	0	0	0	0	0	990000
0.9	00:00:00.900000	0	0	0	0	0	0	900000
000000	00:00:00.000000	0	0	0	0	0	0	0
Warnings:
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '9999-12-31 23:59:59.123456'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 10:20:30.123456'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect interval value: '4294967296:59:59.123456'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect interval value: '4294967295:59:59.123456'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
Warning	1292	Incorrect interval value: '87649416:59:59.123456'
SELECT
b,
CAST(b AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM b) * 24 + EXTRACT(HOUR FROM b) AS dh,
EXTRACT(DAY_HOUR FROM b),
EXTRACT(DAY FROM b),
EXTRACT(HOUR FROM b),
EXTRACT(MINUTE FROM b),
EXTRACT(SECOND FROM b),
EXTRACT(MICROSECOND FROM b)
FROM t1;
b	cidm	dh	EXTRACT(DAY_HOUR FROM b)	EXTRACT(DAY FROM b)	EXTRACT(HOUR FROM b)	EXTRACT(MINUTE FROM b)	EXTRACT(SECOND FROM b)	EXTRACT(MICROSECOND FROM b)
99991231235959.123456000	NULL	767	3123	31	23	59	59	123456
20010101102030.123456000	NULL	34	110	1	10	20	30	123456
42949672965959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
42949672955959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
876494165959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
876494155959.123456000	3652058 23:59:59.123456	87649415	365205823	3652058	23	59	59	123456
876494145959.123456000	3652058 22:59:59.123456	87649414	365205822	3652058	22	59	59	123456
99995959.123456000	416 15:59:59.123456	9999	41615	416	15	59	59	123456
99990101.123456000	416 15:01:01.123456	9999	41615	416	15	1	1	123456
99990101.000000000	416 15:01:01.000000	9999	41615	416	15	1	1	0
0.999999000	00:00:00.999999	0	0	0	0	0	0	999999
0.999990000	00:00:00.999990	0	0	0	0	0	0	999990
0.999900000	00:00:00.999900	0	0	0	0	0	0	999900
0.999000000	00:00:00.999000	0	0	0	0	0	0	999000
0.990000000	00:00:00.990000	0	0	0	0	0	0	990000
0.900000000	00:00:00.900000	0	0	0	0	0	0	900000
0.000000000	00:00:00.000000	0	0	0	0	0	0	0
Warnings:
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '99991231235959.123456000'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '20010101102030.123456000'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '42949672965959.123456000'
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '42949672955959.123456000'
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '876494165959.123456000'
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning	1292	Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.123456000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.123456000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99995959.123456000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.123456000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.000000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999990000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999900000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.990000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.900000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000'
DROP TABLE t1;
# Special case: DAY + TIME
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('9999-01-01');
SELECT a,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a	EXTRACT(DAY_HOUR FROM a)	EXTRACT(DAY_MINUTE FROM a)	EXTRACT(DAY_SECOND FROM a)	EXTRACT(DAY_MICROSECOND FROM a)	EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)
9999-01-01	100	10000	1000000	1000000000000	1	0	0	0	0
DROP TABLE t1;
# Bad values
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('');
SELECT a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a	cidm	EXTRACT(DAY_HOUR FROM a)	EXTRACT(DAY_MINUTE FROM a)	EXTRACT(DAY_SECOND FROM a)	EXTRACT(DAY_MICROSECOND FROM a)	EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)
	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
Warning	1292	Incorrect interval value: ''
DROP TABLE t1;
# Backward compatibility
# This still parses as DATETIME
SELECT EXTRACT(YEAR  FROM '2001/02/03 10:20:30');
EXTRACT(YEAR  FROM '2001/02/03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30');
EXTRACT(MONTH FROM '2001/02/03 10:20:30')
2
SELECT EXTRACT(DAY   FROM '2001/02/03 10:20:30');
EXTRACT(DAY   FROM '2001/02/03 10:20:30')
3
SELECT EXTRACT(YEAR  FROM '01/02/03 10:20:30');
EXTRACT(YEAR  FROM '01/02/03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30');
EXTRACT(MONTH FROM '01/02/03 10:20:30')
2
SELECT EXTRACT(DAY   FROM '01/02/03 10:20:30');
EXTRACT(DAY   FROM '01/02/03 10:20:30')
3
SELECT EXTRACT(YEAR  FROM '01:02:03 10:20:30');
EXTRACT(YEAR  FROM '01:02:03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30');
EXTRACT(MONTH FROM '01:02:03 10:20:30')
2
SELECT EXTRACT(DAY   FROM '01:02:03 10:20:30');
EXTRACT(DAY   FROM '01:02:03 10:20:30')
3
# This still parses as DATETIME and returns NULL
SELECT EXTRACT(YEAR  FROM "2011-02-32 8:46:06.23434");
EXTRACT(YEAR  FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434");
EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(DAY   FROM "2011-02-32 8:46:06.23434");
EXTRACT(DAY   FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning	1292	Incorrect interval value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(HOUR  FROM "2011-02-32 8:46:06.23434");
EXTRACT(HOUR  FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning	1292	Incorrect interval value: '2011-02-32 8:46:06.23434'
# This still parses as DATE
SELECT EXTRACT(YEAR  FROM '2001/02/03');
EXTRACT(YEAR  FROM '2001/02/03')
2001
SELECT EXTRACT(MONTH FROM '2001/02/03');
EXTRACT(MONTH FROM '2001/02/03')
2
SELECT EXTRACT(DAY   FROM '2001/02/03');
EXTRACT(DAY   FROM '2001/02/03')
3
SELECT EXTRACT(YEAR  FROM '01/02/03');
EXTRACT(YEAR  FROM '01/02/03')
2001
SELECT EXTRACT(MONTH FROM '01/02/03');
EXTRACT(MONTH FROM '01/02/03')
2
SELECT EXTRACT(DAY   FROM '01/02/03');
EXTRACT(DAY   FROM '01/02/03')
3
SELECT EXTRACT(YEAR  FROM '01-02-03');
EXTRACT(YEAR  FROM '01-02-03')
2001
SELECT EXTRACT(MONTH FROM '01-02-03');
EXTRACT(MONTH FROM '01-02-03')
2
SELECT EXTRACT(DAY   FROM '01-02-03');
EXTRACT(DAY   FROM '01-02-03')
3
SELECT EXTRACT(YEAR  FROM '1-2-3');
EXTRACT(YEAR  FROM '1-2-3')
1
SELECT EXTRACT(MONTH FROM '1-2-3');
EXTRACT(MONTH FROM '1-2-3')
2
SELECT EXTRACT(DAY   FROM '1-2-3');
EXTRACT(DAY   FROM '1-2-3')
3
SELECT EXTRACT(HOUR  FROM '1-2-3');
EXTRACT(HOUR  FROM '1-2-3')
0
SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////');
EXTRACT(DAY FROM '2024-01-03 garbage /////')
3
Warnings:
Warning	1292	Truncated incorrect date value: '2024-01-03 garbage /////'
SELECT EXTRACT(DAY FROM '24-01-03 garbage /////');
EXTRACT(DAY FROM '24-01-03 garbage /////')
3
Warnings:
Warning	1292	Truncated incorrect date value: '24-01-03 garbage /////'
SELECT EXTRACT(DAY FROM '01-02-03');
EXTRACT(DAY FROM '01-02-03')
3
SELECT EXTRACT(DAY FROM '24:02:03T');
EXTRACT(DAY FROM '24:02:03T')
3
SELECT EXTRACT(DAY FROM '24-02-03');
EXTRACT(DAY FROM '24-02-03')
3
SELECT EXTRACT(DAY FROM '24/02/03');
EXTRACT(DAY FROM '24/02/03')
3
SELECT EXTRACT(DAY FROM '11111');
EXTRACT(DAY FROM '11111')
1
SELECT TIME('2001-01-01T'), TIME('2001-01-01T ');
TIME('2001-01-01T')	TIME('2001-01-01T ')
00:00:00	00:00:00
SELECT TIME('2001/01/01T'), TIME('2001/01/01T ');
TIME('2001/01/01T')	TIME('2001/01/01T ')
00:00:00	00:00:00
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
TIME('2001:01:01T')	TIME('2001:01:01T ')
00:00:00	00:00:00
SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T ');
EXTRACT(DAY FROM '2001-01-01T')	EXTRACT(DAY FROM '2001-01-01T ')
1	1
SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T ');
EXTRACT(DAY FROM '2001/01/01T')	EXTRACT(DAY FROM '2001/01/01T ')
1	1
SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T ');
EXTRACT(DAY FROM '2001:01:01T')	EXTRACT(DAY FROM '2001:01:01T ')
1	1
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
TIME('2001:01:01T')	TIME('2001:01:01T ')
00:00:00	00:00:00
SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T ');
EXTRACT(HOUR FROM '2001-01-01T')	EXTRACT(HOUR FROM '2001-01-01T ')
0	0
SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T ');
EXTRACT(HOUR FROM '2001/01/01T')	EXTRACT(HOUR FROM '2001/01/01T ')
0	0
SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T ');
EXTRACT(HOUR FROM '2001:01:01T')	EXTRACT(HOUR FROM '2001:01:01T ')
0	0
# This still parses as DATE and returns NULL (without trying TIME)
SELECT EXTRACT(DAY FROM '100000:02:03T');
EXTRACT(DAY FROM '100000:02:03T')
NULL
Warnings:
Warning	1292	Incorrect interval value: '100000:02:03T'
SELECT EXTRACT(DAY FROM '100000/02/03');
EXTRACT(DAY FROM '100000/02/03')
NULL
Warnings:
Warning	1292	Incorrect interval value: '100000/02/03'
SELECT EXTRACT(DAY FROM '100000-02-03');
EXTRACT(DAY FROM '100000-02-03')
NULL
Warnings:
Warning	1292	Incorrect interval value: '100000-02-03'
SELECT EXTRACT(DAY FROM '1111');
EXTRACT(DAY FROM '1111')
NULL
Warnings:
Warning	1292	Incorrect interval value: '1111'
SELECT EXTRACT(DAY FROM '111');
EXTRACT(DAY FROM '111')
NULL
Warnings:
Warning	1292	Incorrect interval value: '111'
SELECT EXTRACT(DAY FROM '11');
EXTRACT(DAY FROM '11')
NULL
Warnings:
Warning	1292	Incorrect interval value: '11'
SELECT EXTRACT(DAY FROM '1');
EXTRACT(DAY FROM '1')
NULL
Warnings:
Warning	1292	Incorrect interval value: '1'
# This still parses as TIME
SELECT EXTRACT(HOUR FROM '11111');
EXTRACT(HOUR FROM '11111')
1
SELECT EXTRACT(HOUR FROM '1111');
EXTRACT(HOUR FROM '1111')
0
SELECT EXTRACT(HOUR FROM '111');
EXTRACT(HOUR FROM '111')
0
SELECT EXTRACT(HOUR FROM '11');
EXTRACT(HOUR FROM '11')
0
SELECT EXTRACT(HOUR FROM '1');
EXTRACT(HOUR FROM '1')
0
SELECT TIME('01:02:03:');
TIME('01:02:03:')
01:02:03
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03:'
SELECT TIME('01:02:03-');
TIME('01:02:03-')
01:02:03
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03-'
SELECT TIME('01:02:03;');
TIME('01:02:03;')
01:02:03
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03;'
SELECT TIME('01:02:03/');
TIME('01:02:03/')
01:02:03
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03/'
SELECT EXTRACT(HOUR FROM '01:02:03:');
EXTRACT(HOUR FROM '01:02:03:')
1
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03:'
SELECT EXTRACT(HOUR FROM '01:02:03-');
EXTRACT(HOUR FROM '01:02:03-')
1
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03-'
SELECT EXTRACT(HOUR FROM '01:02:03;');
EXTRACT(HOUR FROM '01:02:03;')
1
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03;'
SELECT EXTRACT(HOUR FROM '01:02:03/');
EXTRACT(HOUR FROM '01:02:03/')
1
Warnings:
Warning	1292	Truncated incorrect time value: '01:02:03/'
# Backward compatibility preserved for YEAR and MONTH only
# (behavior has changed for DAY, see below)
SELECT EXTRACT(YEAR  FROM '01:02:03');
EXTRACT(YEAR  FROM '01:02:03')
2001
SELECT EXTRACT(MONTH FROM '01:02:03');
EXTRACT(MONTH FROM '01:02:03')
2
SELECT EXTRACT(YEAR  FROM '24:01:03 garbage /////');
EXTRACT(YEAR  FROM '24:01:03 garbage /////')
2024
Warnings:
Warning	1292	Truncated incorrect date value: '24:01:03 garbage /////'
SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////');
EXTRACT(MONTH FROM '24:01:03 garbage /////')
1
Warnings:
Warning	1292	Truncated incorrect date value: '24:01:03 garbage /////'
# This still parses as TIME 00:20:01
SELECT TIME('2001/01/01');
TIME('2001/01/01')
00:20:01
Warnings:
Warning	1292	Truncated incorrect time value: '2001/01/01'
SELECT TIME('2001-01-01');
TIME('2001-01-01')
00:20:01
Warnings:
Warning	1292	Truncated incorrect time value: '2001-01-01'
# This still parses as TIME and overflows to '838:59:59'
SELECT TIME('2001:01:01');
TIME('2001:01:01')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '2001:01:01'
# This used to parse as DATE, now parses as TIME interval
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2024:01:03 garbage /////'),
('24:01:03 garbage /////'),
('01:01:03 garbage /////'),
('2024:02:03'),
('100000:02:03'),
('24:02:03'),
('01:02:03'),
('01:02:03:'),
('01:02:03-'),
('01:02:03;'),
('01:02:03/'),
('20 10:20:30');
SELECT
EXTRACT(DAY FROM a),
EXTRACT(DAY_SECOND FROM a), a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm
FROM t1;
EXTRACT(DAY FROM a)	EXTRACT(DAY_SECOND FROM a)	a	cidm
84	84080103	2024:01:03 garbage /////	NULL
1	1000103	24:01:03 garbage /////	NULL
0	10103	01:01:03 garbage /////	NULL
84	84080203	2024:02:03	84 08:02:03.000000
4166	4166160203	100000:02:03	4166 16:02:03.000000
1	1000203	24:02:03	1 00:02:03.000000
0	10203	01:02:03	01:02:03.000000
0	10203	01:02:03:	01:02:03.000000
0	10203	01:02:03-	NULL
0	10203	01:02:03;	01:02:03.000000
0	10203	01:02:03/	01:02:03.000000
20	20102030	20 10:20:30	20 10:20:30.000000
Warnings:
Warning	1292	Truncated incorrect time value: '2024:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '2024:01:03 garbage /////'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '2024:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '24:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '24:01:03 garbage /////'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '24:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '01:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '01:01:03 garbage /////'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '01:01:03 garbage /////'
Warning	1292	Truncated incorrect time value: '01:02:03:'
Warning	1292	Truncated incorrect time value: '01:02:03:'
Warning	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03:'
Warning	1292	Truncated incorrect time value: '01:02:03-'
Warning	1292	Truncated incorrect time value: '01:02:03-'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '01:02:03-'
Warning	1292	Truncated incorrect time value: '01:02:03;'
Warning	1292	Truncated incorrect time value: '01:02:03;'
Warning	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03;'
Warning	1292	Truncated incorrect time value: '01:02:03/'
Warning	1292	Truncated incorrect time value: '01:02:03/'
Warning	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03/'
DROP TABLE t1;