Debian Codegraph @

mysql-test/suite/storage_engine/alter_table.test

raw
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
18 DROP TABLE IF EXISTS t1, t2;
19 --enable_warnings
20
21 --let $create_definition = a $int_col, c $char_col
22 --source create_table.inc
23 INSERT 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
29 if ($mysql_errname)
30 {
31 --source unexpected_result.inc
32 }
33
34 --source mask_engine.inc
35 SHOW CREATE TABLE t1;
36
37 --let $alter_definition = ALTER COLUMN a SET DEFAULT '0'
38 --source alter_table.inc
39 --source mask_engine.inc
40 SHOW CREATE TABLE t1;
41
42 --let $alter_definition = ALTER a DROP DEFAULT
43 --source alter_table.inc
44 --source mask_engine.inc
45 SHOW 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
50 SHOW 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
55 SHOW CREATE TABLE t1;
56
57 --let $alter_definition = CHANGE b b $char_col
58 --source alter_table.inc
59 --source mask_engine.inc
60 SHOW CREATE TABLE t1;
61
62 --let $alter_definition = MODIFY COLUMN b $int_col
63 --source alter_table.inc
64 --source mask_engine.inc
65 SHOW CREATE TABLE t1;
66
67 --let $alter_definition = MODIFY COLUMN b $char_col FIRST
68 --source alter_table.inc
69 --source mask_engine.inc
70 SHOW 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
75 SHOW CREATE TABLE t1;
76
77 --let $alter_definition = DROP COLUMN b
78 --source alter_table.inc
79 --source mask_engine.inc
80 SHOW 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
88 SHOW CREATE TABLE t1;
89 --source check_errors.inc
90 if ($mysql_errname != 'ER_NO_SUCH_TABLE')
91 {
92 --let $functionality = ALTER TABLE
93 --source unexpected_result.inc
94 DROP TABLE t1;
95 }
96 if ($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
106 INSERT INTO t1 (a,b) VALUES (1,5),(2,2),(4,3);
107 --source mask_engine.inc
108 SHOW CREATE TABLE t1;
109
110 --let $alter_definition = ORDER BY b ASC, a DESC
111 --source alter_table.inc
112 --source mask_engine.inc
113 SHOW CREATE TABLE t1;
114 SELECT a,b FROM t1;
115 DROP 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
122 INSERT INTO t1 (a,b,c) VALUES (5,'z','t');
123
124 --source mask_engine.inc
125 SHOW CREATE TABLE t1;
126
127 --let $alter_definition = CONVERT TO CHARACTER SET utf8
128 --source alter_table.inc
129 --source mask_engine.inc
130 SHOW 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
135 SHOW 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
142 SHOW CREATE TABLE t1;
143
144 # Cleanup
145 DROP TABLE t1;
146
147 --source cleanup_engine.inc
148