Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 #
4 # Simple test to show use of UNIQUE indexes
5 #
6
7 CREATE TABLE t1 (
8 a int NOT NULL PRIMARY KEY,
9 b int not null,
10 c int,
11 UNIQUE ib(b)
12 ) engine=ndbcluster;
13
14 insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
15 select * from t1 order by b;
16 select * from t1 where b = 4 order by b;
17 insert into t1 values(7,8,3);
18 select * from t1 where b = 4 order by a;
19
20 -- error ER_DUP_ENTRY
21 insert into t1 values(8, 2, 3);
22 select * from t1 order by a;
23 delete from t1 where a = 1;
24 insert into t1 values(8, 2, 3);
25 select * from t1 order by a;
26
27 alter table t1 drop index ib;
28 insert into t1 values(1, 2, 3);
29 # Bug# #18129
30 --error 1169
31 create unique index ib on t1(b);
32
33 drop table t1;
34
35 #
36 # Indexing NULL values
37 #
38
39 CREATE TABLE t1 (
40 a int unsigned NOT NULL PRIMARY KEY,
41 b int unsigned,
42 c int unsigned,
43 UNIQUE bc(b,c)
44 ) engine = ndb;
45
46 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
47 select * from t1 use index (bc) where b IS NULL order by a;
48
49 select * from t1 use index (bc)order by a;
50 select * from t1 use index (bc) order by a;
51 select * from t1 use index (PRIMARY) where b IS NULL order by a;
52 select * from t1 use index (bc) where b IS NULL order by a;
53 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
54 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
55 select * from t1 use index (bc) where b < 4 order by a;
56 select * from t1 use index (bc) where b IS NOT NULL order by a;
57 -- error ER_DUP_ENTRY
58 insert into t1 values(5,1,1);
59 drop table t1;
60
61
62 #
63 # Show use of UNIQUE USING HASH indexes
64 #
65
66 CREATE TABLE t2 (
67 a int unsigned NOT NULL PRIMARY KEY,
68 b int unsigned not null,
69 c int unsigned not null,
70 UNIQUE (b, c) USING HASH
71 ) engine=ndbcluster;
72
73 insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
74 select * from t2 where a = 3;
75 select * from t2 where b = 4;
76 select * from t2 where c = 6;
77 insert into t2 values(7,8,3);
78 select * from t2 where b = 4 order by a;
79
80 -- error ER_DUP_ENTRY
81 insert into t2 values(8, 2, 3);
82 select * from t2 order by a;
83 delete from t2 where a = 1;
84 insert into t2 values(8, 2, 3);
85 select * from t2 order by a;
86
87 # Bug #24818 CREATE UNIQUE INDEX (...) USING HASH on a NDB table crashes mysqld
88 create unique index bi using hash on t2(b);
89 -- error ER_DUP_ENTRY
90 insert into t2 values(9, 3, 1);
91 alter table t2 drop index bi;
92 insert into t2 values(9, 3, 1);
93 select * from t2 order by a;
94
95 drop table t2;
96
97 CREATE TABLE t2 (
98 a int unsigned NOT NULL PRIMARY KEY,
99 b int unsigned not null,
100 c int unsigned,
101 UNIQUE (b, c) USING HASH
102 ) engine=ndbcluster
103 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
104
105
106 insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL);
107
108 select * from t2 where c IS NULL order by a;
109 select * from t2 where b = 3 AND c IS NULL order by a;
110 select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
111 set @old_os = @@session.optimizer_switch;
112 set optimizer_switch = 'engine_condition_pushdown=on';
113 --replace_column 10 # 11 #
114 explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
115 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
116 set optimizer_switch = @old_os;
117
118 ##################
119 # Bug#33466554 Prevent optimizer from generating range scans on
120 # unique hash indexes.
121 #
122 # The query plan for some of the above queries set up a range scan
123 # using the HASH-index on (b,c) - That is not an available access type
124 # on an 'UNIQUE <key> USING HASH'. Even if set up by the optimizer as such,
125 # ha_ndbcluster::read_range_first_to_buf detected that we tried to do
126 # use an access type not supported by a UNIQUE_INDEX and did multiple
127 # 'full_table_scans' instead.
128
129 # Explain some of the relevant queries. Should *not* do
130 # range scans on the HASH index. Instead do either full table scan,
131 # or a range scan on PK.
132
133 --replace_regex / \(cost=.*//
134 explain format=tree
135 select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
136 --replace_regex / \(cost=.*//
137 explain format=tree
138 select * from t2 where (b = 3 OR b = 5) AND c IS NULL;
139 --replace_regex / \(cost=.*//
140 explain format=tree
141 select * from t2 where b in (1,2,3) AND c IS NULL;
142 --replace_regex / \(cost=.*//
143 explain format=tree
144 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
145 --replace_regex / \(cost=.*//
146 explain format=tree
147 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9;
148
149 ###############################
150 # Bug#33474345 Avoid NULL-constants as part of REF keys.
151 #
152 # Explains of some tests above where the optimizer set up REF / Index lookup
153 # access with a NULL constant on the ix-HASH key, which handler degraded
154 # to full_table_scan.
155 # Will now reject the usage of ix-index and do an ordered scan on PK instead.
156 # More test cases for this bug further below.
157
158 --replace_regex / \(cost=.*//
159 explain format=tree
160 select * from t2 where c IS NULL order by a;
161 --replace_regex / \(cost=.*//
162 explain format=tree
163 select * from t2 where b = 3 AND c IS NULL order by a;
164
165
166 drop table t2;
167
168 #
169 # Show use of PRIMARY KEY USING HASH indexes
170 #
171
172 CREATE TABLE t3 (
173 a int unsigned NOT NULL,
174 b int unsigned not null,
175 c int unsigned,
176 PRIMARY KEY (a, b) USING HASH
177 ) engine=ndbcluster;
178
179 insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
180 select * from t3 where a = 3;
181 select * from t3 where b = 4;
182 select * from t3 where c = 6;
183 insert into t3 values(7,8,3);
184 select * from t3 where b = 4 order by a;
185
186 drop table t3;
187
188 #
189 # Indexes on NULL-able columns
190 #
191
192 CREATE TABLE t1 (
193 pk int NOT NULL PRIMARY KEY,
194 a int unsigned,
195 UNIQUE KEY (a)
196 ) engine=ndbcluster;
197
198 insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
199
200 select * from t1 order by pk;
201
202 --error ER_DUP_ENTRY
203 insert into t1 values (5,0);
204 select * from t1 order by pk;
205 delete from t1 where a = 0;
206 insert into t1 values (5,0);
207 select * from t1 order by pk;
208
209 CREATE TABLE t2 (
210 pk int NOT NULL PRIMARY KEY,
211 a int unsigned,
212 b tinyint NOT NULL,
213 c VARCHAR(10),
214 UNIQUE KEY si(a, c)
215 ) engine=ndbcluster;
216
217 insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
218
219 select * from t2 order by pk;
220
221 --error ER_DUP_ENTRY
222 insert into t2 values(2,3,19,'abc');
223 select * from t2 order by pk;
224 delete from t2 where c IS NOT NULL;
225 insert into t2 values(2,3,19,'abc');
226 select * from t2 order by pk;
227
228 drop table t1, t2;
229
230 #
231 # More complex tables
232 #
233
234 CREATE TABLE t1 (
235 cid smallint(5) unsigned NOT NULL default '0',
236 cv varchar(250) NOT NULL default '',
237 PRIMARY KEY (cid),
238 UNIQUE KEY cv (cv)
239 ) engine=ndbcluster;
240 INSERT INTO t1 VALUES (8,'dummy');
241 CREATE TABLE t2 (
242 cid bigint(20) unsigned NOT NULL auto_increment,
243 cap varchar(255) NOT NULL default '',
244 PRIMARY KEY (cid),
245 UNIQUE KEY (cid, cap)
246 ) engine=ndbcluster;
247 INSERT INTO t2 VALUES (NULL,'another dummy');
248 CREATE TABLE t3 (
249 gid bigint(20) unsigned NOT NULL auto_increment,
250 gn varchar(255) NOT NULL default '',
251 must tinyint(4) default NULL,
252 PRIMARY KEY (gid)
253 ) engine=ndbcluster;
254 INSERT INTO t3 VALUES (1,'V1',NULL);
255 CREATE TABLE t4 (
256 uid bigint(20) unsigned NOT NULL default '0',
257 gid bigint(20) unsigned NOT NULL,
258 rid bigint(20) unsigned NOT NULL,
259 cid bigint(20) unsigned NOT NULL,
260 UNIQUE KEY m (uid,gid,rid,cid)
261 ) engine=ndbcluster;
262 INSERT INTO t4 VALUES (1,1,2,4);
263 INSERT INTO t4 VALUES (1,1,2,3);
264 INSERT INTO t4 VALUES (1,1,5,7);
265 INSERT INTO t4 VALUES (1,1,10,8);
266 CREATE TABLE t5 (
267 rid bigint(20) unsigned NOT NULL auto_increment,
268 rl varchar(255) NOT NULL default '',
269 PRIMARY KEY (rid)
270 ) engine=ndbcluster;
271 CREATE TABLE t6 (
272 uid bigint(20) unsigned NOT NULL auto_increment,
273 un varchar(250) NOT NULL default '',
274 uc smallint(5) unsigned NOT NULL default '0',
275 PRIMARY KEY (uid),
276 UNIQUE KEY nc (un,uc)
277 ) engine=ndbcluster;
278 INSERT INTO t6 VALUES (1,'test',8);
279 INSERT INTO t6 VALUES (2,'test2',9);
280 INSERT INTO t6 VALUES (3,'tre',3);
281 CREATE TABLE t7 (
282 mid bigint(20) unsigned NOT NULL PRIMARY KEY,
283 uid bigint(20) unsigned NOT NULL default '0',
284 gid bigint(20) unsigned NOT NULL,
285 rid bigint(20) unsigned NOT NULL,
286 cid bigint(20) unsigned NOT NULL,
287 UNIQUE KEY m (uid,gid,rid,cid)
288 ) engine=ndbcluster;
289 INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
290 INSERT INTO t7 VALUES(2, 2, 1, 1, 1);
291 INSERT INTO t7 VALUES(3, 3, 1, 1, 1);
292 INSERT INTO t7 VALUES(4, 4, 1, 1, 1);
293 INSERT INTO t7 VALUES(5, 5, 1, 1, 1);
294 INSERT INTO t7 VALUES(6, 1, 1, 1, 6);
295 INSERT INTO t7 VALUES(7, 2, 1, 1, 7);
296 INSERT INTO t7 VALUES(8, 3, 1, 1, 8);
297 INSERT INTO t7 VALUES(9, 4, 1, 1, 9);
298 INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
299
300 select * from t1 where cv = 'dummy';
301 select * from t1 where cv = 'test';
302 select * from t2 where cap = 'another dummy';
303 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
304 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
305 select * from t4 where uid = 1 order by cid;
306 select * from t4 where rid = 2 order by cid;
307 select * from t6 where un='test' and uc=8;
308 select * from t6 where un='test' and uc=7;
309 select * from t6 where un='test';
310 select * from t7 where mid = 8;
311 select * from t7 where uid = 8;
312 select * from t7 where uid = 1 order by mid;
313 select * from t7 where uid = 4 order by mid;
314 select * from t7 where gid = 4;
315 select * from t7 where gid = 1 order by mid;
316 select * from t7 where cid = 4;
317 select * from t7 where cid = 8;
318
319 #
320 # insert more records into t4
321 #
322 let $1=100;
323 disable_query_log;
324 while ($1)
325 {
326 eval insert into t4 values(1, $1, 5, 12);
327 eval insert into t4 values($1, 3, 9, 11);
328 dec $1;
329 }
330 enable_query_log;
331
332 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
333 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
334 select * from t4 where uid = 1 order by gid,cid;
335 select * from t4 where uid = 1 order by gid,cid;
336 select * from t4 where rid = 2 order by cid;
337
338
339 drop table t1,t2,t3,t4,t5,t6,t7;
340
341 # test null in indexes
342 CREATE TABLE t1 (
343 a int unsigned NOT NULL PRIMARY KEY,
344 b int unsigned,
345 c int unsigned,
346 UNIQUE bc(b,c) ) engine = ndb;
347
348 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
349 select * from t1 where b=1 and c=1;
350 select * from t1 where b is null and c is null;
351 select * from t1 where b is null and c = 2;
352 select * from t1 where b = 4 and c is null;
353 create table t8 as
354 select * from t1 where (b = 1 and c = 1)
355 or (b is null and c is null)
356 or (b is null and c = 2)
357 or (b = 4 and c is null);
358 select * from t8 order by a;
359 select * from t1 order by a;
360 drop table t1, t8;
361
362 ###############################
363 # Bug 8101
364 #
365 # Unique index not specified in the same order as in table
366 #
367
368 create table t1(
369 id integer not null auto_increment,
370 month integer not null,
371 year integer not null,
372 code varchar( 2) not null,
373 primary key ( id),
374 unique idx_t1( month, code, year)
375 ) engine=ndb;
376
377 INSERT INTO t1 (month, year, code) VALUES (4,2004,'12');
378 INSERT INTO t1 (month, year, code) VALUES (5,2004,'12');
379
380 select * from t1 where code = '12' and month = 4 and year = 2004 ;
381
382 drop table t1;
383
384 # bug#15918 Unique Key Limit in NDB Engine
385
386 create table t1 (a int primary key, b varchar(1000) not null, unique key (b))
387 engine=ndb charset=utf8;
388
389 insert into t1 values (1, repeat(_utf8 0xe288ab6474, 200));
390 --error ER_DUP_ENTRY
391 insert into t1 values (2, repeat(_utf8 0xe288ab6474, 200));
392 select a, sha1(b) from t1;
393
394 # perl -e 'print pack("H2000","e288ab6474"x200)' | sha1sum
395
396 drop table t1;
397
398 # bug#21873 MySQLD Crash on ALTER...ADD..UNIQUE..USING HASH statement for NDB backed table
399
400 create table t1(id int not null) engine = NDB;
401
402 alter table t1 add constraint uk_test unique (id) using hash;
403
404 drop table t1;
405
406 # End of 4.1 tests
407
408 # bug#44132
409
410 CREATE TABLE t1 (
411 pk0 INT,
412 pk1 VARCHAR(100),
413 col0 INT NOT NULL,
414 PRIMARY KEY(pk0, pk1),
415 UNIQUE (col0) USING HASH
416 ) engine = ndb;
417
418 CREATE TABLE t2 (
419 pk0 VARCHAR(100),
420 pk1 INT,
421 col0 INT NOT NULL,
422 PRIMARY KEY(pk0, pk1),
423 UNIQUE (col0) USING HASH
424 ) engine = ndb;
425
426 let $1=100 ;
427 disable_query_log;
428 while ($1)
429 {
430 eval insert into t1 values($1, 'a', $1);
431 eval insert into t2 values('a', $1, $1);
432 dec $1;
433 }
434 enable_query_log;
435
436 drop table t1, t2;
437
438 # bug#57827 : Don't load dd columns when building unique index
439 # Build a unique index in a table with DD columns to give
440 # some coverage of the scenario
441 #
442
443 CREATE LOGFILE GROUP lg1
444 ADD UNDOFILE 'undofile.dat'
445 INITIAL_SIZE 8M
446 UNDO_BUFFER_SIZE = 1M engine=ndb;
447
448 CREATE TABLESPACE ts1
449 ADD DATAFILE 'datafile.dat'
450 USE LOGFILE GROUP lg1
451 INITIAL_SIZE 32M
452 ENGINE NDB;
453
454 use test;
455
456 create table t1 (pk int primary key, un int, data varchar(500) storage disk) tablespace ts1 engine=ndb;
457
458 insert into t1 values
459 (1,1,repeat('B', 500)),
460 (2,2,repeat('B', 500)),
461 (3,3,repeat('B', 500)),
462 (4,4,repeat('B', 500)),
463 (5,5,repeat('B', 500)),
464 (6,6,repeat('B', 500)),
465 (7,7,repeat('B', 500)),
466 (8,8,repeat('B', 500)),
467 (9,9,repeat('B', 500)),
468 (10,10,repeat('B', 500)),
469 (11,11,repeat('B', 500)),
470 (12,12,repeat('B', 500)),
471 (13,13,repeat('B', 500)),
472 (14,14,repeat('B', 500)),
473 (15,15,repeat('B', 500));
474
475 alter table t1 add unique(un);
476
477 drop table t1;
478 alter tablespace ts1 drop datafile 'datafile.dat';
479 drop tablespace ts1;
480 drop logfile group lg1 engine=ndb;
481
482 # bug#57032 'NOT NULL' evaluation is incorrect when using an 'unique index
483
484 --echo # bug#57032
485
486 create table t1 (
487 a int not null,
488 b int,
489 primary key using hash (a),
490 unique key using hash (b)
491 )
492 engine ndb;
493
494 insert into t1 values
495 (0,0),(1,1),(2,2),(3,3),(4,4),
496 (5,null),(6,null),(7,null),(8,null),(9,null);
497
498
499 set optimizer_switch = 'engine_condition_pushdown=off';
500 # failed, empty result
501 select a from t1 where b is not null order by a;
502 # worked
503 select a from t1 where b is null order by a;
504
505 set optimizer_switch = 'engine_condition_pushdown=on';
506 # failed, empty result
507 select a from t1 where b is not null order by a;
508 # worked
509 select a from t1 where b is null order by a;
510
511 set optimizer_switch = @old_os;
512
513 drop table t1;
514
515 create table t1 (
516 a int not null,
517 b int,
518 c int,
519 primary key using hash (a),
520 unique key using hash (b,c)
521 )
522 engine ndb;
523
524 insert into t1 values
525 (0,0,0),(1,1,1),(2,2,1),(3,3,1),(4,4,2),
526 (5,null,0),(6,null,1),(7,null,1),(8,null,1),(9,null,2),
527 (10,0,null),(11,1,null),(12,1,null),(13,1,null),(14,2,null),
528 (15,null,null),(16,null,null),(17,null,null),(18,null,null),(19,null,null);
529
530
531 set optimizer_switch = 'engine_condition_pushdown=off';
532 # worked
533 select a from t1 where b is not null and c = 1 order by a;
534 # failed, empty result
535 select a from t1 where b is null and c = 1 order by a;
536 # failed, empty result
537 select a from t1 where b = 1 and c is null order by a;
538 # worked
539 select a from t1 where b is null and c is null order by a;
540 select a from t1 where b is not null and c is null order by a;
541 select a from t1 where b is null and c is not null order by a;
542 select a from t1 where b is not null and c is not null order by a;
543
544 set optimizer_switch = 'engine_condition_pushdown=on';
545 # worked
546 select a from t1 where b is not null and c = 1 order by a;
547 # failed, empty result
548 select a from t1 where b is null and c = 1 order by a;
549 # failed, empty result
550 select a from t1 where b = 1 and c is null order by a;
551 # worked
552 select a from t1 where b is null and c is null order by a;
553 select a from t1 where b is not null and c is null order by a;
554 select a from t1 where b is null and c is not null order by a;
555 select a from t1 where b is not null and c is not null order by a;
556
557 set optimizer_switch = @old_os;
558
559 drop table t1;
560
561 #
562 # Bug#58750: 'out of connection objects' due to missing close()
563 # of prev. full_table_scan()
564 #
565 create table t1 (pk int primary key, a int) engine=ndb
566 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
567 create table t2 (pk int primary key, uq int, a int,
568 unique key ix(uq,a) USING HASH) engine=ndb
569 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
570
571 insert into t2 values
572 (0,0,0), (1,1,0), (2,2,0), (3,3,0), (4,4,0),
573 (5,5,1), (6,6,1), (7,7,1), (8,8,1), (9,9,1);
574
575 ##
576 # 10^4 cross product on t2 creates 10.000 rows:
577 ##
578 insert into t1
579 select
580 t1.pk + t2.pk*10 + t3.pk*100 + t4.pk*1000, t1.a
581 from
582 t2 as t1, t2 as t2, t2 as t3, t2 as t4;
583
584 # Execute a 'scan(t1) join REF(t2) using ix'
585 # - Where condition 't2.uq IS NULL' in combination with
586 # join cond. 't2.a=t1.a' will create the REF keys (NULL,t1.a).
587 # This will initiate a ::full_table_scan() which fails to
588 # close any open table scans.
589 # - Table scan(t1) will 'drive' the case above and eventually
590 # cause all ScanOperations / hupp'ed transactions to have been
591 # consumed
592 #
593 # Note that after fixing Bug#33474345, see below, the original test case
594 # didn't behave as intended. Thus it was rewritten from using
595 # 't2.uq IS NULL' to 't2.uq <=> t1.a+NULL'.
596 # I.e. from a known NULL-valued key, to an expression evaluating
597 # to NULL at execution time. Note the usage of '<=>' operator which
598 # compare NULLs as equal.
599 #
600 # The original test case became a test case for bug#33474345, where
601 # we try to avoid doing such repeated scans whenever possible.
602
603 explain
604 SELECT STRAIGHT_JOIN count(*) FROM
605 t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL;
606 SELECT STRAIGHT_JOIN count(*) FROM
607 t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL;
608
609 #
610 # Bug#33474345:
611 # Avoid table REF-access in query plan,
612 # when handler does full table scan.
613 #
614 # There is a known NULL-constant in the key for a potential
615 # REF / Index lookup access on t2, using the 'ix' HASH index. Thus the
616 # handler would have fallen back to do repeated full table scan.
617 # Avoid that when it can be easily detected by the optimizer.
618 #
619 # Test case below will now do a single table scan on t2 and hash-join(t1,t2),
620 # which is about 10x faster for this query
621
622 --replace_regex / \(cost=.*//
623 explain format=tree
624 SELECT STRAIGHT_JOIN count(*) FROM
625 t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL;
626 SELECT STRAIGHT_JOIN count(*) FROM
627 t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL;
628
629 drop table t1,t2;
630
631 #
632 # Bug #16678033 IN-SUBQUERY WITH TRUE/FALSE/UNKNOWN TEST
633 # MAY RETURN INCORRECT RESULT OR ASSERT
634 #
635 CREATE TABLE t1 (
636 i int
637 ) ENGINE=ndbcluster
638 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
639
640 INSERT INTO t1 VALUES (1);
641
642 CREATE TABLE t2 (
643 col_int_unique int,
644 UNIQUE KEY ix1 (col_int_unique) USING HASH
645 ) ENGINE=ndbcluster
646 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
647
648 INSERT INTO t2 VALUES (42);
649
650 EXPLAIN SELECT * FROM t1 WHERE i IN
651 (SELECT t2.col_int_unique FROM t2) IS TRUE;
652
653 # Remember 'row' conter when test started.
654 --disable_warnings
655 create temporary table row_counts_at_startup
656 select *
657 from performance_schema.session_status
658 WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT';
659 --enable_warnings
660
661 SELECT * FROM t1 WHERE i IN
662 (SELECT t2.col_int_unique FROM t2) IS TRUE;
663
664 SELECT * FROM t1 WHERE i IN
665 (SELECT t2.col_int_unique FROM t2) IS FALSE;
666
667 SELECT * FROM t1 WHERE i IN
668 (SELECT t2.col_int_unique FROM t2) IS UNKNOWN;
669
670 SELECT * FROM t1 WHERE i IN
671 (SELECT t2.col_int_unique FROM t2) IS NOT TRUE;
672
673 SELECT * FROM t1 WHERE i IN
674 (SELECT t2.col_int_unique FROM t2) IS NOT FALSE;
675
676 SELECT * FROM t1 WHERE i IN
677 (SELECT t2.col_int_unique FROM t2) IS NOT UNKNOWN;
678
679 # Verify row counter at end:
680 # Used to verify that correct hi/low limits was set for
681 # the EQ_RANGE checking for col_int_unique NULL values
682 --disable_warnings
683 create temporary table row_counts_at_end
684 select *
685 from performance_schema.session_status
686 WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT';
687 --enable_warnings
688
689 select row_counts_at_end.VARIABLE_NAME,
690 row_counts_at_end.VARIABLE_VALUE - row_counts_at_startup.VARIABLE_VALUE
691 from row_counts_at_end, row_counts_at_startup
692 where row_counts_at_end.VARIABLE_NAME = row_counts_at_startup.VARIABLE_NAME;
693
694 drop table row_counts_at_end;
695 drop table row_counts_at_startup;
696
697 drop table t1,t2;
698
699 --echo #
700 --echo # Bug#21777589 : UNIQUE KEY IS PARTIALLY DROPPED FROM MYSQLD EVEN WHEN A 'DROP INDEX' FAILS
701 --echo # Bug#22104597 : DROP INDEX FAILURE CORRUPTS THE INDEX METADATA IN MYSQLD
702 --echo #
703
704 --echo # Create table t1 with multiple keys
705 create table t1(
706 a int,
707 b int,
708 unique key uk1(a),
709 unique key uk2(b) using hash
710 )engine=ndb;
711
712 --echo # Create table t2 with a fk based on t1's uk
713 create table t2(
714 a int,
715 b int,
716 c int,
717 foreign key fk1(a) references t1(a),
718 unique key uk3(b),
719 unique key uk4(c)
720 )engine=ndb;
721
722 --echo # Try dropping uk1. should fail with correct uk constraint
723 --error ER_DROP_INDEX_FK
724 drop index uk1 on t1;
725
726 --echo # Perform a select from t1 to verify the index has not been partially dropped
727 --echo # also to ensure the ordering in m_index is intact
728 select * from t1;
729
730 --echo # Try dropping uk2 now. should be dropped without any problem
731 drop index uk2 on t1;
732
733 --echo # Verify through show create table
734 --disable_warnings
735 show create table t1;
736 --enable_warnings
737
738 --echo # Make sure dropping multiple indexes together works
739 alter table t2 drop index uk3, drop index uk4;
740
741 --echo # Verify through show create table
742 --disable_warnings
743 show create table t1;
744 --enable_warnings
745
746 --echo # cleanup
747 drop table t2,t1;
748
749 --echo #
750 --echo # Bug#22110051 : ASSERTION IN PROTOCOL::END_STATEMENT DURING DROP INDEX
751 --echo #
752
753 --echo # Create table t1 with unique key
754 create table t1(
755 a int not null,
756 b int not null,
757 unique key uk1(a),
758 unique key uk2(b)
759 )engine=ndb;
760
761 --echo # Create table t2 with a fk mapped to t1(a)
762 create table t2(
763 a int,
764 constraint fk1 foreign key (a) references t1(a)
765 )engine=ndb;
766
767 --echo # desc t1 to show the implicit pk
768 desc t1;
769
770 --echo # Drop index uk1 - it should fail
771 --error ER_DROP_INDEX_FK
772 drop index uk1 on t1;
773
774 --echo # Verify table is intact
775 --disable_warnings
776 show create table t1;
777 --enable_warnings
778
779 --echo # Modify column on t1 - remove not null
780 alter table t1 modify column a int;
781
782 --echo # verify that pk is now on b.
783 desc t1;
784 --disable_warnings
785 show create table t1;
786 --enable_warnings
787 --echo # Verify that the fk constraint is intact through inserts
788 insert into t1 values (1,10), (2,20), (3,30);
789 insert into t2 values (1), (2), (3);
790 --error ER_NO_REFERENCED_ROW_2
791 insert into t2 values (4);
792
793 --echo # Try removing uk2. should pass.
794 alter table t1 drop index uk2;
795
796 --echo # Verify that everything is fine.
797 desc t1;
798 --disable_warnings
799 show create table t1;
800 --enable_warnings
801 --echo # Verify that the fk constraint is intact through inserts
802 insert into t2 values (1), (2), (3);
803 --error ER_NO_REFERENCED_ROW_2
804 insert into t2 values (4);
805
806 --echo #cleanup
807 drop table t2, t1;
808
809 --echo #
810 --echo # Bug#24444899 MYSQLD CRASHED WHILE DROPPING UNIQUE KEY ...
811 --echo # - test read using unique key where keys are specified in
812 --echo # different order than in the NDB table, thus testing
813 --echo # the NDB_INDEX_DATA::Attrid_map mapping
814 CREATE TABLE t1 (
815 a int PRIMARY KEY,
816 b int, c int, d int, e int, f int, g int, h int, i int,
817 UNIQUE INDEX unique_hgfedcb (h,f,d,e,g,c,b)
818 ) engine = ndb;
819 CREATE TABLE t2 (
820 a int PRIMARY KEY,
821 b int
822 ) engine = ndb;
823 INSERT INTO t1 VALUES
824 (1,2,3,4,5,6,7,8,40),
825 (2,2,4,4,5,6,7,9,41),
826 (3,2,5,4,5,6,7,10,42);
827 INSERT INTO t2 VALUES
828 (1,2);
829
830 --echo # Read with pushed join through unique index and use mapping
831 SELECT t1.i FROM t1, t2
832 WHERE t2.a = t1.a /* join with t2 to trigger pushed join */ AND
833 t1.b = 2 AND t1.c = 3 AND t1.d = 4 AND
834 t1.e = 5 AND t1.f = 6 AND t1.g = 7 AND
835 t1.h = 8 /* use unique index on t1 */;
836
837 DROP TABLE t1, t2;
838
839
840 --echo #
841 --echo # Bug#33317872 Incorrect Index selected leading to slower execution of queries
842 --echo #
843 --echo # When choosing between a Multi-range-read on an unique index,
844 --echo # and a range scan on an ordered index, where few rows are expected
845 --echo # to be returned, prefer the unique index.
846 --echo # (An ordered index scan has higher cost, as all fragments are scanned)
847 --echo #
848
849 CREATE TABLE t (
850 delivery_id bigint unsigned NOT NULL AUTO_INCREMENT,
851 msg_id int unsigned NOT NULL,
852 auth_login_type char(1) DEFAULT NULL,
853 auth_login_id varchar(64),
854 PRIMARY KEY (delivery_id),
855 UNIQUE KEY msg_id(msg_id,auth_login_type,auth_login_id),
856 KEY auth_login_id(auth_login_id)
857 ) ENGINE=ndbcluster;
858
859 disable_query_log;
860
861 insert into t (msg_id, auth_login_type, auth_login_id) values
862 (4866, '5', '91774132'), (4869, '5', '91884132');
863 insert into t (msg_id, auth_login_type, auth_login_id) values
864 (4866, '5', '91774134'), (4869, '5', '91884134');
865
866 insert into t (msg_id, auth_login_type, auth_login_id) values
867 (4866, '4', '91774133'), (4869, '4', '91884133');
868 insert into t (msg_id, auth_login_type, auth_login_id) values
869 (4866, '6', '91774133'), (4869, '6', '91884133');
870
871 insert into t (msg_id, auth_login_type, auth_login_id) values
872 (4865, '5', '91774133'), (4868, '5', '91774133');
873 insert into t (msg_id, auth_login_type, auth_login_id) values
874 (4867, '5', '91774133'), (4870, '5', '91774133');
875 # ^^^ Add some more rows, none will match the query
876
877 # Use these as base for filling in more rows
878 insert into t (msg_id, auth_login_type, auth_login_id) select
879 msg_id - 4800, auth_login_type, auth_login_id+10 from t;
880
881 insert into t (msg_id, auth_login_type, auth_login_id) select
882 msg_id + 1000, auth_login_type, auth_login_id+20 from t where msg_id < 100;
883 insert into t (msg_id, auth_login_type, auth_login_id) select
884 msg_id + 2000, auth_login_type, auth_login_id+30 from t where msg_id < 100;
885 insert into t (msg_id, auth_login_type, auth_login_id) select
886 msg_id + 3000, auth_login_type, auth_login_id+40 from t where msg_id < 100;
887
888 insert into t (msg_id, auth_login_type, auth_login_id) select
889 msg_id + 5000, auth_login_type, auth_login_id+50 from t where msg_id < 100;
890 insert into t (msg_id, auth_login_type, auth_login_id) select
891 msg_id + 6000, auth_login_type, auth_login_id+60 from t where msg_id < 100;
892 insert into t (msg_id, auth_login_type, auth_login_id) select
893 msg_id + 7000, auth_login_type, auth_login_id+70 from t where msg_id < 100;
894
895
896 ##########################################
897 # Statistics is asynchronous: We can not predict when it
898 # is available in the statistics cache, and thus which
899 # statistics is being used for the query. Thus we
900 # need to cheat:
901 #
902 # We 'analyze' without the rows to be select yet inserted.
903 # -> statistics will predictable contain 0 rows to be returned.
904 # Which is 'rounded' up to 2 rows, which are the lowest number of
905 # rows allowed to be estimated from non unique indexes.
906 #
907 # Note that the estimated 2 rows is actually what _is_ in the table
908 # as well, when we later insert these rows below!
909
910 analyze table t;
911 enable_query_log;
912
913 ##########################################
914 # Then insert the rows we want returned, without being part of the statistics
915 insert into t (msg_id, auth_login_type, auth_login_id) values
916 (4866, '5', '81774133'),
917 (4869, '5', '81774133');
918 # ^^^^ Rows we want from the query
919
920 # optimizer trace, For debugging, or understanding of test case + bug:
921 #
922 # If enabled, we will find that optimizer first calculate the range-access
923 # cost, using handler ::cost methods, and correctly find the msg_id to
924 # have the lowest cost.
925 # Possible REF-accesses are then investigated and estimated: However, these
926 # cost estimates was 'pre-patch' based in an entirely different page/cache
927 # cost metric, not being compare compatible with the former. A lower cost
928 # was found for using REF access on auth_login_id index, incorrectly causing
929 # this index to to be used for the table access.
930 #
931 #SET optimizer_trace_max_mem_size=1048576; # 1MB
932 #SET end_markers_in_json=on;
933 #SET optimizer_trace="enabled=on,one_line=off";
934
935 explain format=tree
936 select * from t
937 where auth_login_type='5' AND auth_login_id = '81774133' AND msg_id IN (4866,4869);
938
939 #SELECT * FROM information_schema.optimizer_trace;
940
941 drop table t;