Merge 10.2 into 10.3

FIXME: Properly resolve conflicts between MDEV-18883
and MDEV-7742/MDEV-8305, and record the correct result for
main.log_slow

20 files changed, 575 insertions, 71 deletions

diff --git a/mysql-test/main/loaddata.result b/mysql-test/main/loaddata.result
index 4c5cee0aa25..f09e6d70258 100644
--- a/mysql-test/main/loaddata.result
+++ b/mysql-test/main/loaddata.result
@@ -581,6 +581,56 @@ HEX(a)
 C3A4
 DROP TABLE t1;
 #
+# MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert
+#
+create table t1 (a int, b int);
+CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
+FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+select * from v2;
+a	b
+2	2
+3	3
+4	4
+5	5
+6	6
+select * from t2;
+a	b
+2	2
+3	3
+4	4
+5	5
+6	6
+DROP VIEW IF EXISTS v2,t2;
+DROP TABLE IF EXISTS t1;
+#
+# MDEV-15950: LOAD DATA INTO compex_view crashed
+#
+create table t1 (a int, b int);
+create table t0 (x int, y int);
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0;
+CREATE VIEW v2 AS SELECT * FROM v1;
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1
+FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
+FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
+DROP VIEW IF EXISTS v2,v1;
+DROP TABLE IF EXISTS t1,t0;
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b));
+INSERT INTO t1 VALUES (1,1);
+CREATE TABLE t2 (c INT);
+CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2;
+SELECT a, b FROM t1 INTO OUTFILE '15645.data';
+LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b);
+ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
+LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b);
+ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
+drop table t1,t2;
+drop view v;
+#
 # MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA
 #
 SET sql_mode='';
diff --git a/mysql-test/main/loaddata.test b/mysql-test/main/loaddata.test
index a283e64d473..3a5bcad1685 100644
--- a/mysql-test/main/loaddata.test
+++ b/mysql-test/main/loaddata.test
@@ -677,6 +677,51 @@ LOAD DATA INFILE '../../std_data/loaddata/mdev-11631.txt' INTO TABLE t1 CHARACTE
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert
+--echo #
+
+create table t1 (a int, b int);
+CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
+     FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+select * from v2;
+select * from t2;
+DROP VIEW IF EXISTS v2,t2;
+DROP TABLE IF EXISTS t1;
+
+--echo #
+--echo # MDEV-15950: LOAD DATA INTO compex_view crashed
+--echo #
+create table t1 (a int, b int);
+create table t0 (x int, y int);
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0;
+CREATE VIEW v2 AS SELECT * FROM v1;
+
+--error ER_WRONG_USAGE
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1
+FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+
+--error ER_WRONG_USAGE
+LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
+FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+
+DROP VIEW IF EXISTS v2,v1;
+DROP TABLE IF EXISTS t1,t0;
+
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b));
+INSERT INTO t1 VALUES (1,1);
+CREATE TABLE t2 (c INT);
+CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2;
+SELECT a, b FROM t1 INTO OUTFILE '15645.data';
+--error ER_WRONG_USAGE
+LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b);
+--error ER_WRONG_USAGE
+LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b);
+drop table t1,t2;
+drop view v;
+
 
 --echo #
 --echo # MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA
diff --git a/mysql-test/main/log_slow.result b/mysql-test/main/log_slow.result
index 59149633b99..74a77e3f11d 100644
--- a/mysql-test/main/log_slow.result
+++ b/mysql-test/main/log_slow.result
@@ -77,3 +77,40 @@ set @@log_slow_filter=default;
 set @@log_slow_verbosity=default;
 set global log_output= default;
 truncate mysql.slow_log;
+#
+# MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off
+#
+SET SESSION slow_query_log=OFF;
+SET GLOBAL slow_query_log=OFF;
+SET long_query_time=0.1;
+# Although this query is disallowed by slow_query_log, it should still increment Slow_queries
+SELECT VARIABLE_VALUE INTO @global_slow_queries
+FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE VARIABLE_NAME='SLOW_QUERIES';
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT
+CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
+FROM
+INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE
+VARIABLE_NAME='SLOW_QUERIES';
+Slow_queries_increment
+0
+# Although this query is disallowed by log_slow_filter, it should still increment Slow_queries
+SET log_slow_filter=filesort;
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT VARIABLE_VALUE INTO @global_slow_queries
+FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE VARIABLE_NAME='SLOW_QUERIES';
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT
+CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
+FROM
+INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE
+VARIABLE_NAME='SLOW_QUERIES';
+Slow_queries_increment
+0
+SET log_slow_filter=DEFAULT;
+SET @@long_query_time=default;
+SET GLOBAL slow_query_log= @org_slow_query_log;
diff --git a/mysql-test/main/log_slow.test b/mysql-test/main/log_slow.test
index 56e35bd5a20..edb1865b5ae 100644
--- a/mysql-test/main/log_slow.test
+++ b/mysql-test/main/log_slow.test
@@ -58,3 +58,43 @@ set @@log_slow_filter=default;
 set @@log_slow_verbosity=default;
 set global log_output= default;
 truncate mysql.slow_log;
