MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))

11 files changed, 353 insertions, 86 deletions

diff --git a/include/my_time.h b/include/my_time.h
index ab0d2b0661d..94632bbbf38 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -208,6 +208,7 @@ void set_zero_time(MYSQL_TIME *tm, enum enum_mysql_timestamp_type time_type);
 #define MAX_DATE_STRING_REP_LENGTH 30
 #define AUTO_SEC_PART_DIGITS DECIMAL_NOT_SPECIFIED
 
+int my_interval_DDhhmmssff_to_str(const MYSQL_TIME *, char *to, uint digits);
 int my_time_to_str(const MYSQL_TIME *l_time, char *to, uint digits);
 int my_date_to_str(const MYSQL_TIME *l_time, char *to);
 int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, uint digits);
diff --git a/mysql-test/main/func_extract.result b/mysql-test/main/func_extract.result
index 7a751a8dd72..46a0053add5 100644
--- a/mysql-test/main/func_extract.result
+++ b/mysql-test/main/func_extract.result
@@ -87,6 +87,7 @@ a	b	EXTRACT(MICROSECOND FROM a)	EXTRACT(MICROSECOND FROM b)
 # 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),
@@ -95,25 +96,28 @@ 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
+a	cidm	dh	EXTRACT(DAY_HOUR FROM a)	EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)
+9999-12-31 23:59:59.123456	NULL	767	3123	31	23	59	59	123456
+2001-01-01 10:20:30.123456	NULL	34	110	1	10	20	30	123456
+4294967296:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+4294967295:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+87649416:59:59.123456	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+87649415:59:59.123456	3652058 23:59:59.123456	87649415	365205823	3652058	23	59	59	123456
+87649414:59:59.123456	3652058 22:59:59.123456	87649414	365205822	3652058	22	59	59	123456
+9999:59:59.123456	416 15:59:59.123456	9999	41615	416	15	59	59	123456
+9999:01:01.123456	416 15:01:01.123456	9999	41615	416	15	1	1	123456
+9999:01:01	416 15:01:01.000000	9999	41615	416	15	1	1	0
+0.999999	00:00:00.999999	0	0	0	0	0	0	999999
+0.99999	00:00:00.999990	0	0	0	0	0	0	999990
+0.9999	00:00:00.999900	0	0	0	0	0	0	999900
+0.999	00:00:00.999000	0	0	0	0	0	0	999000
+0.99	00:00:00.990000	0	0	0	0	0	0	990000
+0.9	00:00:00.900000	0	0	0	0	0	0	900000
+000000	00:00:00.000000	0	0	0	0	0	0	0
 Warnings:
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '9999-12-31 23:59:59.123456'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 10:20:30.123456'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND 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'
@@ -122,6 +126,7 @@ 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 DAY TO SECOND 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'
@@ -130,6 +135,7 @@ 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 DAY TO SECOND 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'
@@ -140,6 +146,7 @@ Warning	1292	Incorrect interval value: '87649416:59:59.123456'
 Warning	1292	Incorrect interval value: '87649416:59:59.123456'
 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),
@@ -148,25 +155,28 @@ 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
+b	cidm	dh	EXTRACT(DAY_HOUR FROM b)	EXTRACT(DAY FROM b)	EXTRACT(HOUR FROM b)	EXTRACT(MINUTE FROM b)	EXTRACT(SECOND FROM b)	EXTRACT(MICROSECOND FROM b)
+99991231235959.123456000	NULL	767	3123	31	23	59	59	123456
+20010101102030.123456000	NULL	34	110	1	10	20	30	123456
+42949672965959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+42949672955959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+876494165959.123456000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+876494155959.123456000	3652058 23:59:59.123456	87649415	365205823	3652058	23	59	59	123456
+876494145959.123456000	3652058 22:59:59.123456	87649414	365205822	3652058	22	59	59	123456
+99995959.123456000	416 15:59:59.123456	9999	41615	416	15	59	59	123456
+99990101.123456000	416 15:01:01.123456	9999	41615	416	15	1	1	123456
+99990101.000000000	416 15:01:01.000000	9999	41615	416	15	1	1	0
+0.999999000	00:00:00.999999	0	0	0	0	0	0	999999
+0.999990000	00:00:00.999990	0	0	0	0	0	0	999990
+0.999900000	00:00:00.999900	0	0	0	0	0	0	999900
+0.999000000	00:00:00.999000	0	0	0	0	0	0	999000
+0.990000000	00:00:00.990000	0	0	0	0	0	0	990000
+0.900000000	00:00:00.900000	0	0	0	0	0	0	900000
+0.000000000	00:00:00.000000	0	0	0	0	0	0	0
 Warnings:
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '99991231235959.123456000'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '20010101102030.123456000'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '42949672965959.123456000'
 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
