1--echo # 2--echo # MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument 3--echo # 4 5CREATE TABLE t1 (v VARCHAR(64), ll BIGINT, t TIME, dt DATETIME, d DATE); 6CREATE TABLE t2 AS SELECT 7 EXTRACT(DAY FROM t), 8 EXTRACT(DAY_HOUR FROM t), 9 EXTRACT(DAY_MINUTE FROM t), 10 EXTRACT(DAY_SECOND FROM t), 11 EXTRACT(DAY_MICROSECOND FROM t), 12 EXTRACT(DAY FROM d), 13 EXTRACT(DAY_HOUR FROM d), 14 EXTRACT(DAY_MINUTE FROM d), 15 EXTRACT(DAY_SECOND FROM d), 16 EXTRACT(DAY_MICROSECOND FROM d), 17 EXTRACT(DAY FROM v), 18 EXTRACT(DAY_HOUR FROM v), 19 EXTRACT(DAY_MINUTE FROM v), 20 EXTRACT(DAY_SECOND FROM v), 21 EXTRACT(DAY_MICROSECOND FROM v), 22 EXTRACT(DAY FROM ll), 23 EXTRACT(DAY_HOUR FROM ll), 24 EXTRACT(DAY_MINUTE FROM ll), 25 EXTRACT(DAY_SECOND FROM ll), 26 EXTRACT(DAY_MICROSECOND FROM ll) 27FROM t1; 28SHOW CREATE TABLE t2; 29DROP TABLE t2; 30DROP TABLE t1; 31 32 33CREATE TABLE t1 (a VARCHAR(64), b DECIMAL(32,9)); 34INSERT INTO t1 VALUES 35('9999-12-31 23:59:59.123456', 99991231235959.123456), 36('2001-01-01 10:20:30.123456', 20010101102030.123456), 37('4294967296:59:59.123456', 42949672965959.123456), 38('4294967295:59:59.123456', 42949672955959.123456), 39('87649416:59:59.123456', 876494165959.123456), 40('87649415:59:59.123456', 876494155959.123456), 41('87649414:59:59.123456', 876494145959.123456), 42('9999:59:59.123456', 99995959.123456), 43('9999:01:01.123456', 99990101.123456), 44('9999:01:01', 99990101), 45('0.999999', 0.999999), 46('0.99999', 0.99999), 47('0.9999', 0.9999), 48('0.999', 0.999), 49('0.99', 0.99), 50('0.9', 0.9), 51('000000',0); 52 53--echo # Summary: 54--echo # Check that FUNC(varchar) and FUNC(decimal) give equal results 55--echo # Expect empty sets 56--disable_warnings 57SELECT 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)); 58SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (EXTRACT(DAY FROM a)<=>EXTRACT(DAY FROM b)); 59SELECT a, b, EXTRACT(HOUR FROM a), EXTRACT(HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(HOUR FROM a)<=>EXTRACT(HOUR FROM b)); 60SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (EXTRACT(MINUTE FROM a)<=>EXTRACT(MINUTE FROM b)); 61SELECT a, b, EXTRACT(SECOND FROM a), EXTRACT(SECOND FROM b) FROM t1 WHERE NOT (EXTRACT(SECOND FROM a)<=>EXTRACT(SECOND FROM b)); 62SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b)); 63--enable_warnings 64 65--echo # Detailed results 66SELECT 67 a, 68 CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm, 69 EXTRACT(DAY FROM a) * 24 + EXTRACT(HOUR FROM a) AS dh, 70 EXTRACT(DAY_HOUR FROM a), 71 EXTRACT(DAY FROM a), 72 EXTRACT(HOUR FROM a), 73 EXTRACT(MINUTE FROM a), 74 EXTRACT(SECOND FROM a), 75 EXTRACT(MICROSECOND FROM a) 76FROM t1; 77SELECT 78 b, 79 CAST(b AS INTERVAL DAY_SECOND(6)) AS cidm, 80 EXTRACT(DAY FROM b) * 24 + EXTRACT(HOUR FROM b) AS dh, 81 EXTRACT(DAY_HOUR FROM b), 82 EXTRACT(DAY FROM b), 83 EXTRACT(HOUR FROM b), 84 EXTRACT(MINUTE FROM b), 85 EXTRACT(SECOND FROM b), 86 EXTRACT(MICROSECOND FROM b) 87FROM t1; 88DROP TABLE t1; 89 90--echo # Special case: DAY + TIME 91CREATE TABLE t1 (a VARCHAR(64)); 92INSERT INTO t1 VALUES ('9999-01-01'); 93SELECT a, 94 EXTRACT(DAY_HOUR FROM a), 95 EXTRACT(DAY_MINUTE FROM a), 96 EXTRACT(DAY_SECOND FROM a), 97 EXTRACT(DAY_MICROSECOND FROM a), 98 EXTRACT(DAY FROM a), 99 EXTRACT(HOUR FROM a), 100 EXTRACT(MINUTE FROM a), 101 EXTRACT(SECOND FROM a), 102 EXTRACT(MICROSECOND FROM a) 103FROM t1; 104DROP TABLE t1; 105 106--echo # Bad values 107CREATE TABLE t1 (a VARCHAR(64)); 108INSERT INTO t1 VALUES (''); 109SELECT a, 110 CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm, 111 EXTRACT(DAY_HOUR FROM a), 112 EXTRACT(DAY_MINUTE FROM a), 113 EXTRACT(DAY_SECOND FROM a), 114 EXTRACT(DAY_MICROSECOND FROM a), 115 EXTRACT(DAY FROM a), 116 EXTRACT(HOUR FROM a), 117 EXTRACT(MINUTE FROM a), 118 EXTRACT(SECOND FROM a), 119 EXTRACT(MICROSECOND FROM a) 120FROM t1; 121DROP TABLE t1; 122 123 124--echo # Backward compatibility 125 126--echo # This still parses as DATETIME 127SELECT EXTRACT(YEAR FROM '2001/02/03 10:20:30'); 128SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30'); 129SELECT EXTRACT(DAY FROM '2001/02/03 10:20:30'); 130 131SELECT EXTRACT(YEAR FROM '01/02/03 10:20:30'); 132SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30'); 133SELECT EXTRACT(DAY FROM '01/02/03 10:20:30'); 134 135SELECT EXTRACT(YEAR FROM '01:02:03 10:20:30'); 136SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30'); 137SELECT EXTRACT(DAY FROM '01:02:03 10:20:30'); 138 139--echo # This still parses as DATETIME and returns NULL 140 141SELECT EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434"); 142SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434"); 143SELECT EXTRACT(DAY FROM "2011-02-32 8:46:06.23434"); 144SELECT EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434"); 145 146--echo # This still parses as DATE 147 148SELECT EXTRACT(YEAR FROM '2001/02/03'); 149SELECT EXTRACT(MONTH FROM '2001/02/03'); 150SELECT EXTRACT(DAY FROM '2001/02/03'); 151 152SELECT EXTRACT(YEAR FROM '01/02/03'); 153SELECT EXTRACT(MONTH FROM '01/02/03'); 154SELECT EXTRACT(DAY FROM '01/02/03'); 155 156SELECT EXTRACT(YEAR FROM '01-02-03'); 157SELECT EXTRACT(MONTH FROM '01-02-03'); 158SELECT EXTRACT(DAY FROM '01-02-03'); 159 160SELECT EXTRACT(YEAR FROM '1-2-3'); 161SELECT EXTRACT(MONTH FROM '1-2-3'); 162SELECT EXTRACT(DAY FROM '1-2-3'); 163SELECT EXTRACT(HOUR FROM '1-2-3'); 164 165SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////'); 166SELECT EXTRACT(DAY FROM '24-01-03 garbage /////'); 167SELECT EXTRACT(DAY FROM '01-02-03'); 168 169SELECT EXTRACT(DAY FROM '24:02:03T'); 170SELECT EXTRACT(DAY FROM '24-02-03'); 171SELECT EXTRACT(DAY FROM '24/02/03'); 172 173SELECT EXTRACT(DAY FROM '11111'); 174 175SELECT TIME('2001-01-01T'), TIME('2001-01-01T '); 176SELECT TIME('2001/01/01T'), TIME('2001/01/01T '); 177SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); 178 179SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T '); 180SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T '); 181SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T '); 182 183 184SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); 185SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T '); 186SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T '); 187SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T '); 188 189--echo # This still parses as DATE and returns NULL (without trying TIME) 190SELECT EXTRACT(DAY FROM '100000:02:03T'); 191SELECT EXTRACT(DAY FROM '100000/02/03'); 192SELECT EXTRACT(DAY FROM '100000-02-03'); 193 194SELECT EXTRACT(DAY FROM '1111'); 195SELECT EXTRACT(DAY FROM '111'); 196SELECT EXTRACT(DAY FROM '11'); 197SELECT EXTRACT(DAY FROM '1'); 198 199 200--echo # This still parses as TIME 201 202SELECT EXTRACT(HOUR FROM '11111'); 203SELECT EXTRACT(HOUR FROM '1111'); 204SELECT EXTRACT(HOUR FROM '111'); 205SELECT EXTRACT(HOUR FROM '11'); 206SELECT EXTRACT(HOUR FROM '1'); 207 208SELECT TIME('01:02:03:'); 209SELECT TIME('01:02:03-'); 210SELECT TIME('01:02:03;'); 211SELECT TIME('01:02:03/'); 212 213SELECT EXTRACT(HOUR FROM '01:02:03:'); 214SELECT EXTRACT(HOUR FROM '01:02:03-'); 215SELECT EXTRACT(HOUR FROM '01:02:03;'); 216SELECT EXTRACT(HOUR FROM '01:02:03/'); 217 218--echo # Backward compatibility preserved for YEAR and MONTH only 219--echo # (behavior has changed for DAY, see below) 220SELECT EXTRACT(YEAR FROM '01:02:03'); 221SELECT EXTRACT(MONTH FROM '01:02:03'); 222 223SELECT EXTRACT(YEAR FROM '24:01:03 garbage /////'); 224SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////'); 225 226--echo # This still parses as TIME 00:20:01 227 228SELECT TIME('2001/01/01'); 229SELECT TIME('2001-01-01'); 230 231--echo # This still parses as TIME and overflows to '838:59:59' 232SELECT TIME('2001:01:01'); 233 234 235--echo # This used to parse as DATE, now parses as TIME interval 236 237CREATE TABLE t1 (a VARCHAR(64)); 238INSERT INTO t1 VALUES 239('2024:01:03 garbage /////'), 240('24:01:03 garbage /////'), 241('01:01:03 garbage /////'), 242('2024:02:03'), 243('100000:02:03'), 244('24:02:03'), 245('01:02:03'), 246('01:02:03:'), 247('01:02:03-'), 248('01:02:03;'), 249('01:02:03/'), 250('20 10:20:30'); 251 252SELECT 253 EXTRACT(DAY FROM a), 254 EXTRACT(DAY_SECOND FROM a), a, 255 CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm 256FROM t1; 257DROP TABLE t1; 258