1
by brian
clean slate |
1 |
#
|
2 |
# Test of alter table
|
|
3 |
#
|
|
4 |
--disable_warnings |
|
5 |
drop table if exists t1,t2; |
|
6 |
drop database if exists mysqltest; |
|
7 |
--enable_warnings |
|
8 |
||
9 |
create table t1 ( |
|
10 |
col1 int not null auto_increment primary key, |
|
11 |
col2 varchar(30) not null, |
|
12 |
col3 varchar (20) not null, |
|
13 |
col4 varchar(4) not null, |
|
14 |
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, |
|
15 |
col6 int not null, to_be_deleted int); |
|
16 |
insert into t1 values (2,4,3,5,"PENDING",1,7); |
|
17 |
alter table t1 |
|
18 |
add column col4_5 varchar(20) not null after col4, |
|
19 |
add column col7 varchar(30) not null after col5, |
|
20 |
add column col8 datetime not null, drop column to_be_deleted, |
|
21 |
change column col2 fourth varchar(30) not null after col3, |
|
22 |
modify column col6 int not null first; |
|
23 |
select * from t1; |
|
24 |
drop table t1; |
|
25 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
26 |
create table t1 (bandID INT NOT NULL PRIMARY KEY, payoutID int NOT NULL); |
1
by brian
clean slate |
27 |
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); |
201
by Brian Aker
Convert default engine to Innodb |
28 |
alter table t1 add column new_col int; |
1
by brian
clean slate |
29 |
select * from t1; |
201
by Brian Aker
Convert default engine to Innodb |
30 |
alter table t1; |
1
by brian
clean slate |
31 |
select * from t1; |
32 |
drop table t1; |
|
33 |
||
34 |
# Check that pack_keys and dynamic length rows are not forced.
|
|
35 |
||
36 |
CREATE TABLE t1 ( |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
37 |
GROUP_ID int DEFAULT '0' NOT NULL, |
38 |
LANG_ID int DEFAULT '0' NOT NULL, |
|
1
by brian
clean slate |
39 |
NAME varchar(80) DEFAULT '' NOT NULL, |
40 |
PRIMARY KEY (GROUP_ID,LANG_ID), |
|
41 |
KEY NAME (NAME)); |
|
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
42 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
43 |
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; |
44 |
--replace_column 8 # |
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
45 |
show COLUMNS FROM t1; |
1
by brian
clean slate |
46 |
DROP TABLE t1; |
47 |
||
48 |
#
|
|
49 |
# Test of ALTER TABLE ... ORDER BY
|
|
50 |
#
|
|
51 |
||
52 |
create table t1 (n int); |
|
53 |
insert into t1 values(9),(3),(12),(10); |
|
54 |
alter table t1 order by n; |
|
55 |
select * from t1; |
|
56 |
drop table t1; |
|
57 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
58 |
CREATE TEMPORARY TABLE t1 ( |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
59 |
id int NOT NULL default '0', |
60 |
category_id int NOT NULL default '0', |
|
61 |
type_id int NOT NULL default '0', |
|
1
by brian
clean slate |
62 |
body text NOT NULL, |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
63 |
user_id int NOT NULL default '0', |
1
by brian
clean slate |
64 |
status enum('new','old') NOT NULL default 'new', |
65 |
PRIMARY KEY (id) |
|
66 |
) ENGINE=MyISAM; |
|
67 |
||
68 |
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; |
|
69 |
DROP TABLE t1; |
|
70 |
||
71 |
#
|
|
72 |
# Drop and add an auto_increment column
|
|
73 |
#
|
|
74 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
75 |
create table t1 (i int not null auto_increment primary key); |
1
by brian
clean slate |
76 |
insert into t1 values (null),(null),(null),(null); |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
77 |
alter table t1 drop i,add i int not null auto_increment, drop primary key, add primary key (i); |
1
by brian
clean slate |
78 |
select * from t1; |
79 |
drop table t1; |
|
80 |
||
81 |
#
|
|
82 |
# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1
|
|
83 |
# if it exists
|
|
84 |
#
|
|
85 |
create table t1 (name char(15)); |
|
86 |
insert into t1 (name) values ("current"); |
|
87 |
create database mysqltest; |
|
88 |
create table mysqltest.t1 (name char(15)); |
|
89 |
insert into mysqltest.t1 (name) values ("mysqltest"); |
|
90 |
select * from t1; |
|
91 |
select * from mysqltest.t1; |
|
92 |
--error ER_TABLE_EXISTS_ERROR |
|
93 |
alter table t1 rename mysqltest.t1; |
|
94 |
select * from t1; |
|
95 |
select * from mysqltest.t1; |
|
96 |
drop table t1; |
|
97 |
drop database mysqltest; |
|
98 |
||
99 |
#
|
|
100 |
# ALTER TABLE ... ENABLE/DISABLE KEYS
|
|
101 |
||
102 |
create table t1 (n1 int not null, n2 int, n3 int, n4 float, |
|
103 |
unique(n1), |
|
104 |
key (n1, n2, n3, n4), |
|
105 |
key (n2, n3, n4, n1), |
|
106 |
key (n3, n4, n1, n2), |
|
107 |
key (n4, n1, n2, n3) ); |
|
201
by Brian Aker
Convert default engine to Innodb |
108 |
alter table t1; |
1
by brian
clean slate |
109 |
show keys from t1; |
110 |
#let $1=10000;
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
111 |
set autocommit=0; |
112 |
begin; |
|
1
by brian
clean slate |
113 |
let $1=10; |
114 |
while ($1) |
|
115 |
{
|
|
116 |
eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); |
|
117 |
dec $1; |
|
118 |
}
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
119 |
commit; |
120 |
set autocommit=1; |
|
1
by brian
clean slate |
121 |
alter table t1 enable keys; |
122 |
show keys from t1; |
|
123 |
drop table t1; |
|
124 |
||
125 |
#
|
|
126 |
# Alter table and rename
|
|
127 |
#
|
|
128 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
129 |
create table t1 (i int not null auto_increment primary key); |
1
by brian
clean slate |
130 |
alter table t1 rename t2; |
131 |
alter table t2 rename t1, add c char(10) comment "no comment"; |
|
132 |
show columns from t1; |
|
133 |
drop table t1; |
|
134 |
||
135 |
# implicit analyze
|
|
136 |
||
137 |
create table t1 (a int, b int); |
|
138 |
let $1=100; |
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
139 |
set autocommit=0; |
140 |
begin; |
|
1
by brian
clean slate |
141 |
while ($1) |
142 |
{
|
|
143 |
eval insert into t1 values(1,$1), (2,$1), (3, $1); |
|
144 |
dec $1; |
|
145 |
}
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
146 |
commit; |
147 |
set autocommit=1; |
|
1
by brian
clean slate |
148 |
alter table t1 add unique (a,b), add key (b); |
149 |
show keys from t1; |
|
150 |
analyze table t1; |
|
151 |
show keys from t1; |
|
152 |
drop table t1; |
|
153 |
||
154 |
#
|
|
155 |
# Test with two keys
|
|
156 |
#
|
|
157 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
158 |
CREATE TEMPORARY TABLE t1 ( |
1217
by Brian Aker
Removed bits of charset support from the parser. |
159 |
Host varchar(16) NOT NULL default '', |
160 |
User varchar(16) NOT NULL default '', |
|
1
by brian
clean slate |
161 |
PRIMARY KEY (Host,User), |
162 |
KEY (Host) |
|
163 |
) ENGINE=MyISAM; |
|
164 |
||
165 |
ALTER TABLE t1 DISABLE KEYS; |
|
1273.19.2
by Brian Aker
First pass through show indexs. |
166 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
167 |
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); |
1273.19.2
by Brian Aker
First pass through show indexs. |
168 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
169 |
ALTER TABLE t1 ENABLE KEYS; |
1273.19.2
by Brian Aker
First pass through show indexs. |
170 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
171 |
CHECK TABLES t1; |
172 |
||
1054.1.1
by Brian Aker
Remove guts in parser for LOCK TABLE. |
173 |
# Test RENAME
|
1
by brian
clean slate |
174 |
ALTER TABLE t1 RENAME t2; |
175 |
select * from t2; |
|
176 |
DROP TABLE t2; |
|
177 |
||
178 |
#
|
|
179 |
# BUG#4717 - check for valid table names
|
|
180 |
#
|
|
181 |
create table t1 (a int); |
|
182 |
--error ER_WRONG_TABLE_NAME |
|
183 |
alter table t1 rename to ``; |
|
184 |
--error ER_WRONG_TABLE_NAME |
|
185 |
rename table t1 to ``; |
|
186 |
drop table t1; |
|
187 |
||
188 |
#
|
|
189 |
# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns
|
|
190 |
#
|
|
191 |
drop table if exists t1; |
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
192 |
create TEMPORARY table t1 ( a varchar(10) not null primary key ) engine=myisam; |
1
by brian
clean slate |
193 |
flush tables; |
194 |
alter table t1 modify a varchar(10); |
|
195 |
flush tables; |
|
196 |
alter table t1 modify a varchar(10) not null; |
|
197 |
drop table if exists t1; |
|
198 |
||
199 |
# The following is also part of bug #6236 (CREATE TABLE didn't properly count
|
|
200 |
# not null columns for primary keys)
|
|
201 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
202 |
create TEMPORARY table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; |
896.3.6
by Stewart Smith
Read Fields out of proto instead of FRM. |
203 |
insert into t1 (a,b,c,d,e,f,g,h,i) values(1,1,1,1,1,1,1,1,1); |
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
204 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
205 |
show table status like 't1'; |
206 |
alter table t1 modify a int; |
|
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
207 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
208 |
show table status like 't1'; |
209 |
drop table t1; |
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
210 |
create TEMPORARY table t1 (a int not null default 0, b int not null default 0, c int not null default 0, d int not null default 0, e int not null default 0, f int not null default 0, g int not null default 0, h int not null default 0,i int not null default 0, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; |
1
by brian
clean slate |
211 |
insert into t1 (a) values(1); |
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
212 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
213 |
show table status like 't1'; |
214 |
drop table t1; |
|
215 |
||
216 |
#
|
|
217 |
# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)
|
|
218 |
#
|
|
219 |
||
220 |
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); |
|
221 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
222 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
223 |
SHOW CREATE TABLE t1; |
224 |
--error ER_CANT_DROP_FIELD_OR_KEY |
|
225 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
226 |
DROP TABLE t1; |
|
227 |
||
228 |
# BUG#3899
|
|
229 |
create table t1 (a int, b int, key(a)); |
|
230 |
insert into t1 values (1,1), (2,2); |
|
231 |
--error ER_CANT_DROP_FIELD_OR_KEY |
|
232 |
alter table t1 drop key no_such_key; |
|
233 |
alter table t1 drop key a; |
|
234 |
drop table t1; |
|
235 |
||
236 |
#
|
|
237 |
# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)
|
|
238 |
#
|
|
239 |
# Some platforms (Mac OS X, Windows) will send the error message using small letters.
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
240 |
CREATE TEMPORARY TABLE T12207(a int) ENGINE=MYISAM; |
1
by brian
clean slate |
241 |
--replace_result t12207 T12207 |
242 |
--error ER_ILLEGAL_HA |
|
243 |
ALTER TABLE T12207 DISCARD TABLESPACE; |
|
244 |
DROP TABLE T12207; |
|
245 |
||
246 |
#
|
|
247 |
# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"
|
|
248 |
# MySQL should not think that packed field with non-zero decimals is
|
|
249 |
# geometry field and allow to create prefix index which is
|
|
250 |
# shorter than packed field length.
|
|
251 |
#
|
|
252 |
create table t1 ( a timestamp ); |
|
253 |
--error ER_WRONG_SUB_KEY |
|
254 |
alter table t1 add unique ( a(1) ); |
|
255 |
drop table t1; |
|
256 |
||
257 |
#
|
|
258 |
# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table
|
|
259 |
#
|
|
260 |
# This problem happens if the data change is compatible.
|
|
261 |
# Changing to the same type is compatible for example.
|
|
262 |
#
|
|
263 |
--disable_warnings |
|
264 |
drop table if exists t1; |
|
265 |
--enable_warnings |
|
266 |
create table t1 (a int, key(a)); |
|
267 |
show indexes from t1; |
|
268 |
--echo "this used not to disable the index" |
|
201
by Brian Aker
Convert default engine to Innodb |
269 |
alter table t1 modify a int; |
270 |
show indexes from t1; |
|
271 |
||
272 |
alter table t1 enable keys; |
|
273 |
show indexes from t1; |
|
274 |
||
275 |
alter table t1 modify a bigint; |
|
276 |
show indexes from t1; |
|
277 |
||
278 |
alter table t1 enable keys; |
|
279 |
show indexes from t1; |
|
280 |
||
281 |
alter table t1 add b char(10); |
|
282 |
show indexes from t1; |
|
283 |
||
284 |
alter table t1 add c decimal(10,2); |
|
1
by brian
clean slate |
285 |
show indexes from t1; |
286 |
||
287 |
--echo "this however did" |
|
201
by Brian Aker
Convert default engine to Innodb |
288 |
alter table t1; |
1
by brian
clean slate |
289 |
show indexes from t1; |
290 |
||
291 |
desc t1; |
|
292 |
||
293 |
alter table t1 add d decimal(15,5); |
|
294 |
--echo "The key should still be disabled" |
|
295 |
show indexes from t1; |
|
296 |
||
297 |
drop table t1; |
|
298 |
||
299 |
--echo "Now will test with one unique index" |
|
300 |
create table t1(a int, b char(10), unique(a)); |
|
301 |
show indexes from t1; |
|
201
by Brian Aker
Convert default engine to Innodb |
302 |
alter table t1; |
1
by brian
clean slate |
303 |
show indexes from t1; |
304 |
alter table t1 enable keys; |
|
305 |
||
306 |
--echo "If no copy on noop change, this won't touch the data file" |
|
307 |
--echo "Unique index, no change" |
|
201
by Brian Aker
Convert default engine to Innodb |
308 |
alter table t1 modify a int; |
1
by brian
clean slate |
309 |
show indexes from t1; |
310 |
||
311 |
--echo "Change the type implying data copy" |
|
312 |
--echo "Unique index, no change" |
|
201
by Brian Aker
Convert default engine to Innodb |
313 |
alter table t1 modify a bigint; |
1
by brian
clean slate |
314 |
show indexes from t1; |
315 |
||
316 |
alter table t1 modify a bigint; |
|
317 |
show indexes from t1; |
|
318 |
||
319 |
alter table t1 modify a int; |
|
320 |
show indexes from t1; |
|
321 |
||
322 |
drop table t1; |
|
323 |
||
324 |
--echo "Now will test with one unique and one non-unique index" |
|
325 |
create table t1(a int, b char(10), unique(a), key(b)); |
|
326 |
show indexes from t1; |
|
201
by Brian Aker
Convert default engine to Innodb |
327 |
alter table t1; |
1
by brian
clean slate |
328 |
show indexes from t1; |
329 |
alter table t1 enable keys; |
|
330 |
||
331 |
||
332 |
--echo "If no copy on noop change, this won't touch the data file" |
|
333 |
--echo "The non-unique index will be disabled" |
|
201
by Brian Aker
Convert default engine to Innodb |
334 |
alter table t1 modify a int; |
1
by brian
clean slate |
335 |
show indexes from t1; |
336 |
alter table t1 enable keys; |
|
337 |
show indexes from t1; |
|
338 |
||
339 |
--echo "Change the type implying data copy" |
|
340 |
--echo "The non-unique index will be disabled" |
|
201
by Brian Aker
Convert default engine to Innodb |
341 |
alter table t1 modify a bigint; |
1
by brian
clean slate |
342 |
show indexes from t1; |
343 |
||
344 |
--echo "Change again the type, but leave the indexes as_is" |
|
345 |
alter table t1 modify a int; |
|
346 |
show indexes from t1; |
|
347 |
--echo "Try the same. When data is no copied on similar tables, this is noop" |
|
348 |
alter table t1 modify a int; |
|
349 |
show indexes from t1; |
|
350 |
||
351 |
drop table t1; |
|
352 |
||
353 |
||
354 |
#
|
|
355 |
# Bug#11493 - Alter table rename to default database does not work without
|
|
356 |
# db name qualifying
|
|
357 |
#
|
|
358 |
create database mysqltest; |
|
359 |
create table t1 (c1 int); |
|
360 |
# Move table to other database.
|
|
361 |
alter table t1 rename mysqltest.t1; |
|
362 |
# Assure that it has moved.
|
|
363 |
--error ER_BAD_TABLE_ERROR |
|
364 |
drop table t1; |
|
365 |
# Move table back.
|
|
366 |
alter table mysqltest.t1 rename t1; |
|
367 |
# Assure that it is back.
|
|
368 |
drop table t1; |
|
369 |
# Now test for correct message if no database is selected.
|
|
370 |
# Create t1 in 'test'.
|
|
371 |
create table t1 (c1 int); |
|
372 |
# Change to other db.
|
|
373 |
use mysqltest; |
|
374 |
# Drop the current db. This de-selects any db.
|
|
375 |
drop database mysqltest; |
|
376 |
# Now test for correct message.
|
|
377 |
--error ER_NO_DB_ERROR |
|
378 |
alter table test.t1 rename t1; |
|
379 |
# Check that explicit qualifying works even with no selected db.
|
|
380 |
alter table test.t1 rename test.t1; |
|
381 |
# Go back to standard 'test' db.
|
|
382 |
use test; |
|
383 |
drop table t1; |
|
384 |
||
385 |
#
|
|
1223.1.4
by Brian Aker
Merge Brian |
386 |
# BUG#23404 - ROW_FORMAT=COMPACT option is lost is an index is added to the
|
1
by brian
clean slate |
387 |
# table
|
388 |
#
|
|
1222.1.6
by Brian Aker
Fix engines to not rely on HA_CREATE_INFO. |
389 |
CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT; |
1
by brian
clean slate |
390 |
CREATE INDEX i1 ON t1(a); |
942.3.1
by Vladimir Kolesnikov
test generalizations |
391 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
392 |
SHOW CREATE TABLE t1; |
393 |
DROP INDEX i1 ON t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
394 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
395 |
SHOW CREATE TABLE t1; |
396 |
DROP TABLE t1; |
|
397 |
||
398 |
#
|
|
399 |
# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash
|
|
400 |
#
|
|
401 |
--disable_warnings |
|
402 |
DROP TABLE IF EXISTS bug24219; |
|
403 |
DROP TABLE IF EXISTS bug24219_2; |
|
404 |
--enable_warnings |
|
405 |
||
406 |
CREATE TABLE bug24219 (a INT, INDEX(a)); |
|
407 |
||
408 |
SHOW INDEX FROM bug24219; |
|
409 |
||
410 |
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; |
|
411 |
||
412 |
SHOW INDEX FROM bug24219_2; |
|
413 |
||
414 |
DROP TABLE bug24219_2; |
|
415 |
||
416 |
#
|
|
417 |
# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
|
|
418 |
#
|
|
419 |
||
420 |
--disable_warnings |
|
421 |
drop table if exists table_24562; |
|
422 |
--enable_warnings |
|
423 |
||
424 |
create table table_24562( |
|
425 |
section int, |
|
426 |
subsection int, |
|
427 |
title varchar(50)); |
|
428 |
||
429 |
insert into table_24562 values |
|
430 |
(1, 0, "Introduction"), |
|
431 |
(1, 1, "Authors"), |
|
432 |
(1, 2, "Acknowledgements"), |
|
433 |
(2, 0, "Basics"), |
|
434 |
(2, 1, "Syntax"), |
|
435 |
(2, 2, "Client"), |
|
436 |
(2, 3, "Server"), |
|
437 |
(3, 0, "Intermediate"), |
|
438 |
(3, 1, "Complex queries"), |
|
439 |
(3, 2, "Stored Procedures"), |
|
440 |
(3, 3, "Stored Functions"), |
|
441 |
(4, 0, "Advanced"), |
|
442 |
(4, 1, "Replication"), |
|
443 |
(4, 2, "Load balancing"), |
|
444 |
(4, 3, "High availability"), |
|
445 |
(5, 0, "Conclusion"); |
|
446 |
||
447 |
select * from table_24562; |
|
448 |
||
449 |
alter table table_24562 add column reviewer varchar(20), |
|
450 |
order by title; |
|
451 |
||
452 |
select * from table_24562; |
|
453 |
||
454 |
update table_24562 set reviewer="Me" where section=2; |
|
455 |
update table_24562 set reviewer="You" where section=3; |
|
456 |
||
457 |
alter table table_24562 |
|
458 |
order by section ASC, subsection DESC; |
|
459 |
||
460 |
select * from table_24562; |
|
461 |
||
462 |
alter table table_24562 |
|
463 |
order by table_24562.subsection ASC, table_24562.section DESC; |
|
464 |
||
465 |
select * from table_24562; |
|
466 |
||
467 |
--error ER_PARSE_ERROR |
|
468 |
alter table table_24562 order by 12; |
|
469 |
--error ER_PARSE_ERROR |
|
470 |
alter table table_24562 order by (section + 12); |
|
471 |
--error ER_PARSE_ERROR |
|
472 |
alter table table_24562 order by length(title); |
|
473 |
||
474 |
--error ER_BAD_FIELD_ERROR |
|
475 |
alter table table_24562 order by no_such_col; |
|
476 |
||
477 |
drop table table_24562; |
|
478 |
||
479 |
# End of 4.1 tests
|
|
480 |
||
481 |
#
|
|
482 |
# Bug #14693 (ALTER SET DEFAULT doesn't work)
|
|
483 |
#
|
|
484 |
||
223
by Brian Aker
Cleanup int() work. |
485 |
create table t1 (mycol int not null); |
1
by brian
clean slate |
486 |
alter table t1 alter column mycol set default 0; |
487 |
desc t1; |
|
488 |
drop table t1; |
|
489 |
||
490 |
#
|
|
491 |
# Bug#25262 Auto Increment lost when changing Engine type
|
|
492 |
#
|
|
493 |
||
1233.2.1
by Monty Taylor
Renamed instances of HEAP engine to MEMORY. Removed the alias. |
494 |
create TEMPORARY table t1(id int primary key auto_increment) engine=MEMORY; |
1
by brian
clean slate |
495 |
|
496 |
insert into t1 values (null); |
|
497 |
insert into t1 values (null); |
|
498 |
||
499 |
select * from t1; |
|
500 |
||
501 |
# Set auto increment to 50
|
|
502 |
alter table t1 auto_increment = 50; |
|
503 |
||
504 |
# Alter to myisam
|
|
505 |
alter table t1 engine = myisam; |
|
506 |
||
507 |
# This insert should get id 50
|
|
508 |
insert into t1 values (null); |
|
509 |
select * from t1; |
|
510 |
||
1233.2.1
by Monty Taylor
Renamed instances of HEAP engine to MEMORY. Removed the alias. |
511 |
# Alter to MEMORY again
|
512 |
alter table t1 engine = MEMORY; |
|
1
by brian
clean slate |
513 |
insert into t1 values (null); |
514 |
select * from t1; |
|
515 |
||
516 |
drop table t1; |
|
517 |
||
186
by Brian Aker
Partial fix for alter table |
518 |
##
|
519 |
## Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
|
|
520 |
## NO_ZERO_DATE mode.
|
|
521 |
##
|
|
522 |
#create table t1(f1 int);
|
|
523 |
#alter table t1 add column f2 datetime not null, add column f21 date not null;
|
|
524 |
#insert into t1 values(1,'2000-01-01','2000-01-01');
|
|
525 |
#--error 1292
|
|
526 |
#alter table t1 add column f3 datetime not null;
|
|
527 |
#--error 1292
|
|
528 |
#alter table t1 add column f3 date not null;
|
|
529 |
#--error 1292
|
|
530 |
#alter table t1 add column f4 datetime not null default '2002-02-02',
|
|
531 |
# add column f41 date not null;
|
|
532 |
#alter table t1 add column f4 datetime not null default '2002-02-02',
|
|
533 |
# add column f41 date not null default '2002-02-02';
|
|
534 |
#select * from t1;
|
|
535 |
#drop table t1;
|
|
1
by brian
clean slate |
536 |
|
537 |
#
|
|
538 |
# Some additional tests for new, faster alter table. Note that most of the
|
|
539 |
# whole alter table code is being tested all around the test suite already.
|
|
540 |
#
|
|
541 |
||
542 |
create table t1 (v varchar(32)); |
|
543 |
insert into t1 values ('def'),('abc'),('hij'),('3r4f'); |
|
544 |
select * from t1; |
|
545 |
# Fast alter, no copy performed
|
|
546 |
alter table t1 change v v2 varchar(32); |
|
547 |
select * from t1; |
|
548 |
# Fast alter, no copy performed
|
|
549 |
alter table t1 change v2 v varchar(64); |
|
550 |
select * from t1; |
|
551 |
update t1 set v = 'lmn' where v = 'hij'; |
|
552 |
select * from t1; |
|
553 |
# Regular alter table
|
|
554 |
alter table t1 add i int auto_increment not null primary key first; |
|
555 |
select * from t1; |
|
556 |
update t1 set i=5 where i=3; |
|
557 |
select * from t1; |
|
558 |
alter table t1 change i i bigint; |
|
559 |
select * from t1; |
|
560 |
alter table t1 add unique key (i, v); |
|
561 |
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); |
|
562 |
drop table t1; |
|
563 |
||
564 |
#
|
|
565 |
# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
|
|
566 |
# without # prefix is not allowed for TEXT columns, while index
|
|
567 |
# is defined with prefix.
|
|
568 |
#
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
569 |
create TEMPORARY table t1 (t varchar(255) default null, key t (t(80))) engine=myisam; |
1
by brian
clean slate |
570 |
alter table t1 change t t text; |
571 |
drop table t1; |
|
572 |
||
573 |
#
|
|
574 |
# Bug#18038 MySQL server corrupts binary columns data
|
|
575 |
#
|
|
576 |
||
1217
by Brian Aker
Removed bits of charset support from the parser. |
577 |
CREATE TABLE t1 (s CHAR(8)); |
1
by brian
clean slate |
578 |
INSERT INTO t1 VALUES ('test'); |
579 |
SELECT LENGTH(s) FROM t1; |
|
1217
by Brian Aker
Removed bits of charset support from the parser. |
580 |
ALTER TABLE t1 MODIFY s CHAR(10); |
1
by brian
clean slate |
581 |
SELECT LENGTH(s) FROM t1; |
582 |
DROP TABLE t1; |
|
583 |
||
233
by Brian Aker
Fix to remove binary/nchar |
584 |
CREATE TABLE t1 (s varbinary(8)); |
1
by brian
clean slate |
585 |
INSERT INTO t1 VALUES ('test'); |
586 |
SELECT LENGTH(s) FROM t1; |
|
587 |
SELECT HEX(s) FROM t1; |
|
233
by Brian Aker
Fix to remove binary/nchar |
588 |
ALTER TABLE t1 MODIFY s varbinary(10); |
1
by brian
clean slate |
589 |
SELECT HEX(s) FROM t1; |
590 |
SELECT LENGTH(s) FROM t1; |
|
591 |
DROP TABLE t1; |
|
592 |
||
593 |
#
|
|
594 |
# Bug#19386: Multiple alter causes crashed table
|
|
595 |
# The trailing column would get corrupted data, or server could not even read
|
|
596 |
# it.
|
|
597 |
#
|
|
598 |
||
599 |
CREATE TABLE t1 (v VARCHAR(3), b INT); |
|
600 |
INSERT INTO t1 VALUES ('abc', 5); |
|
601 |
SELECT * FROM t1; |
|
602 |
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); |
|
603 |
SELECT * FROM t1; |
|
604 |
DROP TABLE t1; |
|
605 |
||
606 |
--echo End of 5.0 tests |
|
607 |
||
608 |
#
|
|
609 |
# Bug#18775 - Temporary table from alter table visible to other threads
|
|
610 |
#
|
|
611 |
# Check if special characters work and duplicates are detected.
|
|
612 |
--disable_warnings |
|
613 |
DROP TABLE IF EXISTS `t+1`, `t+2`; |
|
614 |
--enable_warnings |
|
615 |
CREATE TABLE `t+1` (c1 INT); |
|
616 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
617 |
CREATE TABLE `t+1` (c1 INT); |
|
618 |
--error ER_TABLE_EXISTS_ERROR |
|
619 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
620 |
DROP TABLE `t+1`, `t+2`; |
|
621 |
#
|
|
622 |
# Same for temporary tables though these names do not become file names.
|
|
623 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
624 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
625 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
626 |
--error ER_TABLE_EXISTS_ERROR |
|
627 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
628 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
629 |
SHOW CREATE TABLE `tt+1`; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
630 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
631 |
SHOW CREATE TABLE `tt+2`; |
632 |
DROP TABLE `tt+1`, `tt+2`; |
|
186
by Brian Aker
Partial fix for alter table |
633 |
##
|
634 |
## Check if special characters as in tmp_file_prefix work.
|
|
635 |
#CREATE TABLE `#sql1` (c1 INT);
|
|
636 |
#CREATE TABLE `@0023sql2` (c1 INT);
|
|
637 |
#SHOW TABLES;
|
|
638 |
#RENAME TABLE `#sql1` TO `@0023sql1`;
|
|
639 |
#RENAME TABLE `@0023sql2` TO `#sql2`;
|
|
640 |
#SHOW TABLES;
|
|
641 |
#ALTER TABLE `@0023sql1` RENAME `#sql-1`;
|
|
642 |
#ALTER TABLE `#sql2` RENAME `@0023sql-2`;
|
|
643 |
#SHOW TABLES;
|
|
644 |
#INSERT INTO `#sql-1` VALUES (1);
|
|
645 |
#INSERT INTO `@0023sql-2` VALUES (2);
|
|
646 |
#DROP TABLE `#sql-1`, `@0023sql-2`;
|
|
1
by brian
clean slate |
647 |
#
|
648 |
# Same for temporary tables though these names do not become file names.
|
|
649 |
CREATE TEMPORARY TABLE `#sql1` (c1 INT); |
|
650 |
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); |
|
651 |
SHOW TABLES; |
|
652 |
ALTER TABLE `#sql1` RENAME `@0023sql1`; |
|
653 |
ALTER TABLE `@0023sql2` RENAME `#sql2`; |
|
654 |
SHOW TABLES; |
|
655 |
INSERT INTO `#sql2` VALUES (1); |
|
656 |
INSERT INTO `@0023sql1` VALUES (2); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
657 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
658 |
SHOW CREATE TABLE `#sql2`; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
659 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
660 |
SHOW CREATE TABLE `@0023sql1`; |
661 |
DROP TABLE `#sql2`, `@0023sql1`; |
|
662 |
||
663 |
#
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
664 |
#
|
1
by brian
clean slate |
665 |
# Bug #22369: Alter table rename combined with other alterations causes lost tables
|
666 |
#
|
|
667 |
# This problem happens if the data change is compatible.
|
|
668 |
# Changing to the same type is compatible for example.
|
|
669 |
#
|
|
670 |
--disable_warnings |
|
671 |
DROP TABLE IF EXISTS t1; |
|
672 |
DROP TABLE IF EXISTS t2; |
|
673 |
--enable_warnings |
|
674 |
CREATE TABLE t1 ( |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
675 |
int_field INTEGER NOT NULL, |
1
by brian
clean slate |
676 |
char_field CHAR(10), |
677 |
INDEX(`int_field`) |
|
678 |
);
|
|
679 |
||
680 |
DESCRIBE t1; |
|
681 |
||
682 |
SHOW INDEXES FROM t1; |
|
683 |
||
684 |
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); |
|
685 |
--echo "Non-copy data change - new frm, but old data and index files" |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
686 |
ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2; |
1
by brian
clean slate |
687 |
|
688 |
--error ER_NO_SUCH_TABLE |
|
689 |
SELECT * FROM t1 ORDER BY int_field; |
|
690 |
SELECT * FROM t2 ORDER BY unsigned_int_field; |
|
691 |
DESCRIBE t2; |
|
692 |
DESCRIBE t2; |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
693 |
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL; |
1
by brian
clean slate |
694 |
DESCRIBE t2; |
695 |
||
696 |
DROP TABLE t2; |
|
697 |
||
698 |
#
|
|
699 |
# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
|
|
700 |
#
|
|
701 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); |
|
702 |
INSERT INTO t1 VALUES (1, 2, NULL); |
|
703 |
SELECT * FROM t1; |
|
704 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; |
|
705 |
SELECT * FROM t1; |
|
706 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; |
|
707 |
SELECT * FROM t1; |
|
708 |
DROP TABLE t1; |