Debian Codegraph @

mysql-test/suite/ndb/t/ndb_index.test

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 drop table if exists t1;
5 --enable_warnings
6
7 CREATE TABLE t1 (
8 PORT varchar(16) NOT NULL,
9 ACCESSNODE varchar(16) NOT NULL,
10 POP varchar(48) NOT NULL,
11 ACCESSTYPE int unsigned NOT NULL,
12 CUSTOMER_ID varchar(20) collate latin1_bin NOT NULL,
13 PROVIDER varchar(16),
14 TEXPIRE int unsigned,
15 NUM_IP int unsigned,
16 LEASED_NUM_IP int unsigned,
17 LOCKED_IP int unsigned,
18 STATIC_DNS int unsigned,
19 SUSPENDED_SERVICE int unsigned,
20 SUSPENDED_REASON int unsigned,
21 BGP_COMMUNITY int unsigned,
22 INDEX CUSTOMER_ID_INDEX(CUSTOMER_ID),
23 INDEX FQPN_INDEX(POP,ACCESSNODE,PORT),
24 PRIMARY KEY(POP,ACCESSNODE,PORT,ACCESSTYPE)
25 ) engine=ndbcluster;
26
27 INSERT INTO t1 VALUES ('port67', 'node78', 'pop98', 1, 'kllopmn', 'pr_43', 121212, 1, 2, 3, 8, NULL, NULL, NULL);
28 INSERT INTO t1 VALUES ('port67', 'node78', 'pop99', 2, 'klkighh', 'pr_44', 121213, 3, 3, 6, 7, NULL, NULL, NULL);
29 INSERT INTO t1 VALUES ('port79', 'node79', 'pop79', 2, 'kpongfaa', 'pr_44', 981213, 2, 4, 10, 11, 2, 99, 1278);
30
31
32 # Test select using port
33 select port, accessnode, pop, accesstype from t1 where port='port67' order by accesstype;
34 select port, accessnode, pop, accesstype from t1 where port='foo';
35
36 # Test select using accessnode
37 select port, accessnode, pop, accesstype from t1 where accessnode='node78' order by accesstype;
38 select port, accessnode, pop, accesstype from t1 where accessnode='foo';
39
40 # Test select using pop
41 select port, accessnode, pop, accesstype from t1 where pop='pop98';
42 select port, accessnode, pop, accesstype from t1 where pop='pop98';
43 select port, accessnode, pop, accesstype from t1 where pop='pop98';
44 select port, accessnode, pop, accesstype from t1 where pop='pop98' order by accesstype;
45 # The following two querys will not return any rows since
46 # the index used for access is case sensitive
47 # They are thus disabled for now
48 #select port, accessnode, pop, accesstype from t1 where pop='POP98';
49 #select port, accessnode, pop, accesstype from t1 where pop='POP98' order by accesstype;
50 select port, accessnode, pop, accesstype from t1 where pop='foo';
51
52 # Test select using accesstype
53 select port, accessnode, pop, accesstype from t1 where accesstype=1;
54 select port, accessnode, pop, accesstype from t1 where accesstype=2 order by port;
55 select port, accessnode, pop, accesstype from t1 where accesstype=98 order by port;
56
57 # Test select using customer_id
58 # NOTE! customer_id has a INDEX (ordered index in NDB), it's case sensitive!
59 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
60 select port, accessnode, pop, accesstype from t1 where customer_id='KLLOPMN';
61 select port, accessnode, pop, accesstype from t1 where customer_id='kLLoPMn';
62 select port, accessnode, pop, accesstype from t1 where customer_id='foo';
63
64 # Test select using provider
65 select port, accessnode, pop, accesstype from t1 where provider='pr_43';
66 select port, accessnode, pop, accesstype from t1 where provider='foo';
67
68 # Test select using texpire
69 select port, accessnode from t1 where texpire=121212;
70 select port, accessnode from t1 where texpire=2323;
71
72 # Test select using num_ip
73 select port, accessnode, pop, accesstype from t1 where num_ip=1;
74 select port, accessnode, pop, accesstype from t1 where num_ip=89;
75
76 # Test select using leased_num_ip
77 select port, accessnode, pop, accesstype from t1 where leased_num_ip=2;
78 select port, accessnode, pop, accesstype from t1 where leased_num_ip=89;
79
80 # Test select using locked_ip
81 select port, accessnode, pop, accesstype from t1 where locked_ip=3;
82 select port, accessnode, pop, accesstype from t1 where locked_ip=89;
83
84 # Test select using static_dns
85 select port, accessnode, pop, accesstype from t1 where static_dns=8;
86 select port, accessnode, pop, accesstype from t1 where static_dns=89;
87
88 # Test select using suspended_service
89 select port, accessnode, pop, accesstype from t1 where suspended_service=8;
90 select port, accessnode, pop, accesstype from t1 where suspended_service=89;
91
92 # Test select using suspended_reason
93 select port, accessnode, pop, accesstype from t1 where suspended_reason=NULL;
94 select port, accessnode, pop, accesstype from t1 where suspended_reason=89;
95 select port, accessnode, pop, accesstype from t1 where suspended_reason=0;
96
97 # Test select using bgp_community
98 select port, accessnode, pop, accesstype from t1 where bgp_community=NULL;
99 select port, accessnode, pop, accesstype from t1 where bgp_community=89;
100 select port, accessnode, pop, accesstype from t1 where bgp_community=0;
101
102 # Test select using full primary key
103 select port, accessnode, pop, accesstype from t1 where port='port67' and accessnode='node78' and pop='pop98' and accesstype=1;
104 select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78' and pop='pop98';
105 select port, accessnode, pop, accesstype from t1 where pop='pop98' and port='port67' and accesstype=1 and accessnode='node78';
106 select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo' and accesstype=99;
107
108 # Test select using partial primary key
109 select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accesstype=1;
110 select port, accessnode, pop, accesstype from t1 where accesstype=1 and accessnode='node78' and pop='pop98';
111 select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78';
112 select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo';
113
114 # Test select using CUSTOMER_ID_INDEX
115 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
116 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=1;
117 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=2;
118 select port, accessnode, pop, accesstype from t1 where accesstype=2 and customer_id='kllopmn';
119
120 # Test select using FQPN_INDEX
121 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67';
122 #select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' order by accesstype;
123 #select port, accessnode, pop, accesstype from t1 where accessnode='node78' and port='port67' and pop='pop98' order by accesstype;
124 #select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accessnode='node78' order by accesstype;
125 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='kllopmn';
126 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='foo';
127
128 drop table t1;
129
130 # End of 4.1 tests
131
132 # bug#45525
133 create table t1 (
134 a int not null primary key,
135 c00 int not null, c01 int not null, c02 int not null, c03 int not null, c04 int not null, c05 int not null, c06 int not null, c07 int not null,
136 c10 int not null, c11 int not null, c12 int not null, c13 int not null, c14 int not null, c15 int not null, c16 int not null, c17 int not null,
137 c20 int not null, c21 int not null, c22 int not null, c23 int not null, c24 int not null, c25 int not null, c26 int not null, c27 int not null,
138 c30 int not null, c31 int not null, c32 int not null, c33 int not null, c34 int not null, c35 int not null, c36 int not null, c37 int not null,
139 c40 int not null, c41 int not null, c42 int not null, c43 int not null, c44 int not null, c45 int not null, c46 int not null, c47 int not null,
140 c50 int not null, c51 int not null, c52 int not null, c53 int not null, c54 int not null, c55 int not null, c56 int not null, c57 int not null,
141 c60 int not null, c61 int not null, c62 int not null, c63 int not null, c64 int not null, c65 int not null, c66 int not null, c67 int not null,
142 c70 int not null, c71 int not null, c72 int not null, c73 int not null, c74 int not null, c75 int not null, c76 int not null, c77 int not null,
143 unique (c00), unique (c01), unique (c02), unique (c03), unique (c04), unique (c05), unique (c06), unique (c07),
144 unique (c10), unique (c11), unique (c12), unique (c13), unique (c14), unique (c15), unique (c16), unique (c17),
145 unique (c20), unique (c21), unique (c22), unique (c23), unique (c24), unique (c25), unique (c26), unique (c27),
146 unique (c30), unique (c31), unique (c32), unique (c33), unique (c34), unique (c35), unique (c36), unique (c37),
147 unique (c40), unique (c41), unique (c42), unique (c43), unique (c44), unique (c45), unique (c46), unique (c47),
148 unique (c50), unique (c51), unique (c52), unique (c53), unique (c54), unique (c55), unique (c56), unique (c57),
149 unique (c60), unique (c61), unique (c62), unique (c63), unique (c64), unique (c65), unique (c66), unique (c67),
150 unique (c70), unique (c71), unique (c72), unique (c73), unique (c74), unique (c75), unique (c76)) engine = ndb;
151
152 drop table t1;
153
154 CREATE TABLE t1 (
155 a INT NOT NULL AUTO_INCREMENT ,
156 b INT NOT NULL ,
157 c VARCHAR ( 11 ) NOT NULL ,
158 PRIMARY KEY ( a ) ,
159 INDEX ( b )
160 ) ENGINE = NDB
161 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
162
163 INSERT INTO t1 (a ,b ,c) VALUES
164 (NULL , '1', 'test'), (NULL , '2', 'test'), (NULL , '1', 'test'),
165 (NULL , '2', 'test'), (NULL , '3', 'test'), (NULL , '4', 'test'),
166 (NULL , '5', 'test'), (NULL , '6', 'test'), (NULL , '1', 'test'),
167 (NULL , '1', 'test');
168
169 explain select count(a) from t1 group by a;
170 explain select count(a) from t1 FORCE INDEX FOR GROUP BY (PRIMARY) group by a;
171 explain select count(b) from t1 group by b;
172 explain select count(b) from t1 FORCE INDEX FOR GROUP BY (b) group by b;
173 explain select distinct a from t1;
174 explain select distinct a from t1 group by a;
175 explain select distinct b from t1;
176 explain select distinct b from t1 group by b;
177
178 --sorted_result
179 select count(a) from t1 group by a;
180 --sorted_result
181 select count(a) from t1 FORCE INDEX FOR GROUP BY (PRIMARY) group by a;
182 --sorted_result
183 select count(b) from t1 group by b;
184 --sorted_result
185 select count(b) from t1 FORCE INDEX FOR GROUP BY (b) group by b;
186 --sorted_result
187 select distinct a from t1;
188 --sorted_result
189 select distinct a from t1 group by a;
190 --sorted_result
191 select distinct b from t1;
192 --sorted_result
193 select distinct b from t1 group by b;
194
195 drop table t1;
196
197 # bug#58280
198 create table t1(vc varchar(16), i int, vc2 varchar(1024)
199 , PRIMARY KEY(vc,vc2) USING HASH
200 , KEY i1(i)
201 , KEY i2(vc)
202 ) ENGINE=ndbcluster
203 CHARACTER SET latin1
204 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
205
206 insert into t1 values
207 ('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
208 ('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'),
209 ('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
210 ('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
211 ('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
212
213 # Scale up datevolume to get better statistics when sampling a single fragment
214 insert into t1 select concat(vc, '-0'), i, concat(vc2,'-0') from t1;
215 insert into t1 select concat(vc, '-1'), i, concat(vc2,'-1') from t1;
216
217 # Add non-matching rows to encourage usage of 'index_merge'
218 insert into t1 select concat('#-', vc), 0, concat('2-',vc2) from t1;
219 insert into t1 select concat('$-', vc), 0, concat('3-',vc2) from t1;
220 insert into t1 select concat('%-', vc), 0, concat('4-',vc2) from t1;
221
222 analyze table t1;
223
224 --echo As testcase for bug#58280, query should 'index_merge i1,i2'
225 --replace_column 10 # 11 #
226 explain
227 select i,vc from t1
228 where i>=1 or vc > '0';
229
230 --sorted_result
231 select i,vc from t1
232 where i>=1 or vc > '0';
233
234 # also test with hidden primary key
235 create table t2(vc varchar(16), i int, vc2 varchar(1024)
236 , KEY i1(i)
237 , KEY i2(vc)
238 ) ENGINE=ndbcluster
239 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
240
241 insert into t2 select * from t1;
242 analyze table t2;
243
244 --echo As testcase for bug#58280, query should 'index_merge i1,i2'
245 --replace_column 10 # 11 #
246 explain
247 select i,vc from t2
248 where i>=1 or vc > '0';
249
250 --sorted_result
251 select i,vc from t2
252 where i>=1 or vc > '0';
253
254 drop table t1, t2;
255
256 # Bug#38914 permanent table crash after truncate table with slash in keyname
257 CREATE TABLE inleveroverzicht (
258 id int(11) NOT NULL auto_increment,
259 klantid int(11) NOT NULL default '0',
260 productcode varchar(255) NOT NULL default '0',
261 aantal int(11) NOT NULL default '0',
262 PRIMARY KEY (id),
263 KEY klantid (klantid),
264 KEY `klantid/productcode` (klantid,productcode)
265 ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
266
267 INSERT INTO inleveroverzicht (id, klantid, productcode, aantal) VALUES
268 (33, 31, '15674', 11),
269 (32, 31, '0001256', 1);
270
271 SELECT * from inleveroverzicht ORDER BY id;
272
273 TRUNCATE TABLE inleveroverzicht;
274
275 SELECT * from inleveroverzicht ORDER BY id;
276
277 DROP TABLE inleveroverzicht;
278
279 # Bug#18487960: SIG 11 ON DELETE OPERATION WITH INDEX MERGE
280 CREATE TABLE indexmerge(
281 `id` bigint(20) unsigned NOT NULL,
282 `similar_id` bigint(20) unsigned NOT NULL,
283 `similarity` varchar(512) NOT NULL,
284 PRIMARY KEY (`id`,`similar_id`),
285 KEY `fk_similar_id` (`similar_id`),
286 KEY `ix_id_similarity` (`id`,`similarity`) USING BTREE
287 ) ENGINE=ndb CHARACTER SET utf8;
288
289 insert into indexmerge values(1, 2, REPEAT("A",512));
290 insert into indexmerge values(2, 1, REPEAT("B",512));
291 insert into indexmerge values(3, 4, REPEAT("C",512));
292 insert into indexmerge values(4, 3, REPEAT("D",512));
293 insert into indexmerge values(5, 6, REPEAT("E",512));
294 insert into indexmerge values(6, 5, REPEAT("F",512));
295 insert into indexmerge values(7, 8, REPEAT("G",512));
296 insert into indexmerge values(8, 7, REPEAT("H",512));
297 insert into indexmerge values(9, 0, REPEAT("I",512));
298 insert into indexmerge values(0, 9, REPEAT("J",512));
299
300 delete quick from indexmerge where id = 2 or similar_id = 2;
301 drop table indexmerge;
302
303 #
304 # Bug#31408095 WRONG INDEX ENTRY RELEASED WHEN DROPPING INDEX
305 # - the wrong index entry was released, causing crash when dropping
306 # index on table with 64 indexes.
307 #
308
309 # Create table with enough columns to create 64 indexes
310 CREATE TABLE t1(
311 col1 int unsigned not null primary key AUTO_INCREMENT, col2 int unsigned,
312 col3 varchar(27), col4 varchar(27), col5 varchar(27), col6 varchar(27),
313 col7 char(27), col8 int unsigned, col9 varchar(27), col10 varchar(27),
314 col11 varchar(27), col12 varchar(27), col13 varchar(27),
315 col14 varchar(27), col15 smallint unsigned, col16 char(27),
316 col17 varchar(27), col18 varchar(27), col19 varchar(27),
317 col20 varchar(27), col21 char(27), col22 varchar(27),
318 col23 mediumint unsigned, col24 varchar(27), col25 varchar(27),
319 col26 char(27), col27 varchar(27), col28 varchar(27), col29 varchar(27),
320 col30 varchar(27), col31 varchar(27), col32 bigint, col33 varchar(27),
321 col34 varchar(27), col35 smallint unsigned, col36 char(27),
322 col37 bigint unsigned, col38 varchar(27), col39 varchar(27),
323 col40 varchar(27), col41 varchar(27), col42 bigint, col43 varchar(27),
324 col44 varchar(27), col45 smallint unsigned, col46 char(27),
325 col47 varchar(27), col48 varchar(27), col49 varchar(27),
326 col50 varchar(27), col51 varchar(27), col52 bigint unsigned,
327 col53 varchar(27), col54 varchar(27), col55 varchar(27),
328 col56 tinyint unsigned, col57 varchar(27), col58 varchar(27),
329 col59 varchar(27), col60 tinyint unsigned, col61 int unsigned,
330 col62 varchar(27), col63 bigint unsigned, col64 int unsigned,
331 col65 varchar(27), col66 char(27), col67 varchar(27), col68 varchar(27),
332 col69 varchar(27), col70 varchar(27), col71 varchar(27),
333 col72 smallint unsigned, col73 varchar(27), col74 varchar(27),
334 col75 varchar(27), col76 char(27), col77 bigint unsigned, col78 varchar(27),
335 col79 varchar(27), col80 varchar(27), col81 varchar(27), col82 varchar(27),
336 col83 varchar(27), col84 varchar(27), col85 varchar(27), col86 char(27),
337 col87 varchar(27), col88 int unsigned, col89 varchar(27), col90 int unsigned,
338 col91 varchar(27), col92 tinyint unsigned, col93 varchar(27),
339 col94 varchar(27), col95 varchar(27), col96 varchar(27), col97 char(27),
340 col98 varchar(27), col99 varchar(27)
341 ) engine=ndbcluster;
342
343 # Create unique indexes until the max limit of 64 indexes is reached (the
344 # primary key also counts)
345 let $indexes = 64;
346 let $i = 1;
347 while ($i <= $indexes) {
348 let $index_name = col$i;
349 # Allow failure to create index when max limit is reached
350 --error 0,ER_TOO_MANY_KEYS
351 eval ALTER TABLE t1 ADD UNIQUE KEY $index_name ($index_name);
352 inc $i;
353 }
354
355 # Drop the unique indexes again
356 let $i = 1;
357 while ($i <= $indexes) {
358 let $index_name = col$i;
359 # Allow failure to drop those who couldn't be created
360 --error 0,ER_CANT_DROP_FIELD_OR_KEY
361 eval ALTER TABLE t1 DROP INDEX $index_name;
362 inc $i;
363 }
364
365 # Check that adding and dropping indexes still work
366 ALTER TABLE t1 ADD UNIQUE KEY (col24);
367 ALTER TABLE t1 ADD UNIQUE KEY (col25);
368 ALTER TABLE t1 DROP INDEX col24;
369 ALTER TABLE t1 DROP INDEX col25;
370
371 DROP TABLE t1;
372
373 --echo #
374 --echo # Bug#31452597 BUILD ATTRID MAP FOR PRIMARY KEY
375 --echo # - test read using primary key where keys are specified in
376 --echo # different order than in the NDB table, thus testing
377 --echo # the NDB_INDEX_DATA::Attrid_map mapping
378 CREATE TABLE t1 (
379 a int, b int, c int, d int, e int, f int, g int, h int,
380 PRIMARY KEY(d,b,c),
381 UNIQUE KEY(a)
382 ) engine = ndb;
383 CREATE TABLE t1hash (
384 a int, b int, c int, d int, e int, f int, g int, h int,
385 PRIMARY KEY(b,d,c) USING HASH,
386 UNIQUE KEY(a)
387 ) engine = ndb;
388 CREATE TABLE t2 (
389 a int, b int,
390 PRIMARY KEY(a)
391 ) engine = ndb;
392
393 INSERT INTO t1 VALUES
394 (1,2,3,4,5,6,7,8),
395 (2,2,4,4,5,6,7,9),
396 (3,2,5,4,5,6,7,10);
397 INSERT INTO t1hash VALUES
398 (1,2,3,4,5,6,7,8),
399 (2,2,4,4,5,6,7,9),
400 (3,2,5,4,5,6,7,10);
401 INSERT INTO t2 VALUES
402 (1,2);
403
404 # Read with pushed join primary key index, should use mapping
405 SELECT t1.a, t1.h FROM t1, t2
406 WHERE t2.a = t1.a /* join with t2 to trigger pushed join */ AND
407 t1.b = 2 AND t1.c = 3 AND t1.d = 4 /* use primary key on t1 */;
408
409 # Read with pushed join primary key index using hash, should use mapping
410 SELECT t1hash.a, t1hash.h FROM t1hash, t2
411 WHERE t2.a = t1hash.a /* join with t2 to trigger pushed join */ AND
412 t1hash.b = 2 AND t1hash.c = 3 AND t1hash.d = 4 /* use primary key on t1 */;
413
414 DROP TABLE t1,t1hash,t2;