Debian Codegraph @

mysql-test/suite/ndbcluster/charset.test

raw
1 --source include/have_ndb.inc
2
3 #
4 # Minimal NDB charset test.
5 #
6
7 # pk - binary
8
9 create table t1 (
10 a char(3) character set latin1 collate latin1_bin primary key
11 ) engine=ndb;
12 # ok
13 insert into t1 values('aAa');
14 insert into t1 values('aaa');
15 insert into t1 values('AAA');
16 # 3
17 select * from t1 order by a;
18 # 1
19 select * from t1 where a = 'aAa';
20 # 1
21 select * from t1 where a = 'aaa';
22 # 0
23 select * from t1 where a = 'AaA';
24 # 1
25 select * from t1 where a = 'AAA';
26 drop table t1;
27
28 # pk - case insensitive
29
30 create table t1 (
31 a char(3) character set latin1 collate latin1_swedish_ci primary key
32 ) engine=ndb;
33 # ok
34 insert into t1 values('aAa');
35 # fail
36 --error ER_DUP_ENTRY
37 insert into t1 values('aaa');
38 --error ER_DUP_ENTRY
39 insert into t1 values('AAA');
40 # 1
41 select * from t1 order by a;
42 # 1
43 select * from t1 where a = 'aAa';
44 # 1
45 select * from t1 where a = 'aaa';
46 # 1
47 select * from t1 where a = 'AaA';
48 # 1
49 select * from t1 where a = 'AAA';
50 drop table t1;
51
52 # pk - varchar
53
54 create table t1 (
55 a varchar(20) character set latin1 collate latin1_swedish_ci primary key
56 ) engine=ndb;
57 #
58 insert into t1 values ('A'),('b '),('C '),('d '),('E'),('f');
59 -- error ER_DUP_ENTRY
60 insert into t1 values('b');
61 -- error ER_DUP_ENTRY
62 insert into t1 values('a ');
63 #
64 select a,length(a) from t1 order by a;
65 select a,length(a) from t1 order by a desc;
66 select * from t1 where a = 'a';
67 select * from t1 where a = 'a ';
68 select * from t1 where a = 'd';
69 drop table t1;
70
71 # unique hash index - binary
72
73 create table t1 (
74 p int primary key,
75 a char(3) character set latin1 collate latin1_bin not null,
76 unique key(a)
77 ) engine=ndb;
78 # ok
79 insert into t1 values(1, 'aAa');
80 insert into t1 values(2, 'aaa');
81 insert into t1 values(3, 'AAA');
82 # 3
83 select * from t1 order by p;
84 # 1
85 select * from t1 where a = 'aAa';
86 # 1
87 select * from t1 where a = 'aaa';
88 # 0
89 select * from t1 where a = 'AaA';
90 # 1
91 select * from t1 where a = 'AAA';
92 drop table t1;
93
94 # unique hash index - case insensitive
95
96 create table t1 (
97 p int primary key,
98 a char(3) character set latin1 collate latin1_swedish_ci not null,
99 unique key(a)
100 ) engine=ndb;
101 # ok
102 insert into t1 values(1, 'aAa');
103 # fail
104 --error ER_DUP_ENTRY
105 insert into t1 values(2, 'aaa');
106 --error ER_DUP_ENTRY
107 insert into t1 values(3, 'AAA');
108 # 1
109 select * from t1 order by p;
110 # 1
111 select * from t1 where a = 'aAa';
112 # 1
113 select * from t1 where a = 'aaa';
114 # 1
115 select * from t1 where a = 'AaA';
116 # 1
117 select * from t1 where a = 'AAA';
118 drop table t1;
119
120 # unique hash index - varchar
121
122 create table t1 (
123 p int primary key,
124 a varchar(20) character set latin1 collate latin1_swedish_ci not null,
125 unique key(a)
126 ) engine=ndb;
127 #
128 insert into t1 values (1,'A'),(2,'b '),(3,'C '),(4,'d '),(5,'E'),(6,'f');
129 -- error ER_DUP_ENTRY
130 insert into t1 values(99,'b');
131 -- error ER_DUP_ENTRY
132 insert into t1 values(99,'a ');
133 #
134 select a,length(a) from t1 order by a;
135 select a,length(a) from t1 order by a desc;
136 select * from t1 where a = 'a';
137 select * from t1 where a = 'a ';
138 select * from t1 where a = 'd';
139 drop table t1;
140
141 # ordered index - binary
142
143 create table t1 (
144 p int primary key,
145 a char(3) character set latin1 collate latin1_bin not null,
146 index(a)
147 ) engine=ndb;
148 # ok
149 insert into t1 values(1, 'aAa');
150 insert into t1 values(2, 'aaa');
151 insert into t1 values(3, 'AAA');
152 insert into t1 values(4, 'aAa');
153 insert into t1 values(5, 'aaa');
154 insert into t1 values(6, 'AAA');
155 # 6
156 select * from t1 order by p;
157 # plan too flaky
158 #--replace_column 10 # 11 #
159 #explain select * from t1 where a = 'zZz' order by p;
160 # 2
161 select * from t1 where a = 'aAa' order by p;
162 # 2
163 select * from t1 where a = 'aaa' order by p;
164 # 0
165 select * from t1 where a = 'AaA' order by p;
166 # 2
167 select * from t1 where a = 'AAA' order by p;
168 drop table t1;
169
170 # ordered index - case insensitive
171
172 create table t1 (
173 p int primary key,
174 a char(3) character set latin1 collate latin1_swedish_ci not null,
175 index(a)
176 ) engine=ndb;
177 # ok
178 insert into t1 values(1, 'aAa');
179 insert into t1 values(2, 'aaa');
180 insert into t1 values(3, 'AAA');
181 insert into t1 values(4, 'aAa');
182 insert into t1 values(5, 'aaa');
183 insert into t1 values(6, 'AAA');
184 # 6
185 select * from t1 order by p;
186 # plan too flaky
187 #--replace_column 10 # 11 #
188 #explain select * from t1 where a = 'zZz' order by p;
189 # 6
190 select * from t1 where a = 'aAa' order by p;
191 # 6
192 select * from t1 where a = 'aaa' order by p;
193 # 6
194 select * from t1 where a = 'AaA' order by p;
195 # 6
196 select * from t1 where a = 'AAA' order by p;
197 drop table t1;
198
199 # ordered index - varchar
200
201 create table t1 (
202 p int primary key,
203 a varchar(20) character set latin1 collate latin1_swedish_ci not null,
204 index(a, p)
205 ) engine=ndb;
206 #
207 insert into t1 values (1,'A'),(2,'b '),(3,'C '),(4,'d '),(5,'E'),(6,'f');
208 insert into t1 values (7,'a'),(8,'B '),(9,'c '),(10,'D'),(11,'e'),(12,'F ');
209 select p,a,length(a) from t1 order by a, p;
210 select * from t1 where a = 'a ' order by a desc, p desc;
211 select * from t1 where a >= 'D' order by a, p;
212 select * from t1 where a < 'D' order by a, p;
213 #
214 select count(*) from t1 x, t1 y, t1 z where x.a = y.a and y.a = z.a;
215 drop table t1;
216
217 # minimal multi-byte test
218 # removed by jonas as this requires a configure --with-extra-charsets
219 #create table t1 (
220 # a char(5) character set ucs2,
221 # b varchar(7) character set utf8,
222 # primary key(a, b)
223 #) engine=ndb;
224 #
225 #insert into t1 values
226 # ('a','A '),('B ','b'),('c','C '),('D','d'),('e ','E'),('F','f '),
227 # ('A','b '),('b ','C'),('C','d '),('d','E'),('E ','f'),
228 # ('a','C '),('B ','d'),('c','E '),('D','f');
229 #-- error ER_DUP_ENTRY
230 #insert into t1 values('d','f');
231 #
232 #select a,b,length(a),length(b) from t1 order by a,b limit 3;
233 #select a,b,length(a),length(b) from t1 order by a desc, b desc limit 3;
234 #select a,b,length(a),length(b) from t1 where a='c' and b='c';
235 #drop table t1;
236
237 # bug#14007
238 create table t1 (
239 a char(10) primary key
240 ) engine=ndbcluster default charset=latin1;
241
242 insert into t1 values ('aaabb');
243 select * from t1;
244 replace into t1 set a = 'AAABB';
245 select * from t1;
246 replace into t1 set a = 'aAaBb';
247 select * from t1;
248 replace into t1 set a = 'aaabb';
249 select * from t1;
250 drop table t1;
251
252 # bug#33158 NDB table name problem(sensitive/insensitive)
253 # Check that tables with same letters, but different case
254 # don't conflict
255 create table t1(a int) engine = ndbcluster;
256
257 if (`SELECT @@lower_case_table_names = 0`)
258 {
259 create table T1(a int) engine = ndbcluster;
260 drop table T1;
261 }
262 if (`SELECT @@lower_case_table_names <> 0`)
263 {
264 --echo create table T1(a int) engine = ndbcluster;
265 --echo drop table T1;
266 }
267 drop table t1;
268
269
270 # End of 4.1 tests
271
272
273 # Start of 8.0 tests
274
275 # Bug #27477829 UNICODE 9.0 COLLATIONS INCORRECTLY DETECT DUPLICATES
276 # Truncation of the xfrm'ed normalized string caused false
277 # detection of duplicates.
278
279 create table t1 (
280 c1 varchar(10) collate utf8mb4_0900_as_cs,
281 primary key(c1)
282 ) engine = ndbcluster;
283
284
285 #Inserting short string was OK.
286 insert into t1 values('ABC_x'), ('ABC_X');
287
288 # However, longer strings were truncated when transformed into
289 # a normalized form. Thus false duplicates were detected.
290 insert into t1 values('ABCDEF_x'), ('ABCDEF_X');
291 insert into t1 values('ABCDEFGH_x'), ('ABCDEFGH_X');
292
293 --sorted_result
294 select * from t1;
295
296 drop table t1;
297
298 ####################################
299 # Bug#27515000 ERROR 743: CHARSET UTF8MB4_JA_0900_AS_CS_KS IS NOT SUPPORTED
300 #
301 # The above character set could not be used in any kind
302 # of index or key. Below test cases should pass after fix.
303
304 create table t1 (
305 c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
306 primary key(c1)
307 ) engine = ndbcluster;
308
309 drop table t1;
310
311
312 create table t1 (
313 c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
314 primary key using hash(c1)
315 ) engine = ndbcluster;
316
317 drop table t1;
318
319
320 create table t1 (
321 c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
322 unique key(c1)
323 ) engine = ndbcluster;
324
325 drop table t1;
326
327
328 create table t1 (
329 c1 varchar(10) not null collate utf8mb4_ja_0900_as_cs_ks,
330 unique key using hash(c1)
331 ) engine = ndbcluster;
332
333 drop table t1;
334
335 create table t1 (
336 c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
337 key(c1)
338 ) engine = ndbcluster;
339
340 drop table t1;
341
342 ####################################
343 # Bug#29322313 ANGSTROM AND A-RING NOT EQUAL WITH CHAR(N) COLUMN AND UTF8MB4_0900_AI_CI
344 #
345 # NO_PAD collations did not ignore trailing spaces as supposed to
346 # when hashing and comparing fix character columns.
347
348 # Insert two strings being binary different, but compares equal
349 # Require 2'nd insert to fail.
350
351 create table t (
352 ck char(1) collate utf8mb4_0900_ai_ci primary key
353 ) engine=ndb;
354
355 insert into t values (_utf16 X'212B');
356 --error ER_DUP_ENTRY
357 insert into t values (_utf16 X'00C5');
358 select count(*) from t;
359 drop table t;
360
361 create table t (
362 ck char(10) collate utf8mb4_0900_ai_ci primary key
363 ) engine=ndb;
364
365 insert into t values (_utf16 X'212B');
366 --error ER_DUP_ENTRY
367 insert into t values (_utf16 X'00C5');
368 select count(*) from t;
369 drop table t;
370
371 create table t (
372 ck varchar(1) collate utf8mb4_0900_ai_ci primary key
373 ) engine=ndb;
374
375 insert into t values (_utf16 X'212B');
376 --error ER_DUP_ENTRY
377 insert into t values (_utf16 X'00C5');
378 select count(*) from t;
379 drop table t;
380
381 create table t (
382 ck varchar(10) collate utf8mb4_0900_ai_ci primary key
383 ) engine=ndb;
384
385 insert into t values (_utf16 X'212B');
386 --error ER_DUP_ENTRY
387 insert into t values (_utf16 X'00C5');
388 select count(*) from t;
389 drop table t;
390
391
392 # End of 8.0 tests