release/src/router/mysql/mysql-test/suite/ndb/t/ndb_index_unique.test

   1 -- source include/have_ndb.inc
   2 -- source include/not_embedded.inc
   3
   4 --disable_warnings
   5 drop table if exists t1, t2, t3, t4, t5, t6, t7, t8;
   6 --enable_warnings
   7
   8 #
   9 # Simple test to show use of UNIQUE indexes
  10 #
  11
  12 CREATE TABLE t1 (
  13   a int NOT NULL PRIMARY KEY,
  14   b int not null,
  15   c int,
  16   UNIQUE ib(b)
  17 ) engine=ndbcluster;
  18
  19 insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
  20 select * from t1 order by b;
  21 select * from t1 where b = 4 order by b;
  22 insert into t1 values(7,8,3);
  23 select * from t1 where b = 4 order by a;
  24
  25 -- error ER_DUP_ENTRY
  26 insert into t1 values(8, 2, 3);
  27 select * from t1 order by a;
  28 delete from t1 where a = 1;
  29 insert into t1 values(8, 2, 3);
  30 select * from t1 order by a;
  31
  32 alter table t1 drop index ib;
  33 insert into t1 values(1, 2, 3);
  34 # Bug# #18129
  35 --error 1169
  36 create unique index ib on t1(b);
  37
  38 drop table t1;
  39
  40 #
  41 # Indexing NULL values
  42 #
  43
  44 CREATE TABLE t1 (
  45   a int unsigned NOT NULL PRIMARY KEY,
  46   b int unsigned,
  47   c int unsigned,
  48   UNIQUE bc(b,c)
  49 ) engine = ndb;
  50
  51 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
  52 select * from t1 use index (bc) where b IS NULL order by a;
  53
  54 select * from t1 use index (bc)order by a;
  55 select * from t1 use index (bc) order by a;
  56 select * from t1 use index (PRIMARY) where b IS NULL order by a;
  57 select * from t1 use index (bc) where b IS NULL order by a;
  58 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
  59 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
  60 select * from t1 use index (bc) where b < 4 order by a;
  61 select * from t1 use index (bc) where b IS NOT NULL order by a;
  62 -- error ER_DUP_ENTRY
  63 insert into t1 values(5,1,1);
  64 drop table t1;
  65
  66
  67 #
  68 # Show use of UNIQUE USING HASH indexes
  69 #
  70
  71 CREATE TABLE t2 (
  72   a int unsigned NOT NULL PRIMARY KEY,
  73   b int unsigned not null,
  74   c int unsigned not null,
  75   UNIQUE (b, c) USING HASH
  76 ) engine=ndbcluster;
  77
  78 insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
  79 select * from t2 where a = 3;
  80 select * from t2 where b = 4;
  81 select * from t2 where c = 6;
  82 insert into t2 values(7,8,3);
  83 select * from t2 where b = 4 order by a;
  84
  85 -- error ER_DUP_ENTRY
  86 insert into t2 values(8, 2, 3);
  87 select * from t2 order by a;
  88 delete from t2 where a = 1;
  89 insert into t2 values(8, 2, 3);
  90 select * from t2 order by a;
  91
  92 # Bug #24818 CREATE UNIQUE INDEX (...) USING HASH on a NDB table crashes mysqld
  93 create unique index bi using hash on t2(b);
  94 -- error ER_DUP_ENTRY
  95 insert into t2 values(9, 3, 1);
  96 alter table t2 drop index bi;
  97 insert into t2 values(9, 3, 1);
  98 select * from t2 order by a;
  99
 100 drop table t2;
 101
 102 CREATE TABLE t2 (
 103   a int unsigned NOT NULL PRIMARY KEY,
 104   b int unsigned not null,
 105   c int unsigned,
 106   UNIQUE (b, c) USING HASH
 107 ) engine=ndbcluster;
 108
 109
 110 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);
 111
 112 select * from t2 where c IS NULL order by a;
 113 select * from t2 where b = 3 AND c IS NULL order by a;
 114 select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
 115 set @old_ecpd = @@session.engine_condition_pushdown;
 116 set engine_condition_pushdown = true;
 117 explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
 118 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
 119 set engine_condition_pushdown = @old_ecpd;
 120
 121 drop table t2;
 122
 123 #
 124 # Show use of PRIMARY KEY USING HASH indexes
 125 #
 126
 127 CREATE TABLE t3 (
 128   a int unsigned NOT NULL,
 129   b int unsigned not null,
 130   c int unsigned,
 131   PRIMARY KEY (a, b) USING HASH
 132 ) engine=ndbcluster;
 133
 134 insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
 135 select * from t3 where a = 3;
 136 select * from t3 where b = 4;
 137 select * from t3 where c = 6;
 138 insert into t3 values(7,8,3);
 139 select * from t3 where b = 4 order by a;
 140
 141 drop table t3;
 142
 143 #
 144 # Indexes on NULL-able columns
 145 #
 146
 147 CREATE TABLE t1 (
 148   pk int NOT NULL PRIMARY KEY,
 149   a int unsigned,
 150   UNIQUE KEY (a)
 151 ) engine=ndbcluster;
 152
 153 insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
 154
 155 select * from t1 order by pk;
 156
 157 --error ER_DUP_ENTRY
 158 insert into t1 values (5,0);
 159 select * from t1 order by pk;
 160 delete from t1 where a = 0;
 161 insert into t1 values (5,0);
 162 select * from t1 order by pk;
 163
 164 CREATE TABLE t2 (
 165   pk int NOT NULL PRIMARY KEY,
 166   a int unsigned,
 167   b tinyint NOT NULL,
 168   c VARCHAR(10),
 169   UNIQUE KEY si(a, c)
 170 ) engine=ndbcluster;
 171
 172 insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
 173
 174 select * from t2 order by pk;
 175
 176 --error ER_DUP_ENTRY
 177 insert into t2 values(2,3,19,'abc');
 178 select * from t2 order by pk;
 179 delete from t2 where c IS NOT NULL;
 180 insert into t2 values(2,3,19,'abc');
 181 select * from t2 order by pk;
 182
 183 drop table t1, t2;
 184
 185 #
 186 # More complex tables
 187 #
 188
 189 CREATE TABLE t1 (
 190   cid smallint(5) unsigned NOT NULL default '0',
 191   cv varchar(250) NOT NULL default '',
 192   PRIMARY KEY  (cid),
 193   UNIQUE KEY cv (cv)
 194 ) engine=ndbcluster;
 195 INSERT INTO t1 VALUES (8,'dummy');
 196 CREATE TABLE t2 (
 197   cid bigint(20) unsigned NOT NULL auto_increment,
 198   cap varchar(255) NOT NULL default '',
 199   PRIMARY KEY  (cid),
 200   UNIQUE KEY (cid, cap)
 201 ) engine=ndbcluster;
 202 INSERT INTO t2 VALUES (NULL,'another dummy');
 203 CREATE TABLE t3 (
 204   gid bigint(20) unsigned NOT NULL auto_increment,
 205   gn varchar(255) NOT NULL default '',
 206   must tinyint(4) default NULL,
 207   PRIMARY KEY  (gid)
 208 ) engine=ndbcluster;
 209 INSERT INTO t3 VALUES (1,'V1',NULL);
 210 CREATE TABLE t4 (
 211   uid bigint(20) unsigned NOT NULL default '0',
 212   gid bigint(20) unsigned NOT NULL,
 213   rid bigint(20) unsigned NOT NULL,
 214   cid bigint(20) unsigned NOT NULL,
 215   UNIQUE KEY m (uid,gid,rid,cid)
 216 ) engine=ndbcluster;
 217 INSERT INTO t4 VALUES (1,1,2,4);
 218 INSERT INTO t4 VALUES (1,1,2,3);
 219 INSERT INTO t4 VALUES (1,1,5,7);
 220 INSERT INTO t4 VALUES (1,1,10,8);
 221 CREATE TABLE t5 (
 222   rid bigint(20) unsigned NOT NULL auto_increment,
 223   rl varchar(255) NOT NULL default '',
 224   PRIMARY KEY  (rid)
 225 ) engine=ndbcluster;
 226 CREATE TABLE t6 (
 227   uid bigint(20) unsigned NOT NULL auto_increment,
 228   un varchar(250) NOT NULL default '',
 229   uc smallint(5) unsigned NOT NULL default '0',
 230   PRIMARY KEY  (uid),
 231   UNIQUE KEY nc (un,uc)
 232 ) engine=ndbcluster;
 233 INSERT INTO t6 VALUES (1,'test',8);
 234 INSERT INTO t6 VALUES (2,'test2',9);
 235 INSERT INTO t6 VALUES (3,'tre',3);
 236 CREATE TABLE t7 (
 237   mid bigint(20) unsigned NOT NULL PRIMARY KEY,
 238   uid bigint(20) unsigned NOT NULL default '0',
 239   gid bigint(20) unsigned NOT NULL,
 240   rid bigint(20) unsigned NOT NULL,
 241   cid bigint(20) unsigned NOT NULL,
 242   UNIQUE KEY m (uid,gid,rid,cid)
 243 ) engine=ndbcluster;
 244 INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
 245 INSERT INTO t7 VALUES(2, 2, 1, 1, 1);
 246 INSERT INTO t7 VALUES(3, 3, 1, 1, 1);
 247 INSERT INTO t7 VALUES(4, 4, 1, 1, 1);
 248 INSERT INTO t7 VALUES(5, 5, 1, 1, 1);
 249 INSERT INTO t7 VALUES(6, 1, 1, 1, 6);
 250 INSERT INTO t7 VALUES(7, 2, 1, 1, 7);
 251 INSERT INTO t7 VALUES(8, 3, 1, 1, 8);
 252 INSERT INTO t7 VALUES(9, 4, 1, 1, 9);
 253 INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
 254
 255 select * from t1 where cv = 'dummy';
 256 select * from t1 where cv = 'test';
 257 select * from t2 where cap = 'another dummy';
 258 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
 259 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
 260 select * from t4 where uid = 1 order by cid;
 261 select * from t4 where rid = 2 order by cid;
 262 select * from t6 where un='test' and uc=8;
 263 select * from t6 where un='test' and uc=7;
 264 select * from t6 where un='test';
 265 select * from t7 where mid = 8;
 266 select * from t7 where uid = 8;
 267 select * from t7 where uid = 1 order by mid;
 268 select * from t7 where uid = 4 order by mid;
 269 select * from t7 where gid = 4;
 270 select * from t7 where gid = 1 order by mid;
 271 select * from t7 where cid = 4;
 272 select * from t7 where cid = 8;
 273
 274 #
 275 # insert more records into t4
 276 #
 277 let $1=100;
 278 disable_query_log;
 279 while ($1)
 280 {
 281  eval insert into t4 values(1, $1, 5, 12);
 282  eval insert into t4 values($1, 3, 9, 11);
 283  dec $1;
 284 }
 285 enable_query_log;
 286
 287 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
 288 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
 289 select * from t4 where uid = 1 order by gid,cid;
 290 select * from t4 where uid = 1 order by gid,cid;
 291 select * from t4 where rid = 2 order by cid;
 292
 293
 294 drop table t1,t2,t3,t4,t5,t6,t7;
 295
 296 # test null in indexes
 297 CREATE TABLE t1 (
 298    a int unsigned NOT NULL PRIMARY KEY,
 299    b int unsigned,
 300    c int unsigned,
 301    UNIQUE bc(b,c) ) engine = ndb;
 302
 303 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
 304 select * from t1 where b=1 and c=1;
 305 select * from t1 where b is null and c is null;
 306 select * from t1 where b is null and c = 2;
 307 select * from t1 where b = 4 and c is null;
 308 create table t8 as
 309 select * from t1 where (b = 1 and c = 1)
 310                     or (b is null and c is null)
 311                     or (b is null and c = 2)
 312                     or (b = 4 and c is null);
 313 select * from t8 order by a;
 314 select * from t1 order by a;
 315 drop table t1, t8;
 316
 317 ###############################
 318 # Bug 8101
 319 #
 320 # Unique index not specified in the same order as in table
 321 #
 322
 323 create table t1(
 324         id integer not null auto_increment,
 325         month integer not null,
 326         year integer not null,
 327         code varchar( 2) not null,
 328         primary key ( id),
 329         unique idx_t1( month, code, year)
 330 ) engine=ndb;
 331
 332 INSERT INTO t1 (month, year, code) VALUES (4,2004,'12');
 333 INSERT INTO t1 (month, year, code) VALUES (5,2004,'12');
 334
 335 select * from t1 where code = '12' and month = 4 and year = 2004 ;
 336
 337 drop table t1;
 338
 339 # bug#15918 Unique Key Limit in NDB Engine
 340
 341 create table t1 (a int primary key, b varchar(1000) not null, unique key (b))
 342 engine=ndb charset=utf8;
 343
 344 insert into t1 values (1, repeat(_utf8 0xe288ab6474, 200));
 345 --error ER_DUP_ENTRY
 346 insert into t1 values (2, repeat(_utf8 0xe288ab6474, 200));
 347 select a, sha1(b) from t1;
 348
 349 # perl -e 'print pack("H2000","e288ab6474"x200)' | sha1sum
 350
 351 drop table t1;
 352
 353 # bug#21873 MySQLD Crash on ALTER...ADD..UNIQUE..USING HASH statement for NDB backed table
 354
 355 create table t1(id int not null) engine = NDB;
 356
 357 alter table t1 add constraint uk_test unique (id) using hash;
 358
 359 drop table t1;
 360
 361 # End of 4.1 tests