1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
#
# testing of the TIME column type
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (t time);
insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34");
insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
select * from t1;
# Test wrong values
insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
select * from t1;
drop table t1;
create table t1 (t time);
insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00');
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
select sec_to_time(time_to_sec(t)) from t1;
drop table t1;
#
# BUG #12440: Incorrect processing of time values containing
# long fraction part and/or large exponent part.
#
# These must return normal result:
# ##########################################################
# To be uncommented after fix BUG #15805
# ##########################################################
# SELECT CAST(235959.123456 AS TIME);
# SELECT CAST(0.235959123456e+6 AS TIME);
# SELECT CAST(235959123456e-6 AS TIME);
# These must cut fraction part and produce warning:
# SELECT CAST(235959.1234567 AS TIME);
# SELECT CAST(0.2359591234567e6 AS TIME);
# This must return NULL and produce warning:
# SELECT CAST(0.2359591234567e+30 AS TIME);
# ##########################################################
--echo End of 4.1 tests
#
# Bug#29555: Comparing time values as strings may lead to a wrong result.
#
select cast('100:55:50' as time) < cast('24:00:00' as time);
select cast('100:55:50' as time) < cast('024:00:00' as time);
select cast('300:55:50' as time) < cast('240:00:00' as time);
select cast('100:55:50' as time) > cast('24:00:00' as time);
select cast('100:55:50' as time) > cast('024:00:00' as time);
select cast('300:55:50' as time) > cast('240:00:00' as time);
create table t1 (f1 time);
insert into t1 values ('24:00:00');
select cast('24:00:00' as time) = (select f1 from t1);
drop table t1;
#
# Bug#29739: Incorrect time comparison in BETWEEN.
#
create table t1(f1 time, f2 time);
insert into t1 values('20:00:00','150:00:00');
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
drop table t1;
#
# Bug#29729: Wrong conversion error led to an empty result set.
#
CREATE TABLE t1 (
f2 date NOT NULL,
f3 int(11) unsigned NOT NULL default '0',
PRIMARY KEY (f3, f2)
);
insert into t1 values('2007-07-01', 1);
insert into t1 values('2007-07-01', 2);
insert into t1 values('2007-07-02', 1);
insert into t1 values('2007-07-02', 2);
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
drop table t1;
--echo #
--echo # Bug #44792: valgrind warning when casting from time to time
--echo #
CREATE TABLE t1 (c TIME);
INSERT INTO t1 VALUES ('0:00:00');
SELECT CAST(c AS TIME) FROM t1;
DROP TABLE t1;
--echo End of 5.0 tests
--echo #
--echo # Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
--echo #
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
DROP TABLE t1;
--echo End of 5.1 tests
#
# Bug#42664 - Sign ignored for TIME types when not comparing as longlong
#
CREATE TABLE t1 (f1 TIME);
INSERT INTO t1 VALUES ('24:00:00');
SELECT '24:00:00' = (SELECT f1 FROM t1);
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
TRUNCATE t1;
INSERT INTO t1 VALUES ('-24:00:00');
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT '-24:00:00' = (SELECT f1 FROM t1);
DROP TABLE t1;
--echo End of 6.0 tests
|
