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