MDEV-17740 Extend EXTRACT(x AS DAY*) to understand long time intervals

13 files changed, 1234 insertions, 140 deletions

diff --git a/include/my_time.h b/include/my_time.h
index a7911f0f8da..ab0d2b0661d 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -79,6 +79,16 @@ extern uchar days_in_month[];
 /* Useful constants */
 #define SECONDS_IN_24H 86400L
 
+/* Limits for the INTERVAL data type */
+
+ /* Number of hours between '0001-01-01 00h' and '9999-12-31 23h' */
+#define TIME_MAX_INTERVAL_HOUR             87649415
+#define TIME_MAX_INTERVAL_HOUR_CHAR_LENGTH 8
+
+/* Number of full days between '0001-01-01' and '9999-12-31'*/
+#define TIME_MAX_INTERVAL_DAY              3652058 /*87649415/24*/
+#define TIME_MAX_INTERVAL_DAY_CHAR_LENGTH  7
+
 /* Limits for the TIME data type */
 #define TIME_MAX_HOUR 838
 #define TIME_MAX_MINUTE 59
@@ -112,7 +122,21 @@ my_bool str_to_DDhhmmssff(const char *str, size_t length, MYSQL_TIME *l_time,
                           ulong max_hour, MYSQL_TIME_STATUS *status);
 my_bool str_to_datetime_or_date_or_time(const char *str, size_t length,
                                         MYSQL_TIME *to, ulonglong flag,
-                                        MYSQL_TIME_STATUS *status);
+                                        MYSQL_TIME_STATUS *status,
+                                        ulong time_max_hour,
+                                        ulong time_err_hour);
+my_bool
+str_to_datetime_or_date_or_interval_hhmmssff(const char *str, size_t length,
+                                             MYSQL_TIME *to, ulonglong flag,
+                                             MYSQL_TIME_STATUS *status,
+                                             ulong time_max_hour,
+                                             ulong time_err_hour);
+my_bool
+str_to_datetime_or_date_or_interval_day(const char *str, size_t length,
+                                        MYSQL_TIME *to, ulonglong flag,
+                                        MYSQL_TIME_STATUS *status,
+                                        ulong time_max_hour,
+                                        ulong time_err_hour);
 my_bool str_to_datetime_or_date(const char *str, size_t length, MYSQL_TIME *to,
                                 ulonglong flags, MYSQL_TIME_STATUS *status);
 
@@ -120,7 +144,7 @@ longlong number_to_datetime_or_date(longlong nr, ulong sec_part,
                                     MYSQL_TIME *time_res,
                                     ulonglong flags, int *was_cut);
 int number_to_time_only(my_bool neg, ulonglong nr, ulong sec_part,
-                        MYSQL_TIME *ltime, int *was_cut);
+                        ulong max_hour, MYSQL_TIME *to, int *was_cut);
 
 ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *);
 ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *);
