MDEV-13176 ALTER TABLE…CHANGE col col TIMESTAMP NOT NULL DEFAULT… fails

When it comes to DEFAULT values of columns, InnoDB is imposing both
unnecessary and insufficient conditions on whether ALGORITHM=INPLACE
should be allowed for ALTER TABLE.

When changing an existing column to NOT NULL, any NULL values in the
columns only get a special treatment if the column is changed to an
AUTO_INCREMENT column (which is not supported by ALGORITHM=INPLACE)
or the column type is TIMESTAMP. In all other cases, an error
must be reported for the failure to convert a NULL value to NOT NULL.

InnoDB was unnecessarily interested in whether the DEFAULT value
is not constant when altering other than TIMESTAMP columns. Also,
when changing a TIMESTAMP column to NOT NULL, InnoDB was performing
an insufficient check, and it was incorrectly allowing a constant
DEFAULT value while not being able to replace NULL values with that
constant value.

Furthermore, in ADD COLUMN, InnoDB is unnecessarily rejecting certain
nondeterministic DEFAULT expressions (depending on the session
parameters or the current time).

1 files changed, 48 insertions, 6 deletions

diff --git a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
index 935320fb553..d8acc02cbdb 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test
@@ -1,16 +1,46 @@
 --source include/have_innodb.inc
 
-CREATE TABLE t1 (
-  `i1` INT(10) UNSIGNED NOT NULL,
-  `d1` TIMESTAMP NULL DEFAULT NULL
-) ENGINE=innodb;
+CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb;
+INSERT INTO t1 VALUES(NULL);
+--enable_info
+--error ER_INVALID_USE_OF_NULL
+ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
+ALGORITHM=INPLACE;
+--error WARN_DATA_TRUNCATED
+ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
+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;
+--error ER_INVALID_USE_OF_NULL
+ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
+ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+ADD PRIMARY KEY(id);
+--disable_info
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB;
 
 SHOW CREATE TABLE t1;
 
 INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
 select * from t1;
 set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
-ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP DEFAULT CURRENT_TIMESTAMP  NOT NULL;
+ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:23:45',
+ALGORITHM=INPLACE;
+SELECT DISTINCT d1 FROM t1;
+ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:32:45',
+ALGORITHM=COPY;
+SELECT DISTINCT d1 FROM t1;
+--enable_info
+ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP DEFAULT '2017-05-08 16:32:54';
+--disable_info
+--echo # Note: NULL was changed to CURRENT_TIMESTAMP(),
+--echo # not the specified constant DEFAULT value!
+SELECT COUNT(DISTINCT d1),COUNT(d1),COUNT(*) FROM t1;
+SELECT DISTINCT (CURRENT_TIMESTAMP()-d1) <= 60 FROM t1;
 drop table t1;
 
 CREATE TABLE t1 (
@@ -18,8 +48,14 @@ CREATE TABLE t1 (
   `d1` TIMESTAMP NULL DEFAULT NULL
 ) ENGINE=innodb;
 INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
-ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
+--enable_info
+ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE t1 ADD COLUMN w1 varchar(20) NULL DEFAULT USER();
+ALTER TABLE t1 CHANGE w1 u1 varchar(30) NULL DEFAULT substr(USER(),1);
+--disable_info
+SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1;
 
+--enable_info
 ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
 LOCK=NONE;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED
@@ -27,6 +63,12 @@ ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED
 ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
 ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
+--disable_info
 SELECT d1-d3, d2 FROM t1;
 SHOW CREATE TABLE t1;
+--enable_info
+ALTER TABLE t1 ADD COLUMN d4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
+--disable_info
+SELECT COUNT(DISTINCT d4),COUNT(d4),COUNT(*) FROM t1;
+SELECT DISTINCT (CURRENT_TIMESTAMP()-d4) <= 60 FROM t1;
 DROP TABLE t1;