#
# Full-column secondary index on an externally stored clustered field
#
CREATE TABLE t1 (
id INT PRIMARY KEY,
c VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
p1 VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
p2 VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
p3 VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
p4 VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
p5 VARCHAR(2000) CHARACTER SET latin1 NOT NULL,
KEY k_c(c)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
SET @p = REPEAT('b', 2000);
INSERT INTO t1 VALUES (1, REPEAT('a', 2000), @p, @p, @p, @p, @p);
SELECT id, LEFT(p1, 1) AS p1_prefix
FROM t1 FORCE INDEX(k_c)
WHERE c = REPEAT('a', 2000);
id	p1_prefix
1	b
#
# READ UNCOMMITTED forces the secondary-to-clustered consistency check
#
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT id, LEFT(p1, 1) AS p1_prefix
FROM t1 FORCE INDEX(k_c)
WHERE c = REPEAT('a', 2000);
id	p1_prefix
1	b
#
# READ COMMITTED current clustered version control
#
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT id, LEFT(p1, 1) AS p1_prefix
FROM t1 FORCE INDEX(k_c)
WHERE c = REPEAT('a', 2000);
id	p1_prefix
1	b
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
#
# Old clustered version reached through the same full-column secondary
#
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT id
FROM t1
WHERE id = 1;
id
1
UPDATE t1
SET p5 = REPEAT('c', 2000)
WHERE id = 1;
SELECT id, LEFT(p1, 1) AS p1_prefix
FROM t1 FORCE INDEX(k_c)
WHERE c = REPEAT('a', 2000);
id	p1_prefix
1	b
COMMIT;
#
# READ COMMITTED old version while another transaction is uncommitted
#
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
START TRANSACTION;
UPDATE t1
SET p5 = REPEAT('d', 2000)
WHERE id = 1;
SELECT id, LEFT(p1, 1) AS p1_prefix
FROM t1 FORCE INDEX(k_c)
WHERE c = REPEAT('a', 2000);
id	p1_prefix
1	b
COMMIT;
ROLLBACK;
DROP TABLE t1;