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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
|
drop table if exists t1,t2;
set session debug= "+d,myrocks_enable_unknown_collation_index_only_scans";
#
# Issue 257: Sort order for varchars is different between
# MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk hex(pk) col1
a 61 a
b 622020 b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk hex(pk) col1
a 6109 a-tab
a 612009 a-space-tab
a 61 a
b 622020 b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk col1
a a-tab
a a-space-tab
a a
b b-2x-space
c c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a', 'a');
insert into t1 values (3, 'a \t', 'a-tab');
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL col1 195 NULL # Using index
select col1, hex(col1) from t1;
col1 hex(col1)
a 61202009
a 6120
a 61
ab 6162
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 where col1 < 'b';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 # col1 col1 195 NULL # Using where; Using index
select col1, hex(col1) from t1 where col1 < 'b';
col1 hex(col1)
a 61202009
a 6120
a 61
ab 6162
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 # NULL col1 195 NULL # Using index
select pk, col1, hex(col1), length(col1) from t1;
pk col1 hex(col1) length(col1)
10 0
11 2020202020202020 8
12 20202020202020202020202020202020 16
13 202020202020202020202020202020202020202020202020 24
21 202020202020202020 9
22 2020202020202020202020202020202020 17
23 202020202020202020202020202020202020 18
14 a 2020202020202020202020202020202061 17
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk length(a)
1 301
2 301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk length(a) rtrim(a)
1 301 a
2 301 b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk length(a) rtrim(a)
1 301 a
2 301 b
drop table t1;
set session debug= "-d,myrocks_enable_unknown_collation_index_only_scans";
#
# Check backwards compatibility:
#
set session debug= '+d,MYROCKS_FORMAT_VERSION_INITIAL';
# Create the tables in the old format
create table t1 (
pk varchar(64) collate latin1_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
# The following will not produce an error:
insert into t1 values ('a ', 'a-space');
select pk, hex(pk), col1 from t1;
pk hex(pk) col1
a 61 a
a 6120 a-space
create table t2 (
pk int not null primary key,
col1 varchar(64) collate latin1_bin,
col2 varchar(64),
unique key (col1)
);
insert into t2 values (0, 'ab', 'a-b');
# The following will not produce an error:
insert into t2 values (1, 'a ', 'a-space');
insert into t2 values (2, 'a', 'a');
select pk, col1, hex(col1), col2 from t2;
pk col1 hex(col1) col2
0 ab 6162 a-b
1 a 6120 a-space
2 a 61 a
# Check the format version:
select table_name,index_name,kv_format_version
from information_schema.ROCKSDB_DDL
where TABLE_SCHEMA=database() AND table_name in ('t1','t2');
table_name index_name kv_format_version
t1 PRIMARY 10
t2 PRIMARY 10
t2 col1 10
flush tables;
set session debug= '-d,MYROCKS_FORMAT_VERSION_INITIAL';
select pk, hex(pk), col1 from t1;
pk hex(pk) col1
a 61 a
a 6120 a-space
select pk, col1, hex(col1), col2 from t2;
pk col1 hex(col1) col2
0 ab 6162 a-b
1 a 6120 a-space
2 a 61 a
select pk, hex(pk), col1 from t1;
pk hex(pk) col1
a 61 a
a 6120 a-space
select pk, col1, hex(col1), col2 from t2;
pk col1 hex(col1) col2
0 ab 6162 a-b
1 a 6120 a-space
2 a 61 a
drop table t1,t2;
#
# General upgrade tests to see that they work.
#
set session debug= '+d,MYROCKS_FORMAT_VERSION_INITIAL';
create table t2 (
id int primary key,
col1 varchar(64) collate latin1_swedish_ci,
unique key (col1)
) engine=rocksdb;
set session debug= '-d,MYROCKS_FORMAT_VERSION_INITIAL';
insert into t2 values (1, 'a');
insert into t2 values (2, 'b');
insert into t2 values (3, 'c');
insert into t2 values (4, 'c ');
select col1 from t2;
col1
a
b
c
c
delete from t2 where id = 4;
alter table t2 engine=rocksdb;
select col1 from t2;
col1
a
b
c
insert into t2 values (4, 'c ');
ERROR 23000: Duplicate entry 'c ' for key 'col1'
drop table t2;
set session debug= '+d,MYROCKS_FORMAT_VERSION_INITIAL';
create table t2 (
id int primary key,
col1 varchar(64) collate latin1_bin,
unique key (col1)
) engine=rocksdb;
set session debug= '-d,MYROCKS_FORMAT_VERSION_INITIAL';
insert into t2 values (1, 'a');
insert into t2 values (2, 'b');
insert into t2 values (3, 'c');
insert into t2 values (4, 'c ');
select col1 from t2;
col1
a
b
c
c
delete from t2 where id = 4;
alter table t2 engine=rocksdb;
select col1 from t2;
col1
a
b
c
insert into t2 values (4, 'c ');
ERROR 23000: Duplicate entry 'c ' for key 'col1'
drop table t2;
#
# Check what happens when one tries to 'upgrade' to the new data format
# and causes a unique key violation:
#
set session debug= '+d,MYROCKS_FORMAT_VERSION_INITIAL';
create table t2 (
pk int not null primary key,
col1 varchar(64) collate latin1_bin,
col2 varchar(64),
unique key (col1)
);
insert into t2 values (1, 'a ', 'a-space');
insert into t2 values (2, 'a', 'a');
select * from t2;
pk col1 col2
1 a a-space
2 a a
set session debug= '-d,MYROCKS_FORMAT_VERSION_INITIAL';
alter table t2 engine=rocksdb;
ERROR 23000: Duplicate entry 'a' for key 'col1'
drop table t2;
|