@@ -175,6 +185,7 @@ Warning	1292	Incorrect interval value: '42949672965959.123456000' for column 'b'
 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 DAY TO SECOND value: '42949672955959.123456000'
 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
@@ -183,6 +194,7 @@ Warning	1292	Incorrect interval value: '42949672955959.123456000' for column 'b'
 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 DAY TO SECOND value: '876494165959.123456000'
 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
@@ -191,6 +203,18 @@ Warning	1292	Incorrect interval value: '876494165959.123456000' for column 'b' a
 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
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.123456000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.123456000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99995959.123456000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.123456000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999990000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999900000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.999000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.990000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.900000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000'
 DROP TABLE t1;
 # Special case: DAY + TIME
 CREATE TABLE t1 (a VARCHAR(64));
@@ -213,6 +237,7 @@ DROP TABLE t1;
 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),
@@ -223,9 +248,10 @@ 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
+a	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)
+	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 Warnings:
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: ''
 Warning	1292	Incorrect interval value: ''
 Warning	1292	Incorrect interval value: ''
 Warning	1292	Incorrect interval value: ''
@@ -523,33 +549,44 @@ INSERT INTO t1 VALUES
 ('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
+SELECT
+EXTRACT(DAY FROM a),
+EXTRACT(DAY_SECOND FROM a), a,
+CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm
+FROM t1;
+EXTRACT(DAY FROM a)	EXTRACT(DAY_SECOND FROM a)	a	cidm
+84	84080103	2024:01:03 garbage /////	NULL
+1	1000103	24:01:03 garbage /////	NULL
+0	10103	01:01:03 garbage /////	NULL
+84	84080203	2024:02:03	84 08:02:03.000000
+4166	4166160203	100000:02:03	4166 16:02:03.000000
+1	1000203	24:02:03	1 00:02:03.000000
+0	10203	01:02:03	01:02:03.000000
+0	10203	01:02:03:	01:02:03.000000
+0	10203	01:02:03-	NULL
+0	10203	01:02:03;	01:02:03.000000
+0	10203	01:02:03/	01:02:03.000000
+20	20102030	20 10:20:30	20 10:20:30.000000
 Warnings:
 Warning	1292	Truncated incorrect time value: '2024:01:03 garbage /////'
 Warning	1292	Truncated incorrect time value: '2024:01:03 garbage /////'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND 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	Incorrect INTERVAL DAY TO SECOND 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	Incorrect INTERVAL DAY TO SECOND 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 INTERVAL DAY TO SECOND value: '01:02:03:'
 Warning	1292	Truncated incorrect time value: '01:02:03-'
 Warning	1292	Truncated incorrect time value: '01:02:03-'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND 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 INTERVAL DAY TO SECOND 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 INTERVAL DAY TO SECOND value: '01:02:03/'
 DROP TABLE t1;
diff --git a/mysql-test/main/func_extract.test b/mysql-test/main/func_extract.test
index f128b6509a2..edc99b9c00c 100644
--- a/mysql-test/main/func_extract.test
+++ b/mysql-test/main/func_extract.test
@@ -65,6 +65,7 @@ SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WH
 --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),
@@ -75,6 +76,7 @@ SELECT
 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),
@@ -105,6 +107,7 @@ DROP TABLE t1;
 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),
@@ -246,5 +249,9 @@ INSERT INTO t1 VALUES
 ('01:02:03/'),
 ('20 10:20:30');
 
