2
# Test of auto_increment; The test for BDB tables is in bdb.test
5
drop table if exists t1;
6
drop table if exists t2;
10
create TEMPORARY table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
11
insert into t1 values (1,1),(NULL,3),(NULL,4);
12
delete from t1 where a=4;
13
insert into t1 values (NULL,5),(NULL,6);
15
delete from t1 where a=6;
16
--replace_column 1 # 6 # 7 # 8 # 9 # 10 #
17
show table status like "t1";
18
replace t1 values (3,1);
19
ALTER TABLE t1 add c int;
20
replace t1 values (3,3,3);
21
insert into t1 values (NULL,7,7);
22
update t1 set a=8,b=b+1,c=c+1 where a=7;
23
insert into t1 values (NULL,9,9);
28
skey int NOT NULL auto_increment PRIMARY KEY,
31
insert into t1 values (NULL, "hello");
32
insert into t1 values (NULL, "hey");
34
select _rowid,t1._rowid,skey,sval from t1;
38
# Test auto_increment on sub key
40
#Drizzle does not support auto_increment on sub key.
41
#create table t1 (a char(10) not null, b int not null auto_increment, primary key(b));
42
#insert into t1 values ("a",1),("b",2),("a",2),("c",1);
43
#insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
44
#insert into t1 (a) values ("a"),("b"),("c"),("d");
45
#insert into t1 (a) values ('k'),('d');
46
#insert into t1 (a) values ("a");
47
#insert into t1 values ("d",last_insert_id());
51
#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
52
#insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
56
#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
57
#insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
61
#create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
62
#create table t2 (sid char(20), id int(2));
63
#insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
64
#insert into t1 select * from t2;
69
# Test of auto_increment columns when they are set to 0
72
create table t1 (a int not null primary key auto_increment);
73
insert into t1 values (0);
80
# Test negative values (Bug #1366)
83
create table t1 (a int not null auto_increment primary key);
84
insert into t1 values (NULL);
85
insert into t1 values (-1);
86
select last_insert_id();
87
insert into t1 values (NULL);
92
create temporary table t1 (a int not null auto_increment primary key) /*!40102 engine=MEMORY */;
93
insert into t1 values (NULL);
94
insert into t1 values (-1);
95
select last_insert_id();
96
insert into t1 values (NULL);
100
create table t1 (i int not null auto_increment, key (i));
101
insert into t1 set i = 254;
102
insert into t1 set i = null;
103
select last_insert_id();
104
insert into t1 set i = null;
105
select last_insert_id();
108
create table t1 (i int not null auto_increment primary key, b int, unique (b));
109
insert into t1 values (NULL, 10);
110
select last_insert_id();
111
insert into t1 values (NULL, 15);
112
select last_insert_id();
113
--error ER_DUP_ENTRY, ER_DUP_KEY
114
insert into t1 values (NULL, 10);
115
select last_insert_id();
119
create table t1(a int auto_increment,b int null,primary key(a));
120
insert into t1(a,b)values(NULL,1);
121
insert into t1(a,b)values(200,2);
122
insert into t1(a,b)values(0,3);
123
insert into t1(b)values(4);
124
insert into t1(b)values(5);
125
insert into t1(b)values(6);
126
insert into t1(b)values(7);
127
select * from t1 order by b;
128
#mediumint is not supported. changed to b int.
129
alter table t1 modify b int;
131
select * from t1 order by b;
132
create table t2 (a int primary key);
133
insert t2 values (1),(2);
134
alter table t2 drop primary key, add b int auto_increment primary key;
137
delete from t1 where a=0;
139
show create table t1;
140
update t1 set a=0 where b=5;
141
select * from t1 order by b;
142
delete from t1 where a=0;
143
--error ER_BAD_NULL_ERROR
144
update t1 set a=NULL where b=6;
145
update t1 set a=300 where b=7;
146
insert into t1(a,b)values(NULL,8);
147
insert into t1(a,b)values(400,9);
148
insert into t1(a,b)values(0,10);
149
insert into t1(b)values(11);
150
insert into t1(b)values(12);
151
insert into t1(b)values(13);
152
insert into t1(b)values(14);
153
select * from t1 order by b;
154
delete from t1 where a=0;
155
update t1 set a=0 where b=12;
156
select * from t1 order by b;
157
delete from t1 where a=0;
158
--error ER_BAD_NULL_ERROR
159
update t1 set a=NULL where b=13;
160
update t1 set a=500 where b=14;
161
select * from t1 order by b;
165
# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is
166
# converted to AUTO_INCREMENT column
168
create table t1 (a bigint, b int auto_increment primary key);
169
insert into t1 (a) values (1), (2), (3), (NULL), (NULL);
170
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
174
create table t1 (a bigint, b int auto_increment primary key);
175
insert into t1 (a) values (1), (2), (3), (0), (0);
176
--error ER_DUP_KEY, ER_DUP_ENTRY
177
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
181
# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode
182
create table t1 (a bigint, b int auto_increment primary key);
183
insert into t1 (a) values (0), (1), (2), (3);
185
# Bug314567 - ALTER TABLE causes auto_increment resequencing,
186
# resulting in duplicate entry since sql_mode=NO_AUTO_VALUE_ON_ZERO
188
# NO_AUTO_VALUE_ON_ZERO is now default for Drizzle
190
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
195
# It also sensible to preserve zeroes if we are converting auto_increment
196
# column to auto_increment column (or not touching it at all, which is more
197
# common case probably)
198
create table t1 (a int auto_increment primary key , b int null);
199
#sql_mode is not supported.
200
#set sql_mode=NO_AUTO_VALUE_ON_ZERO;
201
insert into t1 values (0,1),(1,2),(2,3);
204
alter table t1 modify b varchar(255);
205
--error ER_DUP_KEY, ER_DUP_ENTRY
206
insert into t1 values (0,4);
211
# BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key
213
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b VARBINARY(1000), PRIMARY KEY (a,b(10)));
214
INSERT INTO t1 (b) VALUES ('aaaa');
216
INSERT INTO t1 (b) VALUES ('');
218
INSERT INTO t1 (b) VALUES ('bbbb');
220
DROP TABLE IF EXISTS t1;
223
#changed syntax of int(10) to int
225
t1_name VARCHAR(255) DEFAULT NULL,
226
t1_id INT not null AUTO_INCREMENT,
229
) AUTO_INCREMENT = 1000;
231
INSERT INTO t1 (t1_name) VALUES('MySQL');
232
INSERT INTO t1 (t1_name) VALUES('MySQL');
233
INSERT INTO t1 (t1_name) VALUES('MySQL');
237
SHOW CREATE TABLE `t1`;
242
# Bug #6880: LAST_INSERT_ID() within a statement
245
create table t1(a int not null auto_increment primary key);
246
create table t2(a int not null auto_increment primary key, t1a int);
247
insert into t1 values(NULL);
248
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
249
insert into t1 values (NULL);
250
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
251
(NULL, LAST_INSERT_ID());
252
insert into t1 values (NULL);
253
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
254
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
258
--echo End of 4.1 tests
261
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
263
#changed syntax of int (11) to int
264
#CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
265
#insert into t1 (b) values (1);
266
#replace into t1 (b) values (2), (1), (3);
269
#insert into t1 (b) values (1);
270
#replace into t1 (b) values (2);
271
#replace into t1 (b) values (1);
272
#replace into t1 (b) values (3);
276
#create table t1 (rowid int not null auto_increment, val int not null,primary
277
#key (rowid), unique(val));
278
#replace into t1 (val) values ('1'),('2');
279
#replace into t1 (val) values ('1'),('2');
280
#--error ER_DUP_ENTRY
281
#insert into t1 (val) values ('1'),('2');
286
# Test that update changes internal auto-increment value
289
#create table t1 (a int not null auto_increment primary key, val int);
290
#insert into t1 (val) values (1);
291
#update t1 set a=2 where a=1;
292
#insert into t1 (val) values (1);
297
# Test key duplications with auto-increment in ALTER TABLE
300
#changed syntax of int(11) to int.
301
CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT);
302
INSERT INTO t1 VALUES(0, 0);
303
INSERT INTO t1 VALUES(1, 1);
304
ALTER TABLE t1 CHANGE t1 t1 INT auto_increment;
305
--error ER_DUP_KEY, ER_DUP_ENTRY
306
INSERT INTO t1 VALUES(0,0);
309
# Test of REPLACE when it does INSERT+DELETE and not UPDATE:
310
# see if it sets LAST_INSERT_ID() ok
311
#create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
312
#insert into t1 values(null,1,1,now());
313
#insert into t1 values(null,0,0,null);
314
# this will delete two rows
315
#replace into t1 values(null,1,0,null);
316
#select last_insert_id();