diff options
Diffstat (limited to 'mysql-test/main/func_extract.test')
| -rw-r--r-- | mysql-test/main/func_extract.test | 257 |
1 files changed, 257 insertions, 0 deletions
diff --git a/mysql-test/main/func_extract.test b/mysql-test/main/func_extract.test new file mode 100644 index 00000000000..edc99b9c00c --- /dev/null +++ b/mysql-test/main/func_extract.test @@ -0,0 +1,257 @@ +--echo # +--echo # MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument +--echo # + +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; +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); + +--echo # Summary: +--echo # Check that FUNC(varchar) and FUNC(decimal) give equal results +--echo # Expect empty sets +--disable_warnings +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)); +SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (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)); +SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (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)); +SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b)); +--enable_warnings + +--echo # 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; +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; +DROP TABLE t1; + +--echo # 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; +DROP TABLE t1; + +--echo # 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; +DROP TABLE t1; + + +--echo # Backward compatibility + +--echo # This still parses as DATETIME +SELECT EXTRACT(YEAR FROM '2001/02/03 10:20:30'); +SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30'); +SELECT EXTRACT(DAY FROM '2001/02/03 10:20:30'); + +SELECT EXTRACT(YEAR FROM '01/02/03 10:20:30'); +SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30'); +SELECT EXTRACT(DAY FROM '01/02/03 10:20:30'); + +SELECT EXTRACT(YEAR FROM '01:02:03 10:20:30'); +SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30'); +SELECT EXTRACT(DAY FROM '01:02:03 10:20:30'); + +--echo # This still parses as DATETIME and returns NULL + +SELECT EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434"); +SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434"); +SELECT EXTRACT(DAY FROM "2011-02-32 8:46:06.23434"); +SELECT EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434"); + +--echo # This still parses as DATE + +SELECT EXTRACT(YEAR FROM '2001/02/03'); +SELECT EXTRACT(MONTH FROM '2001/02/03'); +SELECT EXTRACT(DAY FROM '2001/02/03'); + +SELECT EXTRACT(YEAR FROM '01/02/03'); +SELECT EXTRACT(MONTH FROM '01/02/03'); +SELECT EXTRACT(DAY FROM '01/02/03'); + +SELECT EXTRACT(YEAR FROM '01-02-03'); +SELECT EXTRACT(MONTH FROM '01-02-03'); +SELECT EXTRACT(DAY FROM '01-02-03'); + +SELECT EXTRACT(YEAR FROM '1-2-3'); +SELECT EXTRACT(MONTH FROM '1-2-3'); +SELECT EXTRACT(DAY FROM '1-2-3'); +SELECT EXTRACT(HOUR FROM '1-2-3'); + +SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////'); +SELECT EXTRACT(DAY FROM '24-01-03 garbage /////'); +SELECT EXTRACT(DAY FROM '01-02-03'); + +SELECT EXTRACT(DAY FROM '24:02:03T'); +SELECT EXTRACT(DAY FROM '24-02-03'); +SELECT EXTRACT(DAY FROM '24/02/03'); + +SELECT EXTRACT(DAY FROM '11111'); + +SELECT TIME('2001-01-01T'), TIME('2001-01-01T '); +SELECT TIME('2001/01/01T'), TIME('2001/01/01T '); +SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); + +SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T '); +SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T '); +SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T '); + + +SELECT TIME('2001:01:01T'), TIME('2001:01:01T '); +SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T '); +SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T '); +SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T '); + +--echo # This still parses as DATE and returns NULL (without trying TIME) +SELECT EXTRACT(DAY FROM '100000:02:03T'); +SELECT EXTRACT(DAY FROM '100000/02/03'); +SELECT EXTRACT(DAY FROM '100000-02-03'); + +SELECT EXTRACT(DAY FROM '1111'); +SELECT EXTRACT(DAY FROM '111'); +SELECT EXTRACT(DAY FROM '11'); +SELECT EXTRACT(DAY FROM '1'); + + +--echo # This still parses as TIME + +SELECT EXTRACT(HOUR FROM '11111'); +SELECT EXTRACT(HOUR FROM '1111'); +SELECT EXTRACT(HOUR FROM '111'); +SELECT EXTRACT(HOUR FROM '11'); +SELECT EXTRACT(HOUR FROM '1'); + +SELECT TIME('01:02:03:'); +SELECT TIME('01:02:03-'); +SELECT TIME('01:02:03;'); +SELECT TIME('01:02:03/'); + +SELECT EXTRACT(HOUR FROM '01:02:03:'); +SELECT EXTRACT(HOUR FROM '01:02:03-'); +SELECT EXTRACT(HOUR FROM '01:02:03;'); +SELECT EXTRACT(HOUR FROM '01:02:03/'); + +--echo # Backward compatibility preserved for YEAR and MONTH only +--echo # (behavior has changed for DAY, see below) +SELECT EXTRACT(YEAR FROM '01:02:03'); +SELECT EXTRACT(MONTH FROM '01:02:03'); + +SELECT EXTRACT(YEAR FROM '24:01:03 garbage /////'); +SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////'); + +--echo # This still parses as TIME 00:20:01 + +SELECT TIME('2001/01/01'); +SELECT TIME('2001-01-01'); + +--echo # This still parses as TIME and overflows to '838:59:59' +SELECT TIME('2001:01:01'); + + +--echo # 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; +DROP TABLE t1; |