Debian Codegraph @

mysql-test/suite/innodb/r/innodb-alter-nullable.result

raw
1 CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB;
2 INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
3 ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
4 ERROR 42000: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
5 ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
6 affected rows: 0
7 info: Records: 0 Duplicates: 0 Warnings: 0
8 ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
9 ERROR 42S22: Unknown column 'c2' in 't'
10 ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
11 ERROR 42S22: Unknown column 'c2' in 't'
12 ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
13 ERROR 42S22: Unknown column 'c2' in 't'
14 ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
15 ERROR 42S22: Unknown column 'c2' in 't'
16 ALTER TABLE t CHANGE c2 c2 INT NOT NULL;
17 affected rows: 0
18 info: Records: 0 Duplicates: 0 Warnings: 0
19 ALTER TABLE t MODIFY c2 INT NOT NULL;
20 affected rows: 0
21 info: Records: 0 Duplicates: 0 Warnings: 0
22 UPDATE t SET c2=NULL;
23 ERROR 23000: Column 'c2' cannot be null
24 SELECT * FROM t;
25 c1 c2 c3
26 1 2 3
27 4 5 6
28 7 8 9
29 ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE;
30 BEGIN;
31 UPDATE t SET c2=NULL;
32 SELECT * FROM t;
33 c1 c2 c3
34 1 NULL 3
35 4 NULL 6
36 7 NULL 9
37 ROLLBACK;
38 SELECT * FROM t;
39 c1 c2 c3
40 1 2 3
41 4 5 6
42 7 8 9
43 ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE;
44 SELECT TABLE_ID, NAME, FLAG, N_COLS, SPACE, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE,
45 INSTANT_COLS, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
46 WHERE NAME='test/t';
47 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE INSTANT_COLS TOTAL_ROW_VERSIONS
48 # test/t 33 6 # Dynamic 0 Single 0 0
49 DROP TABLE t;
50 CREATE TABLE t1(c1 INT);
51 ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1);
52 ALTER TABLE t1 CHANGE c1 c1 INT SIGNED NOT NULL,ADD KEY(c1);
53 DROP TABLE t1;