DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (id1 INT, id2 INT, id3 INT,
PRIMARY KEY (id1, id2, id3),
UNIQUE KEY (id3, id1)) ENGINE=XENGINE;
CREATE TABLE t2 (id1 INT, id2 INT, id3 INT,
PRIMARY KEY (id1, id2, id3),
UNIQUE KEY (id3, id1) COMMENT 'cf') ENGINE=XENGINE;
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 9;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	1	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	1	1
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 10;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	10	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	10	1
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 11;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	11	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	11	1
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 12;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	12	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	12	5
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 13;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	13	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	13	5
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 14;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	14	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	14	5
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 15;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	15	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	15	9
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 16;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	16	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	16	9
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 17;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t1;
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
SELECT * FROM t1 FORCE INDEX (id3);
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 9;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	1	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	1	1
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 10;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	10	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	10	1
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 11;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	11	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	11	1
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 12;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	12	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	12	5
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 13;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	13	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	13	5
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 14;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	14	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	14	5
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 15;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	15	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	15	9
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 16;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	16	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	16	9
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 17;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t2;
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
SELECT * FROM t2 FORCE INDEX (id3);
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (id1 varchar(128) CHARACTER SET latin1 COLLATE latin1_bin,
id2 varchar(256) CHARACTER SET utf8 COLLATE utf8_bin,
id3 varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
PRIMARY KEY (id1, id2, id3),
UNIQUE KEY (id3, id1)) ENGINE=XENGINE;
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.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
CREATE TABLE t2 (id1 varchar(128) CHARACTER SET latin1 COLLATE latin1_bin,
id2 varchar(256) CHARACTER SET utf8 COLLATE utf8_bin,
id3 varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
PRIMARY KEY (id1, id2, id3),
UNIQUE KEY (id3, id1) COMMENT 'cf') ENGINE=XENGINE;
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.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 9;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	1	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	1	1
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 10;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	10	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	10	1
INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 11;
SELECT * FROM t1 WHERE id1 = 1;
id1	id2	id3
1	11	1
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	11	1
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 12;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	12	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	12	5
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 13;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	13	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	13	5
INSERT INTO t1 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 14;
SELECT * FROM t1 WHERE id1 = 5;
id1	id2	id3
5	14	5
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	14	5
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 15;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	15	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	15	9
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 16;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	16	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	16	9
INSERT INTO t1 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 17;
SELECT * FROM t1 WHERE id1 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t1 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t1;
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
SELECT * FROM t1 FORCE INDEX (id3);
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 9;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	1	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	1	1
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 10;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	10	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	10	1
INSERT INTO t2 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 11;
SELECT * FROM t2 WHERE id1 = 1;
id1	id2	id3
1	11	1
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 1;
id1	id2	id3
1	11	1
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 12;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	12	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	12	5
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 13;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	13	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	13	5
INSERT INTO t2 VALUES (5, 5, 5) ON DUPLICATE KEY UPDATE id2 = 14;
SELECT * FROM t2 WHERE id1 = 5;
id1	id2	id3
5	14	5
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 5;
id1	id2	id3
5	14	5
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 15;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	15	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	15	9
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 16;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	16	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	16	9
INSERT INTO t2 VALUES (9, 9, 9) ON DUPLICATE KEY UPDATE id2 = 17;
SELECT * FROM t2 WHERE id1 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t2 FORCE INDEX (id3) WHERE id3 = 9;
id1	id2	id3
9	17	9
SELECT * FROM t2;
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
SELECT * FROM t2 FORCE INDEX (id3);
id1	id2	id3
1	11	1
2	2	2
3	3	3
4	4	4
5	14	5
6	6	6
7	7	7
8	8	8
9	17	9
DROP TABLE t1;
DROP TABLE t2;