+
+--echo #
+--echo # MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off
+--echo #
+
+SET SESSION slow_query_log=OFF;
+SET GLOBAL slow_query_log=OFF;
+SET long_query_time=0.1;
+
+--echo # Although this query is disallowed by slow_query_log, it should still increment Slow_queries
+
+SELECT VARIABLE_VALUE INTO @global_slow_queries
+  FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE VARIABLE_NAME='SLOW_QUERIES';
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT
+    CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
+  FROM
+    INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE
+    VARIABLE_NAME='SLOW_QUERIES';
+
+--echo # Although this query is disallowed by log_slow_filter, it should still increment Slow_queries
+
+SET log_slow_filter=filesort;
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT VARIABLE_VALUE INTO @global_slow_queries
+  FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE VARIABLE_NAME='SLOW_QUERIES';
+SELECT sleep(0.2) INTO @tmp FROM DUAL;
+SELECT
+    CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
+  FROM
+    INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE
+    VARIABLE_NAME='SLOW_QUERIES';
+SET log_slow_filter=DEFAULT;
+
+SET @@long_query_time=default;
+SET GLOBAL slow_query_log= @org_slow_query_log;
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index b7a2f865c1d..191b889b078 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -7927,6 +7927,39 @@ CALL sp;
 c	a	b	a	b
 DROP PROCEDURE sp;
 DROP TABLE t1;
+#
+# MDEV-17055: Server crashes in find_order_in_list upon
+# 2nd (3rd) execution of SP with UPDATE
+#
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (c INT);
+CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1;
+LOCK TABLE t2 READ;
+CALL sp;
+ERROR HY000: Table 'v1' was not locked with LOCK TABLES
+UNLOCK TABLES;
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'order clause'
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'order clause'
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'order clause'
+DROP PROCEDURE sp;
+CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2;
+LOCK TABLE t2 READ;
+CALL sp;
+ERROR HY000: Table 'v1' was not locked with LOCK TABLES
+UNLOCK TABLES;
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'where clause'
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'where clause'
+CALL sp;
+ERROR 42S22: Unknown column 'b' in 'where clause'
+DROP PROCEDURE sp;
+DROP VIEW v1;
+DROP TABLE t1, t2;
 # End of 5.5 test
 #
 #  MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index 98b85983687..b0c9120007f 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -9364,6 +9364,47 @@ CALL sp;
 DROP PROCEDURE sp;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-17055: Server crashes in find_order_in_list upon
+--echo # 2nd (3rd) execution of SP with UPDATE
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (c INT);
+
+CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1;
+LOCK TABLE t2 READ;
+--error ER_TABLE_NOT_LOCKED
+CALL sp;
+UNLOCK TABLES;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+
+# Cleanup
+DROP PROCEDURE sp;
+
+CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2;
+LOCK TABLE t2 READ;
+--error ER_TABLE_NOT_LOCKED
+CALL sp;
+UNLOCK TABLES;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+--error ER_BAD_FIELD_ERROR
+CALL sp;
+
+# Cleanup
+DROP PROCEDURE sp;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
 
 --echo # End of 5.5 test
 
diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff
index cd66df7440b..d963cde132a 100644
--- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff
+++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff
@@ -1,5 +1,5 @@
 --- suite/encryption/r/innodb-checksum-algorithm.result
-+++ suite/encryption/r/innodb-checksum-algorithm,32k.reject
++++ suite/encryption/r/innodb-checksum-algorithm.result
 @@ -13,9 +13,9 @@
  SET GLOBAL innodb_default_encryption_key_id=4;
  SET GLOBAL innodb_checksum_algorithm=crc32;
