-- source include/have_innodb.inc
# Embedded server tests do not support restarting.
-- source include/not_embedded.inc
--source include/test_db_charset_latin1.inc

let $MYSQLD_DATADIR= `select @@datadir`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;

# Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD
create table t1(a varchar(2) primary key) engine=innodb;
insert into t1 values('');
--enable_info
create index t1a1 on t1(a(1));
--disable_info
drop table t1;

create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
commit;
--error ER_DUP_KEYNAME
alter table t1 add index b (b), add index b (b);
--error ER_DUP_FIELDNAME
alter table t1 add index (b,b);
--enable_info
alter table t1 add index d2 (d);
--disable_info
show create table t1;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(d2) order by d;
select * from t1 force index (d2) order by d;
--error ER_DUP_ENTRY
alter table t1 add unique index (b);
show create table t1;
--enable_info
alter table t1 add index (b);
--disable_info
show create table t1;

--enable_info
alter table t1 add unique index (c), add index (d);
--disable_info
show create table t1;
analyze table t1;
explain select * from t1 force index(c) order by c;
--enable_info
alter table t1 add primary key (a), drop index c;
show create table t1;
--error ER_MULTIPLE_PRI_KEY
alter table t1 add primary key (c);
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key (b);
create unique index c on t1 (c);
--disable_info
show create table t1;
analyze table t1;
explain select * from t1 force index(c) order by c;
select * from t1 force index(c) order by c;
--enable_info
alter table t1 drop index b, add index (b);
--disable_info
show create table t1;
insert into t1 values(6,1,'ggg','ggg');
select * from t1;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
select * from t1 force index(d) order by d;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 force index(d) order by d;
show create table t1;
drop table t1;

create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
commit;
--enable_info
alter table t1 add index (c(2));
show create table t1;
alter table t1 add unique index (d(10));
show create table t1;
--disable_info
insert into t1 values(5,1,'ggg','ggg');
-- disable_result_log
analyze table t1;
-- enable_result_log
select * from t1;
select * from t1 force index(c) order by c;
select * from t1 force index(d) order by d;
explain select * from t1 order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 force index(d) order by d;
show create table t1;
--enable_info
alter table t1 drop index d;
--disable_info
insert into t1 values(8,9,'fff','fff');
select * from t1;
select * from t1 force index(c) order by c;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 order by d;
show create table t1;
drop table t1;

create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
commit;
--enable_info
alter table t1 add unique index (b,c);
--disable_info
insert into t1 values(8,9,'fff','fff');
select * from t1;
select * from t1 force index(b) order by b;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
show create table t1;
--enable_info
alter table t1 add index (b,c);
--disable_info
insert into t1 values(11,11,'kkk','kkk');
select * from t1;
select * from t1 force index(b) order by b;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
show create table t1;
--enable_info
alter table t1 add unique index (c,d);
--disable_info
insert into t1 values(13,13,'yyy','aaa');
select * from t1;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
explain select * from t1 force index(c) order by c;
show create table t1;
drop table t1;

