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