DROP TABLE IF EXISTS t1;
connect  con1,localhost,root,,;
connect  con2,localhost,root,,;
connection con1;
CREATE TABLE t1 (id1 INT NOT NULL, id2 INT NOT NULL, id3 VARCHAR(32),
id4 INT, id5 VARCHAR(32),
value1 INT, value2 INT, value3 VARCHAR(32),
PRIMARY KEY (id1, id2)  , 
UNIQUE INDEX (id2, id1)  ,
UNIQUE INDEX (id2, id3, id4)  ,
INDEX (id1)  ,
INDEX (id3, id1)  ,
UNIQUE INDEX(id5)  , 
INDEX (id2, id5)) ENGINE=ROCKSDB;
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test inserting a key that returns duplicate error
INSERT INTO t1 VALUES (1, 1, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
INSERT INTO t1 VALUES (5, 5, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '5-5' for key 'PRIMARY'
INSERT INTO t1 VALUES (10, 10, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '10-10' for key 'PRIMARY'
INSERT INTO t1 VALUES (11, 1, 1, 1, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
INSERT INTO t1 VALUES (11, 5, 5, 5, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '5-5-5' for key 'id2_2'
INSERT INTO t1 VALUES (11, 10, 10, 10, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '10-10-10' for key 'id2_2'
INSERT INTO t1 VALUES (11, 11, 11, 11, 1, 11, 11, 11);
ERROR 23000: Duplicate entry '1' for key 'id5'
INSERT INTO t1 VALUES (11, 11, 11, 11, 5, 11, 11, 11);
ERROR 23000: Duplicate entry '5' for key 'id5'
INSERT INTO t1 VALUES (11, 11, 11, 11, 10, 11, 11, 11);
ERROR 23000: Duplicate entry '10' for key 'id5'
# Test updating a key that returns duplicate error
UPDATE t1 SET id2=1, id3=1, id4=1 WHERE id1=2;
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
UPDATE t1 SET id2=1, id3=1, id4=1;
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test updating a key to itself
UPDATE t1 set id2=id4;
UPDATE t1 set id5=id3, value1=value2;
UPDATE t1 set value3=value1;
# Test modifying values should not cause duplicates
UPDATE t1 SET value1=value3+1;
UPDATE t1 SET value3=value3 div 2;
UPDATE t1 SET value2=value3;
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test NULL values are considered unique
INSERT INTO t1 VALUES (20, 20, 20, NULL, NULL, 20, 20, 20);
INSERT INTO t1 VALUES (21, 20, 20, NULL, NULL, 20, 20, 20);
INSERT INTO t1 VALUES (22, 20, 20, NULL, NULL, 20, 20, 20);
SELECT COUNT(*) FROM t1;
COUNT(*)
13
# Adding multiple rows where one of the rows fail the duplicate
# check should fail the whole statement
INSERT INTO t1 VALUES (23, 23, 23, 23, 23, 23, 23, 23),
(24, 24, 24, 24, 24, 24, 24, 24),
(25, 10, 10, 10, 25, 25, 25, 25),
(26, 26, 26, 26, 26, 26, 26, 26);
ERROR 23000: Duplicate entry '10-10-10' for key 'id2_2'
SELECT COUNT(*) FROM t1;
COUNT(*)
13
connection con1;
BEGIN;
INSERT INTO t1 VALUES (30, 31, 32, 33, 34, 30, 30, 30);
connection con2;
BEGIN;
SELECT COUNT(*) FROM t1;
COUNT(*)
13
# Primary key should prevent duplicate on insert
INSERT INTO t1 VALUES (30, 31, 30, 30, 30, 30, 30, 30);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Primary key should prevent duplicate on update
UPDATE t1 SET id1=30, id2=31 WHERE id2=10;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Unique secondary key should prevent duplicate on insert
INSERT INTO t1 VALUES (31, 31, 32, 33, 30, 30, 30, 30);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (32, 32, 32, 32, 34, 32, 32, 32);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Unique secondary key should prevent duplicate on update
UPDATE t1 SET id2=31, id3=32, id4=33 WHERE id2=8;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t1 SET id5=34 WHERE id2=8;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Adding multiple rows where one of the rows fail the duplicate
# check should fail the whole statement
INSERT INTO t1 VALUES (35, 35, 35, 35, 35, 35, 35, 35),
(36, 36, 36, 36, 36, 36, 36, 36),
(37, 31, 32, 33, 37, 37, 37, 37),
(38, 38, 38, 38, 38, 38, 38, 38);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (35, 35, 35, 35, 35, 35, 35, 35),
(36, 36, 36, 36, 36, 36, 36, 36),
(37, 37, 37, 37, 34, 37, 37, 37),
(38, 38, 38, 38, 38, 38, 38, 38);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# NULL values are unique and duplicates in value fields are ignored
INSERT INTO t1 VALUES (37, 31, 32, NULL, 37, 37, 37, 37),
(38, 31, 32, NULL, 38, 37, 37, 37),
(39, 31, 32, NULL, 39, 37, 37, 37);
SELECT COUNT(*) FROM t1;
COUNT(*)
16
# Fail on duplicate key update for row added in our transaction
UPDATE t1 SET id5=37 WHERE id1=38;
ERROR 23000: Duplicate entry '37' for key 'id5'
# Fail on lock timeout for row modified in another transaction
UPDATE t1 SET id5=34 WHERE id1=38;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# NULL values are unique
UPDATE t1 SET id5=NULL WHERE value1 > 37;
connection con1;
COMMIT;
connection con2;
COMMIT;
connection con2;
BEGIN;
SELECT COUNT(*) FROM t1;
COUNT(*)
17
connection con1;
BEGIN;
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
connection con2;
# When transaction is pending, fail on lock acquisition
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (41, 40, 40, 40, 40, 40, 40, 40);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT COUNT(*) FROM t1;
COUNT(*)
17
connection con1;
COMMIT;
connection con2;
# When transaction is committed, fail on duplicate key
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
Got one of the listed errors
INSERT INTO t1 VALUES (41, 40, 40, 40, 40, 40, 40, 40);
ERROR 23000: Duplicate entry '40-40-40' for key 'id2_2'
ROLLBACK;
SELECT * FROM t1;
id1	id2	id3	id4	id5	value1	value2	value3
1	1	1	1	1	2	0	0
2	2	2	2	2	3	1	1
3	3	3	3	3	4	1	1
4	4	4	4	4	5	2	2
5	5	5	5	5	6	2	2
6	6	6	6	6	7	3	3
7	7	7	7	7	8	3	3
8	8	8	8	8	9	4	4
9	9	9	9	9	10	4	4
10	10	10	10	10	11	5	5
20	20	20	NULL	NULL	20	20	20
21	20	20	NULL	NULL	20	20	20
22	20	20	NULL	NULL	20	20	20
30	31	32	33	34	30	30	30
37	31	32	NULL	37	37	37	37
38	31	32	NULL	38	37	37	37
39	31	32	NULL	39	37	37	37
40	40	40	40	40	40	40	40
disconnect con1;
disconnect con2;
connection default;
DROP TABLE t1;
#
# Issue #88: Creating unique index over column with duplicate values succeeds
#
create table t1 (pk int primary key, a int) engine=rocksdb;
insert into t1 values 
(1, 1),
(2, 2),
(3, 3),
(4, 1),
(5, 5);
alter table t1 add unique(a);
ERROR 23000: Duplicate entry '1' for key 'a'
drop table t1;
#
# Issue #111
#
CREATE TABLE t2 (pk int, a int, PRIMARY KEY (pk, a), UNIQUE KEY (a)) ENGINE=ROCKSDB PARTITION BY KEY (a) PARTITIONS 16;
INSERT INTO t2 VALUES (1,1);
INSERT INTO t2 VALUES (1,1);
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
INSERT INTO t2 VALUES (2,1);
ERROR 23000: Duplicate entry '1' for key 'a'
DROP TABLE t2;
#
# Issue #491 (https://github.com/facebook/mysql-5.6/issues/491)
#
CREATE TABLE t (a BLOB, PRIMARY KEY(a(2)), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
DROP TABLE t;
CREATE TABLE t (a VARCHAR(255), PRIMARY KEY(a), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
DROP TABLE t;
CREATE TABLE t (a VARCHAR(255), PRIMARY KEY(a(2)), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
DROP TABLE t;