mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 18:05:32 +01:00
29624ea304
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).
74 lines
2.6 KiB
Text
74 lines
2.6 KiB
Text
--source include/have_innodb.inc
|
|
|
|
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 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 (
|
|
`i1` INT(10) UNSIGNED NOT NULL,
|
|
`d1` TIMESTAMP NULL DEFAULT NULL
|
|
) ENGINE=innodb;
|
|
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
|
|
--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
|
|
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;
|