Debian Codegraph @

mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc

raw
1 # skip sort for prefix change
2 # pk(o1(2)) to pk(o1(3))
3 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
4 insert into t1 values('abd'), ('acd');
5 alter table t1 drop primary key, add primary key(o1(3)), lock=none;
6 drop table t1;
7
8 # pk(o1(2)) to pk(o1)
9 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
10 insert into t1 values('abd'), ('acd');
11 alter table t1 drop primary key, add primary key(o1), lock=none;
12 drop table t1;
13
14 # pk(o1(2)) to pk(o1(3),n1)
15 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
16 insert into t1 values('abd'), ('acd');
17 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
18 drop table t1;
19
20 # pk(o1(2)) to pk(o1,n1)
21 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
22 insert into t1 values('abd'), ('acd');
23 alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
24 drop table t1;
25
26 # pk(o1(2)) to pk(o1(3), o2)
27 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
28 insert into t1 values('abd', 1), ('acd', 2);
29 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
30 drop table t1;
31
32 # pk(o1(2)) to pk(o1, o2)
33 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
34 insert into t1 values('abd', 1), ('acd', 2);
35 alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
36 drop table t1;
37
38 # pk(o1(3)) to pk(o1(2))
39 create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
40 insert into t1 values('abd'), ('acd');
41 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
42 drop table t1;
43
44 # pk(o1) to pk(o1(2))
45 create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
46 insert into t1 values('abd'), ('acd');
47 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
48 drop table t1;
49
50 # pk(o1(3),o2) to pk(o1(2))
51 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
52 insert into t1 values('abd', 1), ('acd', 2);
53 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
54 drop table t1;
55
56 # pk(o1,o2) to pk(o1(2))
57 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
58 insert into t1 values('abd', 1), ('acd', 2);
59 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
60 drop table t1;
61
62 # pk(o1(3),o2) to pk(o1(2),n1)
63 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
64 insert into t1 values('abd', 1), ('acd', 2);
65 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
66 drop table t1;
67
68 # pk(o1,o2) to pk(o1(2),n1)
69 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
70 insert into t1 values('abd', 1), ('acd', 2);
71 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
72 drop table t1;
73
74 # pk(o1(3),o2) to pk(o1(3),n1)
75 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
76 insert into t1 values('abd', 1), ('acd', 2);
77 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
78 drop table t1;
79
80 # pk(o1,o2) to pk(o1,n1)
81 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
82 insert into t1 values('abd', 1), ('acd', 2);
83 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
84 drop table t1;
85
86 # pk(o1,o2(3)) to pk(o1,o2(2))
87 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
88 insert into t1 values(1,'abd'), (2,'acd');
89 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
90 drop table t1;
91
92 # pk(o1,o2) to pk(o1,o2(2))
93 create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
94 insert into t1 values(1,'abd'), (2,'acd');
95 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
96 drop table t1;
97
98 # pk(o1,o2(2)) to pk(o1,o2(3))
99 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
100 insert into t1 values(1, 'abd'), (2, 'acd');
101 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
102 drop table t1;
103
104 # pk(o1,o2(2)) to pk(o1,o2)
105 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
106 insert into t1 values(1, 'abd'), (2, 'acd');
107 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
108 drop table t1;
109
110 # pk(o1,o2(3),o3) to pk(o1,o2(2))
111 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
112 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
113 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
114 drop table t1;
115
116 # pk(o1,o2,o3) to pk(o1,o2(2))
117 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
118 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
119 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
120 drop table t1;
121
122 # pk(o1(3),o2(3)) to pk(o1(3),o2(2))
123 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
124 insert into t1 values('abd', 'acd'), ('acd', 'abd');
125 alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
126 drop table t1;
127
128 # pk(o1,o2) to pk(o1,o2(2))
129 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
130 insert into t1 values('abd', 'acd'), ('acd', 'abd');
131 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
132 drop table t1;
133
134 # pk(o1(3),o2(2)) to pk(o1(3),o2(3))
135 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
136 insert into t1 values('abd', 'acd'), ('acd', 'abd');
137 alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
138 drop table t1;
139
140 # pk(o1,o2(2)) to pk(o1,o2)
141 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
142 insert into t1 values('abd', 'acd'), ('acd', 'abd');
143 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
144 drop table t1;
145
146 # pk(o1(3),o2,o3(2)) to pk(o1(3),o2,o3(3))
147 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
148 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
149 alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
150 drop table t1;
151
152 # pk(o1,o2,o3(2)) to pk(o1,o2,o3)
153 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
154 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
155 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
156 drop table t1;
157
158 # pk(o1(3),o2,o3(3)) to pk(o1(3),o2,o3(2))
159 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
160 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
161 alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
162 drop table t1;
163
164 # pk(o1,o2,o3) to pk(o1,o2,o3(2))
165 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
166 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
167 alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
168 drop table t1;
169
170 # skip sort for adding existing columns/newly added columns, dropping pk columns at the end.
171 # pk(o1) to pk(o1,o2)
172 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
173 insert into t1 values(1,1),(2,2);
174 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
175 drop table t1;
176
177 # pk(o1) to pk(o1,n1)
178 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
179 insert into t1 values(1,1),(2,2);
180 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
181 drop table t1;
182
183 # pk(o1) to pk(n1,o1)
184 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
185 insert into t1 values(1,1),(2,2);
186 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
187 drop table t1;
188
189 # pk(o1) to pk(n1,o1,n2)
190 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
191 insert into t1 values(1,1),(2,2);
192 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
193 drop table t1;
194
195 # pk(o1) to pk(n1,n2,o1)
196 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
197 insert into t1 values(1,1),(2,2);
198 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
199 drop table t1;
200
201 # pk(o1) to pk(o1,n1,n2)
202 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
203 insert into t1 values(1,1),(2,2);
204 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
205 drop table t1;
206
207 # pk(o1) to pk(o1,o2,n1)
208 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
209 insert into t1 values(1,1),(2,2);
210 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
211 drop table t1;
212
213 # pk(o1) to pk(o1,n1,o2)
214 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
215 insert into t1 values(1,1),(2,2);
216 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
217 drop table t1;
218
219 # pk(o1) to pk(n1,o1,o2)
220 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
221 insert into t1 values(1,1),(2,2);
222 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
223 drop table t1;
224
225 # pk(o1) to pk(o1,o2,o3)
226 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
227 insert into t1 values(1,1,2),(2,2,1);
228 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
229 drop table t1;
230
231 # pk(o1) to pk(o1,o3,o2)
232 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
233 insert into t1 values(1,1,2),(2,2,1);
234 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
235 drop table t1;
236
237 # pk(o1,o2,o3) to pk(o1,o2)
238 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
239 insert into t1 values(1,1,2),(2,2,1);
240 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
241 drop table t1;
242
243 # pk(o1,o2,o3) to pk(o1,o2,o3,o4)
244 create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
245 insert into t1 values(1,1,2,2),(2,2,1,1);
246 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
247 drop table t1;
248
249 # pk(o1,o2,o3) to pk(o1,o2,n1)
250 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
251 insert into t1 values(1,1,2),(2,2,1);
252 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
253 drop table t1;
254
255 # pk(o1,o2,o3) to pk(o1,n1,o2)
256 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
257 insert into t1 values(1,1,2),(2,2,1);
258 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
259 drop table t1;
260
261 # pk(o1,o2,o3) to pk(o1)
262 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
263 insert into t1 values(1,1,2),(2,2,1);
264 alter table t1 drop primary key, add primary key(o1), lock=none;
265 drop table t1;
266
267 # pk(o1) to pk(o1,o2)
268 CREATE TABLE t1(o1 int, o2 int, o3 int, PRIMARY KEY(o1), KEY(o2)) ENGINE=INNODB;
269 INSERT INTO t1 VALUES(1,1,2),(2,2,1);
270
271 EXPLAIN SELECT * FROM t1 ORDER BY o2;
272 SELECT * FROM t1 ORDER BY o2;
273
274 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 DESC);
275
276 CHECK TABLE t1;
277 SELECT * FROM t1;
278 EXPLAIN SELECT * FROM t1 ORDER BY o2;
279 SELECT * FROM t1 ORDER BY o2;
280
281 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 ASC);
282
283 CHECK TABLE t1;
284 SELECT * FROM t1;
285 EXPLAIN SELECT * FROM t1 ORDER BY o2;
286 SELECT * FROM t1 ORDER BY o2;
287
288 DROP TABLE t1;