1 2--source include/have_innodb.inc 3 4SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30'); 5 6--disable_warnings 7drop table if exists t1, t2, t3; 8--enable_warnings 9 10--error ER_TOO_BIG_PRECISION 11eval create table t1 (a $type(7)); 12 13eval create table t1 (a $type(3), key(a)); 14insert t1 values ('2010-12-11 00:20:03.1234'); 15insert t1 values ('2010-12-11 15:47:11.1234'); 16insert t1 values (20101211010203.45678); 17insert t1 values (20101211030405.789e0); 18insert ignore t1 values (99991231235959e1); 19select * from t1; 20--replace_regex /121000/121094/ /457000/457031/ /789000/789062/ 21select cast(a AS double(30,6)) from t1; # Field::val_real() 22select a DIV 1 from t1; # Field::val_int() 23select group_concat(distinct a) from t1; # Field::cmp() 24alter table t1 engine=innodb; 25select * from t1 order by a; 26select * from t1 order by a+0; 27drop table t1; 28let attr=; 29if ($type == timestamp) 30{ 31 let attr=NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4); 32} 33eval create table t1 (a $type(4)$attr) engine=innodb; 34insert t1 values ('2010-12-11 01:02:03.456789'); 35select * from t1; 36select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; 37select a from t1 where a>'2010-11-12 01:02:03.456' group by a; 38 39# 40# metadata 41# 42show create table t1; 43show columns from t1; 44--query_vertical select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1' 45 46# 47# update/delete 48# 49select a, a+interval 9876543 microsecond from t1; 50update t1 set a=a+interval 9876543 microsecond; 51select * from t1; 52select a, a + interval 2 year from t1; 53insert ignore t1 select a + interval 2 year from t1; 54select * from t1; 55delete from t1 where a < 20110101; 56select * from t1; 57 58if ($type == time) 59{ 60delete from t1 where a is not null; 61select * from t1; 62} 63 64# 65# create ... select 66# 67create table t2 select * from t1; 68create table t3 like t1; 69 70show create table t2; 71show create table t3; 72drop table t2, t3; 73 74# math, aggregation 75insert t1 values ('2010-12-13 14:15:16.222222'); 76select a, a+0, a-1, a*1, a/2 from t1; 77select max(a), min(a), sum(a), avg(a) from t1; 78create table t2 select a, a+0, a-1, a*1, a/2 from t1; 79create table t3 select max(a), min(a), sum(a), avg(a) from t1; 80show create table t2; 81show create table t3; 82 83drop table t1, t2, t3; 84 85# insert, alter with conversion 86--vertical_results 87eval create table t1 (f0_$type $type(0), f1_$type $type(1), f2_$type $type(2), f3_$type $type(3), f4_$type $type(4), f5_$type $type(5), f6_$type $type(6)); 88insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); 89select * from t1; 90eval select cast(f0_$type as time(4)) time4_f0_$type, cast(f1_$type as datetime(3)) datetime3_f1_$type, cast(f2_$type as date) date_f2_$type, cast(f4_$type as double) double_f3_$type, cast(f4_$type as decimal(40,5)) decimal5_f4_$type, cast(f5_$type as signed) bigint_f5_$type, cast(f6_$type as char(255)) varchar_f6_$type from t1; 91eval create table t2 (time4_f0_$type time(4), datetime3_f1_$type datetime(3), date_f2_$type date, double_f3_$type double, decimal5_f4_$type decimal(40,5), bigint_f5_$type bigint, varchar_f6_$type varchar(255)); 92insert t2 select * from t1; 93select * from t2; 94eval alter table t1 change f0_$type time4_f0_$type time(4), change f1_$type datetime3_f1_$type datetime(3), change f2_$type date_f2_$type date, change f3_$type double_f3_$type double, change f4_$type decimal5_f4_$type decimal(40,5), change f5_$type bigint_f5_$type bigint, change f6_$type varchar_f6_$type varchar(255); 95select * from t1; 96eval alter table t1 modify time4_f0_$type $type(0), modify datetime3_f1_$type $type(1), modify date_f2_$type $type(2), modify double_f3_$type $type(3), modify decimal5_f4_$type $type(4), modify bigint_f5_$type $type(5), modify varchar_f6_$type $type(6); 97select * from t1; 98delete from t1; 99insert t1 select * from t2; 100select * from t1; 101drop table t1, t2; 102--horizontal_results 103 104# 105# SP 106# 107let attr=; 108if ($type == timestamp) 109{ 110 let attr=NOT NULL DEFAULT '0000-00-00 00:00:00.000000'; 111} 112eval create table t1 (a $type(6)$attr, b $type(6)$attr); 113eval create procedure foo(x $type, y $type(4)) insert into t1 values (x, y); 114call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); 115select * from t1; 116delimiter |; 117eval create procedure bar(a int, c $type(5)) 118begin 119 declare b $type(4); 120 set b = c + interval a microsecond; 121 insert t1 values (b, c + interval a microsecond); 122end| 123delimiter ;| 124call bar(1111111, '2011-01-02 3:4:5.123456'); 125select * from t1; 126drop procedure foo; 127drop procedure bar; 128eval create function xyz(s char(20)) returns $type(4) 129 return addtime('2010-10-10 10:10:10.101010', s); 130select xyz('1:1:1.010101'); 131drop function xyz; 132 133# 134# Views 135# 136 137create view v1 as select * from t1 group by a,b; 138select * from v1; 139show columns from v1; 140create table t2 select * from v1; 141show create table t2; 142select * from t2; 143 144drop view v1; 145drop table t1, t2; 146 147SET timestamp=DEFAULT; 148