EVOLUTION-MANAGER

Edit File: unique_sec_rev_cf.result

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) COMMENT 'rev:cf', UNIQUE INDEX (id2, id1) COMMENT 'rev:cf', UNIQUE INDEX (id2, id3, id4) COMMENT 'rev:cf', INDEX (id1) COMMENT 'rev:cf', INDEX (id3, id1) COMMENT 'rev:cf', UNIQUE INDEX(id5) COMMENT 'rev:cf', 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 40 40 40 40 40 40 40 40 39 31 32 NULL 39 37 37 37 38 31 32 NULL 38 37 37 37 37 31 32 NULL 37 37 37 37 30 31 32 33 34 30 30 30 22 20 20 NULL NULL 20 20 20 21 20 20 NULL NULL 20 20 20 20 20 20 NULL NULL 20 20 20 10 10 10 10 10 11 5 5 9 9 9 9 9 10 4 4 8 8 8 8 8 9 4 4 7 7 7 7 7 8 3 3 6 6 6 6 6 7 3 3 5 5 5 5 5 6 2 2 4 4 4 4 4 5 2 2 3 3 3 3 3 4 1 1 2 2 2 2 2 3 1 1 1 1 1 1 1 2 0 0 disconnect con1; disconnect con2; connection default; DROP TABLE t1;