diff --git a/libmysqld/libmysql.c b/libmysqld/libmysql.c
index 98dc9f17f60..cd170b42b42 100644
--- a/libmysqld/libmysql.c
+++ b/libmysqld/libmysql.c
@@ -3215,7 +3215,8 @@ static void fetch_string_with_conversion(MYSQL_BIND *param, char *value, size_t
   {
     MYSQL_TIME *tm= (MYSQL_TIME *)buffer;
     MYSQL_TIME_STATUS status;
-    str_to_datetime_or_date_or_time(value, length, tm, 0, &status);
+    str_to_datetime_or_date_or_time(value, length, tm, 0, &status,
+                                    TIME_MAX_HOUR, UINT_MAX32);
     err= status.warnings;
     *param->error= MY_TEST(err);
     break;
diff --git a/mysql-test/main/func_extract.result b/mysql-test/main/func_extract.result
new file mode 100644
index 00000000000..7a751a8dd72
--- /dev/null
+++ b/mysql-test/main/func_extract.result
@@ -0,0 +1,555 @@
+#
+# 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
+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,
+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	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	767	3123	31	23	59	59	123456
+2001-01-01 10:20:30.123456	34	110	1	10	20	30	123456
+4294967296:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+4294967295:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+87649416:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+87649415:59:59.123456	87649415	365205823	3652058	23	59	59	123456
+87649414:59:59.123456	87649414	365205822	3652058	22	59	59	123456
+9999:59:59.123456	9999	41615	416	15	59	59	123456
+9999:01:01.123456	9999	41615	416	15	1	1	123456
+9999:01:01	9999	41615	416	15	1	1	0
+0.999999	0	0	0	0	0	0	999999
+0.99999	0	0	0	0	0	0	999990
+0.9999	0	0	0	0	0	0	999900
+0.999	0	0	0	0	0	0	999000
+0.99	0	0	0	0	0	0	990000
+0.9	0	0	0	0	0	0	900000
+000000	0	0	0	0	0	0	0
+Warnings:
+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 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: '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,
+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	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	767	3123	31	23	59	59	123456
+20010101102030.123456000	34	110	1	10	20	30	123456
+42949672965959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+42949672955959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+876494165959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+876494155959.123456000	87649415	365205823	3652058	23	59	59	123456
+876494145959.123456000	87649414	365205822	3652058	22	59	59	123456
+99995959.123456000	9999	41615	416	15	59	59	123456
+99990101.123456000	9999	41615	416	15	1	1	123456
+99990101.000000000	9999	41615	416	15	1	1	0
+0.999999000	0	0	0	0	0	0	999999
+0.999990000	0	0	0	0	0	0	999990
+0.999900000	0	0	0	0	0	0	999900
+0.999000000	0	0	0	0	0	0	999000
+0.990000000	0	0	0	0	0	0	990000
+0.900000000	0	0	0	0	0	0	900000
+0.000000000	0	0	0	0	0	0	0
+Warnings:
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b' at row 3
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b' at row 4
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' at row 5
+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,
+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)
+	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+Warnings:
+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 FROM t1;
+EXTRACT(DAY FROM a)	EXTRACT(DAY_SECOND FROM a)	a
+84	84080103	2024:01:03 garbage /////
+1	1000103	24:01:03 garbage /////
+0	10103	01:01:03 garbage /////
+84	84080203	2024:02:03
+4166	4166160203	100000:02:03
+1	1000203	24:02:03
+0	10203	01:02:03
+0	10203	01:02:03:
+0	10203	01:02:03-
+0	10203	01:02:03;
+0	10203	01:02:03/
+20	20102030	20 10:20:30
+Warnings:
+Warning	1292	Truncated incorrect time value: '2024:01:03 garbage /////'
+Warning	1292	Truncated incorrect time 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	Truncated incorrect time value: '01:01:03 garbage /////'
+Warning	1292	Truncated incorrect time 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 time 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 time value: '01:02:03;'
+Warning	1292	Truncated incorrect time value: '01:02:03/'
+Warning	1292	Truncated incorrect time value: '01:02:03/'
+DROP TABLE t1;
diff --git a/mysql-test/main/func_extract.test b/mysql-test/main/func_extract.test
new file mode 100644
index 00000000000..f128b6509a2
--- /dev/null
+++ b/mysql-test/main/func_extract.test
@@ -0,0 +1,250 @@
+--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,
+  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,
+  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,
+  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 FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result
index a6e1ed9cb19..2315f5cd645 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -367,9 +367,7 @@ extract(DAY_MINUTE FROM "02 10:11:12")
 21011
 select extract(DAY_SECOND FROM "225 10:11:12");
 extract(DAY_SECOND FROM "225 10:11:12")
-34225959
-Warnings:
-Warning	1292	Truncated incorrect time value: '225 10:11:12'
+225101112
 select extract(HOUR FROM "1999-01-02 10:11:12");
 extract(HOUR FROM "1999-01-02 10:11:12")
 10
@@ -1033,9 +1031,7 @@ Note	1105	Cast to unsigned converted negative integer to it's positive complemen
 Warning	1292	Truncated incorrect time value: '18446744073709551615:00:00'
 SELECT EXTRACT(HOUR FROM '10000:02:03');
 EXTRACT(HOUR FROM '10000:02:03')
-22
-Warnings:
-Warning	1292	Truncated incorrect time value: '10000:02:03'
+16
 CREATE TABLE t1(f1 TIME);
 INSERT IGNORE INTO t1 VALUES('916:00:00 a');
 Warnings:
diff --git a/mysql-test/main/str_to_datetime_457.result b/mysql-test/main/str_to_datetime_457.result
index a024b30944c..e365c303c81 100644
--- a/mysql-test/main/str_to_datetime_457.result
+++ b/mysql-test/main/str_to_datetime_457.result
@@ -17,10 +17,7 @@ Warnings:
 Warning	1292	Incorrect datetime value: '0'
 select extract(hour from '100000:02:03'), extract(hour from '100000:02:03 ');
 extract(hour from '100000:02:03')	extract(hour from '100000:02:03 ')
-NULL	NULL
-Warnings:
-Warning	1292	Incorrect time value: '100000:02:03'
-Warning	1292	Incorrect time value: '100000:02:03 '
+16	16
 #
 # backward compatibility craziness
 #
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index e0c894023f6..cc4249074e0 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -262,12 +262,25 @@ static void get_microseconds(ulong *val, MYSQL_TIME_STATUS *status,
 
 
 static int check_time_range_internal(MYSQL_TIME *ltime,
-                                     ulong max_hour, uint dec,
-                                     int *warning);
+                                     ulong max_hour, ulong err_hour,
+                                     uint dec, int *warning);
 
 int check_time_range(MYSQL_TIME *ltime, uint dec, int *warning)
 {
-  return check_time_range_internal(ltime, TIME_MAX_HOUR, dec, warning);
+  return check_time_range_internal(ltime, TIME_MAX_HOUR, UINT_MAX32,
+                                   dec, warning);
+}
+
+
+static my_bool
+set_neg(my_bool neg, MYSQL_TIME_STATUS *st, MYSQL_TIME *ltime)
+{
+  if ((ltime->neg= neg) && ltime->time_type != MYSQL_TIMESTAMP_TIME)
+  {
+    st->warnings|= MYSQL_TIME_WARN_OUT_OF_RANGE;
+    return TRUE;
+  }
+  return FALSE;
 }
 
 
@@ -351,20 +364,51 @@ static my_bool find_body(my_bool *neg, const char *str, size_t length,
 }
 
 
+typedef struct
+{
+  uint count_punct;
+  uint count_colon;
+  uint count_iso_date_time_separator;
+} MYSQL_TIME_USED_CHAR_STATISTICS;
+
+
+static void
+mysql_time_used_char_statistics_init(MYSQL_TIME_USED_CHAR_STATISTICS *to,
+                                     const char *str, const char *end)
+{
+  const char *s;
+  bzero((void *) to, sizeof(MYSQL_TIME_USED_CHAR_STATISTICS));
+  for (s= str; s < end; s++)
+  {
+    if (my_ispunct(&my_charset_latin1, *s))
+      to->count_punct++;
+    if (*s == ':')
+      to->count_colon++;
+    if (*s == 'T')
+      to->count_iso_date_time_separator++;
+  }
+}
+
+
 static my_bool
-is_datetime_body_candidate(const char *str, size_t length)
+is_datetime_body_candidate(const char *str, size_t length,
+                           my_bool allow_dates_delimited,
+                           my_bool allow_dates_numeric)
 {
   static uint min_date_length= 5; /* '1-1-1' -> '0001-01-01' */
-  uint pos;
+  uint pos, count_punct= 0;
+  uint date_time_separator_length= MY_TEST(!allow_dates_delimited);
   if (length >= 12)
     return TRUE;
   /*
     The shortest possible DATE is '1-1-1', which is 5 characters.
     To make a full datetime it should be at least followed by a space or a 'T'.
+    To make a date it should be just not less that 5 characters.
   */
-  if (length < min_date_length + 1/* DATE/TIME separator */)
+  if (length < min_date_length + date_time_separator_length &&
+      !allow_dates_numeric)
     return FALSE;
-  for (pos= min_date_length; pos < length; pos++)
+  for (pos= 0; pos < length; pos++)
   {
     if (str[pos] == 'T') /* Date/time separator */
       return TRUE;
@@ -381,21 +425,23 @@ is_datetime_body_candidate(const char *str, size_t length)
           TIME('111 11')    -> 838:59:59  = 111 days 11 hours with overflow
           TIME('1111 11')   -> 838:59:59  = 1111 days 11 hours with overflow
       */
-      for (pos= 0 ; pos < min_date_length; pos++)
-      {
-        if (my_ispunct(&my_charset_latin1, str[pos])) /* Can be a DATE */
-          return TRUE;
-      }
-      return FALSE;
+      return count_punct > 0; /* Can be a DATE if already had separators*/
+    }
+    if (my_ispunct(&my_charset_latin1, str[pos]))
+    {
+      if (allow_dates_delimited && str[pos] != ':')
+        return TRUE;
+      count_punct++;
     }
   }
-  return FALSE;
+  return allow_dates_numeric && count_punct == 0;
 }
 
 
 static my_bool
 str_to_DDhhmmssff_internal(my_bool neg, const char *str, size_t length,
-                           MYSQL_TIME *l_time, ulong max_hour,
+                           MYSQL_TIME *l_time,
+                           ulong max_hour, ulong err_hour,
                            MYSQL_TIME_STATUS *status,
                            const char **endptr);
 
