163 lines
5.9 KiB
Plaintext
163 lines
5.9 KiB
Plaintext
DROP TABLE IF EXISTS t1;
|
|
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=XENGINE;
|
|
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
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (30, 31, 32, 33, 34, 30, 30, 30);
|
|
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;
|
|
COMMIT;
|
|
COMMIT;
|
|
BEGIN;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
17
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
|
|
# 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
|
|
COMMIT;
|
|
# 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);
|
|
Got one of the listed errors
|
|
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
|
|
DROP TABLE t1;
|