1--source include/have_rocksdb.inc 2--source include/have_debug.inc 3 4# test adding duplicate value before unique index 5CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; 6INSERT INTO t1 (a, b) VALUES (1, 5); 7INSERT INTO t1 (a, b) VALUES (2, 6); 8INSERT INTO t1 (a, b) VALUES (3, 7); 9 10INSERT INTO t1 (a,b) VALUES (4,5); 11 12# should cause error here, duplicate value on b 13--error ER_DUP_ENTRY 14ALTER TABLE t1 ADD UNIQUE INDEX kb(b), ALGORITHM=INPLACE; 15 16SHOW CREATE TABLE t1; 17DROP TABLE t1; 18 19# test dup value AFTER unique index 20CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; 21INSERT INTO t1 (a, b) VALUES (1, 5); 22INSERT INTO t1 (a, b) VALUES (2, 6); 23INSERT INTO t1 (a, b) VALUES (3, 7); 24ALTER TABLE t1 ADD UNIQUE INDEX kb(b), ALGORITHM=INPLACE; 25 26# should error here, duplicate value on b 27--error ER_DUP_ENTRY 28INSERT INTO t1 (a,b) VALUES (4,5); 29 30# should succeed 31INSERT INTO t1 (a,b) VALUES (5,8); 32 33SHOW CREATE TABLE t1; 34SELECT * FROM t1 FORCE INDEX(kb); 35DROP TABLE t1; 36 37# test what happens when duplicate nulls exist 38CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; 39INSERT INTO t1 (a, b) VALUES (1, 5); 40INSERT INTO t1 (a, b) VALUES (2, NULL); 41INSERT INTO t1 (a, b) VALUES (3, NULL); 42 43# should pass, because in MySQL we allow multiple NULLS in unique key 44ALTER TABLE t1 ADD UNIQUE INDEX kb(b), ALGORITHM=INPLACE; 45INSERT INTO t1 (a, b) VALUES (4, NULL); 46 47SHOW CREATE TABLE t1; 48SELECT COUNT(*) FROM t1 FORCE INDEX(kb); 49DROP TABLE t1; 50 51## test case with multi-part key with nulls 52CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; 53INSERT INTO t1 (a,b,c) VALUES (1,1,NULL); 54INSERT INTO t1 (a,b,c) VALUES (2,1,NULL); 55INSERT INTO t1 (a,b,c) VALUES (3,1,NULL); 56INSERT INTO t1 (a,b,c) VALUES (4,1,5); 57 58# should pass 59ALTER TABLE t1 ADD UNIQUE INDEX kbc(b,c), ALGORITHM=INPLACE; 60 61SHOW CREATE TABLE t1; 62SELECT COUNT(*) FROM t1 FORCE INDEX(kbc); 63DROP TABLE t1; 64 65## test case with table w/ no primary key, and we try to add unique key 66CREATE TABLE t1 (a INT, b INT) ENGINE=RocksDB; 67INSERT INTO t1 (a, b) VALUES (1, 5); 68INSERT INTO t1 (a, b) VALUES (2, 6); 69INSERT INTO t1 (a, b) VALUES (3, 7); 70 71# should succeed, we can add unique index on table w/ no pk 72ALTER TABLE t1 ADD UNIQUE INDEX kb(b); 73INSERT INTO t1 (a, b) VALUES (4, 8); 74--error ER_DUP_ENTRY 75INSERT INTO t1 (a, b) VALUES (5, 5); 76 77SHOW CREATE TABLE t1; 78DROP TABLE t1; 79 80 81# Test changing key with same name and parts from not unique to unique, should 82# fail. 83CREATE TABLE t1 ( 84a INT PRIMARY KEY, 85b INT, 86c INT, 87KEY kbc(b,c)) ENGINE = ROCKSDB; 88INSERT INTO t1 (a,b,c) VALUES (1,1,1); 89INSERT INTO t1 (a,b,c) VALUES (2,2,2); 90INSERT INTO t1 (a,b,c) VALUES (3,2,2); 91 92# should cause error here, duplicate value 2-2 93--error ER_DUP_ENTRY 94ALTER TABLE t1 DROP INDEX kbc, ADD UNIQUE INDEX kbc(b,c), ALGORITHM=INPLACE; 95 96DROP TABLE t1; 97