create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
create table t2(a int not null, b int, c int, d int, e int,
foreign key (b) references t1(b) on delete set null,
foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
engine = innodb;
--error ER_DROP_INDEX_FK
alter table t1 drop index b;
--error ER_DROP_INDEX_FK
alter table t3 drop index c;
--error ER_DROP_INDEX_FK
alter table t4 drop index d;
--error ER_DROP_INDEX_FK
alter table t2 drop index b;
--error ER_DROP_INDEX_FK
alter table t2 drop index b, drop index c, drop index d;
--error ER_FK_COLUMN_NOT_NULL
alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
set @old_sql_mode = @@sql_mode;
set @@sql_mode = 'STRICT_TRANS_TABLES';
--error ER_FK_COLUMN_NOT_NULL
alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
set @@sql_mode = @old_sql_mode;

SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_COLUMN_CANNOT_DROP
alter table t2 DROP COLUMN b, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_DROP
alter table t2 DROP COLUMN b;
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
alter table t1 DROP COLUMN b, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
alter table t1 DROP COLUMN b;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

--enable_info
# Apparently, the following makes mysql_alter_table() drop index d.
create unique index dc on t2 (d,c);
create index dc on t1 (b,c);
# This should preserve the foreign key constraints.
--disable_info
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
set @@sql_mode = 'STRICT_TRANS_TABLES';
--enable_info
--error ER_FK_COLUMN_NOT_NULL
alter table t2 add primary key (alpha), change a alpha int,
change b beta int not null, change c charlie int not null;
--error ER_FK_COLUMN_NOT_NULL
alter table t2 add primary key (alpha), change a alpha int,
change c charlie int not null, change d delta int not null;
alter table t2 add primary key (alpha), change a alpha int,
change b beta int, modify c int not null;
--disable_info
set @@sql_mode = @old_sql_mode;
insert into t1 values (1,1,1);
insert into t3 values (1,1,1);
insert into t4 values (1,1,1);
insert into t2 values (1,1,1,1,1);
commit;
--enable_info
alter table t4 add constraint dc foreign key (a) references t1(a);
--disable_info
show create table t4;
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
# a foreign key 'test/dc' already exists
--error ER_CANT_CREATE_TABLE
alter table t3 add constraint dc foreign key (a) references t1(a);
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_FAIL_ADD_SYSTEM
alter table t3 add constraint dc foreign key (a) references t1(a);
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
show create table t3;
--enable_info
alter table t2 drop index b, add index (beta);
--disable_info
show create table t2;
--error ER_ROW_IS_REFERENCED_2
delete from t1;
--error ER_CANT_DROP_FIELD_OR_KEY
drop index dc on t4;
--enable_info
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t3 drop foreign key dc;
alter table t4 drop foreign key dc;
--disable_info
select * from t2;
delete from t1;
select * from t2;

drop table t2,t4,t3,t1;

-- let charset = utf8
-- source include/innodb-index.inc

create table t1(a int not null, b int) engine = innodb;
insert into t1 values (1,1),(1,1),(1,1),(1,1);
--error ER_DUP_ENTRY
alter table t1 add unique index (a);
--error ER_DUP_ENTRY
alter table t1 add unique index (b);
--error ER_DUP_ENTRY
alter table t1 add unique index (a), add unique index(b);
show create table t1;
drop table t1;

create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
alter table t1 drop index c, drop index b;
show create table t1;
drop table t1;

create table t1(a int not null, b int, primary key(a)) engine = innodb;
alter table t1 add index (b);
show create table t1;
drop table t1;

create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
--error ER_DUP_ENTRY
alter table t1 add unique index (b), add unique index (c), add unique index (d);
--error ER_DUP_ENTRY
alter table t1 add unique index (c), add unique index (b), add index (d);
show create table t1;
drop table t1;

create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
--enable_info
alter table t1 add unique index (b);
--disable_info
insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
show create table t1;
check table t1;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(c) order by c;
explain select * from t1 order by a;
explain select * from t1 force index(b) order by b;
select * from t1 order by a;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
drop table t1;

create table t1(a int not null, b int not null) engine=innodb;
insert into t1 values (1,1);
--enable_info
alter table t1 add primary key(b);
--disable_info
insert into t1 values (2,2);
show create table t1;
check table t1;
select * from t1;
analyze table t1;
explain select * from t1;
explain select * from t1 order by a;
explain select * from t1 order by b;
checksum table t1;
drop table t1;

create table t1(a int not null) engine=innodb;
insert into t1 values (1);
--enable_info
alter table t1 add primary key(a);
--disable_info
insert into t1 values (2);
show create table t1;
check table t1;
commit;
select * from t1;
analyze table t1;
explain select * from t1;
explain select * from t1 order by a;
drop table t1;

#
# Test to check whether CREATE INDEX handles implicit foreign key
# constraint modifications (Issue #70, Bug #38786)
#
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE t1(
  c1	BIGINT(12) NOT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE t2(
  c1	BIGINT(16) NOT NULL,
  c2	BIGINT(12) NOT NULL,
  c3	BIGINT(12) NOT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3) REFERENCES t1(c1);

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

SHOW CREATE TABLE t2;

CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);

SHOW CREATE TABLE t2;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES(0,0,0);
INSERT INTO t1 VALUES(0);
INSERT INTO t2 VALUES(0,0,0);

DROP TABLE t2;

CREATE TABLE t2(
  c1	BIGINT(16) NOT NULL,
  c2	BIGINT(12) NOT NULL,
  c3	BIGINT(12) NOT NULL,
  PRIMARY KEY (c1,c2,c3)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3) REFERENCES t1(c1);
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SHOW CREATE TABLE t2;

CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);

SHOW CREATE TABLE t2;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES(0,0,1);
INSERT INTO t2 VALUES(0,0,0);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1;
DELETE FROM t2;

DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1(
  c1	BIGINT(12) NOT NULL,
  c2	INT(4) NOT NULL,
  PRIMARY KEY (c2,c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE t2(
  c1	BIGINT(16) NOT NULL,
  c2	BIGINT(12) NOT NULL,
  c3	BIGINT(12) NOT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=0;
--enable_info

# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);

--error ER_FK_INCORRECT_OPTION
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;

# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;

ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
SHOW CREATE TABLE t2;
--disable_info

DROP TABLE t2;
DROP TABLE t1;

connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t2i SELECT * FROM t1;
INSERT INTO t2c SELECT * FROM t1;
connection b;
BEGIN;
# This acquires a MDL lock on t1 until commit.
SELECT * FROM t1;
connection a;
# This times out before of the MDL lock held by connection b.
SET lock_wait_timeout=1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE INDEX t1a ON t1(a);
--enable_info
CREATE INDEX t2a ON t2(a);
--disable_info
set @old_sql_mode = @@sql_mode;
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
set @@sql_mode = 'STRICT_TRANS_TABLES';
--enable_info
ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
--disable_info
set @@sql_mode = @old_sql_mode;
--enable_info
ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
--disable_info
connection b;
# t2i and t2c are too new for this transaction, because they were rebuilt
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2i;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2c;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
# In t2, only the new index t2a is too new for this transaction.
SELECT * FROM t2;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2;
COMMIT;
# For a new transaction, all of t2, t2i, t2c are accessible.
SELECT * FROM t2;
SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2i;
SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2c;
SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
connection default;
disconnect a;
disconnect b;

--error ER_DUP_KEYNAME
alter table t2 add index t2a(b);
alter table t2 drop index t2a, add index t2a(b);
show create table t2;
show create table t2i;
show create table t2c;

--disable_info

DROP TABLE t1,t2,t2c,t2i;

CREATE TABLE t1 (c VARCHAR(1024),
c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL,
c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL,
c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL,
ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL,
cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL,
d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL,
d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL,
d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL,
d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL,
dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL,
UNIQUE KEY(c))
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES
(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''),
(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
--error ER_INDEX_COLUMN_TOO_LONG
ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace;
--error ER_INDEX_COLUMN_TOO_LONG
ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy;
SELECT COUNT(*) FROM t1;
CHECK TABLE t1;
DROP TABLE t1;

--echo #
--echo # Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH
--echo # IN I_S TABLES
--echo #
let $i_s_query=SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
cast(INDEX_LENGTH/@@innodb_page_size as int) I
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';

CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1;
eval $i_s_query;
--enable_info
ALTER TABLE t1 ADD INDEX (a);
--disable_info
eval $i_s_query;
--enable_info
ALTER TABLE t1 ADD INDEX (b);
--disable_info
eval $i_s_query;
DROP TABLE t1;

--echo #
--echo # Bug #17657223	EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
--echo #

SET GLOBAL innodb_monitor_enable = module_ddl;
let $innodb_metrics_select=
SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
subsystem = 'ddl' and count_reset > 0;

# Table with Blob data.
create table t1(f1 int not null, f2 blob)engine=innodb;
insert into t1 values(1, repeat('a',20000));
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 force;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
drop table t1;

# Table with small data.
create table t1(f1 int not null, f2 int not null,
primary key(f1))engine=innodb;
insert into t1 values(1,2), (3,4);
--echo # Add Secondary index.
--echo # Skip temp file usage due to small table size
--enable_info
alter table t1 add key(f2);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
drop table t1;

# Table with large data which is greater than sort buffer
create table t480(a serial)engine=innodb;
insert into t480
values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
(),(),(),(),(),(),(),();
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
create table t1(f1 int auto_increment not null,
		f2 char(200) not null, f3 char(200) not null,
		f4 char(200) not null,primary key(f1))engine=innodb;
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
select count(*) from t1;

--echo # Skip sort
--echo # Change PK from (f1) to (f1,f2,f3,f4)
--enable_info
alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;


# Temp file not used during clustered index rebuild
# for the following alter table commands.

--echo # Skip sort
--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
--enable_info
alter table t1 drop primary key,add column f5 int not null,
add column f6 int not null,add primary key(f1,f2,f5,f6);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f6;
--enable_info
alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same PK
--enable_info
alter table t1 add column f6 int;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 drop column f6;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Must sort
--echo # Change PK from (f1,f2,f5) to (f1,f5)
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f2;
--enable_info
alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 add column f2n int after f1, drop primary key, add
primary key (f1,f5,f2n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 change f5 f2n int not null,change f2n f5 int not null,
add column f8 int not null;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
--enable_info
alter table t1 add column f7 int, drop primary key,
add primary key (f1,f5,f7,f2n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 force;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 row_format=compact;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 engine=innodb;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Optimize table
--enable_info
optimize table t1;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Sort files used for adding secondary index
--enable_info
alter table t1 drop primary key, add primary key(f1,f5,f7), add index
i(f3);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # No sort files used for dropping secondary index
--enable_info
alter table t1 drop primary key, add primary key(f1,f5),drop index i;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Change PK(f1,f5) to (f1,added_columns) and drop f5
--enable_info
alter table t1 drop primary key, add primary key(f1,f12),
drop column f5, add column f12 int not null;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Must sort
--echo # Change PK(f1,f12) to (f1,existing_columns)
--enable_info
alter table t1 drop primary key, add primary key(f1,f3);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort
--echo # Change PK(f1,f3) to (f1,added_column,f3,added_column)
--enable_info
alter table t1 drop primary key, add column f3n int,
add column f4n int, add primary key(f1,f3n,f3,f4n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Adding Secondary index alone.
--enable_info
alter table t1 add key(f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Must sort
--echo # Change PK(f1,f3) to (existing_column,f1)
--enable_info
alter table t1 drop primary key, add primary key(f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort for PK.
--echo # Change PK(f4,f1) to (added_columns,f4,f1)
--echo # Secondary index rebuild happens
--enable_info
alter table t1 drop primary key, add column f5n int,
add column f6n int, add primary key(f5n,f6n,f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

--echo # Skip sort for PK.
--echo # Change PK(f5n,f6n,f4,f1) to
--echo # (added_columns,f5n,added_column,f6n,f4,f1)
--echo # Secondary index rebuild happens
--enable_info
alter table t1 drop primary key, add column f7n int,
add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;

SET GLOBAL innodb_monitor_disable = module_ddl;
select count(*) from t1;
drop table t1;
--disable_warnings
SET GLOBAL innodb_monitor_reset = default;
SET GLOBAL innodb_monitor_enable = default;
SET GLOBAL innodb_monitor_disable = default;
--enable_warnings

--echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
--echo # WHEN SKIPPING SORT FOR CLUSTER INDEX

# last mtuple in previous buffer and first mtuple in next buffer
#	are equal.
SELECT @@innodb_sort_buffer_size;
create table t1(f1 int auto_increment not null,
		f2 char(200) not null, f3 char(200) not null,
		f4 char(200) not null,primary key(f1,f2,f3,f4));
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 values(106, 'aaa','bbb','cccc');
select count(*) from t1;
--echo # Skip sort
--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key(f1,f2,f3);
select count(*) from t1;
drop table t1;

# Duplicates exist with in the buffer
create table t1(f1 int auto_increment not null,
		f2 char(200) not null, f3 char(200) not null,
		f4 char(200) not null,primary key(f1,f2,f3,f4));
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 values(108,'aaa','bbb','cccc');
select count(*) from t1;
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key(f1,f2,f3);
select count(*) from t1;
drop table t1, t480;

--echo #
--echo # Bug #19896922	SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
--echo #		FIELD IS CHANGED
--echo #

# Prefix length changes for the varchar column.
create table t1(a int not null, b varchar(30) not null,
		primary key (b(10), a)) engine = innodb;
insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
select * from t1;
alter table t1 drop primary key, add primary key (b(18),a);
select * from t1;
drop table t1;

create table t1(a int not null, b varchar(30) not null,
		primary key (b(10), a)) engine = innodb;
insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
select * from t1;
alter table t1 drop primary key, add primary key (b(8),a);
select * from t1;
drop table t1;


--echo #
--echo # Bug #21103101	SORTING SKIPPED WHEN DROPPING THE SINGLE
--echo #			COLUMN PRIMARY KEY
--echo #

# Drop primary key column.
create table t1(f1 int not null, f2 int not null,
		primary key (f1), unique key(f1, f2))engine=innodb;
insert into t1 values(1,3), (2,2);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f1;
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f1, drop primary key;
# DROP PRIMARY KEY is implied for a single-column PRIMARY KEY
alter table t1 drop column f1, drop key f1;
drop table t1;

# Drop Primary key when lock is none.
create table t1(f1 int not null, f2 int not null,
		primary key (f1), unique key(f1, f2))engine=innodb;
insert into t1 values(1,3), (2,2);
alter table t1 drop primary key, lock=none;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
alter table t1 drop index f1, algorithm=inplace, lock=none;
drop table t1;

--echo #
--echo #  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
--echo #		LAST COLUMN OF OLD PK
--echo #

# no skip sort cases
# pk(o1,o2) to pk(o1,o3), drop o2
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
drop table t1;

# pk(o1,o2) to pk(o3), drop o1, o2
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(o1,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(n1,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(o3,n1)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(2,2,1);
alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
drop table t1;

# pk(o1,o2) to pk(o2,o1)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2, o1), lock=none;
drop table t1;

# pk(o1,o2) to pk(o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2), lock=none;
drop table t1;

# pk(o1,o2) to pk(o2,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2,o3), lock=none;
drop table t1;

# pk(o2,o1) to pk(o2,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
insert into t1 values(1,1,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2,o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(o1,o3,o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
drop table t1;

# pk(o1,o2) to pk(o3,o1,o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
drop table t1;

# pk(o1,o2,o3) to pk(o1,o3)
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), lock=none;
drop table t1;

# pk(o1,o2,o3) to pk(o2,o3) by drop o1
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o1, lock=none;
alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
drop table t1;

# pk(o1,o2,o3) to pk(o1,o3) by drop o2
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o2, lock=none;
alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
drop table t1;

# pk(o1,o2,o3) to pk(o3) by drop o1,o2
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o1, drop o2, lock=none;
alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
drop table t1;

# no skip sort for prefix change
# pk(o1(2),o2) to pk(o1(3),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
drop table t1;

# pk(o1(2),o2) to pk(o1,o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1, o2), lock=none;
drop table t1;

# pk(o1(2),o2) to pk(o1(3),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
drop table t1;

# pk(o1(2),o2) to pk(o1,o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1, o3), lock=none;
drop table t1;

# pk(o1(3),o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
insert into t1 values('abc', 2, 1), ('abd', 1, 2);
alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
drop table t1;

# pk(o1,o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
insert into t1 values('abc', 2, 1), ('abd', 1, 2);
alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
drop table t1;

# pk(o1(3),o2) to pk(o1(2),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
insert into t1 values('abc', 2, 2), ('abd', 1, 1);
alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
drop table t1;

# pk(o1,o2) to pk(o1(2),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
insert into t1 values('abc', 2, 2), ('abd', 1, 1);
alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
drop table t1;

# pk(o1,o2(2),o3) to pk(o1,o2(3))
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
drop table t1;

# pk(o1,o2(2),o3) to pk(o1,o2)
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
drop table t1;

# pk(o1(3),o2(3)) to pk(o1(2),o2(3))
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
insert into t1 values('abc', 'acd'), ('abd', 'abd');
alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
drop table t1;

# pk(o1,o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
insert into t1 values('abc', 'acd'), ('abd', 'abd');
alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
drop table t1;

# pk(o1(3),o2(3)) to pk(o2(3),o1(3))
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
insert into t1 values('abd', 'acd'), ('acd', 'abd');
alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
drop table t1;

# pk(o1,o2) to pk(o2,o1)
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
insert into t1 values('abd', 'acd'), ('acd', 'abd');
alter table t1 drop primary key, add primary key(o2,o1), lock=none;
drop table t1;

# no skip sort cases
--source suite/innodb/include/alter_table_pk_no_sort.inc

--echo #
--echo # MDEV-15325 Incomplete validation of missing tablespace during recovery
--echo #

--source include/no_checkpoint_start.inc
CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB;

CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB;

--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1, t2;
--source include/no_checkpoint_end.inc

let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let $check_no_innodb=SELECT * FROM INFORMATION_SCHEMA.ENGINES
WHERE engine = 'innodb'
AND support IN ('YES', 'DEFAULT', 'ENABLED');

--echo # Wrong space_id in a dirty file and a missing file

--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t0.ibd
--move_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd

--source include/start_mysqld.inc
--eval $check_no_innodb
--source include/shutdown_mysqld.inc

--echo # Restore t1 and t2

--move_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
--move_file $MYSQLD_DATADIR/test/t0.ibd $MYSQLD_DATADIR/test/t1.ibd

--source include/start_mysqld.inc

SELECT * FROM t1;
SELECT * FROM t2;

DROP TABLE t1, t2;

--echo #
--echo #  MDEV-18186 assertion failure on missing InnoDB index
--echo #

--disable_query_log
call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary");
call mtr.add_suppression("InnoDB indexes are inconsistent with what defined");
call mtr.add_suppression("Table test/t contains 0 indexes");
call mtr.add_suppression("InnoDB could not find key no");
--enable_query_log

# Test an attempt to rename a nonexistent index inside InnoDB
-- let $MYSQL_DATA_DIR = `SELECT @@datadir`
CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
-- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_
DROP TABLE t;
CREATE TABLE t (a INT) ENGINE=INNODB;
-- remove_file $MYSQL_DATA_DIR/test/t.frm
-- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm
--enable_prepare_warnings
SHOW CREATE TABLE t;
--disable_prepare_warnings
DROP TABLE t;

--echo #
--echo #  MDEV-27374  InnoDB table becomes corrupt after renaming DESC-indexed column
--echo #
CREATE TABLE t1 (a VARCHAR(8), PRIMARY KEY(a DESC)) ENGINE=InnoDB;
ALTER TABLE t1 RENAME COLUMN a TO b, ALGORITHM=INPLACE;
--disable_cursor_protocol
SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id;
--enable_cursor_protocol
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
DROP TABLE t1;

--echo #
--echo #  MDEV-27432 ASC/DESC primary and unique keys cause index
--echo #	inconsistency between InnoDB and server
--echo #
CREATE TABLE t1 (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
ALTER TABLE t1 ADD PRIMARY KEY (id), ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
DROP TABLE t1;

--echo #
--echo #  MDEV-27445 Index inconsistency and assertion failure after
--echo #	renaming virtual column with DESC key
--echo #
CREATE TABLE t1(a INT, b INT AS (a), KEY(a, b DESC)) ENGINE=InnoDB;
ALTER TABLE t1 RENAME COLUMN IF EXISTS b TO v;
ALTER TABLE t1 FORCE;
DROP TABLE t1;

--echo #
--echo #  MDEV-27592 DESC primary index fails to set wide format
--echo #	while renaming the column
--echo #
CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(8), b INT, KEY(a DESC,b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'foo',10);
ALTER TABLE t1 RENAME COLUMN b TO c, ALGORITHM=INPLACE;
--disable_cursor_protocol
SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id ORDER BY INDEX_ID DESC LIMIT 1;
--enable_cursor_protocol
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
DROP TABLE t1;

--echo # End of 10.8 tests

--echo #
--echo # MDEV-35849: index records in a wrong order
--echo #

CREATE TABLE t (pk INT PRIMARY KEY, a VARCHAR(1), KEY (a DESC)) ENGINE=InnoDB;
INSERT INTO t VALUES (1,NULL),(2,'x');
CHECK TABLE t;
DROP TABLE t;

--echo # End of 11.8 tests

--disable_query_log

call mtr.add_suppression("InnoDB: Tablespace .* was not found at .*t[12].ibd.");
call mtr.add_suppression("InnoDB: Plugin initialization aborted");
call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed");
call mtr.add_suppression("Fewer engines are enabled now than were before the crash");

--enable_query_log
--source include/test_db_charset_restore.inc