@@ -452,14 +498,18 @@ static my_bool
 str_to_datetime_or_date_body(const char *str, size_t length, MYSQL_TIME *l_time,
                              ulonglong flags,
                              my_bool punct_is_date_time_separator,
-                             MYSQL_TIME_STATUS *status)
+                             MYSQL_TIME_STATUS *status,
+                             uint *number_of_fields,
+                             const char **endptr)
 {
   const char *end=str+length, *pos;
-  uint number_of_fields= 0, digits, year_length, not_zero_date;
+  uint digits, year_length, not_zero_date;
   int warn= 0;
   DBUG_ENTER("str_to_datetime_or_date_body");
   DBUG_ASSERT(C_FLAGS_OK(flags));
   bzero(l_time, sizeof(*l_time));
+  *number_of_fields= 0;
+  *endptr= str;
 
   /*
     Calculate number of digits in first part.
@@ -487,40 +537,41 @@ str_to_datetime_or_date_body(const char *str, size_t length, MYSQL_TIME *l_time,
       (only numbers like [YY]YYMMDD[T][hhmmss[.uuuuuu]])
     */
     year_length= (digits == 4 || digits == 8 || digits >= 14) ? 4 : 2;
-    if (get_digits(&l_time->year, &number_of_fields, &str, end, year_length) 
-        || get_digits(&l_time->month, &number_of_fields, &str, end, 2)
-        || get_digits(&l_time->day, &number_of_fields, &str, end, 2)
+    if (get_digits(&l_time->year, number_of_fields, &str, end, year_length) 
+        || get_digits(&l_time->month, number_of_fields, &str, end, 2)
+        || get_digits(&l_time->day, number_of_fields, &str, end, 2)
         || get_maybe_T(&str, end)
-        || get_digits(&l_time->hour, &number_of_fields, &str, end, 2)
-        || get_digits(&l_time->minute, &number_of_fields, &str, end, 2)
-        || get_digits(&l_time->second, &number_of_fields, &str, end, 2))
+        || get_digits(&l_time->hour, number_of_fields, &str, end, 2)
+        || get_digits(&l_time->minute, number_of_fields, &str, end, 2)
+        || get_digits(&l_time->second, number_of_fields, &str, end, 2))
      warn|= MYSQL_TIME_WARN_TRUNCATED;
   }
   else
   {
     const char *start= str;
-    if (get_number(&l_time->year, &number_of_fields, &str, end))
+    if (get_number(&l_time->year, number_of_fields, &str, end))
       warn|= MYSQL_TIME_WARN_TRUNCATED;
     year_length= (uint)(str - start);
 
     if (!warn &&
         (get_punct(&str, end)
-         || get_number(&l_time->month, &number_of_fields, &str, end)
+         || get_number(&l_time->month, number_of_fields, &str, end)
          || get_punct(&str, end)
-         || get_number(&l_time->day, &number_of_fields, &str, end)
-         || get_date_time_separator(&number_of_fields,
+         || get_number(&l_time->day, number_of_fields, &str, end)
+         || get_date_time_separator(number_of_fields,
                                     punct_is_date_time_separator, &str, end)
-         || get_number(&l_time->hour, &number_of_fields, &str, end)
+         || get_number(&l_time->hour, number_of_fields, &str, end)
          || get_punct(&str, end)
-         || get_number(&l_time->minute, &number_of_fields, &str, end)
+         || get_number(&l_time->minute, number_of_fields, &str, end)
          || get_punct(&str, end)
-         || get_number(&l_time->second, &number_of_fields, &str, end)))
+         || get_number(&l_time->second, number_of_fields, &str, end)))
       warn|= MYSQL_TIME_WARN_TRUNCATED;
   }
   status->warnings|= warn;
 
+  *endptr= str;
   /* we're ok if date part is correct. even if the rest is truncated */
