246 lines
8.5 KiB
Plaintext
246 lines
8.5 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) ,
|
|
UNIQUE INDEX (id2, id1) ,
|
|
UNIQUE INDEX (id2, id3, id4) ,
|
|
INDEX (id1) ,
|
|
INDEX (id3, id1) ,
|
|
UNIQUE INDEX(id5) ,
|
|
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;
|
|
#
|
|
# Issue #88: Creating unique index over column with duplicate values succeeds
|
|
#
|
|
create table t1 (pk int primary key, a int) engine=xengine;
|
|
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=XENGINE PARTITION BY KEY (a) PARTITIONS 16;
|
|
ERROR 42000: Create partitioned table is not supported yet in xengine.
|
|
CREATE TABLE t2 (pk int, a int, PRIMARY KEY (pk, a), UNIQUE KEY (a)) ENGINE=XENGINE;
|
|
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=xengine;
|
|
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=xengine;
|
|
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=xengine;
|
|
INSERT INTO t VALUES('a');
|
|
CHECK TABLE t EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
DROP TABLE t;
|
|
#
|
|
# https://workitem.aone.alibaba-inc.com/project/573930/issue/13901272
|
|
# for duplicate key,create index
|
|
create table tt(id int primary key , c1 varchar(100)) character set utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
insert into tt values(1,'a');
|
|
insert into tt values(2,'a');
|
|
alter table tt add unique index idx_c1(c1);
|
|
ERROR 23000: Duplicate entry 'a' for key 'idx_c1'
|
|
show create table tt;
|
|
Table Create Table
|
|
tt CREATE TABLE `tt` (
|
|
`id` int(11) NOT NULL,
|
|
`c1` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8
|
|
drop table tt;
|
|
create table tt2(id int primary key , c1 varchar(100)) character set utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
insert into tt2 values(1,'a');
|
|
insert into tt2 values(2,'b');
|
|
alter table tt2 add unique index idx_c1(c1);
|
|
show create table tt2;
|
|
Table Create Table
|
|
tt2 CREATE TABLE `tt2` (
|
|
`id` int(11) NOT NULL,
|
|
`c1` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_c1` (`c1`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8
|
|
select * from tt2;
|
|
id c1
|
|
1 a
|
|
2 b
|
|
drop table tt2;
|