# Bug 88072
#   some forms of VARCHAR size extension are not metadata only
#   when they should be
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS tc;
# without the patch index is rebuilt whereas it should not be
CREATE TABLE t1 (fld1 VARCHAR(3), KEY idx1(fld1)) ENGINE=InnoDB
row_format=dynamic;
CREATE TABLE tc (p2 int unsigned not null,
p1 VARCHAR(4) character set latin1 not null, 
v1 VARCHAR(4) character set utf8mb4,
u1 VARCHAR(400) character set latin1 not null,
i1 BIGINT,
PRIMARY KEY (p1,p2),
KEY (v1,i1,u1),
KEY (v1(2),i1),
UNIQUE KEY (i1,u1),
UNIQUE KEY (u1)
) ENGINE=InnoDB row_format=compressed key_block_size=8;
INSERT INTO t1 (fld1) VALUES ('a'),('bc'),('def');
INSERT INTO tc VALUES (1,'zoo','zoo','well done',47);
INSERT INTO tc VALUES (3,'two','moo','not so well done',45);
INSERT INTO tc VALUES (9,'zoo','om','why not so well done',49);
INSERT INTO tc VALUES (9,'goo',null,'what not so well done',null);
SET debug="+d,innodb_index_drop_count_zero";
# VARCHAR(3) to VARCHAR(4) or similar, no other changes: index is not rebuilt
# without the patch, an error is reported and the test aborts
ALTER TABLE t1 MODIFY fld1 VARCHAR(4);
ALTER TABLE tc MODIFY p1 VARCHAR(8) character set latin1 not null, modify v1 VARCHAR(5) character set utf8mb4, modify u1 VARCHAR(500) character set latin1 not null;
ALTER TABLE tc MODIFY p1 VARCHAR(255) character set latin1 not null, modify v1 VARCHAR(6) character set utf8mb4, modify u1 VARCHAR(3000) character set latin1 not null;
SET debug="-d,innodb_index_drop_count_zero";
SET debug="+d,innodb_index_drop_count_one";
# change charset: index rebuilt
ALTER TABLE t1 MODIFY fld1 VARCHAR(5) character set utf8mb4;
ALTER TABLE t1 MODIFY fld1 VARCHAR(6) character set latin1;
# change engine: index rebuilt
ALTER TABLE t1 MODIFY fld1 VARCHAR(7) character set latin1, engine=MyISAM;
ALTER TABLE t1 MODIFY fld1 VARCHAR(8) character set latin1, engine=InnoDB;
# shrink VARCHAR: index rebuilt
ALTER TABLE t1 MODIFY fld1 VARCHAR(7) character set latin1;
# extend VARCHAR too much: index rebuilt
ALTER TABLE t1 MODIFY fld1 VARCHAR(2000) character set latin1;
# shrink one more time: index rebuilt
ALTER TABLE t1 MODIFY fld1 VARCHAR(26) character set latin1;
SET debug="-d,innodb_index_drop_count_one";
SET debug="+d,innodb_index_drop_count_zero";
# border value test: 255: no rebuild
ALTER TABLE t1 MODIFY fld1 VARCHAR(255) character set latin1;
SET debug="-d,innodb_index_drop_count_zero";
SET debug="+d,innodb_index_drop_count_one";
# border value test: 256: rebuild
ALTER TABLE t1 MODIFY fld1 VARCHAR(256) character set latin1;
SET debug="-d,innodb_index_drop_count_one";
SET debug="+d,innodb_index_drop_count_zero";
# two byte length extension: no rebuild
ALTER TABLE t1 MODIFY fld1 VARCHAR(257) character set latin1;
ALTER TABLE tc MODIFY p1 VARCHAR(255) character set latin1 not null, modify v1 VARCHAR(14) character set utf8mb4, modify u1 VARCHAR(3001) character set latin1 not null;
UPDATE t1 SET fld1 = REPEAT('a',257);
UPDATE tc SET v1 = REPEAT('v',14);
ALTER TABLE t1 MODIFY fld1 VARCHAR(3000) character set latin1;
ALTER TABLE t1 MODIFY fld1 VARCHAR(3072) character set latin1;
SET debug="-d,innodb_index_drop_count_zero";
# cleanup
DROP TABLE t1;
DROP TABLE tc;