-  if (number_of_fields < 3)
+  if (*number_of_fields < 3)
   {
     l_time->time_type= MYSQL_TIMESTAMP_NONE;
     status->warnings|= MYSQL_TIME_WARN_TRUNCATED;
@@ -531,7 +582,8 @@ str_to_datetime_or_date_body(const char *str, size_t length, MYSQL_TIME *l_time,
   {
     str++;
     get_microseconds(&l_time->second_part, status,
-                     &number_of_fields, &str, end);
+                     number_of_fields, &str, end);
+    *endptr= str;
   }
 
   not_zero_date = l_time->year || l_time->month || l_time->day ||
@@ -551,7 +603,7 @@ str_to_datetime_or_date_body(const char *str, size_t length, MYSQL_TIME *l_time,
   if (check_date(l_time, not_zero_date, flags, &status->warnings))
     goto err;
 
-  l_time->time_type= (number_of_fields <= 3 ?
+  l_time->time_type= (*number_of_fields <= 3 ?
                       MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
 
   if (str != end)
@@ -594,27 +646,104 @@ err:
      TRUE  on error
 */
 
-static
-my_bool str_to_datetime_or_date_or_time_body(const char *str, size_t length,
-                                             MYSQL_TIME *l_time,
-                                             ulonglong fuzzydate,
-                                             MYSQL_TIME_STATUS *status)
+static my_bool
+str_to_datetime_or_date_or_time_body(const char *str, size_t length,
+                                     MYSQL_TIME *l_time,
+                                     ulonglong fuzzydate,
+                                     MYSQL_TIME_STATUS *status,
+                                     ulong time_max_hour,
+                                     ulong time_err_hour,
+                                     my_bool allow_dates_delimited,
+                                     my_bool allow_dates_numeric)
 {
   const char *endptr;
   DBUG_ASSERT(C_FLAGS_OK(fuzzydate));
 
   /* Check first if this is a full TIMESTAMP */
-  if (is_datetime_body_candidate(str, length))
+  if (is_datetime_body_candidate(str, length,
+                                 allow_dates_delimited,
+                                 allow_dates_numeric))
   {                                             /* Probably full timestamp */
     int warn_copy= status->warnings; /* could already be set by find_body() */
-    (void) str_to_datetime_or_date_body(str, length, l_time,
-                                        fuzzydate, FALSE, status);
-    if (l_time->time_type >= MYSQL_TIMESTAMP_ERROR)
-      return l_time->time_type == MYSQL_TIMESTAMP_ERROR;
+    uint number_of_fields;
+    (void) str_to_datetime_or_date_body(str, length, l_time, fuzzydate,
+                                        FALSE, status,
+                                        &number_of_fields, &endptr);
+    DBUG_ASSERT(endptr >= str);
+    DBUG_ASSERT(endptr <= str + length);
+    switch (l_time->time_type) {
+    case MYSQL_TIMESTAMP_DATETIME:
+      return FALSE;
+    case MYSQL_TIMESTAMP_DATE:
+      {
+       /*
+         Successfully parsed as DATE, but it can also be a TIME:
+           '24:02:03'               - continue and parse as TIME
+           '24:02:03 garbage /////' - continue and parse as TIME
+           '24:02:03T'              - return DATE
+           '24-02-03'               - return DATE
+           '24/02/03'               - return DATE
+           '11111'                  - return DATE
+       */
+        MYSQL_TIME_USED_CHAR_STATISTICS used_chars;
+        mysql_time_used_char_statistics_init(&used_chars, str, endptr);
+        if (used_chars.count_iso_date_time_separator || !used_chars.count_colon)
+          return FALSE;
+      }
+      break;
+    case MYSQL_TIMESTAMP_ERROR:
+      {
+        MYSQL_TIME_USED_CHAR_STATISTICS used_chars;
+        /*
+          Check if it parsed as DATETIME but then failed as out of range:
+            '2011-02-32 8:46:06.23434' - return error
+        */
+        if (number_of_fields > 3)
+          return TRUE;
+        /*
+          Check if it parsed as DATE but then failed as out of range:
+            '100000:02:03'  - continue and parse as TIME
+            '100000:02:03T' - return error
+            '100000/02/03'  - return error
+            '100000-02-03'  - return error
+        */
+        mysql_time_used_char_statistics_init(&used_chars, str, endptr);
+        if (used_chars.count_iso_date_time_separator || !used_chars.count_colon)
+          return TRUE;
+      }
+      break;
+    case MYSQL_TIMESTAMP_NONE:
+      {
+        if (allow_dates_numeric && endptr >= str + length)
+        {
+          /*
+            For backward compatibility this parses as DATE and fails:
+              EXTRACT(DAY FROM '1111') -- return error
+              EXTRACT(DAY FROM '1')    -- return error
+          */
+          MYSQL_TIME_USED_CHAR_STATISTICS used_chars;
+          mysql_time_used_char_statistics_init(&used_chars, str, endptr);
+          if (!used_chars.count_iso_date_time_separator &&
+              !used_chars.count_colon &&
+              !used_chars.count_punct)
+            return TRUE;
+        }
+        /*
+          - '256 10:30:30'               - continue and parse as TIME
+          - '4294967296:59:59.123456456' - continue and parse as TIME
+        */
+      }
+      break;
+    case MYSQL_TIMESTAMP_TIME:
+      DBUG_ASSERT(0);
+      break;
+    }
+    my_time_status_init(status);
     status->warnings= warn_copy;
   }
 
-  if (!str_to_DDhhmmssff_internal(FALSE, str, length, l_time, TIME_MAX_HOUR,
+  if (!str_to_DDhhmmssff_internal(FALSE, str, length, l_time,
+                                  time_max_hour, time_err_hour,
                                   status, &endptr))
     return FALSE;
 
@@ -640,10 +769,11 @@ my_bool str_to_DDhhmmssff(const char *str, size_t length, MYSQL_TIME *ltime,
     return TRUE;
 
   /* Reject anything that might be parsed as a full TIMESTAMP */
-  if (is_datetime_body_candidate(str, length))
+  if (is_datetime_body_candidate(str, length, FALSE, FALSE))
   {
-    (void) str_to_datetime_or_date_body(str, length, ltime,
-                                        0, FALSE, status);
+    uint number_of_fields;
+    (void) str_to_datetime_or_date_body(str, length, ltime, 0, FALSE,
+                                        status, &number_of_fields, &endptr);
     if (ltime->time_type > MYSQL_TIMESTAMP_ERROR)
     {
       status->warnings|= MYSQL_TIME_WARN_TRUNCATED;
@@ -659,7 +789,7 @@ my_bool str_to_DDhhmmssff(const char *str, size_t length, MYSQL_TIME *ltime,
     will scan only '2001'.
   */
   if (str_to_DDhhmmssff_internal(neg, str, length, ltime, max_hour,
-                                 status, &endptr) ||
+                                 UINT_MAX32, status, &endptr) ||
       (endptr < str + length && endptr[0] == '-'))
     return TRUE;
   return FALSE;
@@ -668,29 +798,63 @@ my_bool str_to_DDhhmmssff(const char *str, size_t length, MYSQL_TIME *ltime,
 
 my_bool
 str_to_datetime_or_date_or_time(const char *str, size_t length,
-                                MYSQL_TIME *l_time,
-                                ulonglong fuzzydate,
-                                MYSQL_TIME_STATUS *status)
+                                MYSQL_TIME *to, ulonglong mode,
+                                MYSQL_TIME_STATUS *status,
+                                ulong time_max_hour,
+                                ulong time_err_hour)
 {
-  my_bool neg, rc;
-  DBUG_ASSERT(C_FLAGS_OK(fuzzydate));
+  my_bool neg;
+  DBUG_ASSERT(C_FLAGS_OK(mode));
+  my_time_status_init(status);
+  return
+    find_body(&neg, str, length, to, &status->warnings, &str, &length) ||
+    str_to_datetime_or_date_or_time_body(str, length, to, mode, status,
+                                         time_max_hour, time_err_hour,
+                                         FALSE, FALSE) ||
+    set_neg(neg, status, to);
+}
+
+
+my_bool
+str_to_datetime_or_date_or_interval_hhmmssff(const char *str, size_t length,
+                                             MYSQL_TIME *to, ulonglong mode,
+                                             MYSQL_TIME_STATUS *status,
+                                             ulong time_max_hour,
+                                             ulong time_err_hour)
+{
+  my_bool neg;
+  DBUG_ASSERT(C_FLAGS_OK(mode));
+  my_time_status_init(status);
+  return
+    find_body(&neg, str, length, to, &status->warnings, &str, &length) ||
+    str_to_datetime_or_date_or_time_body(str, length, to, mode, status,
+                                         time_max_hour, time_err_hour,
+                                         TRUE, FALSE) ||
+    set_neg(neg, status, to);
+}
+
+
+my_bool
+str_to_datetime_or_date_or_interval_day(const char *str, size_t length,
+                                        MYSQL_TIME *to, ulonglong mode,
+                                        MYSQL_TIME_STATUS *status,
+                                        ulong time_max_hour,
+                                        ulong time_err_hour)
+{
+  my_bool neg;
+  DBUG_ASSERT(C_FLAGS_OK(mode));
   my_time_status_init(status);
-  if (find_body(&neg, str, length, l_time, &status->warnings, &str, &length))
-    return TRUE;
   /*
-    QQ: Perhaps we should modify xxx_body() to return endptr.
-    If endptr points to '-', return an error.
+    For backward compatibility we allow to parse non-delimited
+    values as DATE rather than as TIME:
+      EXTRACT(DAY FROM '11111') 
   */
-  rc= str_to_datetime_or_date_or_time_body(str, length, l_time,
-                                           fuzzydate, status);
-  if (rc)
-    return rc;
-  if ((l_time->neg= neg) && l_time->time_type != MYSQL_TIMESTAMP_TIME)
-  {
-    status->warnings|= MYSQL_TIME_WARN_OUT_OF_RANGE;
-    return TRUE;
-  }
-  return FALSE;
+  return
+    find_body(&neg, str, length, to, &status->warnings, &str, &length) ||
+    str_to_datetime_or_date_or_time_body(str, length, to, mode, status,
+                                         time_max_hour, time_err_hour,
+                                         TRUE, TRUE) ||
+    set_neg(neg, status, to);
 }
 
 
@@ -698,20 +862,16 @@ my_bool
 str_to_datetime_or_date(const char *str, size_t length, MYSQL_TIME *l_time,
                         ulonglong flags, MYSQL_TIME_STATUS *status)
 {
-  my_bool neg, rc;
+  my_bool neg;
+  uint number_of_fields;
+  const char *endptr;
   DBUG_ASSERT(C_FLAGS_OK(flags));
   my_time_status_init(status);
-  if (find_body(&neg, str, length, l_time, &status->warnings, &str, &length))
-    return TRUE;
-  rc= str_to_datetime_or_date_body(str, length, l_time, flags, TRUE, status);
-  if (rc)
-    return rc;
-  if ((l_time->neg= neg))
-  {
-    status->warnings|= MYSQL_TIME_WARN_OUT_OF_RANGE;
-    return TRUE;
-  }
-  return FALSE;
+  return
+    find_body(&neg, str, length, l_time, &status->warnings, &str, &length) ||
+    str_to_datetime_or_date_body(str, length, l_time, flags, TRUE,
+                                 status, &number_of_fields, &endptr) ||
+    set_neg(neg, status, l_time);
 }
 
 
@@ -738,7 +898,8 @@ str_to_datetime_or_date(const char *str, size_t length, MYSQL_TIME *l_time,
 */
 static my_bool
 str_to_DDhhmmssff_internal(my_bool neg, const char *str, size_t length,
-                           MYSQL_TIME *l_time, ulong max_hour,
+                           MYSQL_TIME *l_time,
+                           ulong max_hour, ulong err_hour,
                            MYSQL_TIME_STATUS *status, const char **endptr)
 {
   ulong date[5];
@@ -891,7 +1052,8 @@ fractional:
   *endptr= str;
 
   /* Check if the value is valid and fits into MYSQL_TIME range */
-  if (check_time_range_internal(l_time, max_hour, 6, &status->warnings))
+  if (check_time_range_internal(l_time, max_hour, err_hour,
+                                6, &status->warnings))
     return TRUE;
 
   /* Check if there is garbage at end of the MYSQL_TIME specification */
@@ -911,7 +1073,9 @@ err:
   SYNOPSIS:
     check_time_range_internal()
     time     pointer to MYSQL_TIME value
-    ulong    max_hour - maximum allowed hour value
+    ulong    max_hour - maximum allowed hour value. if the hour is greater,
+                        cut the time value to 'max_hour:59:59.999999'
+    ulong    err_hour - if hour is greater than this value, return an error
     uint     dec
     warning  set MYSQL_TIME_WARN_OUT_OF_RANGE flag if the value is out of range
 
@@ -926,13 +1090,15 @@ err:
 */
 
 int check_time_range_internal(struct st_mysql_time *my_time,
-                              ulong max_hour, uint dec, int *warning)
+                              ulong max_hour, ulong err_hour,
+                              uint dec, int *warning)
 {
   ulonglong hour;
   static ulong max_sec_part[TIME_SECOND_PART_DIGITS+1]= {000000, 900000, 990000,
                                              999000, 999900, 999990, 999999};
 
-  if (my_time->minute >= 60 || my_time->second >= 60)
+  if (my_time->minute >= 60 || my_time->second >= 60 ||
+      my_time->hour > err_hour)
   {
     *warning|= MYSQL_TIME_WARN_TRUNCATED;
     return 1;
@@ -1581,17 +1747,19 @@ longlong number_to_datetime_or_date(longlong nr, ulong sec_part,
     -1       time value is invalid
 */
 int number_to_time_only(my_bool neg, ulonglong nr, ulong sec_part,
-                        MYSQL_TIME *ltime, int *was_cut)
+                        ulong max_hour, MYSQL_TIME *ltime, int *was_cut)
 {
+  static const ulonglong TIME_MAX_mmss= TIME_MAX_MINUTE*100 + TIME_MAX_SECOND;
+  ulonglong time_max_value= max_hour * 10000ULL + TIME_MAX_mmss;
   *was_cut= 0;
   ltime->year= ltime->month= ltime->day= 0;
   ltime->time_type= MYSQL_TIMESTAMP_TIME;
 
   ltime->neg= neg;
 
-  if (nr > TIME_MAX_VALUE)
+  if (nr > time_max_value)
   {
-    nr= TIME_MAX_VALUE;
+    nr= time_max_value;
     sec_part= TIME_MAX_SECOND_PART;
     *was_cut= MYSQL_TIME_WARN_OUT_OF_RANGE;
   }
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 2da92f971d1..41a5798c3b5 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2092,16 +2092,18 @@ void Item_extract::print(String *str, enum_query_type query_type)
 bool Item_extract::fix_length_and_dec()
 {
   maybe_null=1;					// If wrong date
+  uint32 daylen= args[0]->cmp_type() == TIME_RESULT ? 2 :
+                 TIME_MAX_INTERVAL_DAY_CHAR_LENGTH;
   switch (int_type) {
   case INTERVAL_YEAR:             set_date_length(4); break; // YYYY
   case INTERVAL_YEAR_MONTH:       set_date_length(6); break; // YYYYMM
   case INTERVAL_QUARTER:          set_date_length(2); break; // 1..4
   case INTERVAL_MONTH:            set_date_length(2); break; // MM
   case INTERVAL_WEEK:             set_date_length(2); break; // 0..52
-  case INTERVAL_DAY:              set_day_length(2); break; // DD
-  case INTERVAL_DAY_HOUR:         set_time_length(4); break; // DDhh
-  case INTERVAL_DAY_MINUTE:       set_time_length(6); break; // DDhhmm
-  case INTERVAL_DAY_SECOND:       set_time_length(8); break; // DDhhmmss
+  case INTERVAL_DAY:              set_day_length(daylen); break; // DD
+  case INTERVAL_DAY_HOUR:         set_day_length(daylen+2); break; // DDhh
+  case INTERVAL_DAY_MINUTE:       set_day_length(daylen+4); break; // DDhhmm
+  case INTERVAL_DAY_SECOND:       set_day_length(daylen+6); break; // DDhhmmss
   case INTERVAL_HOUR:             set_time_length(2); break; // hh
   case INTERVAL_HOUR_MINUTE:      set_time_length(4); break; // hhmm
   case INTERVAL_HOUR_SECOND:      set_time_length(6); break; // hhmmss
@@ -2109,7 +2111,7 @@ bool Item_extract::fix_length_and_dec()
   case INTERVAL_MINUTE_SECOND:    set_time_length(4); break; // mmss
   case INTERVAL_SECOND:           set_time_length(2); break; // ss
   case INTERVAL_MICROSECOND:      set_time_length(6); break; // ffffff
-  case INTERVAL_DAY_MICROSECOND:  set_time_length(14); break; // DDhhmmssffffff
+  case INTERVAL_DAY_MICROSECOND:  set_time_length(daylen+12); break; // DDhhmmssffffff
   case INTERVAL_HOUR_MICROSECOND: set_time_length(12); break; // hhmmssffffff
   case INTERVAL_MINUTE_MICROSECOND: set_time_length(10); break; // mmssffffff
   case INTERVAL_SECOND_MICROSECOND: set_time_length(8); break; // ssffffff
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index d2a4e87a3d1..45f10659a76 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -993,12 +993,12 @@ class Item_extract :public Item_int_func,
         EXTRACT(DAY FROM '-24:00:00') -> -1
     */
     set_handler(handler_by_length(max_length= length + 1/*sign*/, 11));
-    m_date_mode= date_mode_t(0);
+    m_date_mode= TIME_INTERVAL_DAY;
   }
   void set_time_length(uint32 length)
   {
     set_handler(handler_by_length(max_length= length + 1/*sign*/, 11));
-    m_date_mode= TIME_TIME_ONLY;
+    m_date_mode= TIME_INTERVAL_hhmmssff;
   }
  public:
   const interval_type int_type; // keep it public
diff --git a/sql/sql_basic_types.h b/sql/sql_basic_types.h
index 84bd165a23f..5062817f911 100644
--- a/sql/sql_basic_types.h
+++ b/sql/sql_basic_types.h
@@ -37,11 +37,13 @@ public:
       FUZZY_DATES is used for the result will only be used for comparison
       purposes. Conversion is as relaxed as possible.
     */
-    FUZZY_DATES=     1U,
-    TIME_ONLY=       4U,
-    NO_ZERO_IN_DATE= (1UL << 23),  // MODE_NO_ZERO_IN_DATE
-    NO_ZERO_DATE=    (1UL << 24),  // MODE_NO_ZERO_DATE
-    INVALID_DATES=   (1UL << 25)   // MODE_INVALID_DATES
+    FUZZY_DATES=        1U,
+    TIME_ONLY=          4U,
+    INTERVAL_hhmmssff=  8U,
+    INTERVAL_DAY=      16U,
+    NO_ZERO_IN_DATE=   (1UL << 23),  // MODE_NO_ZERO_IN_DATE
+    NO_ZERO_DATE=      (1UL << 24),  // MODE_NO_ZERO_DATE
+    INVALID_DATES=     (1UL << 25)   // MODE_INVALID_DATES
   };
 
 private:
@@ -98,6 +100,8 @@ public:
 const date_mode_t
   TIME_FUZZY_DATES            (date_mode_t::value_t::FUZZY_DATES),
   TIME_TIME_ONLY              (date_mode_t::value_t::TIME_ONLY),
+  TIME_INTERVAL_hhmmssff      (date_mode_t::value_t::INTERVAL_hhmmssff),
+  TIME_INTERVAL_DAY           (date_mode_t::value_t::INTERVAL_DAY),
   TIME_NO_ZERO_IN_DATE        (date_mode_t::value_t::NO_ZERO_IN_DATE),
   TIME_NO_ZERO_DATE           (date_mode_t::value_t::NO_ZERO_DATE),
   TIME_INVALID_DATES          (date_mode_t::value_t::INVALID_DATES);
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index ff02bfa0ae4..35a4e1e47c3 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -371,18 +371,14 @@ public:
 };
 
 
-/* Character set-aware version of str_to_datetime_or_date_or_time() */
-bool Temporal::str_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *status,
+/* Character set-aware version of ascii_to_datetime_or_date_or_time() */
+bool Temporal::str_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *st,
                                                const char *str, size_t length,
                                                CHARSET_INFO *cs,
                                                date_mode_t fuzzydate)
 {
   TemporalAsciiBuffer tmp(str, length, cs);
-  bool rc= ::str_to_datetime_or_date_or_time(tmp.str, tmp.length, this,
-                       ulonglong(fuzzydate & TIME_MODE_FOR_XXX_TO_DATE),
-                       status);
-  DBUG_ASSERT(status->warnings || !rc);
-  return rc;
+  return ascii_to_datetime_or_date_or_time(st, tmp.str, tmp.length, fuzzydate);
 }
 
 
@@ -393,11 +389,17 @@ bool Temporal::str_to_datetime_or_date(MYSQL_TIME_STATUS *status,
                                        date_mode_t flags)
 {
   TemporalAsciiBuffer tmp(str, length, cs);
-  bool rc= ::str_to_datetime_or_date(tmp.str, tmp.length, this,
-                           ulonglong(flags & TIME_MODE_FOR_XXX_TO_DATE),
-                           status);
-  DBUG_ASSERT(status->warnings || !rc);
-  return rc;
+  return ascii_to_datetime_or_date(status, tmp.str, tmp.length, flags);
+}
+
+
+/* Character set-aware version of ascii_to_temporal() */
+bool Temporal::str_to_temporal(MYSQL_TIME_STATUS *status,
+                               const char *str, size_t length, CHARSET_INFO *cs,
+                               date_mode_t flags)
+{
+  TemporalAsciiBuffer tmp(str, length, cs);
+  return ascii_to_temporal(status, tmp.str, tmp.length, flags);
 }
 
 
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 6fa4684884d..31b25853514 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -181,9 +181,7 @@ void Temporal::make_from_str(THD *thd, Warn *warn,
                   push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
                                ER_YES, ErrConvString(str, length,cs).ptr()););
 
-  if (fuzzydate & TIME_TIME_ONLY ?
-      str_to_datetime_or_date_or_time(warn, str, length, cs, fuzzydate) :
-      str_to_datetime_or_date(warn, str, length, cs, fuzzydate))
+  if (str_to_temporal(warn, str, length, cs, fuzzydate))
     make_fuzzy_date(&warn->warnings, fuzzydate);
   if (warn->warnings)
     warn->set_str(str, length, &my_charset_bin);
@@ -232,8 +230,11 @@ void Sec6::make_truncated_warning(THD *thd, const char *type_str) const
 bool Sec6::convert_to_mysql_time(THD *thd, int *warn, MYSQL_TIME *ltime,
                                  date_mode_t fuzzydate) const
 {
-  bool is_time= bool(fuzzydate & TIME_TIME_ONLY);
-  bool rc= is_time ? to_time(ltime, warn) : to_datetime(ltime, fuzzydate, warn);
+  bool rc= fuzzydate & (TIME_INTERVAL_hhmmssff | TIME_INTERVAL_DAY) ?
+             to_datetime_or_to_interval_hhmmssff(ltime, warn) :
+           fuzzydate & TIME_TIME_ONLY ?
+             to_datetime_or_time(ltime, warn, fuzzydate) :
+             to_datetime_or_date(ltime, warn, fuzzydate);
   DBUG_ASSERT(*warn || !rc);
   if (truncated())
     *warn|= MYSQL_TIME_WARN_TRUNCATED;
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 2080152e538..3aeb2ad2788 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -264,21 +264,59 @@ public:
                              MYSQL_TIME *ltime,
                              date_mode_t fuzzydate) const;
 
-  // Convert a number in format hhhmmss.ff to TIME'hhh:mm:ss.ff'
-  bool to_time(MYSQL_TIME *to, int *warn) const
+protected:
+
+  bool to_interval_hhmmssff_only(MYSQL_TIME *to, int *warn) const
+  {
+    return number_to_time_only(m_neg, m_sec, m_usec,
+                               TIME_MAX_INTERVAL_HOUR, to, warn);
+  }
+  bool to_datetime_or_to_interval_hhmmssff(MYSQL_TIME *to, int *warn) const
   {
-    bool rc= (m_sec > 9999999 && m_sec <= 99991231235959ULL && !neg()) ?
-      number_to_datetime_or_date(m_sec, m_usec, to,
-                                 C_TIME_INVALID_DATES, warn) < 0 :
-      number_to_time_only(m_neg, m_sec, m_usec, to, warn);
+    /*
+      Convert a number to a time interval.
+      The following formats are understood:
+      -            0 <= x <=   999999995959 - parse as hhhhmmss
+      - 999999995959 <  x <= 99991231235959 - parse as YYYYMMDDhhmmss
+       (YYMMDDhhmmss)       (YYYYMMDDhhmmss)
+
+      Note, these formats are NOT understood:
+      - YYMMDD       - overlaps with INTERVAL range
+      - YYYYMMDD     - overlaps with INTERVAL range
+      - YYMMDDhhmmss - overlaps with INTERVAL range, partially
+                       (see TIME_MAX_INTERVAL_HOUR)
+
+      If we ever need wider intervals, this code switching between
+      full datetime and interval-only should be rewised.
+    */
+    DBUG_ASSERT(TIME_MAX_INTERVAL_HOUR <= 999999995959);
+    /*            (YYMMDDhhmmss) */
+    if (m_sec >    999999995959ULL &&
+        m_sec <= 99991231235959ULL && m_neg == 0)
+      return to_datetime_or_date(to, warn, TIME_INVALID_DATES);
+    if (m_sec / 10000 > TIME_MAX_INTERVAL_HOUR)
+    {
+      *warn= MYSQL_TIME_WARN_OUT_OF_RANGE;
+      return true;
+    }
+    return to_interval_hhmmssff_only(to, warn);
+  }
+public:
+  // [-][DD]hhhmmss.ff,  YYMMDDhhmmss.ff, YYYYMMDDhhmmss.ff
+  bool to_datetime_or_time(MYSQL_TIME *to, int *warn, date_mode_t mode) const
+  {
+    bool rc= m_sec > 9999999 && m_sec <= 99991231235959ULL && !m_neg ?
+             ::number_to_datetime_or_date(m_sec, m_usec, to,
+                        ulonglong(mode & TIME_MODE_FOR_XXX_TO_DATE), warn) < 0 :
+             ::number_to_time_only(m_neg, m_sec, m_usec, TIME_MAX_HOUR, to, warn);
     DBUG_ASSERT(*warn || !rc);
     return rc;
   }
   /*
-    Convert a number in format YYYYMMDDhhmmss.ff to
+    Convert a number in formats YYYYMMDDhhmmss.ff or YYMMDDhhmmss.ff to
     TIMESTAMP'YYYY-MM-DD hh:mm:ss.ff'
   */
-  bool to_datetime(MYSQL_TIME *to, date_mode_t flags, int *warn) const
+  bool to_datetime_or_date(MYSQL_TIME *to, int *warn, date_mode_t flags) const
   {
     if (m_neg)
     {
@@ -458,6 +496,8 @@ public:
                                  timestamp_type tstype, const char *name)
     {
       const char *typestr= tstype >= 0 ? type_name_by_timestamp_type(tstype) :
+                           mode & (TIME_INTERVAL_hhmmssff | TIME_INTERVAL_DAY) ?
+                           "interval" :
                            mode & TIME_TIME_ONLY ? "time" : "datetime";
       Temporal::push_conversion_warnings(thd, totally_useless_value, warnings, typestr,
                                          name, ptr());
@@ -579,6 +619,60 @@ protected:
     if (warn->warnings)
       warn->set_decimal(nr);
   }
+  bool ascii_to_temporal(MYSQL_TIME_STATUS *st,
+                         const char *str, size_t length,
+                         date_mode_t mode)
+  {
+    if (mode & (TIME_INTERVAL_hhmmssff | TIME_INTERVAL_DAY))
+      return ascii_to_datetime_or_date_or_interval_DDhhmmssff(st, str, length,
+                                                              mode);
+    if (mode & TIME_TIME_ONLY)
+      return ascii_to_datetime_or_date_or_time(st, str, length, mode);
+    return ascii_to_datetime_or_date(st, str, length, mode);
+  }
+  bool ascii_to_datetime_or_date_or_interval_DDhhmmssff(MYSQL_TIME_STATUS *st,
+                                                        const char *str,
+                                                        size_t length,
+                                                        date_mode_t mode)
+  {
+    longlong cflags= ulonglong(mode & TIME_MODE_FOR_XXX_TO_DATE);
+    bool rc= mode & TIME_INTERVAL_DAY ?
+      ::str_to_datetime_or_date_or_interval_day(str, length, this, cflags, st,
+                                                TIME_MAX_INTERVAL_HOUR,
+                                                TIME_MAX_INTERVAL_HOUR) :
+      ::str_to_datetime_or_date_or_interval_hhmmssff(str, length, this,
+                                                     cflags, st,
+                                                     TIME_MAX_INTERVAL_HOUR,
+                                                     TIME_MAX_INTERVAL_HOUR);
+    DBUG_ASSERT(!rc || st->warnings);
+    return rc;
+  }
+  bool ascii_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *status,
+                                         const char *str, size_t length,
+                                         date_mode_t fuzzydate)
+  {
+    ulonglong cflags= ulonglong(fuzzydate & TIME_MODE_FOR_XXX_TO_DATE);
+    bool rc= ::str_to_datetime_or_date_or_time(str, length, this,
+                                               cflags, status,
+                                               TIME_MAX_HOUR, UINT_MAX32);
+    DBUG_ASSERT(!rc || status->warnings);
+    return rc;
+  }
+  bool ascii_to_datetime_or_date(MYSQL_TIME_STATUS *status,
+                                 const char *str, size_t length,
+                                 date_mode_t fuzzydate)
+  {
+    DBUG_ASSERT(bool(fuzzydate & TIME_TIME_ONLY) == false);
+    bool rc= ::str_to_datetime_or_date(str, length, this,
+                             ulonglong(fuzzydate & TIME_MODE_FOR_XXX_TO_DATE),
+                             status);
+    DBUG_ASSERT(!rc || status->warnings);
+    return rc;
+  }
+  // Character set aware versions for string conversion routines
+  bool str_to_temporal(MYSQL_TIME_STATUS *st,
+                       const char *str, size_t length,
+                       CHARSET_INFO *cs, date_mode_t fuzzydate);
   bool str_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *st,
                                        const char *str, size_t length,
                                        CHARSET_INFO *cs, date_mode_t fuzzydate);
@@ -858,7 +952,7 @@ public:
   */
   static uint max_useful_hour()
   {
-    return 87649415;
+    return TIME_MAX_INTERVAL_HOUR;
   }
 public:
   Interval_DDhhmmssff(THD *thd, Status *st, bool push_warnings,
@@ -1120,7 +1214,7 @@ public:
   }
   Time(THD *thd, int *warn, const Sec6 &nr, const Options opt)
   {
-    if (nr.to_time(this, warn))
+    if (nr.to_datetime_or_time(this, warn, TIME_INVALID_DATES))
       time_type= MYSQL_TIMESTAMP_NONE;
     xxx_to_time_result_to_valid_value(thd, warn, opt);
   }
@@ -1305,7 +1399,7 @@ protected:
   Temporal_with_date(int *warn, const Sec6 &nr, date_mode_t flags)
   {
     DBUG_ASSERT(bool(flags & TIME_TIME_ONLY) == false);
-    if (nr.to_datetime(this, flags, warn))
+    if (nr.to_datetime_or_date(this, warn, flags))
       time_type= MYSQL_TIMESTAMP_NONE;
   }
   Temporal_with_date(MYSQL_TIME_STATUS *status,