Comment 14 for bug 1192898

A 5.1 testcase. Useful not only for this relatively minor bug, but for testing sibling prefetch on pessimistic B tree update code path too.

--source include/have_innodb_plugin.inc

CREATE TABLE t1 (a BIGINT UNSIGNED PRIMARY KEY,
                 b CHAR(255) NOT NULL,
                 c CHAR(255) NOT NULL,
                 d CHAR(255) NOT NULL,
                 e CHAR(255) NOT NULL) ENGINE=InnoDB;

SET @t1_space_id= (SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
                                WHERE
                                INFORMATION_SCHEMA.INNODB_SYS_TABLES.SCHEMA='test' AND
                                INFORMATION_SCHEMA.INNODB_SYS_TABLES.NAME='T1');

# Fill the root leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (1, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (2, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (3, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (4, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (5, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (6, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (7, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (8, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (9, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (10, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (11, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (12, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (13, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (14, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));

SELECT COUNT(*) = 1 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES WHERE SPACE_ID=@t1_space_id AND PAGE_TYPE LIKE 'index';

# Split the roof leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (15, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));

SELECT COUNT(*) = 3 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES WHERE SPACE_ID=@t1_space_id AND PAGE_TYPE LIKE 'index';

# Fill the right leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (16, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (17, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (18, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (19, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (20, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (21, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));

SELECT COUNT(*) = 3 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES WHERE SPACE_ID=@t1_space_id AND PAGE_TYPE LIKE 'index';

# Trigger bug 1192898: pessimistic descent to the right leaf.
SET innodb_fake_changes=1;
--error ER_ERROR_DURING_COMMIT
INSERT INTO t1 (a, b, c, d, e) VALUES (22, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
SET innodb_fake_changes=0;

DROP TABLE t1;