-SELECT EXTRACT(DAY FROM a), EXTRACT(DAY_SECOND FROM a), a FROM t1;
+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;
diff --git a/mysql-test/main/type_interval.result b/mysql-test/main/type_interval.result
new file mode 100644
index 00000000000..70a5d78b214
--- /dev/null
+++ b/mysql-test/main/type_interval.result
@@ -0,0 +1,83 @@
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))
+#
+CREATE TABLE t1 (a VARCHAR(128));
+INSERT INTO t1 VALUES
+('00:00:00'),
+('+00:00:01'),
+('-00:00:01'),
+('838:59:59'),
+('839:00:00'),
+('2018:01:02'),
+('87649415:59:59'),
+('3652058 23:59:59'),
+('87649416:00:00'),
+('3652059 00:00:00');
+SELECT
+EXTRACT(DAY FROM a) AS d,
+EXTRACT(HOUR FROM a) AS h,
+a,
+CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
+FROM t1;
+d	h	a	cast_itds
+0	0	00:00:00	00:00:00.000000
+0	0	+00:00:01	00:00:01.000000
+0	0	-00:00:01	-00:00:01.000000
+34	22	838:59:59	34 22:59:59.000000
+34	23	839:00:00	34 23:00:00.000000
+84	2	2018:01:02	84 02:01:02.000000
+3652058	23	87649415:59:59	3652058 23:59:59.000000
+3652058	23	3652058 23:59:59	3652058 23:59:59.000000
+NULL	NULL	87649416:00:00	NULL
+NULL	NULL	3652059 00:00:00	NULL
+Warnings:
+Warning	1292	Incorrect interval value: '87649416:00:00'
+Warning	1292	Incorrect interval value: '87649416:00:00'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '87649416:00:00'
+Warning	1292	Incorrect interval value: '3652059 00:00:00'
+Warning	1292	Incorrect interval value: '3652059 00:00:00'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '3652059 00:00:00'
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(0),
+(1),
+(-1),
+(8385959),
+(8390000),
+(20180102),
+(876494155959),
+(876494160000);
+SELECT
+EXTRACT(DAY FROM a) AS d,
+EXTRACT(HOUR FROM a) AS h,
+a,
+CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
+FROM t1;
+d	h	a	cast_itds
+0	0	0.000000000	00:00:00.000000
+0	0	1.000000000	00:00:01.000000
+0	0	-1.000000000	-00:00:01.000000
+34	22	8385959.000000000	34 22:59:59.000000
+34	23	8390000.000000000	34 23:00:00.000000
+84	2	20180102.000000000	84 02:01:02.000000
+3652058	23	876494155959.000000000	3652058 23:59:59.000000
+NULL	NULL	876494160000.000000000	NULL
+Warnings:
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '1.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '-1.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8390000.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '20180102.000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.000000000'
+Warning	1292	Incorrect interval value: '876494160000.000000000' for column 'a' at row 8
+Warning	1292	Incorrect interval value: '876494160000.000000000' for column 'a' at row 8
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '876494160000.000000000'
+DROP TABLE t1;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/type_interval.test b/mysql-test/main/type_interval.test
new file mode 100644
index 00000000000..15999dc609d
--- /dev/null
+++ b/mysql-test/main/type_interval.test
@@ -0,0 +1,54 @@
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(128));
+INSERT INTO t1 VALUES
+('00:00:00'),
+('+00:00:01'),
+('-00:00:01'),
+('838:59:59'),
+('839:00:00'),
+('2018:01:02'),
+('87649415:59:59'),
+('3652058 23:59:59'),
+('87649416:00:00'),
+('3652059 00:00:00');
+
+SELECT
+  EXTRACT(DAY FROM a) AS d,
+  EXTRACT(HOUR FROM a) AS h,
+  a,
+  CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
+FROM t1;
+
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(0),
+(1),
+(-1),
+(8385959),
+(8390000),
+(20180102),
+(876494155959),
+(876494160000);
+
+SELECT
+  EXTRACT(DAY FROM a) AS d,
+  EXTRACT(HOUR FROM a) AS h,
+  a,
+  CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
+FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index cc4249074e0..d16970c0a5f 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -1465,6 +1465,46 @@ static char* fmt_number(uint val, char *out, uint digits)
 }
 
 
