1#
2# Basic ALTER TABLE statements.
3#
4# USAGE of table options in ALTER statements
5#   is covered in tbl_standard_opts and tbl_opt*.tests.
6#
7# Index operations are covered in index* tests.
8#
9# ALTER ONLINE syntax is covered in alter_online_table.test
10# ALTER OFFLINE is not covered as it is not supported, as of 5.5.23
11#
12# ALTER TABLE ... DISCARD|IMPORT TABLESPACE is covered in alter_tablespace.test
13#
14
15--source have_engine.inc
16
17--disable_warnings
18DROP TABLE IF EXISTS t1, t2;
19--enable_warnings
20
21--let $create_definition = a $int_col, c $char_col
22--source create_table.inc
23INSERT INTO t1 (a,c) VALUES (1,'a'),(5,'z');
24
25# Column operations
26
27--let $alter_definition = ADD COLUMN b $int_col
28--source alter_table.inc
29if ($mysql_errname)
30{
31  --source unexpected_result.inc
32}
33
34--source mask_engine.inc
35SHOW CREATE TABLE t1;
36
37--let $alter_definition = ALTER COLUMN a SET DEFAULT '0'
38--source alter_table.inc
39--source mask_engine.inc
40SHOW CREATE TABLE t1;
41
42--let $alter_definition = ALTER a DROP DEFAULT
43--source alter_table.inc
44--source mask_engine.inc
45SHOW CREATE TABLE t1;
46
47--let $alter_definition = CHANGE COLUMN b b1 $char_col FIRST
48--source alter_table.inc
49--source mask_engine.inc
50SHOW CREATE TABLE t1;
51
52--let $alter_definition = CHANGE b1 b $int_col AFTER c
53--source alter_table.inc
54--source mask_engine.inc
55SHOW CREATE TABLE t1;
56
57--let $alter_definition = CHANGE b b $char_col
58--source alter_table.inc
59--source mask_engine.inc
60SHOW CREATE TABLE t1;
61
62--let $alter_definition = MODIFY COLUMN b $int_col
63--source alter_table.inc
64--source mask_engine.inc
65SHOW CREATE TABLE t1;
66
67--let $alter_definition = MODIFY COLUMN b $char_col FIRST
68--source alter_table.inc
69--source mask_engine.inc
70SHOW CREATE TABLE t1;
71
72--let $alter_definition = MODIFY COLUMN b $int_col AFTER a
73--source alter_table.inc
74--source mask_engine.inc
75SHOW CREATE TABLE t1;
76
77--let $alter_definition = DROP COLUMN b
78--source alter_table.inc
79--source mask_engine.inc
80SHOW CREATE TABLE t1;
81
82
83# Rename table
84
85--let $rename_to = t2
86--source alter_table.inc
87--let $error_codes = ER_NO_SUCH_TABLE
88SHOW CREATE TABLE t1;
89--source check_errors.inc
90if ($mysql_errname != 'ER_NO_SUCH_TABLE')
91{
92  --let $functionality = ALTER TABLE
93  --source unexpected_result.inc
94  DROP TABLE t1;
95}
96if ($mysql_errname == ER_NO_SUCH_TABLE)
97{
98  --source mask_engine.inc
99  SHOW CREATE TABLE t2;
100  DROP TABLE t2;
101}
102
103# ORDER BY
104--let $create_definition = a $int_col, b $int_col
105--source create_table.inc
106INSERT INTO t1 (a,b) VALUES (1,5),(2,2),(4,3);
107--source mask_engine.inc
108SHOW CREATE TABLE t1;
109
110--let $alter_definition = ORDER BY b ASC, a DESC
111--source alter_table.inc
112--source mask_engine.inc
113SHOW CREATE TABLE t1;
114SELECT a,b FROM t1;
115DROP TABLE t1;
116
117# Character set, collate
118
119--let $table_options = CHARACTER SET latin1 COLLATE latin1_general_cs
120--let $create_definition = a $int_col, b $char_col, c $char_col
121--source create_table.inc
122INSERT INTO t1 (a,b,c) VALUES (5,'z','t');
123
124--source mask_engine.inc
125SHOW CREATE TABLE t1;
126
127--let $alter_definition = CONVERT TO CHARACTER SET utf8
128--source alter_table.inc
129--source mask_engine.inc
130SHOW CREATE TABLE t1;
131
132--let $alter_definition = DEFAULT CHARACTER SET = latin1 COLLATE latin1_general_ci
133--source alter_table.inc
134--source mask_engine.inc
135SHOW CREATE TABLE t1;
136
137# A 'null' ALTER operation
138
139--let $alter_definition = FORCE
140--source alter_table.inc
141--source mask_engine.inc
142SHOW CREATE TABLE t1;
143
144# Cleanup
145DROP TABLE t1;
146
147--source cleanup_engine.inc
148
149