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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
--source include/have_innodb.inc
SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
--error ER_TOO_BIG_PRECISION
eval create table t1 (a $type(7));
eval create table t1 (a $type(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
insert t1 values ('2010-12-11 15:47:11.1234');
insert t1 values (20101211010203.45678);
insert t1 values (20101211030405.789e0);
insert ignore t1 values (99991231235959e1);
select * from t1;
--replace_regex /121000/121094/ /457000/457031/ /789000/789062/
select cast(a AS double(30,6)) from t1; # Field::val_real()
select a DIV 1 from t1; # Field::val_int()
select group_concat(distinct a) from t1; # Field::cmp()
alter table t1 engine=innodb;
select * from t1 order by a;
select * from t1 order by a+0;
drop table t1;
let attr=;
if ($type == timestamp)
{
let attr=NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4);
}
eval create table t1 (a $type(4)$attr) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
select * from t1;
select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
#
# metadata
#
show create table t1;
show columns from t1;
--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'
#
# update/delete
#
select a, a+interval 9876543 microsecond from t1;
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
select a, a + interval 2 year from t1;
insert ignore t1 select a + interval 2 year from t1;
select * from t1;
delete from t1 where a < 20110101;
select * from t1;
if ($type == time)
{
delete from t1 where a is not null;
select * from t1;
}
#
# create ... select
#
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
show create table t3;
drop table t2, t3;
# math, aggregation
insert t1 values ('2010-12-13 14:15:16.222222');
select a, a+0, a-1, a*1, a/2 from t1;
select max(a), min(a), sum(a), avg(a) from t1;
create table t2 select a, a+0, a-1, a*1, a/2 from t1;
create table t3 select max(a), min(a), sum(a), avg(a) from t1;
show create table t2;
show create table t3;
drop table t1, t2, t3;
# insert, alter with conversion
--vertical_results
eval 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));
insert 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');
select * from t1;
eval 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;
eval 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));
insert t2 select * from t1;
select * from t2;
eval 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);
select * from t1;
eval 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);
select * from t1;
delete from t1;
insert t1 select * from t2;
select * from t1;
drop table t1, t2;
--horizontal_results
#
# SP
#
let attr=;
if ($type == timestamp)
{
let attr=NOT NULL DEFAULT '0000-00-00 00:00:00.000000';
}
eval create table t1 (a $type(6)$attr, b $type(6)$attr);
eval create procedure foo(x $type, y $type(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
select * from t1;
delimiter |;
eval create procedure bar(a int, c $type(5))
begin
declare b $type(4);
set b = c + interval a microsecond;
insert t1 values (b, c + interval a microsecond);
end|
delimiter ;|
call bar(1111111, '2011-01-02 3:4:5.123456');
select * from t1;
drop procedure foo;
drop procedure bar;
eval create function xyz(s char(20)) returns $type(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
drop function xyz;
#
# Views
#
create view v1 as select * from t1 group by a,b;
select * from v1;
show columns from v1;
create table t2 select * from v1;
show create table t2;
select * from t2;
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
|
