2
# testing of the TIME column type
6
DROP TABLE if exists t1;
9
CREATE TABLE t1 (t time);
10
INSERT INTO t1 VALUES ("10:22:33");
11
INSERT INTO t1 VALUES ("12:34:56.78");
13
INSERT INTO t1 VALUES (10);
15
INSERT INTO t1 VALUES (1234);
17
INSERT INTO t1 VALUES (123456.78);
19
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
20
INSERT INTO t1 VALUES (1234559.99);
22
INSERT INTO t1 VALUES ("1");
24
INSERT INTO t1 VALUES ("1:23");
26
INSERT INTO t1 VALUES ("1:23:45");
28
INSERT INTO t1 VALUES ("10.22");
30
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
31
INSERT INTO t1 VALUES ("20 10:22:33");
33
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
34
INSERT INTO t1 VALUES ("-10 1:22:33.45");
36
INSERT INTO t1 VALUES ("1999-02-03 20:33:34");
38
insert t1 values (30);
39
insert t1 values (1230);
40
insert t1 values ("1230");
41
insert t1 values ("12:30");
42
insert t1 values ("12:30:35");
43
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
44
insert t1 values ("1 12:30:31.32");
48
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
49
INSERT INTO t1 VALUES ("10.22.22");
51
INSERT INTO t1 VALUES (1234567);
53
INSERT INTO t1 VALUES (123456789);
55
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
56
INSERT INTO t1 VALUES (123456789.10);
58
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
59
INSERT INTO t1 VALUES ("10 22:22");
61
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
62
INSERT INTO t1 VALUES ("12.45a");
66
CREATE TABLE t1 (t time);
67
INSERT INTO t1 VALUES ('09:00:00');
68
INSERT INTO t1 VALUES ('13:00:00');
69
INSERT INTO t1 VALUES ('19:38:34');
70
INSERT INTO t1 VALUES ('13:00:00');
71
INSERT INTO t1 VALUES ('09:00:00');
72
INSERT INTO t1 VALUES ('09:00:00');
73
INSERT INTO t1 VALUES ('13:00:00');
74
INSERT INTO t1 VALUES ('13:00:00');
75
INSERT INTO t1 VALUES ('13:00:00');
76
INSERT INTO t1 VALUES ('09:00:00');
78
--error ER_SP_DOES_NOT_EXIST
79
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
81
--error ER_SP_DOES_NOT_EXIST
82
select sec_to_time(time_to_sec(t)) from t1;
87
# BUG #12440: Incorrect processing of time values containing
88
# long fraction part and/or large exponent part.
90
# These must return normal result:
91
# ##########################################################
92
# To be uncommented after fix BUG #15805
93
# ##########################################################
94
# SELECT CAST(235959.123456 AS TIME);
95
# SELECT CAST(0.235959123456e+6 AS TIME);
96
# SELECT CAST(235959123456e-6 AS TIME);
97
# These must cut fraction part and produce warning:
98
# SELECT CAST(235959.1234567 AS TIME);
99
# SELECT CAST(0.2359591234567e6 AS TIME);
100
# This must return NULL and produce warning:
101
# SELECT CAST(0.2359591234567e+30 AS TIME);
102
# ##########################################################
107
# Bug#29555: Comparing time values as strings may lead to a wrong result.
109
SELECT CAST('100:55:50' as time) < cast('24:00:00' as time);
111
SELECT CAST('100:55:50' as time) < cast('024:00:00' as time);
113
SELECT CAST('300:55:50' as time) < cast('240:00:00' as time);
115
SELECT CAST('100:55:50' as time) > cast('24:00:00' as time);
117
SELECT CAST('100:55:50' as time) > cast('024:00:00' as time);
119
SELECT CAST('300:55:50' as time) > cast('240:00:00' as time);
121
CREATE TABLE t1 (f1 time);
122
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
123
INSERT INTO t1 VALUES ('24:00:00');
125
SELECT CAST('24:00:00' as time) = (select f1 from t1);
130
# Bug#29739: Incorrect time comparison in BETWEEN.
132
CREATE TABLE t1(f1 time, f2 time);
133
--error ER_INVALID_UNIX_TIMESTAMP_VALUE
134
INSERT INTO t1 VALUES('20:00:00','150:00:00');
136
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
141
# Bug#29729: Wrong conversion error led to an empty result set.
145
f3 int NOT NULL default '0',
148
INSERT INTO t1 VALUES ('2007-07-01', 1);
149
INSERT INTO t1 VALUES ('2007-07-01', 2);
150
INSERT INTO t1 VALUES ('2007-07-02', 1);
151
INSERT INTO t1 VALUES ('2007-07-02', 2);
152
SELECT sum(f3) FROM t1 WHERE f2='2007-07-01 00:00:00' group by f2;