@@ -9,10 +9,10 @@
  create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_crc32(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_crc32(a serial, b blob, index(b(10))) engine=innodb
-@@ -222,9 +222,9 @@
+@@ -153,9 +153,9 @@
  t_crc32, tpe_crc32, tp_crc32;
  SET GLOBAL innodb_checksum_algorithm=innodb;
  create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb
@@ -21,10 +21,10 @@
  create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_innodb(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_innodb(a serial, b blob, index(b(10))) engine=innodb
-@@ -431,9 +431,9 @@
+@@ -293,9 +293,9 @@
  t_innodb, tpe_innodb, tp_innodb;
  SET GLOBAL innodb_checksum_algorithm=none;
  create table tce_none(a serial, b blob, index(b(10))) engine=innodb
@@ -33,6 +33,6 @@
  create table tc_none(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_none(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_none(a serial, b blob, index(b(10))) engine=innodb
diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff
index 523074297da..d963cde132a 100644
--- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff
+++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff
@@ -1,5 +1,5 @@
 --- suite/encryption/r/innodb-checksum-algorithm.result
-+++ suite/encryption/r/innodb-checksum-algorithm,64k.reject
++++ suite/encryption/r/innodb-checksum-algorithm.result
 @@ -13,9 +13,9 @@
  SET GLOBAL innodb_default_encryption_key_id=4;
  SET GLOBAL innodb_checksum_algorithm=crc32;
@@ -9,10 +9,10 @@
  create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_crc32(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_crc32(a serial, b blob, index(b(10))) engine=innodb
-@@ -222,9 +222,9 @@
+@@ -153,9 +153,9 @@
  t_crc32, tpe_crc32, tp_crc32;
  SET GLOBAL innodb_checksum_algorithm=innodb;
  create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb
@@ -21,10 +21,10 @@
  create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_innodb(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_innodb(a serial, b blob, index(b(10))) engine=innodb
-@@ -431,9 +431,9 @@
+@@ -293,9 +293,9 @@
  t_innodb, tpe_innodb, tp_innodb;
  SET GLOBAL innodb_checksum_algorithm=none;
  create table tce_none(a serial, b blob, index(b(10))) engine=innodb
@@ -33,6 +33,6 @@
  create table tc_none(a serial, b blob, index(b(10))) engine=innodb
 -ROW_FORMAT=COMPRESSED encrypted=no;
 +ROW_FORMAT=DYNAMIC encrypted=no;
+ Warnings:
+ Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
  create table te_none(a serial, b blob, index(b(10))) engine=innodb
- encrypted=yes;
- create table t_none(a serial, b blob, index(b(10))) engine=innodb
diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
index afb79dbad8a..ff42e975b8d 100644
--- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
+++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
@@ -14,14 +14,20 @@ create table tce_crc32(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=yes;
 create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table te_crc32(a serial, b blob, index(b(10))) engine=innodb
 encrypted=yes;
 create table t_crc32(a serial, b blob, index(b(10))) engine=innodb
 encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table tpe_crc32(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=yes;
 create table tp_crc32(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 begin;
 insert into tce_crc32(b) values (repeat('secret',20));
 insert into tc_crc32(b) values (repeat('secret',20));
@@ -149,14 +155,20 @@ create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=yes;
 create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table te_innodb(a serial, b blob, index(b(10))) engine=innodb
 encrypted=yes;
 create table t_innodb(a serial, b blob, index(b(10))) engine=innodb
 encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table tpe_innodb(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=yes;
 create table tp_innodb(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 begin;
 insert into tce_innodb(b) values (repeat('secret',20));
 insert into tc_innodb(b) values (repeat('secret',20));
@@ -284,14 +296,20 @@ create table tce_none(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=yes;
 create table tc_none(a serial, b blob, index(b(10))) engine=innodb
 ROW_FORMAT=COMPRESSED encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table te_none(a serial, b blob, index(b(10))) engine=innodb
 encrypted=yes;
 create table t_none(a serial, b blob, index(b(10))) engine=innodb
 encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 create table tpe_none(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=yes;
 create table tp_none(a serial, b blob, index(b(10))) engine=innodb
 page_compressed=yes encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 begin;
 insert into tce_none(b) values (repeat('secret',20));
 insert into tc_none(b) values (repeat('secret',20));
diff --git a/mysql-test/suite/encryption/r/innodb-compressed-blob.result b/mysql-test/suite/encryption/r/innodb-compressed-blob.result
index e156266edc6..f163f6141dd 100644
--- a/mysql-test/suite/encryption/r/innodb-compressed-blob.result
+++ b/mysql-test/suite/encryption/r/innodb-compressed-blob.result
@@ -7,6 +7,8 @@ set GLOBAL innodb_default_encryption_key_id=4;
 create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed;
 create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes;
 create table t3(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=no;
+Warnings:
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 insert into t1 values (1, repeat('secret',6000));
 insert into t2 values (1, repeat('secret',6000));
 insert into t3 values (1, repeat('secret',6000));
diff --git a/mysql-test/suite/encryption/r/innodb-encryption-alter.result b/mysql-test/suite/encryption/r/innodb-encryption-alter.result
index 2c6372e1c61..934f0607958 100644
--- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result
+++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result
@@ -2,9 +2,16 @@ SET GLOBAL innodb_encrypt_tables = ON;
 SET GLOBAL innodb_encryption_threads = 4;
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4;
 Warnings:
-Warning	140	InnoDB: Ignored ENCRYPTION_KEY_ID 4 when encryption is disabled
+Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
 DROP TABLE t1;
+set @save_global = @@GLOBAL.innodb_default_encryption_key_id;
 set innodb_default_encryption_key_id = 99;
+Warnings:
+Warning	1210	innodb_default_encryption_key=99 is not available
+set global innodb_default_encryption_key_id = 99;
+Warnings:
+Warning	1210	innodb_default_encryption_key=99 is not available
+set global innodb_default_encryption_key_id = @save_global;
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB;
 ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
 SHOW WARNINGS;
@@ -38,8 +45,6 @@ t1	CREATE TABLE `t1` (
   PRIMARY KEY (`pk`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4
 CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1;
-Warnings:
-Warning	140	InnoDB: Ignored ENCRYPTION_KEY_ID 1 when encryption is disabled
 ALTER TABLE t1 ENCRYPTION_KEY_ID=99;
 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
 SHOW WARNINGS;
@@ -51,38 +56,32 @@ drop table t1,t2;
 SET GLOBAL innodb_encrypt_tables=OFF;
 CREATE TABLE t1 (a int not null primary key) engine=innodb;
 ALTER TABLE t1 ENCRYPTION_KEY_ID=4;
-ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
-SHOW WARNINGS;
-Level	Code	Message
-Warning	140	InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1
-Error	1478	Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4
 DROP TABLE t1;
 CREATE TABLE t2 (a int not null primary key) engine=innodb;
 ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY;
-ERROR HY000: Can't create table `test`.`t2` (errno: 140 "Wrong create options")
 SHOW WARNINGS;
 Level	Code	Message
-Warning	140	InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1
-Error	1005	Can't create table `test`.`t2` (errno: 140 "Wrong create options")
-Warning	1030	Got error 140 "Wrong create options" from storage engine InnoDB
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` int(11) NOT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4
 CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4;
-ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options")
+DROP TABLE t3;
+SET GLOBAL innodb_encrypt_tables='FORCE';
+CREATE TABLE t1 (a int primary key) engine=innodb encrypted=no;
+ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
 SHOW WARNINGS;
 Level	Code	Message
-Warning	140	InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1
-Error	1005	Can't create table `test`.`t3` (errno: 140 "Wrong create options")
+Warning	140	InnoDB: ENCRYPTED=NO cannot be used with innodb_encrypt_tables=FORCE
+Error	1005	Can't create table `test`.`t1` (errno: 140 "Wrong create options")
 Warning	1030	Got error 140 "Wrong create options" from storage engine InnoDB
 FLUSH TABLES;
 create table t1(f1 int not null, f2 int not null)engine=innodb encrypted=yes;
diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test
index bc7c3b3f13d..d9a0e4e95cd 100644
--- a/mysql-test/suite/encryption/t/innodb-encryption-alter.test
+++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test
@@ -12,7 +12,10 @@ SET GLOBAL innodb_encryption_threads = 4;
 
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4;
 DROP TABLE t1;
+set @save_global = @@GLOBAL.innodb_default_encryption_key_id;
 set innodb_default_encryption_key_id = 99;
+set global innodb_default_encryption_key_id = 99;
+set global innodb_default_encryption_key_id = @save_global;
 --error 1005
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB;
 SHOW WARNINGS;
@@ -83,22 +86,24 @@ drop table t1,t2;
 #
 # MDEV-17230: encryption_key_id from alter is ignored by encryption threads
 #
+--enable_warnings
 SET GLOBAL innodb_encrypt_tables=OFF;
 CREATE TABLE t1 (a int not null primary key) engine=innodb;
---error ER_ILLEGAL_HA_CREATE_OPTION
 ALTER TABLE t1 ENCRYPTION_KEY_ID=4;
-SHOW WARNINGS;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 CREATE TABLE t2 (a int not null primary key) engine=innodb;
---error ER_CANT_CREATE_TABLE
 ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY;
 SHOW WARNINGS;
 SHOW CREATE TABLE t2;
 
---error ER_CANT_CREATE_TABLE
 CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4;
+DROP TABLE t3;
+
+SET GLOBAL innodb_encrypt_tables='FORCE';
+--error ER_CANT_CREATE_TABLE
+CREATE TABLE t1 (a int primary key) engine=innodb encrypted=no;
 SHOW WARNINGS;
 
 FLUSH TABLES;
diff --git a/mysql-test/suite/innodb/r/innodb-alter-nullable.result b/mysql-test/suite/innodb/r/innodb-alter-nullable.result
index 632f7885b8e..68ad6762335 100644
--- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result
+++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result
@@ -3,12 +3,9 @@ INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
 ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
 affected rows: 0
 info: Records: 0  Duplicates: 0  Warnings: 0
-set @old_sql_mode = @@sql_mode;
-set @@sql_mode = 'STRICT_TRANS_TABLES';
 ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
 affected rows: 0
 info: Records: 0  Duplicates: 0  Warnings: 0
-set @@sql_mode = @old_sql_mode;
 ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
 ERROR 42S22: Unknown column 'c2' in 't'
 ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
@@ -24,8 +21,6 @@ ALTER TABLE t MODIFY c2 INT NOT NULL;
 affected rows: 0
 info: Records: 0  Duplicates: 0  Warnings: 0
 connect  con1,localhost,root,,;
-connection con1;
-SET SQL_MODE='STRICT_ALL_TABLES';
 UPDATE t SET c2=NULL;
 ERROR 23000: Column 'c2' cannot be null
 SELECT * FROM t;
@@ -61,3 +56,152 @@ CREATE TABLE t1(c1 INT) ENGINE=InnoDB;
 ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1);
 ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1);
 DROP TABLE t1;
+#
+# MDEV-18732 InnoDB: ALTER IGNORE returns error for NULL
+#
+CREATE TABLE t1(c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL);
+ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+INSERT INTO t1 VALUES (NULL);
+ERROR 23000: Column 'c' cannot be null
+SELECT * FROM t1;
+c
+0
+DROP TABLE t1;
+CREATE TABLE t1(c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL),(1),(1);
+ALTER IGNORE TABLE t1 ADD UNIQUE(c);
+affected rows: 3
+info: Records: 3  Duplicates: 1  Warnings: 0
+ALTER IGNORE TABLE t1 ADD PRIMARY KEY(c);
+affected rows: 2
+info: Records: 2  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+SELECT * FROM t1;
+c
+0
+1
+DROP TABLE t1;
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+SET @old_sql_mode = @@sql_mode;
+SET sql_mode = '';
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+SET sql_mode = @old_sql_mode;
+# MDEV-18819 FIXME: Wrong result g=NULL
+SELECT * FROM t1;
+c	g
+0	NULL
+SELECT * FROM t2;
+c	v
+0	0
+SELECT * FROM t3;
+c	v
+0	0
+SELECT v FROM t3 FORCE INDEX(v);
+v
+0
+CHECK TABLE t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+test.t2	check	status	OK
+test.t3	check	status	OK
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+ALTER IGNORE TABLE t2 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+ALTER IGNORE TABLE t3 MODIFY c INT NOT NULL;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 1
+Warnings:
+Warning	1265	Data truncated for column 'c' at row 1
+# MDEV-18819 FIXME: Wrong result g=NULL
+SELECT * FROM t1;
+c	g
+0	NULL
+SELECT * FROM t2;
+c	v
+0	0
+SELECT * FROM t3;
+c	v
+0	0
+SELECT v FROM t3 FORCE INDEX(v);
+v
+0
+CHECK TABLE t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+test.t2	check	status	OK
+test.t3	check	status	OK
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+ERROR 01000: Data truncated for column 'c' at row 1
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+ERROR 01000: Data truncated for column 'c' at row 1
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+ERROR 01000: Data truncated for column 'c' at row 1
+UPDATE t1 SET c=0;
+UPDATE t2 SET c=0;
+UPDATE t3 SET c=0;
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+# MDEV-18819 FIXME: This should not require ALGORITHM=COPY.
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+c	g
+0	0
+SELECT * FROM t2;
+c	v
+0	0
+SELECT * FROM t3;
+c	v
+0	0
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result
index d4c0aa6a50e..516ac333a87 100644
--- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result
+++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result
@@ -10,10 +10,15 @@ ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
 ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Creating unique indexes with IGNORE requires COPY algorithm to remove duplicate rows. Try ALGORITHM=COPY
+SET @old_sql_mode = @@sql_mode;
+SET sql_mode = '';
+ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
 affected rows: 0
 info: Records: 0  Duplicates: 0  Warnings: 1
 Warnings:
 Warning	1265	Data truncated for column 'i1' at row 1
+SET sql_mode = @old_sql_mode;
 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
 affected rows: 1
 info: Records: 1  Duplicates: 0  Warnings: 0
diff --git a/mysql-test/suite/innodb/r/innodb-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result
index ce2b81242db..8b6c2324431 100644
--- a/mysql-test/suite/innodb/r/innodb-table-online.result
+++ b/mysql-test/suite/innodb/r/innodb-table-online.result
@@ -396,15 +396,11 @@ UPDATE t1 SET c3 = NULL WHERE c3 = '';
 SET lock_wait_timeout = 1;
 ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5));
 ERROR 42000: Key column 'c22f' doesn't exist in table
-SET @old_sql_mode = @@sql_mode;
-SET @@sql_mode = 'STRICT_TRANS_TABLES';
 ALTER IGNORE TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
 ALGORITHM = INPLACE;
-ERROR 23000: Duplicate entry '' for key 'PRIMARY'
-SET @@sql_mode = @old_sql_mode;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Creating unique indexes with IGNORE requires COPY algorithm to remove duplicate rows. Try ALGORITHM=COPY
 UPDATE t1 SET c3=LEFT(CONCAT(c1,REPEAT('foo',c1)),255) WHERE c3 IS NULL;
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0';
-SET @@sql_mode = 'STRICT_TRANS_TABLES';
 ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL, DROP COLUMN c22f,
 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c4(5)),
 ADD COLUMN c5 CHAR(5) DEFAULT 'tired' FIRST;
@@ -418,7 +414,6 @@ SET DEBUG_SYNC = 'now SIGNAL ins_done0';
 # session con1
 connection con1;
 ERROR 01000: Data truncated for column 'c3' at row 323
-SET @@sql_mode = @old_sql_mode;
 # session default
 connection default;
 ROLLBACK;
diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result
index 72e42763569..ebb24d2a5e9 100644
--- a/mysql-test/suite/innodb/r/instant_alter_debug.result
+++ b/mysql-test/suite/innodb/r/instant_alter_debug.result
@@ -219,7 +219,7 @@ ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INSTANT;
 UPDATE t1 SET d=1;
 connection ddl;
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
-ALTER IGNORE TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
 connection default;
 SET DEBUG_SYNC = 'now WAIT_FOR copied';
 BEGIN;
diff --git a/mysql-test/suite/innodb/t/innodb-alter-nullable.test b/mysql-test/suite/innodb/t/innodb-alter-nullable.test
index d039459f91f..9e6f5df2bc9 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test
@@ -13,14 +13,7 @@ INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
 ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
 
 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
---disable_info
-set @old_sql_mode = @@sql_mode;
-set @@sql_mode = 'STRICT_TRANS_TABLES';
---enable_info
 ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
---disable_info
-set @@sql_mode = @old_sql_mode;
---enable_info
 
 # Request some conflicting changes for a single column.
 --error ER_BAD_FIELD_ERROR
@@ -38,9 +31,6 @@ ALTER TABLE t MODIFY c2 INT NOT NULL;
 --disable_info
 
 connect (con1,localhost,root,,);
-connection con1;
-
-SET SQL_MODE='STRICT_ALL_TABLES';
 
 --error ER_BAD_NULL_ERROR
 UPDATE t SET c2=NULL;
@@ -76,6 +66,99 @@ ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1);
 ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1);
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-18732 InnoDB: ALTER IGNORE returns error for NULL
+--echo #
+
+CREATE TABLE t1(c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL);
+--enable_info
+ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
+--disable_info
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL),(1),(1);
+--enable_info
+ALTER IGNORE TABLE t1 ADD UNIQUE(c);
+ALTER IGNORE TABLE t1 ADD PRIMARY KEY(c);
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+SET @old_sql_mode = @@sql_mode;
+# Allow lossy conversions of data
+SET sql_mode = '';
+--enable_info
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+--disable_info
+SET sql_mode = @old_sql_mode;
+--echo # MDEV-18819 FIXME: Wrong result g=NULL
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT v FROM t3 FORCE INDEX(v);
+CHECK TABLE t1,t2,t3;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+--enable_info
+ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
+ALTER IGNORE TABLE t2 MODIFY c INT NOT NULL;
+ALTER IGNORE TABLE t3 MODIFY c INT NOT NULL;
+--disable_info
+--echo # MDEV-18819 FIXME: Wrong result g=NULL
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT v FROM t3 FORCE INDEX(v);
+CHECK TABLE t1,t2,t3;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
+CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
+CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
+INSERT INTO t1 SET c=NULL;
+INSERT INTO t2 SET c=NULL;
+INSERT INTO t3 SET c=NULL;
+--enable_info
+--error WARN_DATA_TRUNCATED
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+--error WARN_DATA_TRUNCATED
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+--error WARN_DATA_TRUNCATED
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+--disable_info
+UPDATE t1 SET c=0;
+UPDATE t2 SET c=0;
+UPDATE t3 SET c=0;
+--enable_info
+ALTER TABLE t1 MODIFY c INT NOT NULL;
+ALTER TABLE t2 MODIFY c INT NOT NULL;
+--echo # MDEV-18819 FIXME: This should not require ALGORITHM=COPY.
+ALTER TABLE t3 MODIFY c INT NOT NULL;
+--disable_info
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+DROP TABLE t1,t2,t3;
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
index 8b56fb5be5d..1fabdc8f24d 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
@@ -12,7 +12,19 @@ ALGORITHM=COPY;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
+
+# ALTER IGNORE cannot create unique or primary key with ALGORITHM=INPLACE.
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
+# Try the same with ALTER TABLE, using non-strict sql_mode.
+--disable_info
+SET @old_sql_mode = @@sql_mode;
+SET sql_mode = '';
+--enable_info
+ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
+--disable_info
+SET sql_mode = @old_sql_mode;
+--enable_info
 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
 --disable_info
 SELECT * FROM t1;
diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test
index 2b3879bc707..ce014b0aa00 100644
--- a/mysql-test/suite/innodb/t/innodb-table-online.test
+++ b/mysql-test/suite/innodb/t/innodb-table-online.test
@@ -350,20 +350,16 @@ UPDATE t1 SET c3 = NULL WHERE c3 = '';
 SET lock_wait_timeout = 1;
 --error ER_KEY_COLUMN_DOES_NOT_EXITS
 ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5));
-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';
---error ER_DUP_ENTRY
+# NULL -> NOT NULL is only allowed INPLACE without IGNORE.
+# Adding a PRIMARY KEY will add NOT NULL implicitly!
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 ALTER IGNORE TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
 ALGORITHM = INPLACE;
 
-SET @@sql_mode = @old_sql_mode;
 UPDATE t1 SET c3=LEFT(CONCAT(c1,REPEAT('foo',c1)),255) WHERE c3 IS NULL;
 
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0';
-# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
-SET @@sql_mode = 'STRICT_TRANS_TABLES';
+# NULL -> NOT NULL is allowed INPLACE.
 --send
 ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL, DROP COLUMN c22f,
 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c4(5)),
@@ -382,7 +378,6 @@ SET DEBUG_SYNC = 'now SIGNAL ins_done0';
 connection con1;
 --error WARN_DATA_TRUNCATED
 reap;
-SET @@sql_mode = @old_sql_mode;
 
 --echo # session default
 connection default;
diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test
index cab9decd3d0..e54623b9cbd 100644
--- a/mysql-test/suite/innodb/t/instant_alter_debug.test
+++ b/mysql-test/suite/innodb/t/instant_alter_debug.test
@@ -248,7 +248,7 @@ UPDATE t1 SET d=1;
 
 connection ddl;
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
-send ALTER IGNORE TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
+send ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
 
 connection default;
 SET DEBUG_SYNC = 'now WAIT_FOR copied';