diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-alter-nullable.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-alter-nullable.test | 103 |
1 files changed, 93 insertions, 10 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-alter-nullable.test b/mysql-test/suite/innodb/t/innodb-alter-nullable.test index d039459f91f..9e6f5df2bc9 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test +++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test @@ -13,14 +13,7 @@ INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9); ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE; # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. ---disable_info -set @old_sql_mode = @@sql_mode; -set @@sql_mode = 'STRICT_TRANS_TABLES'; ---enable_info ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE; ---disable_info -set @@sql_mode = @old_sql_mode; ---enable_info # Request some conflicting changes for a single column. --error ER_BAD_FIELD_ERROR @@ -38,9 +31,6 @@ ALTER TABLE t MODIFY c2 INT NOT NULL; --disable_info connect (con1,localhost,root,,); -connection con1; - -SET SQL_MODE='STRICT_ALL_TABLES'; --error ER_BAD_NULL_ERROR UPDATE t SET c2=NULL; @@ -76,6 +66,99 @@ 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; +--echo # MDEV-18819 FIXME: Wrong result g=NULL +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 +--echo # MDEV-18819 FIXME: Wrong result g=NULL +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 WARN_DATA_TRUNCATED +ALTER TABLE t1 MODIFY c INT NOT NULL; +--error WARN_DATA_TRUNCATED +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 |