auto_increment.test 4.81 KB
Newer Older
1 2 3
#
# Test of auto_increment;  The test for BDB tables is in bdb.test
#
4
--disable_warnings
5
drop table if exists t1;
6
drop table if exists t2;
7
--enable_warnings
unknown's avatar
unknown committed
8
SET SQL_WARNINGS=1;
9

unknown's avatar
unknown committed
10
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
insert into t1 values (1,1),(NULL,3),(NULL,4);
delete from t1 where a=4;
insert into t1 values (NULL,5),(NULL,6);
select * from t1;
delete from t1 where a=6;
#show table status like "t1";
replace t1 values (3,1);
ALTER TABLE t1 add c int;
replace t1 values (3,3,3);
insert into t1 values (NULL,7,7);
update t1 set a=8,b=b+1,c=c+1 where a=7;
insert into t1 values (NULL,9,9);
select * from t1;
drop table t1;

create table t1 (
  skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
  sval char(20)
);
insert into t1 values (NULL, "hello");
insert into t1 values (NULL, "hey");
select * from t1;
select _rowid,t1._rowid,skey,sval from t1;
drop table t1;

#
# Test auto_increment on sub key
#
create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
insert into t1 values ("a",1),("b",2),("a",2),("c",1);
insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
insert into t1 (a) values ("a"),("b"),("c"),("d");
insert into t1 (a) values ('k'),('d');
insert into t1 (a) values ("a");
insert into t1 values ("d",last_insert_id());
select * from t1;
drop table t1;

create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ordid), index(ord,ordid)); 
insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
select * from t1;
drop table t1;

create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid));
insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
select * from t1;
drop table t1;
unknown's avatar
unknown committed
58

59 60 61 62 63 64 65
create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));
create table t2 (sid char(20), id int(2));
insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
insert into t1 select * from t2;
select * from t1;
drop table t1,t2;

unknown's avatar
unknown committed
66 67 68 69 70 71 72 73 74 75
#
# Test of auto_increment columns when they are set to 0
#

create table t1 (a int not null primary key auto_increment);
insert into t1 values (0);
update t1 set a=0;
select * from t1;
check table t1;
drop table t1;
76

77 78 79 80 81 82 83 84 85 86 87 88
#
# Test negative values (Bug #1366)
#

create table t1 (a int not null auto_increment primary key);
insert into t1 values (NULL);
insert into t1 values (-1);
select last_insert_id();
insert into t1 values (NULL);
select * from t1;
drop table t1;

unknown's avatar
unknown committed
89
create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
90 91 92 93 94 95
insert into t1 values (NULL);
insert into t1 values (-1);
select last_insert_id();
insert into t1 values (NULL);
select * from t1;
drop table t1;
96 97 98 99 100 101 102
#
# last_insert_id() madness
#
create table t1 (i tinyint unsigned not null auto_increment primary key);
insert into t1 set i = 254;
insert into t1 set i = null;
select last_insert_id();
103
explain extended select last_insert_id();
104
--error 1062
105 106 107
insert into t1 set i = 254;
select last_insert_id();
--error 1062
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
insert into t1 set i = null;
select last_insert_id();
drop table t1;

create table t1 (i tinyint unsigned not null auto_increment, key (i));
insert into t1 set i = 254;
insert into t1 set i = null;
select last_insert_id();
insert into t1 set i = null;
select last_insert_id();
drop table t1;

create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
insert into t1 values (NULL, 10);
select last_insert_id();
insert into t1 values (NULL, 15);
select last_insert_id();
--error 1062
insert into t1 values (NULL, 10);
select last_insert_id();
128

129 130
drop table t1;

131 132 133 134 135 136
create table t1(a int auto_increment,b int null,primary key(a));
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
insert into t1(a,b)values(NULL,1);
insert into t1(a,b)values(200,2);
insert into t1(a,b)values(0,3);
insert into t1(b)values(4);
unknown's avatar
unknown committed
137 138 139 140
insert into t1(b)values(5);
insert into t1(b)values(6);
insert into t1(b)values(7);
select * from t1 order by b;
unknown's avatar
unknown committed
141 142
alter table t1 modify b mediumint;
select * from t1 order by b;
143 144 145 146 147
create table t2 (a int);
insert t2 values (1),(2);
alter table t2 add b int auto_increment primary key;
select * from t2;
drop table t2;
unknown's avatar
unknown committed
148 149 150 151 152 153
delete from t1 where a=0;
update t1 set a=0 where b=5;
select * from t1 order by b;
delete from t1 where a=0;
update t1 set a=NULL where b=6;
update t1 set a=300 where b=7;
154
SET SQL_MODE='';
unknown's avatar
unknown committed
155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
insert into t1(a,b)values(NULL,8);
insert into t1(a,b)values(400,9);
insert into t1(a,b)values(0,10);
insert into t1(b)values(11);
insert into t1(b)values(12);
insert into t1(b)values(13);
insert into t1(b)values(14);
select * from t1 order by b;
delete from t1 where a=0;
update t1 set a=0 where b=12;
select * from t1 order by b;
delete from t1 where a=0;
update t1 set a=NULL where b=13;
update t1 set a=500 where b=14;
select * from t1 order by b;
170
drop table t1;