MDEV-14168 Unconditionally allow ALGORITHM=INPLACE for setting a column NOT NULL

- Added two new test case for it.

4 files changed, 269 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/alter_not_null.result b/mysql-test/suite/innodb/r/alter_not_null.result
new file mode 100644
index 00000000000..db24bf91223
--- /dev/null
+++ b/mysql-test/suite/innodb/r/alter_not_null.result
@@ -0,0 +1,75 @@
+set @@sql_mode = 'STRICT_TRANS_TABLES';
+CREATE TABLE t1(f1 INT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+f1
+NULL
+ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+f1
+0
+DROP TABLE t1;
+CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+f1
+NULL
+ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+f1
+
+DROP TABLE t1;
+CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+f1
+NULL
+ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+f1
+
+DROP TABLE t1;
+CREATE TABLE t1(f1 TEXT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+f1
+NULL
+ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc';
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+f1
+abc
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(2, 2, NULL);
+SELECT * FROM t1;
+f1	f2	f3
+2	2	NULL
+ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY
+UPDATE t1 SET f3 = 0;
+SELECT * FROM t1;
+f1	f2	f3
+2	2	0
+ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2);
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
+SELECT * FROM t1;
+f1	f2	f3
+2	2	0
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB;
+INSERT INTO t1 VALUES(10, NULL);
+SELECT * FROM t1;
+f1	b
+10	NULL
+ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/alter_not_null_debug.result b/mysql-test/suite/innodb/r/alter_not_null_debug.result
new file mode 100644
index 00000000000..788eef6420b
--- /dev/null
+++ b/mysql-test/suite/innodb/r/alter_not_null_debug.result
@@ -0,0 +1,68 @@
+CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1, NULL);
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connect  con1,localhost,root;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+INSERT INTO t1 VALUES(2, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+ERROR 22004: Invalid use of NULL value
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	NULL
+UPDATE t1 SET c2 = 0 WHERE c1 = 2;
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+# Alter ignore can convert the NULL values from
+# CONCURRENT DML to constants
+ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c2 = NULL WHERE c1 = 2;
+INSERT INTO t1 VALUES (3, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+SELECT * FROM t1;
+c1	c2
+1	2
+2	2
+3	2
+DROP TABLE t1;
+CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1, NULL, NULL);
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+# Alter Successfully converts from null to not null
+ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c2= 2 WHERE c1 = 1;
+INSERT INTO t1 VALUES (2, 3, 4);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+SELECT * FROM t1;
+c1	c2	c3
+1	2	NULL
+2	3	4
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+# Alter fails because concurrent dml inserts null value
+ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c3= 2 WHERE c1 = 2;
+INSERT INTO t1 VALUES (4, 3, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+ERROR 22004: Invalid use of NULL value
+SELECT * FROM t1;
+c1	c2	c3
+1	2	NULL
+2	3	2
+4	3	NULL
+DROP TABLE t1;
+disconnect con1;
+SET DEBUG_SYNC='RESET';
diff --git a/mysql-test/suite/innodb/t/alter_not_null.test b/mysql-test/suite/innodb/t/alter_not_null.test
new file mode 100644
index 00000000000..b0c1f7b8bb3
--- /dev/null
+++ b/mysql-test/suite/innodb/t/alter_not_null.test
@@ -0,0 +1,58 @@
+--source include/have_innodb.inc
+set @@sql_mode = 'STRICT_TRANS_TABLES';
+
+CREATE TABLE t1(f1 INT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL;
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL;
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL;
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 TEXT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(NULL);
+SELECT * FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc';
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB;
+INSERT INTO t1 VALUES(2, 2, NULL);
+SELECT * FROM t1;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE;
+UPDATE t1 SET f3 = 0;
+SELECT * FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2);
+--disable_info
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB;
+INSERT INTO t1 VALUES(10, NULL);
+SELECT * FROM t1;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE;
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/alter_not_null_debug.test b/mysql-test/suite/innodb/t/alter_not_null_debug.test
new file mode 100644
index 00000000000..9c1500dc829
--- /dev/null
+++ b/mysql-test/suite/innodb/t/alter_not_null_debug.test
@@ -0,0 +1,68 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1, NULL);
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connect (con1,localhost,root);
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+INSERT INTO t1 VALUES(2, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+--error ER_INVALID_USE_OF_NULL
+reap;
+SELECT * FROM t1;
+UPDATE t1 SET c2 = 0 WHERE c1 = 2;
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+--echo # Alter ignore can convert the NULL values from
+--echo # CONCURRENT DML to constants
+send ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c2 = NULL WHERE c1 = 2;
+INSERT INTO t1 VALUES (3, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+reap;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1, NULL, NULL);
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+--echo # Alter Successfully converts from null to not null
+
+send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c2= 2 WHERE c1 = 1;
+INSERT INTO t1 VALUES (2, 3, 4);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+reap;
+SELECT * FROM t1;
+
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+--echo # Alter fails because concurrent dml inserts null value
+
+send ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+UPDATE t1 SET c3= 2 WHERE c1 = 2;
+INSERT INTO t1 VALUES (4, 3, NULL);
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection default;
+--error ER_INVALID_USE_OF_NULL
+reap;
+SELECT * FROM t1;
+DROP TABLE t1;
+disconnect con1;
+SET DEBUG_SYNC='RESET';