diff options
Diffstat (limited to 'mysql-test/r/func_time.result')
| -rw-r--r-- | mysql-test/r/func_time.result | 329 |
1 files changed, 329 insertions, 0 deletions
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 9a9ab76883e..399aa978e47 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2806,3 +2806,332 @@ Warning 1292 Truncated incorrect time value: '-1441:00:00' # # End of 10.0 tests # +# +# Start of 10.1 tests +# +# +# MDEV-10317 EXCTACT(MINUTE_MICROSECOND) truncates data +# +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES ('1999-12-31 23:59:59.999999'); +SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 a a 12 26 26 Y 128 6 63 +def EXTRACT(YEAR FROM a) 3 4 4 Y 32896 0 63 +def EXTRACT(YEAR_MONTH FROM a) 3 6 6 Y 32896 0 63 +def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(MONTH FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(WEEK FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY_HOUR FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(DAY_MINUTE FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(DAY_SECOND FROM a) 3 9 8 Y 32896 0 63 +def EXTRACT(HOUR FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(HOUR_MINUTE FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(HOUR_SECOND FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(MINUTE FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(MINUTE_SECOND FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(SECOND FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(MICROSECOND FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(DAY_MICROSECOND FROM a) 8 15 14 Y 32896 0 63 +def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 12 Y 32896 0 63 +def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 10 Y 32896 0 63 +def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 8 Y 32896 0 63 +a 1999-12-31 23:59:59.999999 +EXTRACT(YEAR FROM a) 1999 +EXTRACT(YEAR_MONTH FROM a) 199912 +EXTRACT(QUARTER FROM a) 4 +EXTRACT(MONTH FROM a) 12 +EXTRACT(WEEK FROM a) 52 +EXTRACT(DAY FROM a) 31 +EXTRACT(DAY_HOUR FROM a) 3123 +EXTRACT(DAY_MINUTE FROM a) 312359 +EXTRACT(DAY_SECOND FROM a) 31235959 +EXTRACT(HOUR FROM a) 23 +EXTRACT(HOUR_MINUTE FROM a) 2359 +EXTRACT(HOUR_SECOND FROM a) 235959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +CREATE TABLE t2 AS SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +SELECT * FROM t2; +a 1999-12-31 23:59:59.999999 +EXTRACT(YEAR FROM a) 1999 +EXTRACT(YEAR_MONTH FROM a) 199912 +EXTRACT(QUARTER FROM a) 4 +EXTRACT(MONTH FROM a) 12 +EXTRACT(WEEK FROM a) 52 +EXTRACT(DAY FROM a) 31 +EXTRACT(DAY_HOUR FROM a) 3123 +EXTRACT(DAY_MINUTE FROM a) 312359 +EXTRACT(DAY_SECOND FROM a) 31235959 +EXTRACT(HOUR FROM a) 23 +EXTRACT(HOUR_MINUTE FROM a) 2359 +EXTRACT(HOUR_SECOND FROM a) 235959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` datetime(6) DEFAULT NULL, + `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, + `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, + `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, + `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, + `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, + `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, + `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, + `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, + `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, + `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, + `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, + `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, + `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('-838:59:59.999999'),('838:59:59.999999'); +SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 a a 11 17 17 Y 128 6 63 +def EXTRACT(YEAR FROM a) 3 4 1 Y 32896 0 63 +def EXTRACT(YEAR_MONTH FROM a) 3 6 1 Y 32896 0 63 +def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(MONTH FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(WEEK FROM a) 3 2 9 Y 32896 0 63 +def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY_HOUR FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(DAY_MINUTE FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(DAY_SECOND FROM a) 3 9 9 Y 32896 0 63 +def EXTRACT(HOUR FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(HOUR_MINUTE FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(HOUR_SECOND FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(MINUTE FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(MINUTE_SECOND FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(SECOND FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(MICROSECOND FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(DAY_MICROSECOND FROM a) 8 15 15 Y 32896 0 63 +def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 13 Y 32896 0 63 +def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 11 Y 32896 0 63 +def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 9 Y 32896 0 63 +a -838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) -3422 +EXTRACT(DAY_MINUTE FROM a) -342259 +EXTRACT(DAY_SECOND FROM a) -34225959 +EXTRACT(HOUR FROM a) -22 +EXTRACT(HOUR_MINUTE FROM a) -2259 +EXTRACT(HOUR_SECOND FROM a) -225959 +EXTRACT(MINUTE FROM a) -59 +EXTRACT(MINUTE_SECOND FROM a) -5959 +EXTRACT(SECOND FROM a) -59 +EXTRACT(MICROSECOND FROM a) -999999 +EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) -59999999 +a 838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) 3422 +EXTRACT(DAY_MINUTE FROM a) 342259 +EXTRACT(DAY_SECOND FROM a) 34225959 +EXTRACT(HOUR FROM a) 22 +EXTRACT(HOUR_MINUTE FROM a) 2259 +EXTRACT(HOUR_SECOND FROM a) 225959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +CREATE TABLE t2 AS SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +SELECT * FROM t2; +a -838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) -3422 +EXTRACT(DAY_MINUTE FROM a) -342259 +EXTRACT(DAY_SECOND FROM a) -34225959 +EXTRACT(HOUR FROM a) -22 +EXTRACT(HOUR_MINUTE FROM a) -2259 +EXTRACT(HOUR_SECOND FROM a) -225959 +EXTRACT(MINUTE FROM a) -59 +EXTRACT(MINUTE_SECOND FROM a) -5959 +EXTRACT(SECOND FROM a) -59 +EXTRACT(MICROSECOND FROM a) -999999 +EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) -59999999 +a 838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) 3422 +EXTRACT(DAY_MINUTE FROM a) 342259 +EXTRACT(DAY_SECOND FROM a) 34225959 +EXTRACT(HOUR FROM a) 22 +EXTRACT(HOUR_MINUTE FROM a) 2259 +EXTRACT(HOUR_SECOND FROM a) 225959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` time(6) DEFAULT NULL, + `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, + `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, + `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, + `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, + `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, + `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, + `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, + `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, + `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, + `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, + `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, + `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, + `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +# +# End of 10.1 tests +# |