Debian Codegraph @

mysql-test/suite/innodb/t/innodb-alter-nullable.test

raw
1 --source include/innodb_page_size.inc
2
3 # Save the initial number of concurrent sessions.
4 --source include/count_sessions.inc
5
6 CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB;
7 INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
8
9 --enable_info
10 # This one will be a no-op.
11 # MySQL should perhaps issue an error, because it refuses to modify
12 # the PRIMARY KEY column c1 from NOT NULL to NULL.
13 ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
14
15 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
16 ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
17
18 # Request some conflicting changes for a single column.
19 --error ER_BAD_FIELD_ERROR
20 ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
21 --error ER_BAD_FIELD_ERROR
22 ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
23 --error ER_BAD_FIELD_ERROR
24 ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
25 --error ER_BAD_FIELD_ERROR
26 ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
27
28 # No-ops.
29 ALTER TABLE t CHANGE c2 c2 INT NOT NULL;
30 ALTER TABLE t MODIFY c2 INT NOT NULL;
31 --disable_info
32
33 connect (con1,localhost,root,,);
34
35 --error ER_BAD_NULL_ERROR
36 UPDATE t SET c2=NULL;
37
38 SELECT * FROM t;
39
40 connection default;
41
42 # This should change the column to NULL.
43 ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE;
44
45 connection con1;
46 BEGIN;
47 UPDATE t SET c2=NULL;
48 SELECT * FROM t;
49 ROLLBACK;
50 SELECT * FROM t;
51
52 disconnect con1;
53 connection default;
54
55 # This should be no-op.
56 ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE;
57
58 --replace_column 1 # 5 #
59 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
60 WHERE NAME='test/t';
61
62 DROP TABLE t;
63
64 CREATE TABLE t1(c1 INT) ENGINE=InnoDB;
65 ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1);
66 ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1);
67 DROP TABLE t1;
68
69 --echo #
70 --echo # MDEV-18732 InnoDB: ALTER IGNORE returns error for NULL
71 --echo #
72
73 CREATE TABLE t1(c INT) ENGINE=InnoDB;
74 INSERT INTO t1 VALUES (NULL);
75 --enable_info
76 ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
77 --disable_info
78 --error ER_BAD_NULL_ERROR
79 INSERT INTO t1 VALUES (NULL);
80 SELECT * FROM t1;
81 DROP TABLE t1;
82
83 CREATE TABLE t1(c INT) ENGINE=InnoDB;
84 INSERT INTO t1 VALUES (NULL),(1),(1);
85 --enable_info
86 ALTER IGNORE TABLE t1 ADD UNIQUE(c);
87 ALTER IGNORE TABLE t1 ADD PRIMARY KEY(c);
88 --disable_info
89 SELECT * FROM t1;
90 DROP TABLE t1;
91
92 CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
93 CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
94 CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
95 INSERT INTO t1 SET c=NULL;
96 INSERT INTO t2 SET c=NULL;
97 INSERT INTO t3 SET c=NULL;
98 SET @old_sql_mode = @@sql_mode;
99 # Allow lossy conversions of data
100 SET sql_mode = '';
101 --enable_info
102 ALTER TABLE t1 MODIFY c INT NOT NULL;
103 ALTER TABLE t2 MODIFY c INT NOT NULL;
104 ALTER TABLE t3 MODIFY c INT NOT NULL;
105 --disable_info
106 SET sql_mode = @old_sql_mode;
107 SELECT * FROM t1;
108 SELECT * FROM t2;
109 SELECT * FROM t3;
110 SELECT v FROM t3 FORCE INDEX(v);
111 CHECK TABLE t1,t2,t3;
112 DROP TABLE t1,t2,t3;
113
114 CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
115 CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
116 CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
117 INSERT INTO t1 SET c=NULL;
118 INSERT INTO t2 SET c=NULL;
119 INSERT INTO t3 SET c=NULL;
120 --enable_info
121 ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL;
122 ALTER IGNORE TABLE t2 MODIFY c INT NOT NULL;
123 ALTER IGNORE TABLE t3 MODIFY c INT NOT NULL;
124 --disable_info
125 SELECT * FROM t1;
126 SELECT * FROM t2;
127 SELECT * FROM t3;
128 SELECT v FROM t3 FORCE INDEX(v);
129 CHECK TABLE t1,t2,t3;
130 DROP TABLE t1,t2,t3;
131
132 CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
133 CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
134 CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
135 INSERT INTO t1 SET c=NULL;
136 INSERT INTO t2 SET c=NULL;
137 INSERT INTO t3 SET c=NULL;
138 --enable_info
139 --error WARN_DATA_TRUNCATED
140 ALTER TABLE t1 MODIFY c INT NOT NULL;
141 --error WARN_DATA_TRUNCATED
142 ALTER TABLE t2 MODIFY c INT NOT NULL;
143 --error WARN_DATA_TRUNCATED
144 ALTER TABLE t3 MODIFY c INT NOT NULL;
145 --disable_info
146 UPDATE t1 SET c=0;
147 UPDATE t2 SET c=0;
148 UPDATE t3 SET c=0;
149 --enable_info
150 ALTER TABLE t1 MODIFY c INT NOT NULL;
151 ALTER TABLE t2 MODIFY c INT NOT NULL;
152 ALTER TABLE t3 MODIFY c INT NOT NULL;
153 --disable_info
154 SELECT * FROM t1;
155 SELECT * FROM t2;
156 SELECT * FROM t3;
157 DROP TABLE t1,t2,t3;
158
159 # Check that all connections opened by test cases in this file are really
160 # gone so execution of other tests won't be affected by their presence.
161 --source include/wait_until_count_sessions.inc