1# 2# MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument 3# 4CREATE TABLE t1 (v VARCHAR(64), ll BIGINT, t TIME, dt DATETIME, d DATE); 5CREATE TABLE t2 AS SELECT 6EXTRACT(DAY FROM t), 7EXTRACT(DAY_HOUR FROM t), 8EXTRACT(DAY_MINUTE FROM t), 9EXTRACT(DAY_SECOND FROM t), 10EXTRACT(DAY_MICROSECOND FROM t), 11EXTRACT(DAY FROM d), 12EXTRACT(DAY_HOUR FROM d), 13EXTRACT(DAY_MINUTE FROM d), 14EXTRACT(DAY_SECOND FROM d), 15EXTRACT(DAY_MICROSECOND FROM d), 16EXTRACT(DAY FROM v), 17EXTRACT(DAY_HOUR FROM v), 18EXTRACT(DAY_MINUTE FROM v), 19EXTRACT(DAY_SECOND FROM v), 20EXTRACT(DAY_MICROSECOND FROM v), 21EXTRACT(DAY FROM ll), 22EXTRACT(DAY_HOUR FROM ll), 23EXTRACT(DAY_MINUTE FROM ll), 24EXTRACT(DAY_SECOND FROM ll), 25EXTRACT(DAY_MICROSECOND FROM ll) 26FROM t1; 27SHOW CREATE TABLE t2; 28Table Create Table 29t2 CREATE TABLE `t2` ( 30 `EXTRACT(DAY FROM t)` int(3) DEFAULT NULL, 31 `EXTRACT(DAY_HOUR FROM t)` int(5) DEFAULT NULL, 32 `EXTRACT(DAY_MINUTE FROM t)` int(7) DEFAULT NULL, 33 `EXTRACT(DAY_SECOND FROM t)` int(9) DEFAULT NULL, 34 `EXTRACT(DAY_MICROSECOND FROM t)` bigint(15) DEFAULT NULL, 35 `EXTRACT(DAY FROM d)` int(3) DEFAULT NULL, 36 `EXTRACT(DAY_HOUR FROM d)` int(5) DEFAULT NULL, 37 `EXTRACT(DAY_MINUTE FROM d)` int(7) DEFAULT NULL, 38 `EXTRACT(DAY_SECOND FROM d)` int(9) DEFAULT NULL, 39 `EXTRACT(DAY_MICROSECOND FROM d)` bigint(15) DEFAULT NULL, 40 `EXTRACT(DAY FROM v)` int(8) DEFAULT NULL, 41 `EXTRACT(DAY_HOUR FROM v)` int(10) DEFAULT NULL, 42 `EXTRACT(DAY_MINUTE FROM v)` bigint(12) DEFAULT NULL, 43 `EXTRACT(DAY_SECOND FROM v)` bigint(14) DEFAULT NULL, 44 `EXTRACT(DAY_MICROSECOND FROM v)` bigint(20) DEFAULT NULL, 45 `EXTRACT(DAY FROM ll)` int(8) DEFAULT NULL, 46 `EXTRACT(DAY_HOUR FROM ll)` int(10) DEFAULT NULL, 47 `EXTRACT(DAY_MINUTE FROM ll)` bigint(12) DEFAULT NULL, 48 `EXTRACT(DAY_SECOND FROM ll)` bigint(14) DEFAULT NULL, 49 `EXTRACT(DAY_MICROSECOND FROM ll)` bigint(20) DEFAULT NULL 50) ENGINE=MyISAM DEFAULT CHARSET=latin1 51DROP TABLE t2; 52DROP TABLE t1; 53CREATE TABLE t1 (a VARCHAR(64), b DECIMAL(32,9)); 54INSERT INTO t1 VALUES 55('9999-12-31 23:59:59.123456', 99991231235959.123456), 56('2001-01-01 10:20:30.123456', 20010101102030.123456), 57('4294967296:59:59.123456', 42949672965959.123456), 58('4294967295:59:59.123456', 42949672955959.123456), 59('87649416:59:59.123456', 876494165959.123456), 60('87649415:59:59.123456', 876494155959.123456), 61('87649414:59:59.123456', 876494145959.123456), 62('9999:59:59.123456', 99995959.123456), 63('9999:01:01.123456', 99990101.123456), 64('9999:01:01', 99990101), 65('0.999999', 0.999999), 66('0.99999', 0.99999), 67('0.9999', 0.9999), 68('0.999', 0.999), 69('0.99', 0.99), 70('0.9', 0.9), 71('000000',0); 72# Summary: 73# Check that FUNC(varchar) and FUNC(decimal) give equal results 74# Expect empty sets 75SELECT 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)); 76a b EXTRACT(DAY_HOUR FROM a) EXTRACT(DAY_HOUR FROM b) 77SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (EXTRACT(DAY FROM a)<=>EXTRACT(DAY FROM b)); 78a b EXTRACT(DAY FROM a) EXTRACT(DAY FROM b) 79SELECT a, b, EXTRACT(HOUR FROM a), EXTRACT(HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(HOUR FROM a)<=>EXTRACT(HOUR FROM b)); 80a b EXTRACT(HOUR FROM a) EXTRACT(HOUR FROM b) 81SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (EXTRACT(MINUTE FROM a)<=>EXTRACT(MINUTE FROM b)); 82a b EXTRACT(MINUTE FROM a) EXTRACT(MINUTE FROM b) 83SELECT a, b, EXTRACT(SECOND FROM a), EXTRACT(SECOND FROM b) FROM t1 WHERE NOT (EXTRACT(SECOND FROM a)<=>EXTRACT(SECOND FROM b)); 84a b EXTRACT(SECOND FROM a) EXTRACT(SECOND FROM b) 85SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b)); 86a b EXTRACT(MICROSECOND FROM a) EXTRACT(MICROSECOND FROM b) 87# Detailed results 88SELECT 89a, 90CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm, 91EXTRACT(DAY FROM a) * 24 + EXTRACT(HOUR FROM a) AS dh, 92EXTRACT(DAY_HOUR FROM a), 93EXTRACT(DAY FROM a), 94EXTRACT(HOUR FROM a), 95EXTRACT(MINUTE FROM a), 96EXTRACT(SECOND FROM a), 97EXTRACT(MICROSECOND FROM a) 98FROM t1; 99a 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) 1009999-12-31 23:59:59.123456 NULL 767 3123 31 23 59 59 123456 1012001-01-01 10:20:30.123456 NULL 34 110 1 10 20 30 123456 1024294967296:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL 1034294967295:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL 10487649416:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL 10587649415:59:59.123456 3652058 23:59:59.123456 87649415 365205823 3652058 23 59 59 123456 10687649414:59:59.123456 3652058 22:59:59.123456 87649414 365205822 3652058 22 59 59 123456 1079999:59:59.123456 416 15:59:59.123456 9999 41615 416 15 59 59 123456 1089999:01:01.123456 416 15:01:01.123456 9999 41615 416 15 1 1 123456 1099999:01:01 416 15:01:01.000000 9999 41615 416 15 1 1 0 1100.999999 00:00:00.999999 0 0 0 0 0 0 999999 1110.99999 00:00:00.999990 0 0 0 0 0 0 999990 1120.9999 00:00:00.999900 0 0 0 0 0 0 999900 1130.999 00:00:00.999000 0 0 0 0 0 0 999000 1140.99 00:00:00.990000 0 0 0 0 0 0 990000 1150.9 00:00:00.900000 0 0 0 0 0 0 900000 116000000 00:00:00.000000 0 0 0 0 0 0 0 117Warnings: 118Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '9999-12-31 23:59:59.123456' 119Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 10:20:30.123456' 120Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59.123456' 121Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 122Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 123Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 124Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 125Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 126Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 127Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 128Warning 1292 Incorrect interval value: '4294967296:59:59.123456' 129Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59.123456' 130Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 131Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 132Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 133Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 134Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 135Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 136Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 137Warning 1292 Incorrect interval value: '4294967295:59:59.123456' 138Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59.123456' 139Warning 1292 Incorrect interval value: '87649416:59:59.123456' 140Warning 1292 Incorrect interval value: '87649416:59:59.123456' 141Warning 1292 Incorrect interval value: '87649416:59:59.123456' 142Warning 1292 Incorrect interval value: '87649416:59:59.123456' 143Warning 1292 Incorrect interval value: '87649416:59:59.123456' 144Warning 1292 Incorrect interval value: '87649416:59:59.123456' 145Warning 1292 Incorrect interval value: '87649416:59:59.123456' 146Warning 1292 Incorrect interval value: '87649416:59:59.123456' 147SELECT 148b, 149CAST(b AS INTERVAL DAY_SECOND(6)) AS cidm, 150EXTRACT(DAY FROM b) * 24 + EXTRACT(HOUR FROM b) AS dh, 151EXTRACT(DAY_HOUR FROM b), 152EXTRACT(DAY FROM b), 153EXTRACT(HOUR FROM b), 154EXTRACT(MINUTE FROM b), 155EXTRACT(SECOND FROM b), 156EXTRACT(MICROSECOND FROM b) 157FROM t1; 158b 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) 15999991231235959.123456000 NULL 767 3123 31 23 59 59 123456 16020010101102030.123456000 NULL 34 110 1 10 20 30 123456 16142949672965959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL 16242949672955959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL 163876494165959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL 164876494155959.123456000 3652058 23:59:59.123456 87649415 365205823 3652058 23 59 59 123456 165876494145959.123456000 3652058 22:59:59.123456 87649414 365205822 3652058 22 59 59 123456 16699995959.123456000 416 15:59:59.123456 9999 41615 416 15 59 59 123456 16799990101.123456000 416 15:01:01.123456 9999 41615 416 15 1 1 123456 16899990101.000000000 416 15:01:01.000000 9999 41615 416 15 1 1 0 1690.999999000 00:00:00.999999 0 0 0 0 0 0 999999 1700.999990000 00:00:00.999990 0 0 0 0 0 0 999990 1710.999900000 00:00:00.999900 0 0 0 0 0 0 999900 1720.999000000 00:00:00.999000 0 0 0 0 0 0 999000 1730.990000000 00:00:00.990000 0 0 0 0 0 0 990000 1740.900000000 00:00:00.900000 0 0 0 0 0 0 900000 1750.000000000 00:00:00.000000 0 0 0 0 0 0 0 176Warnings: 177Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '99991231235959.123456000' 178Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '20010101102030.123456000' 179Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '42949672965959.123456000' 180Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 181Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 182Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 183Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 184Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 185Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 186Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 187Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3 188Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '42949672955959.123456000' 189Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 190Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 191Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 192Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 193Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 194Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 195Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 196Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4 197Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '876494165959.123456000' 198Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 199Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 200Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 201Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 202Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 203Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 204Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 205Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5 206Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.123456000' 207Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.123456000' 208Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99995959.123456000' 209Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.123456000' 210Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.000000000' 211Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999999000' 212Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999990000' 213Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999900000' 214Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999000000' 215Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.990000000' 216Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.900000000' 217Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000' 218DROP TABLE t1; 219# Special case: DAY + TIME 220CREATE TABLE t1 (a VARCHAR(64)); 221INSERT INTO t1 VALUES ('9999-01-01'); 222SELECT a, 223EXTRACT(DAY_HOUR FROM a), 224EXTRACT(DAY_MINUTE FROM a), 225EXTRACT(DAY_SECOND FROM a), 226EXTRACT(DAY_MICROSECOND FROM a), 227EXTRACT(DAY FROM a), 228EXTRACT(HOUR FROM a), 229EXTRACT(MINUTE FROM a), 230EXTRACT(SECOND FROM a), 231EXTRACT(MICROSECOND FROM a) 232FROM t1; 233a 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) 2349999-01-01 100 10000 1000000 1000000000000 1 0 0 0 0 235DROP TABLE t1; 236# Bad values 237CREATE TABLE t1 (a VARCHAR(64)); 238INSERT INTO t1 VALUES (''); 239SELECT a, 240CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm, 241EXTRACT(DAY_HOUR FROM a), 242EXTRACT(DAY_MINUTE FROM a), 243EXTRACT(DAY_SECOND FROM a), 244EXTRACT(DAY_MICROSECOND FROM a), 245EXTRACT(DAY FROM a), 246EXTRACT(HOUR FROM a), 247EXTRACT(MINUTE FROM a), 248EXTRACT(SECOND FROM a), 249EXTRACT(MICROSECOND FROM a) 250FROM t1; 251a 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) 252 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 253Warnings: 254Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '' 255Warning 1292 Incorrect interval value: '' 256Warning 1292 Incorrect interval value: '' 257Warning 1292 Incorrect interval value: '' 258Warning 1292 Incorrect interval value: '' 259Warning 1292 Incorrect interval value: '' 260Warning 1292 Incorrect interval value: '' 261Warning 1292 Incorrect interval value: '' 262Warning 1292 Incorrect interval value: '' 263Warning 1292 Incorrect interval value: '' 264DROP TABLE t1; 265# Backward compatibility 266# This still parses as DATETIME 267SELECT EXTRACT(YEAR FROM '2001/02/03 10:20:30'); 268EXTRACT(YEAR FROM '2001/02/03 10:20:30') 2692001 270SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30'); 271EXTRACT(MONTH FROM '2001/02/03 10:20:30') 2722 273SELECT EXTRACT(DAY FROM '2001/02/03 10:20:30'); 274EXTRACT(DAY FROM '2001/02/03 10:20:30') 2753 276SELECT EXTRACT(YEAR FROM '01/02/03 10:20:30'); 277EXTRACT(YEAR FROM '01/02/03 10:20:30') 2782001 279SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30'); 280EXTRACT(MONTH FROM '01/02/03 10:20:30') 2812 282SELECT EXTRACT(DAY FROM '01/02/03 10:20:30'); 283EXTRACT(DAY FROM '01/02/03 10:20:30') 2843 285SELECT EXTRACT(YEAR FROM '01:02:03 10:20:30'); 286EXTRACT(YEAR FROM '01:02:03 10:20:30') 2872001 288SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30'); 289EXTRACT(MONTH FROM '01:02:03 10:20:30') 2902 291SELECT EXTRACT(DAY FROM '01:02:03 10:20:30'); 292EXTRACT(DAY FROM '01:02:03 10:20:30') 2933 294# This still parses as DATETIME and returns NULL 295SELECT EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434"); 296EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434") 297NULL 298Warnings: 299Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434' 300SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434"); 301EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434") 302NULL 303Warnings: 304Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434' 305SELECT EXTRACT(DAY FROM "2011-02-32 8:46:06.23434"); 306EXTRACT(DAY FROM "2011-02-32 8:46:06.23434") 307NULL 308Warnings: 309Warning 1292 Incorrect interval value: '2011-02-32 8:46:06.23434' 310SELECT EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434"); 311EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434") 312NULL 313Warnings: 314Warning 1292 Incorrect interval value: '2011-02-32 8:46:06.23434' 315# This still parses as DATE 316SELECT EXTRACT(YEAR FROM '2001/02/03'); 317EXTRACT(YEAR FROM '2001/02/03') 3182001 319SELECT EXTRACT(MONTH FROM '2001/02/03'); 320EXTRACT(MONTH FROM '2001/02/03') 3212 322SELECT EXTRACT(DAY FROM '2001/02/03'); 323EXTRACT(DAY FROM '2001/02/03') 3243 325SELECT EXTRACT(YEAR FROM '01/02/03'); 326EXTRACT(YEAR FROM '01/02/03') 3272001 328SELECT EXTRACT(MONTH FROM '01/02/03'); 329EXTRACT(MONTH FROM '01/02/03') 3302 331SELECT EXTRACT(DAY FROM '01/02/03'); 332EXTRACT(DAY FROM '01/02/03') 3333 334SELECT EXTRACT(YEAR FROM '01-02-03'); 335EXTRACT(YEAR FROM '01-02-03') 3362001 337SELECT EXTRACT(MONTH FROM '01-02-03'); 338EXTRACT(MONTH FROM '01-02-03') 3392 340SELECT EXTRACT(DAY FROM '01-02-03'); 341EXTRACT(DAY FROM '01-02-03') 3423 343SELECT EXTRACT(YEAR FROM '1-2-3'); 344EXTRACT(YEAR FROM '1-2-3') 3451 346SELECT EXTRACT(MONTH FROM '1-2-3'); 347EXTRACT(MONTH FROM '1-2-3') 3482 349SELECT EXTRACT(DAY FROM '1-2-3'); 350EXTRACT(DAY FROM '1-2-3') 3513 352SELECT EXTRACT(HOUR FROM '1-2-3'); 353EXTRACT(HOUR FROM '1-2-3') 3540 355SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////'); 356EXTRACT(DAY FROM '2024-01-03 garbage /////') 3573 358Warnings: 359Warning 1292 Truncated incorrect date value: '2024-01-03 garbage /////' 360SELECT EXTRACT(DAY FROM '24-01-03 garbage /////'); 361EXTRACT(DAY FROM '24-01-03 garbage /////') 3623 363Warnings: 364Warning 1292 Truncated incorrect date value: '24-01-03 garbage /////' 365SELECT EXTRACT(DAY FROM '01-02-03'); 366EXTRACT(DAY FROM '01-02-03') 3673 368SELECT EXTRACT(DAY FROM '24:02:03T'); 369EXTRACT(DAY FROM '24:02:03T') 3703 371SELECT EXTRACT(DAY FROM '24-02-03'); 372EXTRACT(DAY FROM '24-02-03') 3733 374SELECT EXTRACT(DAY FROM '24/02/03'); 375EXTRACT(DAY FROM '24/02/03') 3763 377SELECT EXTRACT(DAY FROM '11111'); 378EXTRACT(DAY FROM '11111') 3791 380SELECT TIME('2001-01-01T'), TIME('2001-01-01T '); 381TIME('2001-01-01T') TIME('2001-01-01T ') 38200:00:00 00:00:00 383SELECT TIME('2001/01/01T'), TIME('2001/01/01T '); 384TIME('2001/01/01T') TIME('2001/01/01T ') 38500:00:00 00:00:00 386SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); 387TIME('2001:01:01T') TIME('2001:01:01T ') 38800:00:00 00:00:00 389SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T '); 390EXTRACT(DAY FROM '2001-01-01T') EXTRACT(DAY FROM '2001-01-01T ') 3911 1 392SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T '); 393EXTRACT(DAY FROM '2001/01/01T') EXTRACT(DAY FROM '2001/01/01T ') 3941 1 395SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T '); 396EXTRACT(DAY FROM '2001:01:01T') EXTRACT(DAY FROM '2001:01:01T ') 3971 1 398SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); 399TIME('2001:01:01T') TIME('2001:01:01T ') 40000:00:00 00:00:00 401SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T '); 402EXTRACT(HOUR FROM '2001-01-01T') EXTRACT(HOUR FROM '2001-01-01T ') 4030 0 404SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T '); 405EXTRACT(HOUR FROM '2001/01/01T') EXTRACT(HOUR FROM '2001/01/01T ') 4060 0 407SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T '); 408EXTRACT(HOUR FROM '2001:01:01T') EXTRACT(HOUR FROM '2001:01:01T ') 4090 0 410# This still parses as DATE and returns NULL (without trying TIME) 411SELECT EXTRACT(DAY FROM '100000:02:03T'); 412EXTRACT(DAY FROM '100000:02:03T') 413NULL 414Warnings: 415Warning 1292 Incorrect interval value: '100000:02:03T' 416SELECT EXTRACT(DAY FROM '100000/02/03'); 417EXTRACT(DAY FROM '100000/02/03') 418NULL 419Warnings: 420Warning 1292 Incorrect interval value: '100000/02/03' 421SELECT EXTRACT(DAY FROM '100000-02-03'); 422EXTRACT(DAY FROM '100000-02-03') 423NULL 424Warnings: 425Warning 1292 Incorrect interval value: '100000-02-03' 426SELECT EXTRACT(DAY FROM '1111'); 427EXTRACT(DAY FROM '1111') 428NULL 429Warnings: 430Warning 1292 Incorrect interval value: '1111' 431SELECT EXTRACT(DAY FROM '111'); 432EXTRACT(DAY FROM '111') 433NULL 434Warnings: 435Warning 1292 Incorrect interval value: '111' 436SELECT EXTRACT(DAY FROM '11'); 437EXTRACT(DAY FROM '11') 438NULL 439Warnings: 440Warning 1292 Incorrect interval value: '11' 441SELECT EXTRACT(DAY FROM '1'); 442EXTRACT(DAY FROM '1') 443NULL 444Warnings: 445Warning 1292 Incorrect interval value: '1' 446# This still parses as TIME 447SELECT EXTRACT(HOUR FROM '11111'); 448EXTRACT(HOUR FROM '11111') 4491 450SELECT EXTRACT(HOUR FROM '1111'); 451EXTRACT(HOUR FROM '1111') 4520 453SELECT EXTRACT(HOUR FROM '111'); 454EXTRACT(HOUR FROM '111') 4550 456SELECT EXTRACT(HOUR FROM '11'); 457EXTRACT(HOUR FROM '11') 4580 459SELECT EXTRACT(HOUR FROM '1'); 460EXTRACT(HOUR FROM '1') 4610 462SELECT TIME('01:02:03:'); 463TIME('01:02:03:') 46401:02:03 465Warnings: 466Warning 1292 Truncated incorrect time value: '01:02:03:' 467SELECT TIME('01:02:03-'); 468TIME('01:02:03-') 46901:02:03 470Warnings: 471Warning 1292 Truncated incorrect time value: '01:02:03-' 472SELECT TIME('01:02:03;'); 473TIME('01:02:03;') 47401:02:03 475Warnings: 476Warning 1292 Truncated incorrect time value: '01:02:03;' 477SELECT TIME('01:02:03/'); 478TIME('01:02:03/') 47901:02:03 480Warnings: 481Warning 1292 Truncated incorrect time value: '01:02:03/' 482SELECT EXTRACT(HOUR FROM '01:02:03:'); 483EXTRACT(HOUR FROM '01:02:03:') 4841 485Warnings: 486Warning 1292 Truncated incorrect time value: '01:02:03:' 487SELECT EXTRACT(HOUR FROM '01:02:03-'); 488EXTRACT(HOUR FROM '01:02:03-') 4891 490Warnings: 491Warning 1292 Truncated incorrect time value: '01:02:03-' 492SELECT EXTRACT(HOUR FROM '01:02:03;'); 493EXTRACT(HOUR FROM '01:02:03;') 4941 495Warnings: 496Warning 1292 Truncated incorrect time value: '01:02:03;' 497SELECT EXTRACT(HOUR FROM '01:02:03/'); 498EXTRACT(HOUR FROM '01:02:03/') 4991 500Warnings: 501Warning 1292 Truncated incorrect time value: '01:02:03/' 502# Backward compatibility preserved for YEAR and MONTH only 503# (behavior has changed for DAY, see below) 504SELECT EXTRACT(YEAR FROM '01:02:03'); 505EXTRACT(YEAR FROM '01:02:03') 5062001 507SELECT EXTRACT(MONTH FROM '01:02:03'); 508EXTRACT(MONTH FROM '01:02:03') 5092 510SELECT EXTRACT(YEAR FROM '24:01:03 garbage /////'); 511EXTRACT(YEAR FROM '24:01:03 garbage /////') 5122024 513Warnings: 514Warning 1292 Truncated incorrect date value: '24:01:03 garbage /////' 515SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////'); 516EXTRACT(MONTH FROM '24:01:03 garbage /////') 5171 518Warnings: 519Warning 1292 Truncated incorrect date value: '24:01:03 garbage /////' 520# This still parses as TIME 00:20:01 521SELECT TIME('2001/01/01'); 522TIME('2001/01/01') 52300:20:01 524Warnings: 525Warning 1292 Truncated incorrect time value: '2001/01/01' 526SELECT TIME('2001-01-01'); 527TIME('2001-01-01') 52800:20:01 529Warnings: 530Warning 1292 Truncated incorrect time value: '2001-01-01' 531# This still parses as TIME and overflows to '838:59:59' 532SELECT TIME('2001:01:01'); 533TIME('2001:01:01') 534838:59:59 535Warnings: 536Warning 1292 Truncated incorrect time value: '2001:01:01' 537# This used to parse as DATE, now parses as TIME interval 538CREATE TABLE t1 (a VARCHAR(64)); 539INSERT INTO t1 VALUES 540('2024:01:03 garbage /////'), 541('24:01:03 garbage /////'), 542('01:01:03 garbage /////'), 543('2024:02:03'), 544('100000:02:03'), 545('24:02:03'), 546('01:02:03'), 547('01:02:03:'), 548('01:02:03-'), 549('01:02:03;'), 550('01:02:03/'), 551('20 10:20:30'); 552SELECT 553EXTRACT(DAY FROM a), 554EXTRACT(DAY_SECOND FROM a), a, 555CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm 556FROM t1; 557EXTRACT(DAY FROM a) EXTRACT(DAY_SECOND FROM a) a cidm 55884 84080103 2024:01:03 garbage ///// NULL 5591 1000103 24:01:03 garbage ///// NULL 5600 10103 01:01:03 garbage ///// NULL 56184 84080203 2024:02:03 84 08:02:03.000000 5624166 4166160203 100000:02:03 4166 16:02:03.000000 5631 1000203 24:02:03 1 00:02:03.000000 5640 10203 01:02:03 01:02:03.000000 5650 10203 01:02:03: 01:02:03.000000 5660 10203 01:02:03- NULL 5670 10203 01:02:03; 01:02:03.000000 5680 10203 01:02:03/ 01:02:03.000000 56920 20102030 20 10:20:30 20 10:20:30.000000 570Warnings: 571Warning 1292 Truncated incorrect time value: '2024:01:03 garbage /////' 572Warning 1292 Truncated incorrect time value: '2024:01:03 garbage /////' 573Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2024:01:03 garbage /////' 574Warning 1292 Truncated incorrect time value: '24:01:03 garbage /////' 575Warning 1292 Truncated incorrect time value: '24:01:03 garbage /////' 576Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '24:01:03 garbage /////' 577Warning 1292 Truncated incorrect time value: '01:01:03 garbage /////' 578Warning 1292 Truncated incorrect time value: '01:01:03 garbage /////' 579Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '01:01:03 garbage /////' 580Warning 1292 Truncated incorrect time value: '01:02:03:' 581Warning 1292 Truncated incorrect time value: '01:02:03:' 582Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03:' 583Warning 1292 Truncated incorrect time value: '01:02:03-' 584Warning 1292 Truncated incorrect time value: '01:02:03-' 585Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '01:02:03-' 586Warning 1292 Truncated incorrect time value: '01:02:03;' 587Warning 1292 Truncated incorrect time value: '01:02:03;' 588Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03;' 589Warning 1292 Truncated incorrect time value: '01:02:03/' 590Warning 1292 Truncated incorrect time value: '01:02:03/' 591Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03/' 592DROP TABLE t1; 593