1SET DEFAULT_STORAGE_ENGINE='tokudb';
2#
3# testing of the TIME column type
4#
5
6--disable_warnings
7drop table if exists t1;
8--enable_warnings
9
10create table t1 (t time);
11insert 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");
12insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
13select * from t1;
14# Test wrong values
15insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
16select * from t1;
17drop table t1;
18
19create table t1 (t time);
20insert 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');
21select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
22select sec_to_time(time_to_sec(t)) from t1;
23drop table t1;
24
25#
26# BUG #12440: Incorrect processing of time values containing
27# long fraction part and/or large exponent part.
28#
29# These must return normal result:
30# ##########################################################
31# To be uncommented after fix BUG #15805
32# ##########################################################
33# SELECT CAST(235959.123456 AS TIME);
34# SELECT CAST(0.235959123456e+6 AS TIME);
35# SELECT CAST(235959123456e-6 AS TIME);
36# These must cut fraction part and produce warning:
37# SELECT CAST(235959.1234567 AS TIME);
38# SELECT CAST(0.2359591234567e6 AS TIME);
39# This must return NULL and produce warning:
40# SELECT CAST(0.2359591234567e+30 AS TIME);
41# ##########################################################
42
43# End of 4.1 tests
44
45#
46# Bug#29555: Comparing time values as strings may lead to a wrong result.
47#
48select cast('100:55:50' as time) < cast('24:00:00' as time);
49select cast('100:55:50' as time) < cast('024:00:00' as time);
50select cast('300:55:50' as time) < cast('240:00:00' as time);
51select cast('100:55:50' as time) > cast('24:00:00' as time);
52select cast('100:55:50' as time) > cast('024:00:00' as time);
53select cast('300:55:50' as time) > cast('240:00:00' as time);
54create table t1 (f1 time);
55insert into t1 values ('24:00:00');
56select cast('24:00:00' as time) = (select f1 from t1);
57drop table t1;
58
59#
60# Bug#29739: Incorrect time comparison in BETWEEN.
61#
62create table t1(f1 time, f2 time);
63insert into t1 values('20:00:00','150:00:00');
64select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
65drop table t1;
66
67#
68# Bug#29729: Wrong conversion error led to an empty result set.
69#
70CREATE TABLE  t1 (
71  f2 date NOT NULL,
72  f3 int(11) unsigned NOT NULL default '0',
73  PRIMARY KEY  (f3, f2)
74);
75insert into t1 values('2007-07-01', 1);
76insert into t1 values('2007-07-01', 2);
77insert into t1 values('2007-07-02', 1);
78insert into t1 values('2007-07-02', 2);
79SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
80drop table t1;
81