+static int my_mmssff_to_str(const MYSQL_TIME *ltime, char *to, uint fsp)
+{
+  char *pos= to;
+  if (fsp == AUTO_SEC_PART_DIGITS)
+    fsp= ltime->second_part ? TIME_SECOND_PART_DIGITS : 0;
+  DBUG_ASSERT(fsp <= TIME_SECOND_PART_DIGITS);
+  pos= fmt_number(ltime->minute, pos, 2);
+  *pos++= ':';
+  pos= fmt_number(ltime->second, pos, 2);
+  if (fsp)
+  {
+    *pos++= '.';
+    pos= fmt_number((uint)sec_part_shift(ltime->second_part, fsp), pos, fsp);
+  }
+  return (int) (pos - to);
+}
+
+
+int my_interval_DDhhmmssff_to_str(const MYSQL_TIME *ltime, char *to, uint fsp)
+{
+  uint hour= ltime->day * 24 + ltime->hour;
+  char *pos= to;
+  DBUG_ASSERT(!ltime->year);
+  DBUG_ASSERT(!ltime->month);
+
+  if(ltime->neg)
+    *pos++= '-';
+  if (hour >= 24)
+  {
+    pos= longlong10_to_str((longlong) hour / 24, pos, 10);
+    *pos++= ' ';
+  }
+  pos= fmt_number(hour % 24, pos, 2);
+  *pos++= ':';
+  pos+= my_mmssff_to_str(ltime, pos, fsp);
+  *pos= 0;
+  return (int) (pos-to);
+}
+
+
 /*
   Functions to convert time/date/datetime value to a string,
   using default format.
@@ -1482,11 +1522,6 @@ int my_time_to_str(const MYSQL_TIME *l_time, char *to, uint digits)
   uint hour=  day * 24 + l_time->hour;
   char*pos= to;
 
-  if (digits == AUTO_SEC_PART_DIGITS)
-    digits= l_time->second_part ? TIME_SECOND_PART_DIGITS : 0;
-
-  DBUG_ASSERT(digits <= TIME_SECOND_PART_DIGITS);
-
   if(l_time->neg)
     *pos++= '-';
 
@@ -1497,17 +1532,7 @@ int my_time_to_str(const MYSQL_TIME *l_time, char *to, uint digits)
     pos= fmt_number(hour, pos, 2);
 
   *pos++= ':';
-  pos= fmt_number(l_time->minute, pos, 2);
-  *pos++= ':';
-  pos= fmt_number(l_time->second, pos, 2);
-
-  if (digits)
-  {
-    *pos++= '.';
-    pos= fmt_number((uint)sec_part_shift(l_time->second_part, digits), 
-       pos, digits);
-  }
-
+  pos+= my_mmssff_to_str(l_time, pos, digits);
   *pos= 0;
   return (int) (pos-to);
 }
@@ -1529,12 +1554,6 @@ int my_date_to_str(const MYSQL_TIME *l_time, char *to)
 int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, uint digits)
 {
   char *pos= to;
-
-  if (digits == AUTO_SEC_PART_DIGITS)
-    digits= l_time->second_part ? TIME_SECOND_PART_DIGITS : 0;
-
-  DBUG_ASSERT(digits <= TIME_SECOND_PART_DIGITS);
-
   pos= fmt_number(l_time->year, pos, 4);
   *pos++='-';
   pos= fmt_number(l_time->month, pos, 2);
@@ -1543,17 +1562,7 @@ int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, uint digits)
   *pos++=' ';
   pos= fmt_number(l_time->hour, pos, 2);
   *pos++= ':';
-  pos= fmt_number(l_time->minute, pos, 2);
-  *pos++= ':';
-  pos= fmt_number(l_time->second, pos, 2);
-
-  if (digits)
-  {
-    *pos++='.';
-    pos= fmt_number((uint) sec_part_shift(l_time->second_part, digits), pos, 
-      digits);
-  }
-
+  pos+= my_mmssff_to_str(l_time, pos, digits);
   *pos= 0;
   return (int)(pos - to);
 }
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 45f10659a76..318bef22ad1 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -1106,6 +1106,24 @@ public:
 };
 
 
+class Item_interval_DDhhmmssff_typecast :public Item_char_typecast
+{
+  uint m_fsp;
+public:
+  Item_interval_DDhhmmssff_typecast(THD *thd, Item *a, uint fsp)
+   :Item_char_typecast(thd, a,Interval_DDhhmmssff::max_char_length(fsp),
+                       &my_charset_latin1),
+    m_fsp(fsp)
+  { }
+  String *val_str(String *to)
+  {
+    Interval_DDhhmmssff it(current_thd, args[0]);
+    null_value= !it.is_valid_interval_DDhhmmssff();
+    return it.to_string(to, m_fsp);
+  }
+};
+
+
 class Item_date_typecast :public Item_datefunc
 {
 public:
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 31b25853514..278ef608220 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -68,6 +68,8 @@ Type_handler_long_blob   type_handler_long_blob;
 Type_handler_blob        type_handler_blob;
 static Type_handler_blob_compressed type_handler_blob_compressed;
 
+Type_handler_interval_DDhhmmssff type_handler_interval_DDhhmmssff;
+
 #ifdef HAVE_SPATIAL
 Type_handler_geometry    type_handler_geometry;
 #endif
@@ -6568,6 +6570,20 @@ Item *Type_handler_long_blob::
   return new (thd->mem_root) Item_char_typecast(thd, item, len, real_cs);
 }
 
+Item *Type_handler_interval_DDhhmmssff::
+        create_typecast_item(THD *thd, Item *item,
+                             const Type_cast_attributes &attr) const
+{
+  if (attr.decimals() > MAX_DATETIME_PRECISION)
+  {
+    wrong_precision_error(ER_TOO_BIG_PRECISION, item, attr.decimals(),
+                          MAX_DATETIME_PRECISION);
+    return 0;
+  }
+  return new (thd->mem_root) Item_interval_DDhhmmssff_typecast(thd, item,
+                                                               attr.decimals());
+}
+
 /***************************************************************************/
 
 void Type_handler_string_result::Item_param_setup_conversion(THD *thd,
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 3aeb2ad2788..e8af11764f5 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -954,6 +954,16 @@ public:
   {
     return TIME_MAX_INTERVAL_HOUR;
   }
+  static uint max_int_part_char_length()
+  {
+    // e.g. '+3652058 23:59:59'
+    return 1/*sign*/ + TIME_MAX_INTERVAL_DAY_CHAR_LENGTH + 1 + 8/*hh:mm:ss*/;
+  }
+  static uint max_char_length(uint fsp)
+  {
+    DBUG_ASSERT(fsp <= TIME_SECOND_PART_DIGITS);
+    return max_int_part_char_length() + (fsp ? 1 : 0) + fsp;
+  }
 public:
   Interval_DDhhmmssff(THD *thd, Status *st, bool push_warnings,
                       Item *item, ulong max_hour);
@@ -975,6 +985,17 @@ public:
   {
     return time_type == MYSQL_TIMESTAMP_NONE || is_valid_interval_DDhhmmssff();
   }
+  String *to_string(String *str, uint dec) const
+  {
+    if (!is_valid_interval_DDhhmmssff())
+      return NULL;
+    str->set_charset(&my_charset_numeric);
+    if (!str->alloc(MAX_DATE_STRING_REP_LENGTH))
+      str->length(my_interval_DDhhmmssff_to_str(this,
+                                                const_cast<char*>(str->ptr()),
+                                                dec));
+    return str;
+  }
 };
 
 
