--source include/innodb_page_size.inc # Save the initial number of concurrent sessions. --source include/count_sessions.inc CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB; INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9); --enable_info # This one will be a no-op. # MySQL should perhaps issue an error, because it refuses to modify # the PRIMARY KEY column c1 from NOT NULL to NULL. ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE; # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE; # Request some conflicting changes for a single column. --error ER_BAD_FIELD_ERROR ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL; --error ER_BAD_FIELD_ERROR ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL; --error ER_BAD_FIELD_ERROR ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT; --error ER_BAD_FIELD_ERROR ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL; # No-ops. ALTER TABLE t CHANGE c2 c2 INT NOT NULL; ALTER TABLE t MODIFY c2 INT NOT NULL; --disable_info connect (con1,localhost,root,,); --error ER_BAD_NULL_ERROR UPDATE t SET c2=NULL; SELECT * FROM t; connection default; # This should change the column to NULL. ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE; connection con1; BEGIN; UPDATE t SET c2=NULL; SELECT * FROM t; ROLLBACK; SELECT * FROM t; disconnect con1; connection default; # This should be no-op. ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE; --replace_column 1 # 5 # SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t'; DROP TABLE t; 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; --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; 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 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 ER_INVALID_USE_OF_NULL ALTER TABLE t1 MODIFY c INT NOT NULL; --error ER_INVALID_USE_OF_NULL 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