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
148
149
150
151
152
153
154
155
156
157
|
SET DEFAULT_STORAGE_ENGINE = 'tokudb';
DROP TABLE IF EXISTS foo;
set session tokudb_prelock_empty=0;
create table foo (a int, b int, c int, d int, primary key (a), key (b), key (c) clustering=yes);
insert into foo values (1,100,1000,1),(2,20,200,2),(3,300,30,3),(4,4,4,4);
select * from foo;
a b c d
4 4 4 4
3 300 30 3
2 20 200 2
1 100 1000 1
explain select b,a from foo;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE foo index NULL b 5 NULL 4 Using index
select b,a from foo;
b a
4 4
20 2
100 1
300 3
select * from foo where c > 0;
a b c d
4 4 4 4
3 300 30 3
2 20 200 2
1 100 1000 1
#
# only val of primary dictionary and clustering dictionary changes
#
update foo set d=d+100;
select * from foo;
a b c d
4 4 4 104
3 300 30 103
2 20 200 102
1 100 1000 101
explain select b,a from foo;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE foo index NULL b 5 NULL 4 Using index
select b,a from foo;
b a
4 4
20 2
100 1
300 3
select * from foo where c > 0;
a b c d
4 4 4 104
3 300 30 103
2 20 200 102
1 100 1000 101
#
# secondary key changes
#
update foo set b=b+1;
select * from foo;
a b c d
4 5 4 104
3 301 30 103
2 21 200 102
1 101 1000 101
explain select b,a from foo;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE foo index NULL b 5 NULL 4 Using index
select b,a from foo;
b a
5 4
21 2
101 1
301 3
select * from foo where c > 0;
a b c d
4 5 4 104
3 301 30 103
2 21 200 102
1 101 1000 101
#
# clustering key changes
#
update foo set c=c*10;
select * from foo;
a b c d
4 5 40 104
3 301 300 103
2 21 2000 102
1 101 10000 101
explain select b,a from foo;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE foo index NULL b 5 NULL 4 Using index
select b,a from foo;
b a
5 4
21 2
101 1
301 3
select * from foo where c > 0;
a b c d
4 5 40 104
3 301 300 103
2 21 2000 102
1 101 10000 101
drop table foo;
#
# test updates on single dictionary
# Two cases: pk changes, pk does not change
#
create table foo (a int, b int, primary key (a));
insert into foo values (1,10),(2,20),(3,30);
select * from foo;
a b
1 10
2 20
3 30
update foo set b=b*10;
select * from foo;
a b
1 100
2 200
3 300
update foo set a=a+10;
select * From foo;
a b
11 100
12 200
13 300
drop table foo;
#
# test pk uniqueness check during updates
# Two cases: have one dict, have more than one dict
#
create table foo (a int, b int, c int, primary key (a));
insert into foo values (1,10,100),(2,20,200),(3,30,300);
update foo set a=3 where a=1;
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
select * from foo;
a b c
1 10 100
2 20 200
3 30 300
alter table foo add key (c) clustering=yes;
update foo set a=3 where a=1;
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
drop table foo;
#
# test secondary key uniqueness
#
create table foo (a int, b int, c int, primary key (a), unique key (b));
insert into foo values (1,10,100),(2,20,200),(3,30,300);
update foo set b=20 where b=10;
ERROR 23000: Duplicate entry '20' for key 'b'
update foo set c=c*100;
select * from foo;
a b c
1 10 10000
2 20 20000
3 30 30000
DROP TABLE foo;
|