@@ -5311,6 +5332,16 @@ public:
 };
 
 
+// A pseudo type handler, mostly for test purposes for now
+class Type_handler_interval_DDhhmmssff: public Type_handler_long_blob
+{
+public:
+  Item *create_typecast_item(THD *thd, Item *item,
+                             const Type_cast_attributes &attr) const;
+};
+
+
+
 /**
   A handler for hybrid type functions, e.g.
   COALESCE(), IF(), IFNULL(), NULLIF(), CASE,
@@ -5443,6 +5474,9 @@ extern MYSQL_PLUGIN_IMPORT Type_handler_blob        type_handler_blob;
 extern MYSQL_PLUGIN_IMPORT Type_handler_medium_blob type_handler_medium_blob;
 extern MYSQL_PLUGIN_IMPORT Type_handler_long_blob   type_handler_long_blob;
 
+extern MYSQL_PLUGIN_IMPORT Type_handler_interval_DDhhmmssff
+  type_handler_interval_DDhhmmssff;
+
 class Type_aggregator
 {
   bool m_is_commutative;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 350bc7a351c..b2f1131f594 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11703,6 +11703,10 @@ cast_type_temporal:
           DATE_SYM                       { $$.set(&type_handler_newdate); }
         | TIME_SYM opt_field_length      { $$.set(&type_handler_time2, 0, $2); }
         | DATETIME opt_field_length      { $$.set(&type_handler_datetime2, 0, $2); }
+        | INTERVAL_SYM DAY_SECOND_SYM field_length
+          {
+            $$.set(&type_handler_interval_DDhhmmssff, 0, $3);
+          }
         ;
 
 opt_expr_list:
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index c3fd7a78824..e17e959d1e5 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -11737,6 +11737,10 @@ cast_type_temporal:
           DATE_SYM                       { $$.set(&type_handler_newdate); }
         | TIME_SYM opt_field_length      { $$.set(&type_handler_time2, 0, $2); }
         | DATETIME opt_field_length      { $$.set(&type_handler_datetime2, 0, $2); }
+        | INTERVAL_SYM DAY_SECOND_SYM field_length
+          {
+            $$.set(&type_handler_interval_DDhhmmssff, 0, $3);
+          }
         ;
 
 opt_expr_list: