MDEV-11114 Cannot drop column referenced by CHECK constraint

SQL Standard behavior for DROP COLUMN xxx RESTRICT:
* If a constraint (UNIQUE or CHECK) uses only the dropped column,
  it's automatically dropped too. If it uses many columns - an error.

17 files changed, 121 insertions, 200 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 2455bc4f997..92df7bc577e 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -2171,3 +2171,54 @@ DROP TABLE t1;
 CREATE TABLE t1(a INT, b INT, CONSTRAINT min check (a>5),
 CONSTRAINT min check (b>5));
 ERROR HY000: Duplicate CHECK constraint name 'min'
+create table t1 (a int, b int, check(a>b));
+alter table t1 drop column a;
+ERROR 42S22: Unknown column 'a' in 'CHECK'
+alter table t1 drop column b, add column b bigint first;
+ERROR 42S22: Unknown column 'b' in 'CHECK'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (`a` > `b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a int, b int, check(a>0));
+alter table t1 drop column a;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a int, b int, check(a>0));
+alter table t1 drop column a, add column a bigint first;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` bigint(20) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a int, b int, c int, unique(a));
+alter table t1 drop column a;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a int, b int, c int, unique(a,b));
+alter table t1 drop column a;
+ERROR 42000: Key column 'a' doesn't exist in table
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result
index fcda0a84fcb..308b6a2fc2b 100644
--- a/mysql-test/r/check_constraint.result
+++ b/mysql-test/r/check_constraint.result
@@ -136,10 +136,6 @@ insert into t1(c1) values(1);
 ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`
 insert into t1(c1) values(2);
 drop table t1;
-create table t1 (a int, b int, check(a>0));
-alter table t1 drop column a;
-ERROR 42S22: Unknown column 'a' in 'CHECK'
-drop table t1;
 create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or c1 is null ) );
 ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `c1`
 create table t1 (a int check (@b in (select user from mysql.user)));
diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result
index 0eddfa70dd1..5bb3b17d4b8 100644
--- a/mysql-test/r/sp-destruct.result
+++ b/mysql-test/r/sp-destruct.result
@@ -12,7 +12,7 @@ create function bug14233_f() returns int
 return 42;
 create table t1 (id int);
 create trigger t1_ai after insert on t1 for each row call bug14233();
-alter table mysql.proc drop type;
+alter table mysql.proc drop security_type;
 call bug14233();
 ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted
 create view v1 as select bug14233_f();
@@ -142,7 +142,7 @@ drop database if exists mysqltest;
 flush table mysql.proc;
 create database mysqltest;
 # Corrupt mysql.proc to make it unusable by current version of server.
-alter table mysql.proc drop column type;
+alter table mysql.proc drop column security_type;
 # The below statement should not cause assertion failure.
 drop database mysqltest;
 Warnings:
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
index a64a69ad368..ffaaf09acc8 100644
--- a/mysql-test/r/statistics.result
+++ b/mysql-test/r/statistics.result
@@ -1162,7 +1162,7 @@ test	t2	idx4	1	6.2000
 test	t2	idx4	2	1.7222
 test	t2	idx4	3	1.1154
 test	t2	idx4	4	1.0000
-ALTER TABLE t2 DROP COLUMN b;
+ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a);
 SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t2	idx2	1	7.0000
diff --git a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc
index ee8a923485a..cda5144ecc7 100644
--- a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc
+++ b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc
@@ -232,12 +232,6 @@ eval $my_select;
 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
 eval $my_select;
 #
-# Check DROP COLUMN
-eval $my_select;
-ALTER TABLE db_datadict.t1_my_tablex
-DROP COLUMN first_col;
-eval $my_select;
-#
 # Check impact of DROP TABLE
 SELECT table_name, column_name
 FROM information_schema.key_column_usage
diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage.result b/mysql-test/suite/funcs_1/r/is_key_column_usage.result
index 80dd2db5441..cfc8fa7758e 100644
--- a/mysql-test/suite/funcs_1/r/is_key_column_usage.result
+++ b/mysql-test/suite/funcs_1/r/is_key_column_usage.result
@@ -345,28 +345,6 @@ db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
 db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
 db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
 db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
-SELECT constraint_schema, constraint_name, table_schema,
-table_name, column_name, ordinal_position
-FROM information_schema.key_column_usage
-WHERE table_name = 't1_my_tablex'
-ORDER BY constraint_schema, constraint_name, table_schema,
-table_name, ordinal_position;
-constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
-db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
-db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
-db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
-db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
-ALTER TABLE db_datadict.t1_my_tablex
-DROP COLUMN first_col;
-SELECT constraint_schema, constraint_name, table_schema,
-table_name, column_name, ordinal_position
-FROM information_schema.key_column_usage
-WHERE table_name = 't1_my_tablex'
-ORDER BY constraint_schema, constraint_name, table_schema,
-table_name, ordinal_position;
-constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
-db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
-db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	1
 SELECT table_name, column_name
 FROM information_schema.key_column_usage
 WHERE table_name = 't1_my_tablex'
@@ -374,6 +352,8 @@ ORDER BY table_name, column_name;
 table_name	column_name
 t1_my_tablex	f2
 t1_my_tablex	f4
+t1_my_tablex	first_col
+t1_my_tablex	first_col
 DROP TABLE db_datadict.t1_my_tablex;
 SELECT table_name, column_name
 FROM information_schema.key_column_usage
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result
index 37fcce5ae42..0553b4344c8 100644
--- a/mysql-test/suite/funcs_1/r/is_table_constraints.result
+++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result
@@ -162,7 +162,7 @@ SELECT table_name FROM information_schema.table_constraints
 WHERE table_name LIKE 't1_my_table%';
 table_name
 CREATE TABLE test.t1_my_table
-(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
+(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1))
 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
 ENGINE = <engine_type>;
 SELECT constraint_name, table_schema, table_name, constraint_type
diff --git a/mysql-test/suite/funcs_1/t/is_table_constraints.test b/mysql-test/suite/funcs_1/t/is_table_constraints.test
index d1659517e79..96853edac2c 100644
--- a/mysql-test/suite/funcs_1/t/is_table_constraints.test
+++ b/mysql-test/suite/funcs_1/t/is_table_constraints.test
@@ -172,7 +172,7 @@ WHERE table_name LIKE 't1_my_table%';
 --replace_result $engine_type <engine_type>
 eval
 CREATE TABLE test.t1_my_table
-   (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
+   (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1))
 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
 ENGINE = $engine_type;
 # Settings used in CREATE TABLE must be visible
diff --git a/mysql-test/suite/gcol/inc/gcol_keys.inc b/mysql-test/suite/gcol/inc/gcol_keys.inc
index 8b6712e9302..a16d425ed24 100644
--- a/mysql-test/suite/gcol/inc/gcol_keys.inc
+++ b/mysql-test/suite/gcol/inc/gcol_keys.inc
@@ -323,24 +323,6 @@ DESC t1;
 DROP TABLE t1;
 
 --echo #
---echo # Bug#21346132: WL8149:INNODB: FAILING ASSERTION:
---echo # PRIMARY_KEY_NO == -1 || PRIMARY_KEY_NO == 0
---echo #
-CREATE TABLE t1 (
-  col_int_nokey int(11) NOT NULL,
-  col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey),
-  col_varchar_nokey varchar(1) NOT NULL,
-  col_varchar_key varchar(2) GENERATED ALWAYS AS (col_varchar_nokey),
-  UNIQUE KEY col_int_key (col_int_key),
-  UNIQUE KEY col_varchar_key (col_varchar_key),
-  UNIQUE KEY col_int_key_2 (col_int_key,col_varchar_key),
-  UNIQUE KEY col_varchar_key_2 (col_varchar_key,col_varchar_nokey),
-  KEY col_int_key_3 (col_int_key,col_int_nokey)
-);
-
-ALTER TABLE t1 DROP COLUMN col_varchar_key;
-DROP TABLE t1;
---echo #
 --echo # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
 --echo #
 
diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
index 8cd871162fd..dcbba1f70c1 100644
--- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
@@ -303,23 +303,6 @@ col_int_nokey	int(11)	YES		NULL
 col_int_key	int(11)	YES	UNI	NULL	VIRTUAL GENERATED
 DROP TABLE t1;
 #
-# Bug#21346132: WL8149:INNODB: FAILING ASSERTION:
-# PRIMARY_KEY_NO == -1 || PRIMARY_KEY_NO == 0
-#
-CREATE TABLE t1 (
-col_int_nokey int(11) NOT NULL,
-col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey),
-col_varchar_nokey varchar(1) NOT NULL,
-col_varchar_key varchar(2) GENERATED ALWAYS AS (col_varchar_nokey),
-UNIQUE KEY col_int_key (col_int_key),
-UNIQUE KEY col_varchar_key (col_varchar_key),
-UNIQUE KEY col_int_key_2 (col_int_key,col_varchar_key),
-UNIQUE KEY col_varchar_key_2 (col_varchar_key,col_varchar_nokey),
-KEY col_int_key_3 (col_int_key,col_int_nokey)
-);
-ALTER TABLE t1 DROP COLUMN col_varchar_key;
-DROP TABLE t1;
-#
 # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
 #
 CREATE TABLE t1 (
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result
index bd44661f5dd..fdad1db2648 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result
@@ -930,81 +930,6 @@ create index idx on t(c);
 Warnings:
 Warning	1365	Division by 0
 DROP TABLE t;
-CREATE TABLE t (
-pk INTEGER AUTO_INCREMENT,
-col_int_nokey INTEGER /*! NULL */,
-col_int INT GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) STORED,
-col_int_key INTEGER GENERATED ALWAYS AS (col_int + col_int_nokey) VIRTUAL,
-col_date_nokey DATE /*! NULL */,
-col_date DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) STORED,
-col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date,interval 30 day)) VIRTUAL,
-col_datetime_nokey DATETIME /*! NULL */,
-col_time_nokey TIME /*! NULL */,
-col_datetime DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
-col_time TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
-col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime, col_time_nokey)) VIRTUAL,
-col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time)) VIRTUAL,
-col_varchar_nokey VARCHAR(1) /*! NULL */,
-col_varchar VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey,col_varchar_nokey)) STORED,
-col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar, 'x')) VIRTUAL,
-unique KEY (pk,col_int_key),
-KEY(col_int),
-KEY(col_date),
-KEY(col_datetime),
-KEY(col_time),
-KEY(col_varchar),
-UNIQUE KEY (col_int_key),
-KEY (col_time_key),
-KEY (col_datetime_key),
-UNIQUE KEY (col_int_key, col_varchar_key),
-KEY (col_int_key, col_int_nokey),
-KEY(col_int_key,col_date_key),
-KEY(col_int_key, col_time_key),
-KEY(col_int_key, col_datetime_key),
-KEY(col_date_key,col_time_key,col_datetime_key),
-KEY (col_varchar_key, col_varchar_nokey),
-UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
-)  AUTO_INCREMENT=10 ENGINE=INNODB PARTITION BY KEY(col_int_key) PARTITIONS 3;
-ALTER TABLE t DROP COLUMN `pk`;
-SHOW CREATE TABLE t;
-Table	Create Table
-t	CREATE TABLE `t` (
-  `col_int_nokey` int(11) DEFAULT NULL,
-  `col_int` int(11) GENERATED ALWAYS AS (`col_int_nokey` + `col_int_nokey`) STORED,
-  `col_int_key` int(11) GENERATED ALWAYS AS (`col_int` + `col_int_nokey`) VIRTUAL,
-  `col_date_nokey` date DEFAULT NULL,
-  `col_date` date GENERATED ALWAYS AS (`col_date_nokey` + interval 30 day) STORED,
-  `col_date_key` date GENERATED ALWAYS AS (`col_date` + interval 30 day) VIRTUAL,
-  `col_datetime_nokey` datetime DEFAULT NULL,
-  `col_time_nokey` time DEFAULT NULL,
-  `col_datetime` datetime GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time_nokey`)) STORED,
-  `col_time` time GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time_nokey`)) STORED,
-  `col_datetime_key` datetime GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time_nokey`)) VIRTUAL,
-  `col_time_key` time GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time`)) VIRTUAL,
-  `col_varchar_nokey` varchar(1) DEFAULT NULL,
-  `col_varchar` varchar(2) GENERATED ALWAYS AS (concat(`col_varchar_nokey`,`col_varchar_nokey`)) STORED,
-  `col_varchar_key` varchar(2) GENERATED ALWAYS AS (concat(`col_varchar`,'x')) VIRTUAL,
-  UNIQUE KEY `pk` (`col_int_key`),
-  UNIQUE KEY `col_int_key` (`col_int_key`),
-  UNIQUE KEY `col_int_key_2` (`col_int_key`,`col_varchar_key`),
-  UNIQUE KEY `col_int_key_7` (`col_int_key`,`col_varchar_key`,`col_date_key`,`col_time_key`,`col_datetime_key`),
-  KEY `col_int` (`col_int`),
-  KEY `col_date` (`col_date`),
-  KEY `col_datetime` (`col_datetime`),
-  KEY `col_time` (`col_time`),
-  KEY `col_varchar` (`col_varchar`),
-  KEY `col_time_key` (`col_time_key`),
-  KEY `col_datetime_key` (`col_datetime_key`),
-  KEY `col_int_key_3` (`col_int_key`,`col_int_nokey`),
-  KEY `col_int_key_4` (`col_int_key`,`col_date_key`),
-  KEY `col_int_key_5` (`col_int_key`,`col_time_key`),
-  KEY `col_int_key_6` (`col_int_key`,`col_datetime_key`),
-  KEY `col_date_key` (`col_date_key`,`col_time_key`,`col_datetime_key`),
-  KEY `col_varchar_key` (`col_varchar_key`,`col_varchar_nokey`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
- PARTITION BY KEY (`col_int_key`)
-PARTITIONS 3
-DROP TABLE t;
 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test
index 4f88fb7f8f6..c2f2e47cfcf 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_basic.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test
@@ -780,48 +780,6 @@ alter table t add  c INT GENERATED ALWAYS AS(a/b);
 create index idx on t(c);
 DROP TABLE t;
 
-CREATE TABLE t (
-  pk INTEGER AUTO_INCREMENT,
-  col_int_nokey INTEGER /*! NULL */,
-  col_int INT GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) STORED,
-  col_int_key INTEGER GENERATED ALWAYS AS (col_int + col_int_nokey) VIRTUAL,
-  col_date_nokey DATE /*! NULL */,
-  col_date DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) STORED,
-  col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date,interval 30 day)) VIRTUAL,
-  col_datetime_nokey DATETIME /*! NULL */,
-  col_time_nokey TIME /*! NULL */,
-  col_datetime DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
-  col_time TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
-  col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime, col_time_nokey)) VIRTUAL,
-  col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time)) VIRTUAL,
-  col_varchar_nokey VARCHAR(1) /*! NULL */,
-  col_varchar VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey,col_varchar_nokey)) STORED,
-  col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar, 'x')) VIRTUAL,
-  unique KEY (pk,col_int_key),
-  KEY(col_int),
-  KEY(col_date),
-  KEY(col_datetime),
-  KEY(col_time),
-  KEY(col_varchar),
-  UNIQUE KEY (col_int_key),
-  KEY (col_time_key),
-  KEY (col_datetime_key),
-  UNIQUE KEY (col_int_key, col_varchar_key),
-  KEY (col_int_key, col_int_nokey),
-  KEY(col_int_key,col_date_key),
-  KEY(col_int_key, col_time_key),
-  KEY(col_int_key, col_datetime_key),
-  KEY(col_date_key,col_time_key,col_datetime_key),
-  KEY (col_varchar_key, col_varchar_nokey),
-  UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
-)  AUTO_INCREMENT=10 ENGINE=INNODB PARTITION BY KEY(col_int_key) PARTITIONS 3;
-
-ALTER TABLE t DROP COLUMN `pk`;
-
-SHOW CREATE TABLE t;
-
-DROP TABLE t;
-
 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
 
 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 143ba80c53b..93fd23b7fea 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1801,3 +1801,35 @@ DROP TABLE t1;
 --error ER_DUP_CONSTRAINT_NAME
 CREATE TABLE t1(a INT, b INT, CONSTRAINT min check (a>5),
                               CONSTRAINT min check (b>5));
+
+#
+# MDEV-11114 Cannot drop column referenced by CHECK constraint
+#
+create table t1 (a int, b int, check(a>b));
+--error ER_BAD_FIELD_ERROR
+alter table t1 drop column a;
+--error ER_BAD_FIELD_ERROR
+alter table t1 drop column b, add column b bigint first;
+show create table t1;
+drop table t1;
+
+create table t1 (a int, b int, check(a>0));
+alter table t1 drop column a;
+show create table t1;
+drop table t1;
+
+create table t1 (a int, b int, check(a>0));
+alter table t1 drop column a, add column a bigint first;
+show create table t1;
+drop table t1;
+
+create table t1 (a int, b int, c int, unique(a));
+alter table t1 drop column a;
+show create table t1;
+drop table t1;
+
+create table t1 (a int, b int, c int, unique(a,b));
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop column a;
+show create table t1;
+drop table t1;
diff --git a/mysql-test/t/check_constraint.test b/mysql-test/t/check_constraint.test
index c70a208f774..43b4417cfa3 100644
--- a/mysql-test/t/check_constraint.test
+++ b/mysql-test/t/check_constraint.test
@@ -80,14 +80,6 @@ insert into t1(c1) values(2);
 drop table t1;
 
 #
-# MDEV-11114 Cannot drop column referenced by CHECK constraint: Unknown column 'a' in 'virtual column function'
-#
-create table t1 (a int, b int, check(a>0));
---error ER_BAD_FIELD_ERROR
-alter table t1 drop column a;
-drop table t1;
-
-#
 # MDEV-11117 CHECK constraint fails on intermediate step of ALTER
 #
 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test
index 6e19fd885e3..31da235d906 100644
--- a/mysql-test/t/sp-destruct.test
+++ b/mysql-test/t/sp-destruct.test
@@ -39,7 +39,7 @@ create table t1 (id int);
 create trigger t1_ai after insert on t1 for each row call bug14233();
 
 # Unsupported tampering with the mysql.proc definition
-alter table mysql.proc drop type;
+alter table mysql.proc drop security_type;
 --error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2
 call bug14233();
 --error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2
@@ -238,7 +238,7 @@ let $MYSQLD_DATADIR= `select @@datadir`;
 
 create database mysqltest;
 --echo # Corrupt mysql.proc to make it unusable by current version of server.
-alter table mysql.proc drop column type;
+alter table mysql.proc drop column security_type;
 --echo # The below statement should not cause assertion failure.
 drop database mysqltest;
 
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
index 61ef20605af..e9ecb56a1cb 100644
--- a/mysql-test/t/statistics.test
+++ b/mysql-test/t/statistics.test
@@ -500,7 +500,7 @@ SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL;
 SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
 
-ALTER TABLE t2 DROP COLUMN b;
+ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a);
 SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
 
 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 623ed45b1d2..dd425a2bbbc 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -7549,6 +7549,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   bool modified_primary_key= FALSE;
   Create_field *def;
   Field **f_ptr,*field;
+  MY_BITMAP *dropped_fields= NULL; // if it's NULL - no dropped fields
   DBUG_ENTER("mysql_prepare_alter_table");
 
   /*
@@ -7605,6 +7606,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   /*
     First collect all fields from table which isn't in drop_list
   */
+  bitmap_clear_all(&table->tmp_set);
   for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
   {
     Alter_drop *drop;
@@ -7630,6 +7632,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       if (table->s->tmp_table == NO_TMP_TABLE)
         (void) delete_statistics_for_column(thd, table, field);
       drop_it.remove();
+      dropped_fields= &table->tmp_set;
+      bitmap_set_bit(dropped_fields, field->field_index);
       continue;
     }
     /* Check if field is changed */
@@ -7816,6 +7820,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       continue;
     }
 
+    const char *dropped_key_part= NULL;
     KEY_PART_INFO *key_part= key_info->key_part;
     key_parts.empty();
     bool delete_index_stat= FALSE;
@@ -7845,6 +7850,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
         if (table->s->primary_key == i)
           modified_primary_key= TRUE;
         delete_index_stat= TRUE;
+        dropped_key_part= key_part_name;
 	continue;				// Field is removed
       }
       key_part_length= key_part->length;
@@ -7927,6 +7933,11 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
           key_type= Key::PRIMARY;
         else
           key_type= Key::UNIQUE;
+        if (dropped_key_part)
+        {
+          my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), dropped_key_part);
+          goto err;
+        }
       }
       else if (key_info->flags & HA_FULLTEXT)
         key_type= Key::FULLTEXT;
@@ -7977,6 +7988,23 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
           break;
         }
       }
+      /* see if the constraint depends on *only* on dropped fields */
+      if (dropped_fields)
+      {
+        table->default_column_bitmaps();
+        bitmap_clear_all(table->read_set);
+        check->expr->walk(&Item::register_field_in_read_map, 1, 0);
+        if (bitmap_is_subset(table->read_set, dropped_fields))
+          drop= (Alter_drop*)1;
+        else if (bitmap_is_overlapping(dropped_fields, table->read_set))
+        {
+          bitmap_intersect(table->read_set, dropped_fields);
+          uint field_nr= bitmap_get_first_set(table->read_set);
+          my_error(ER_BAD_FIELD_ERROR, MYF(0),
+                   table->field[field_nr]->field_name, "CHECK");
+          goto err;
+        }
+      }
       if (!drop)
         new_constraint_list.push_back(check, thd->mem_root);
     }