MDEV-21744 Assertion `!rec_offs_nth_sql_null(offsets, n)' failed

commit 08ba388713946c03aa591899cd3a446a6202f882 of MDEV-12353
introduced an incorrect assumption, which was documented by
the failing assertion.

After instant ADD COLUMN, we can have a null (and in-place) UPDATE
of NULL to NULL. No data needs to be written for such updates.

For ROW_FORMAT=REDUNDANT, we reserve space for the NULL values,
and to be compatible with existing behaviour, we will zerofill
the unused data bytes when updating to NULL value.

5 files changed, 62 insertions, 5 deletions

diff --git a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
index ddd2bbf89ae..b8ca1164489 100644
--- a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
+++ b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
@@ -318,8 +318,8 @@
  FROM information_schema.global_status
  WHERE variable_name = 'innodb_instant_alter_column';
  instants
--199
-+201
+-202
++204
  SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
  #
  # MDEV-18266: Changing an index comment unnecessarily rebuilds index
diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result
index 5780849d279..0393c8a63c2 100644
--- a/mysql-test/suite/innodb/r/instant_alter.result
+++ b/mysql-test/suite/innodb/r/instant_alter.result
@@ -935,6 +935,18 @@ DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
 ALTER TABLE t1 MODIFY b INT FIRST;
 DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+INSERT INTO t1 SET pk=1;
+ALTER TABLE t1 ADD COLUMN b INT;
+BEGIN;
+UPDATE t1 SET pk=2;
+UPDATE t1 SET pk=1;
+connection analyze;
+SELECT * FROM t1;
+pk	b
+1	NULL
+connection default;
+DROP TABLE t1;
 CREATE TABLE t1
 (id INT PRIMARY KEY, c2 INT UNIQUE,
 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
@@ -1815,6 +1827,18 @@ DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
 ALTER TABLE t1 MODIFY b INT FIRST;
 DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
+INSERT INTO t1 SET pk=1;
+ALTER TABLE t1 ADD COLUMN b INT;
+BEGIN;
+UPDATE t1 SET pk=2;
+UPDATE t1 SET pk=1;
+connection analyze;
+SELECT * FROM t1;
+pk	b
+1	NULL
+connection default;
+DROP TABLE t1;
 CREATE TABLE t1
 (id INT PRIMARY KEY, c2 INT UNIQUE,
 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
@@ -2695,12 +2719,24 @@ DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
 ALTER TABLE t1 MODIFY b INT FIRST;
 DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
+INSERT INTO t1 SET pk=1;
+ALTER TABLE t1 ADD COLUMN b INT;
+BEGIN;
+UPDATE t1 SET pk=2;
+UPDATE t1 SET pk=1;
+connection analyze;
+SELECT * FROM t1;
+pk	b
+1	NULL
+connection default;
+DROP TABLE t1;
 disconnect analyze;
 SELECT variable_value-@old_instant instants
 FROM information_schema.global_status
 WHERE variable_name = 'innodb_instant_alter_column';
 instants
-199
+202
 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
 #
 # MDEV-18266: Changing an index comment unnecessarily rebuilds index
diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test
index 8473984afba..8fc7a8bfaf8 100644
--- a/mysql-test/suite/innodb/t/instant_alter.test
+++ b/mysql-test/suite/innodb/t/instant_alter.test
@@ -830,6 +830,18 @@ eval CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) $engine;
 ALTER TABLE t1 MODIFY b INT FIRST;
 DROP TABLE t1;
 
+# MDEV-21744 Assertion `!rec_offs_nth_sql_null(offsets, n)' failed
+eval CREATE TABLE t1 (pk INT PRIMARY KEY) $engine;
+INSERT INTO t1 SET pk=1;
+ALTER TABLE t1 ADD COLUMN b INT;
+BEGIN;
+UPDATE t1 SET pk=2;
+UPDATE t1 SET pk=1;
+connection analyze;
+SELECT * FROM t1;
+connection default;
+DROP TABLE t1;
+
 dec $format;
 let $redundant_4k= 0;
 }
diff --git a/storage/innobase/btr/btr0cur.cc b/storage/innobase/btr/btr0cur.cc
index 797aaf96e1c..fbb2fbe9b5d 100644
--- a/storage/innobase/btr/btr0cur.cc
+++ b/storage/innobase/btr/btr0cur.cc
@@ -4136,7 +4136,12 @@ void btr_cur_upd_rec_in_place(rec_t *rec, const dict_index_t *index,
 		ut_ad(!rec_offs_nth_default(offsets, n));
 
 		if (UNIV_UNLIKELY(dfield_is_null(&uf->new_val))) {
-			ut_ad(!rec_offs_nth_sql_null(offsets, n));
+			if (rec_offs_nth_sql_null(offsets, n)) {
+				ut_ad(index->table->is_instant());
+				ut_ad(n >= index->n_core_fields);
+				continue;
+			}
+
 			ut_ad(!index->table->not_redundant());
 			if (ulint size = rec_get_nth_field_size(rec, n)) {
 				mtr->memset(
diff --git a/storage/innobase/trx/trx0rec.cc b/storage/innobase/trx/trx0rec.cc
index 50be3196fef..5b479efe452 100644
--- a/storage/innobase/trx/trx0rec.cc
+++ b/storage/innobase/trx/trx0rec.cc
@@ -2411,7 +2411,11 @@ trx_undo_prev_version_build(
 			ut_ad(!rec_offs_nth_default(offsets, n));
 
 			if (UNIV_UNLIKELY(dfield_is_null(&uf->new_val))) {
-				ut_ad(!rec_offs_nth_sql_null(offsets, n));
+				if (rec_offs_nth_sql_null(offsets, n)) {
+					ut_ad(index->table->is_instant());
+					ut_ad(n >= index->n_core_fields);
+					continue;
+				}
 				ut_ad(!index->table->not_redundant());
 				ulint l = rec_get_1byte_offs_flag(*old_vers)
 					? (n + 1) : (n + 1) * 2;