diff options
Diffstat (limited to 'mysql-test/suite/ndb/r/ndb_alter_table.result')
| -rw-r--r-- | mysql-test/suite/ndb/r/ndb_alter_table.result | 437 |
1 files changed, 0 insertions, 437 deletions
diff --git a/mysql-test/suite/ndb/r/ndb_alter_table.result b/mysql-test/suite/ndb/r/ndb_alter_table.result deleted file mode 100644 index 35b983e1901..00000000000 --- a/mysql-test/suite/ndb/r/ndb_alter_table.result +++ /dev/null @@ -1,437 +0,0 @@ -DROP TABLE IF EXISTS t1, t2; -drop database if exists mysqltest; -CREATE TABLE t1 ( -a INT NOT NULL, -b INT NOT NULL -) ENGINE=ndbcluster; -INSERT INTO t1 VALUES (9410,9412); -ALTER TABLE t1 ADD COLUMN c int not null; -SELECT * FROM t1; -a b c -9410 9412 0 -DROP TABLE t1; -CREATE DATABASE mysqltest; -USE mysqltest; -CREATE TABLE t1 ( -a INT NOT NULL, -b INT NOT NULL -) ENGINE=ndbcluster; -RENAME TABLE t1 TO test.t1; -SHOW TABLES; -Tables_in_mysqltest -DROP DATABASE mysqltest; -USE test; -SHOW TABLES; -Tables_in_test -t1 -DROP TABLE t1; -create table t1 ( -col1 int not null auto_increment primary key, -col2 varchar(30) not null, -col3 varchar (20) not null, -col4 varchar(4) not null, -col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, -col6 int not null, to_be_deleted int) ENGINE=ndbcluster; -show table status; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL # -SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; -insert into t1 values -(0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7); -show table status; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ndbcluster 10 Dynamic 9 # # # 0 # 102 # # # latin1_swedish_ci NULL # -select * from t1 order by col1; -col1 col2 col3 col4 col5 col6 to_be_deleted -0 4 3 5 PENDING 1 7 -1 4 3 5 PENDING 1 7 -7 4 3 5 PENDING 1 7 -8 4 3 5 PENDING 1 7 -31 4 3 5 PENDING 1 7 -32 4 3 5 PENDING 1 7 -99 4 3 5 PENDING 1 7 -100 4 3 5 PENDING 1 7 -101 4 3 5 PENDING 1 7 -alter table t1 -add column col4_5 varchar(20) not null after col4, -add column col7 varchar(30) not null after col5, -add column col8 datetime not null, drop column to_be_deleted, -change column col2 fourth varchar(30) not null after col3, -modify column col6 int not null first; -show table status; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ndbcluster 10 Dynamic 9 # # # 0 # 102 # # # latin1_swedish_ci NULL # -select * from t1 order by col1; -col6 col1 col3 fourth col4 col4_5 col5 col7 col8 -1 0 3 4 5 PENDING 0000-00-00 00:00:00 -1 1 3 4 5 PENDING 0000-00-00 00:00:00 -1 7 3 4 5 PENDING 0000-00-00 00:00:00 -1 8 3 4 5 PENDING 0000-00-00 00:00:00 -1 31 3 4 5 PENDING 0000-00-00 00:00:00 -1 32 3 4 5 PENDING 0000-00-00 00:00:00 -1 99 3 4 5 PENDING 0000-00-00 00:00:00 -1 100 3 4 5 PENDING 0000-00-00 00:00:00 -1 101 3 4 5 PENDING 0000-00-00 00:00:00 -insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); -show table status; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ndbcluster 10 Dynamic 10 # # # 0 # 103 # # # latin1_swedish_ci NULL # -select * from t1 order by col1; -col6 col1 col3 fourth col4 col4_5 col5 col7 col8 -1 0 3 4 5 PENDING 0000-00-00 00:00:00 -1 1 3 4 5 PENDING 0000-00-00 00:00:00 -1 7 3 4 5 PENDING 0000-00-00 00:00:00 -1 8 3 4 5 PENDING 0000-00-00 00:00:00 -1 31 3 4 5 PENDING 0000-00-00 00:00:00 -1 32 3 4 5 PENDING 0000-00-00 00:00:00 -1 99 3 4 5 PENDING 0000-00-00 00:00:00 -1 100 3 4 5 PENDING 0000-00-00 00:00:00 -1 101 3 4 5 PENDING 0000-00-00 00:00:00 -2 102 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00 -delete from t1; -insert into t1 values (0,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); -SET SQL_MODE=''; -insert into t1 values (1,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); -select * from t1 order by col1; -col6 col1 col3 fourth col4 col4_5 col5 col7 col8 -0 0 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00 -1 103 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00 -alter table t1 drop column col4_5; -insert into t1 values (2,0,4,3,5,"PENDING","EXTRA",'2004-01-01 00:00:00'); -select * from t1 order by col1; -col6 col1 col3 fourth col4 col5 col7 col8 -0 0 4 3 5 PENDING EXTRA 2004-01-01 00:00:00 -1 103 4 3 5 PENDING EXTRA 2004-01-01 00:00:00 -2 104 4 3 5 PENDING EXTRA 2004-01-01 00:00:00 -drop table t1; -CREATE TABLE t1 ( -a INT NOT NULL, -b INT NOT NULL -) ENGINE=ndbcluster; -INSERT INTO t1 VALUES (9410,9412); -ALTER TABLE t1 ADD COLUMN c int not null; -select * from t1 order by a; -a b c -9410 9412 0 -select * from t1 order by a; -a b c -9410 9412 0 -alter table t1 drop c; -select * from t1 order by a; -a b -9410 9412 -drop table t1; -select * from t1 order by a; -ERROR 42S02: Table 'test.t1' doesn't exist -CREATE TABLE t1 ( -a INT NOT NULL PRIMARY KEY, -b INT NOT NULL -) ENGINE=ndbcluster; -INSERT INTO t1 VALUES (0,1),(17,18); -select * from t1 order by a; -a b -0 1 -17 18 -SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; -alter table t1 modify column a int not null auto_increment; -SET SQL_MODE=''; -select * from t1 order by a; -a b -0 1 -17 18 -INSERT INTO t1 VALUES (0,19),(20,21); -select * from t1 order by a; -a b -0 1 -17 18 -18 19 -20 21 -drop table t1; -CREATE TABLE t1 ( -a INT NOT NULL PRIMARY KEY, -b INT NOT NULL -) ENGINE=ndbcluster; -INSERT INTO t1 VALUES (0,1),(17,18); -select * from t1 order by a; -a b -0 1 -17 18 -alter table t1 add c int not null unique auto_increment; -select c from t1 order by c; -c -1 -2 -INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0); -select c from t1 order by c; -c -1 -2 -3 -4 -5 -drop table t1; -create table t1 ( -ai bigint auto_increment, -c001 int(11) not null, -c002 int(11) not null, -c003 int(11) not null, -c004 int(11) not null, -c005 int(11) not null, -c006 int(11) not null, -c007 int(11) not null, -c008 int(11) not null, -c009 int(11) not null, -c010 int(11) not null, -c011 int(11) not null, -c012 int(11) not null, -c013 int(11) not null, -c014 int(11) not null, -c015 int(11) not null, -c016 int(11) not null, -c017 int(11) not null, -c018 int(11) not null, -c019 int(11) not null, -c020 int(11) not null, -c021 int(11) not null, -c022 int(11) not null, -c023 int(11) not null, -c024 int(11) not null, -c025 int(11) not null, -c026 int(11) not null, -c027 int(11) not null, -c028 int(11) not null, -c029 int(11) not null, -c030 int(11) not null, -c031 int(11) not null, -c032 int(11) not null, -c033 int(11) not null, -c034 int(11) not null, -c035 int(11) not null, -c036 int(11) not null, -c037 int(11) not null, -c038 int(11) not null, -c039 int(11) not null, -c040 int(11) not null, -c041 int(11) not null, -c042 int(11) not null, -c043 int(11) not null, -c044 int(11) not null, -c045 int(11) not null, -c046 int(11) not null, -c047 int(11) not null, -c048 int(11) not null, -c049 int(11) not null, -c050 int(11) not null, -c051 int(11) not null, -c052 int(11) not null, -c053 int(11) not null, -c054 int(11) not null, -c055 int(11) not null, -c056 int(11) not null, -c057 int(11) not null, -c058 int(11) not null, -c059 int(11) not null, -c060 int(11) not null, -c061 int(11) not null, -c062 int(11) not null, -c063 int(11) not null, -c064 int(11) not null, -c065 int(11) not null, -c066 int(11) not null, -c067 int(11) not null, -c068 int(11) not null, -c069 int(11) not null, -c070 int(11) not null, -c071 int(11) not null, -c072 int(11) not null, -c073 int(11) not null, -c074 int(11) not null, -c075 int(11) not null, -c076 int(11) not null, -c077 int(11) not null, -c078 int(11) not null, -c079 int(11) not null, -c080 int(11) not null, -c081 int(11) not null, -c082 int(11) not null, -c083 int(11) not null, -c084 int(11) not null, -c085 int(11) not null, -c086 int(11) not null, -c087 int(11) not null, -c088 int(11) not null, -c089 int(11) not null, -c090 int(11) not null, -c091 int(11) not null, -c092 int(11) not null, -c093 int(11) not null, -c094 int(11) not null, -c095 int(11) not null, -c096 int(11) not null, -c097 int(11) not null, -c098 int(11) not null, -c099 int(11) not null, -c100 int(11) not null, -c101 int(11) not null, -c102 int(11) not null, -c103 int(11) not null, -c104 int(11) not null, -c105 int(11) not null, -c106 int(11) not null, -c107 int(11) not null, -c108 int(11) not null, -c109 int(11) not null, -primary key (ai), -unique key tx1 (c002, c003, c004, c005)) engine=ndb; -create index tx2 -on t1 (c010, c011, c012, c013); -drop table t1; -CREATE TABLE t1 ( -auto int(5) unsigned NOT NULL auto_increment, -string char(10), -vstring varchar(10), -bin binary(2), -vbin varbinary(7), -tiny tinyint(4) DEFAULT '0' NOT NULL , -short smallint(6) DEFAULT '1' NOT NULL , -medium mediumint(8) DEFAULT '0' NOT NULL, -long_int int(11) DEFAULT '0' NOT NULL, -longlong bigint(13) DEFAULT '0' NOT NULL, -real_float float(13,1) DEFAULT 0.0 NOT NULL, -real_double double(16,4), -real_decimal decimal(16,4), -utiny tinyint(3) unsigned DEFAULT '0' NOT NULL, -ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, -umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, -ulong int(11) unsigned DEFAULT '0' NOT NULL, -ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, -bits bit(3), -options enum('zero','one','two','three','four') not null, -flags set('zero','one','two','three','four') not null, -date_field date, -year_field year, -time_field time, -date_time datetime, -time_stamp timestamp, -PRIMARY KEY (auto) -) engine=ndb; -CREATE TEMPORARY TABLE ndb_show_tables (id INT, type VARCHAR(20), state VARCHAR(20), logging VARCHAR(20), _database VARCHAR(255), _schema VARCHAR(20), name VARCHAR(255)); -LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables; -set @t1_id = (select id from ndb_show_tables where name like '%t1%'); -truncate ndb_show_tables; -alter table t1 change tiny new_tiny tinyint(4) DEFAULT '0' NOT NULL; -LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables; -select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%'; -no_copy -set @t1_id = (select id from ndb_show_tables where name like '%t1%'); -truncate ndb_show_tables; -create index i1 on t1(medium); -alter table t1 add index i2(new_tiny); -drop index i1 on t1; -LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables; -select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%'; -no_copy -no_copy -DROP TABLE t1, ndb_show_tables; -create table t1 (a int primary key auto_increment, b int) engine=ndb; -insert into t1 (b) values (101),(102),(103); -select * from t1 where a = 3; -a b -3 103 -alter table t1 rename t2; -insert into t2 (b) values (201),(202),(203); -select * from t2 where a = 6; -a b -6 203 -alter table t2 add c int; -insert into t2 (b) values (301),(302),(303); -select * from t2 where a = 9; -a b c -9 303 NULL -alter table t2 rename t1; -insert into t1 (b) values (401),(402),(403); -select * from t1 where a = 12; -a b c -12 403 NULL -drop table t1; -create table t1(a int not null) engine=ndb; -$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY($PK) - UniqueHashIndex -insert into t1 values (1),(2),(3); -alter table t1 add primary key (a); -a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY(a) - UniqueHashIndex -PRIMARY(a) - OrderedIndex -update t1 set a = 17 where a = 1; -select * from t1 order by a; -a -2 -3 -17 -alter table t1 drop primary key; -$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY($PK) - UniqueHashIndex -update t1 set a = 1 where a = 17; -select * from t1 order by a; -a -1 -2 -3 -drop table t1; -create table t1(a int not null) engine=ndb; -$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY($PK) - UniqueHashIndex -insert into t1 values (1),(2),(3); -create unique index pk on t1(a); -a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY(a) - UniqueHashIndex -update t1 set a = 17 where a = 1; -select * from t1 order by a; -a -2 -3 -17 -alter table t1 drop index pk; -$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY -PRIMARY KEY($PK) - UniqueHashIndex -update t1 set a = 1 where a = 17; -select * from t1 order by a; -a -1 -2 -3 -drop table t1; -create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) NOT NULL DEFAULT '0', - `c` varchar(254) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -alter table t1 alter b set default 1; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) NOT NULL DEFAULT '1', - `c` varchar(254) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -drop table t1; -create table t1 (a int not null, b int not null) engine=ndb; -insert into t1 values (1, 300), (2, 200), (3, 100); -select * from t1 order by a; -a b -1 300 -2 200 -3 100 -alter table t1 order by b; -select * from t1 order by b; -a b -3 100 -2 200 -1 300 -drop table t1; -End of 5.1 tests |