1
drop table if exists t1, t2, t3, t4, t5, t6, t7, t8;
3
a int NOT NULL PRIMARY KEY,
8
insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
9
select * from t1 order by b;
17
select * from t1 where b = 4 order by b;
20
insert into t1 values(7,8,3);
21
select * from t1 where b = 4 order by a;
24
insert into t1 values(8, 2, 3);
25
ERROR 23000: Duplicate entry '2' for key 'ib'
26
select * from t1 order by a;
35
delete from t1 where a = 1;
36
insert into t1 values(8, 2, 3);
37
select * from t1 order by a;
46
alter table t1 drop index ib;
47
insert into t1 values(1, 2, 3);
48
create unique index ib on t1(b);
49
ERROR 23000: Can't write, because of unique constraint, to table 't1'
52
a int unsigned NOT NULL PRIMARY KEY,
57
insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
58
select * from t1 use index (bc) where b IS NULL order by a;
62
select * from t1 use index (bc)order by a;
68
select * from t1 use index (bc) order by a;
74
select * from t1 use index (PRIMARY) where b IS NULL order by a;
78
select * from t1 use index (bc) where b IS NULL order by a;
82
select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
85
select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
88
select * from t1 use index (bc) where b < 4 order by a;
91
select * from t1 use index (bc) where b IS NOT NULL order by a;
95
insert into t1 values(5,1,1);
96
ERROR 23000: Duplicate entry '1-1' for key 'bc'
99
a int unsigned NOT NULL PRIMARY KEY,
100
b int unsigned not null,
101
c int unsigned not null,
102
UNIQUE (b, c) USING HASH
104
insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
105
select * from t2 where a = 3;
108
select * from t2 where b = 4;
111
select * from t2 where c = 6;
114
insert into t2 values(7,8,3);
115
select * from t2 where b = 4 order by a;
118
insert into t2 values(8, 2, 3);
119
ERROR 23000: Duplicate entry '2-3' for key 'b'
120
select * from t2 order by a;
129
delete from t2 where a = 1;
130
insert into t2 values(8, 2, 3);
131
select * from t2 order by a;
140
create unique index bi using hash on t2(b);
141
insert into t2 values(9, 3, 1);
142
ERROR 23000: Duplicate entry '3' for key 'bi'
143
alter table t2 drop index bi;
144
insert into t2 values(9, 3, 1);
145
select * from t2 order by a;
157
a int unsigned NOT NULL PRIMARY KEY,
158
b int unsigned not null,
160
UNIQUE (b, c) USING HASH
163
Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
164
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);
165
select * from t2 where c IS NULL order by a;
173
select * from t2 where b = 3 AND c IS NULL order by a;
178
select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
184
set @old_ecpd = @@session.engine_condition_pushdown;
185
set engine_condition_pushdown = true;
186
explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
187
id select_type table type possible_keys key key_len ref rows Extra
188
1 SIMPLE t2 range PRIMARY,b PRIMARY 4 NULL 1 Using where with pushed condition
189
select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
194
set engine_condition_pushdown = @old_ecpd;
197
a int unsigned NOT NULL,
198
b int unsigned not null,
200
PRIMARY KEY (a, b) USING HASH
202
insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
203
select * from t3 where a = 3;
206
select * from t3 where b = 4;
209
select * from t3 where c = 6;
212
insert into t3 values(7,8,3);
213
select * from t3 where b = 4 order by a;
218
pk int NOT NULL PRIMARY KEY,
222
insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
223
select * from t1 order by pk;
231
insert into t1 values (5,0);
232
ERROR 23000: Duplicate entry '0' for key 'a'
233
select * from t1 order by pk;
241
delete from t1 where a = 0;
242
insert into t1 values (5,0);
243
select * from t1 order by pk;
252
pk int NOT NULL PRIMARY KEY,
258
insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
259
select * from t2 order by pk;
264
insert into t2 values(2,3,19,'abc');
265
ERROR 23000: Duplicate entry '3-abc' for key 'si'
266
select * from t2 order by pk;
271
delete from t2 where c IS NOT NULL;
272
insert into t2 values(2,3,19,'abc');
273
select * from t2 order by pk;
280
cid smallint(5) unsigned NOT NULL default '0',
281
cv varchar(250) NOT NULL default '',
285
INSERT INTO t1 VALUES (8,'dummy');
287
cid bigint(20) unsigned NOT NULL auto_increment,
288
cap varchar(255) NOT NULL default '',
290
UNIQUE KEY (cid, cap)
292
INSERT INTO t2 VALUES (NULL,'another dummy');
294
gid bigint(20) unsigned NOT NULL auto_increment,
295
gn varchar(255) NOT NULL default '',
296
must tinyint(4) default NULL,
299
INSERT INTO t3 VALUES (1,'V1',NULL);
301
uid bigint(20) unsigned NOT NULL default '0',
302
gid bigint(20) unsigned NOT NULL,
303
rid bigint(20) unsigned NOT NULL,
304
cid bigint(20) unsigned NOT NULL,
305
UNIQUE KEY m (uid,gid,rid,cid)
307
INSERT INTO t4 VALUES (1,1,2,4);
308
INSERT INTO t4 VALUES (1,1,2,3);
309
INSERT INTO t4 VALUES (1,1,5,7);
310
INSERT INTO t4 VALUES (1,1,10,8);
312
rid bigint(20) unsigned NOT NULL auto_increment,
313
rl varchar(255) NOT NULL default '',
317
uid bigint(20) unsigned NOT NULL auto_increment,
318
un varchar(250) NOT NULL default '',
319
uc smallint(5) unsigned NOT NULL default '0',
321
UNIQUE KEY nc (un,uc)
323
INSERT INTO t6 VALUES (1,'test',8);
324
INSERT INTO t6 VALUES (2,'test2',9);
325
INSERT INTO t6 VALUES (3,'tre',3);
327
mid bigint(20) unsigned NOT NULL PRIMARY KEY,
328
uid bigint(20) unsigned NOT NULL default '0',
329
gid bigint(20) unsigned NOT NULL,
330
rid bigint(20) unsigned NOT NULL,
331
cid bigint(20) unsigned NOT NULL,
332
UNIQUE KEY m (uid,gid,rid,cid)
334
INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
335
INSERT INTO t7 VALUES(2, 2, 1, 1, 1);
336
INSERT INTO t7 VALUES(3, 3, 1, 1, 1);
337
INSERT INTO t7 VALUES(4, 4, 1, 1, 1);
338
INSERT INTO t7 VALUES(5, 5, 1, 1, 1);
339
INSERT INTO t7 VALUES(6, 1, 1, 1, 6);
340
INSERT INTO t7 VALUES(7, 2, 1, 1, 7);
341
INSERT INTO t7 VALUES(8, 3, 1, 1, 8);
342
INSERT INTO t7 VALUES(9, 4, 1, 1, 9);
343
INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
344
select * from t1 where cv = 'dummy';
347
select * from t1 where cv = 'test';
349
select * from t2 where cap = 'another dummy';
352
select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
355
select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
357
select * from t4 where uid = 1 order by cid;
363
select * from t4 where rid = 2 order by cid;
367
select * from t6 where un='test' and uc=8;
370
select * from t6 where un='test' and uc=7;
372
select * from t6 where un='test';
375
select * from t7 where mid = 8;
378
select * from t7 where uid = 8;
380
select * from t7 where uid = 1 order by mid;
384
select * from t7 where uid = 4 order by mid;
388
select * from t7 where gid = 4;
390
select * from t7 where gid = 1 order by mid;
402
select * from t7 where cid = 4;
404
select * from t7 where cid = 8;
407
select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
410
select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
412
select * from t4 where uid = 1 order by gid,cid;
519
select * from t4 where uid = 1 order by gid,cid;
626
select * from t4 where rid = 2 order by cid;
630
drop table t1,t2,t3,t4,t5,t6,t7;
632
a int unsigned NOT NULL PRIMARY KEY,
635
UNIQUE bc(b,c) ) engine = ndb;
636
insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
637
select * from t1 where b=1 and c=1;
640
select * from t1 where b is null and c is null;
643
select * from t1 where b is null and c = 2;
646
select * from t1 where b = 4 and c is null;
650
select * from t1 where (b = 1 and c = 1)
651
or (b is null and c is null)
652
or (b is null and c = 2)
653
or (b = 4 and c is null);
654
select * from t8 order by a;
660
select * from t1 order by a;
668
id integer not null auto_increment,
669
month integer not null,
670
year integer not null,
671
code varchar( 2) not null,
673
unique idx_t1( month, code, year)
675
INSERT INTO t1 (month, year, code) VALUES (4,2004,'12');
676
INSERT INTO t1 (month, year, code) VALUES (5,2004,'12');
677
select * from t1 where code = '12' and month = 4 and year = 2004 ;
681
create table t1 (a int primary key, b varchar(1000) not null, unique key (b))
682
engine=ndb charset=utf8;
683
insert into t1 values (1, repeat(_utf8 0xe288ab6474, 200));
684
insert into t1 values (2, repeat(_utf8 0xe288ab6474, 200));
685
ERROR 23000: Duplicate entry '∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫d' for key 'b'
686
select a, sha1(b) from t1;
688
1 08f5d02c8b8bc244f275bdfc22c42c5cab0d9d7d
690
create table t1(id int not null) engine = NDB;
691
alter table t1 add constraint uk_test unique (id) using hash;