1#
2# Start of 10.4 tests
3#
4#
5# MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))
6#
7CREATE TABLE t1 (a VARCHAR(128));
8INSERT INTO t1 VALUES
9('00:00:00'),
10('+00:00:01'),
11('-00:00:01'),
12('838:59:59'),
13('839:00:00'),
14('2018:01:02'),
15('87649415:59:59'),
16('3652058 23:59:59'),
17('87649416:00:00'),
18('3652059 00:00:00');
19SELECT
20EXTRACT(DAY FROM a) AS d,
21EXTRACT(HOUR FROM a) AS h,
22a,
23CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
24FROM t1;
25d	h	a	cast_itds
260	0	00:00:00	00:00:00.000000
270	0	+00:00:01	00:00:01.000000
280	0	-00:00:01	-00:00:01.000000
2934	22	838:59:59	34 22:59:59.000000
3034	23	839:00:00	34 23:00:00.000000
3184	2	2018:01:02	84 02:01:02.000000
323652058	23	87649415:59:59	3652058 23:59:59.000000
333652058	23	3652058 23:59:59	3652058 23:59:59.000000
34NULL	NULL	87649416:00:00	NULL
35NULL	NULL	3652059 00:00:00	NULL
36Warnings:
37Warning	1292	Incorrect interval value: '87649416:00:00'
38Warning	1292	Incorrect interval value: '87649416:00:00'
39Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '87649416:00:00'
40Warning	1292	Incorrect interval value: '3652059 00:00:00'
41Warning	1292	Incorrect interval value: '3652059 00:00:00'
42Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '3652059 00:00:00'
43DROP TABLE t1;
44CREATE TABLE t1 (a DECIMAL(32,9));
45INSERT INTO t1 VALUES
46(0),
47(1),
48(-1),
49(8385959),
50(8390000),
51(20180102),
52(876494155959),
53(876494160000);
54SELECT
55EXTRACT(DAY FROM a) AS d,
56EXTRACT(HOUR FROM a) AS h,
57a,
58CAST(a AS INTERVAL DAY_SECOND(6)) AS cast_itds
59FROM t1;
60d	h	a	cast_itds
610	0	0.000000000	00:00:00.000000
620	0	1.000000000	00:00:01.000000
630	0	-1.000000000	-00:00:01.000000
6434	22	8385959.000000000	34 22:59:59.000000
6534	23	8390000.000000000	34 23:00:00.000000
6684	2	20180102.000000000	84 02:01:02.000000
673652058	23	876494155959.000000000	3652058 23:59:59.000000
68NULL	NULL	876494160000.000000000	NULL
69Warnings:
70Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000'
71Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '1.000000000'
72Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '-1.000000000'
73Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.000000000'
74Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8390000.000000000'
75Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '20180102.000000000'
76Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.000000000'
77Warning	1292	Incorrect interval value: '876494160000.000000000' for column `test`.`t1`.`a` at row 8
78Warning	1292	Incorrect interval value: '876494160000.000000000' for column `test`.`t1`.`a` at row 8
79Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '876494160000.000000000'
80DROP TABLE t1;
81#
82# End of 10.4 tests
83#
84