~drizzle-trunk/drizzle/development : contents of mysql-test/r/auto

~drizzle-trunk/drizzle/development : (revision 33)

drop table if exists t1;
drop table if exists t2;
SET SQL_WARNINGS=1;
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
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;
a	b
1	1
3	3
5	5
6	6
delete from t1 where a=6;
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;
a	b	c
1	1	NULL
3	3	3
5	5	NULL
8	8	8
9	9	9
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;
skey	sval
1	hello
2	hey
select _rowid,t1._rowid,skey,sval from t1;
_rowid	_rowid	skey	sval
1	1	1	hello
2	2	2	hey
drop table t1;
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;
a	b
a	1
a	2
a	3
a	4
a	5
b	2
b	3
b	4
c	1
c	2
c	3
d	1
d	2
d	5
e	1
k	1
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;
ordid	ord
1	sdj
2	sdj
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;
ordid	ord
1	abc
2	abc
3	abc
1	sdj
2	sdj
3	sdj
1	zzz
drop table t1;
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;
sid	id
skr	1
skr	2
test	1
drop table t1,t2;
create table t1 (a int not null primary key auto_increment);
insert into t1 values (0);
update t1 set a=0;
select * from t1;
a
0
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	warning	Found row where the auto_increment column has the value 0
test.t1	check	status	OK
drop table t1;
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();
last_insert_id()
1
insert into t1 values (NULL);
select * from t1;
a
-1
1
2
drop table t1;
create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
insert into t1 values (NULL);
insert into t1 values (-1);
select last_insert_id();
last_insert_id()
1
insert into t1 values (NULL);
select * from t1;
a
1
-1
2
drop table t1;
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();
last_insert_id()
255
explain extended select last_insert_id();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select last_insert_id() AS `last_insert_id()`
insert into t1 set i = 254;
ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
select last_insert_id();
last_insert_id()
255
insert into t1 set i = null;
ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
select last_insert_id();
last_insert_id()
255
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();
last_insert_id()
255
insert into t1 set i = null;
Warnings:
Warning	1264	Out of range value for column 'i' at row 1
select last_insert_id();
last_insert_id()
255
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();
last_insert_id()
1
insert into t1 values (NULL, 15);
select last_insert_id();
last_insert_id()
2
insert into t1 values (NULL, 10);
ERROR 23000: Duplicate entry '10' for key 'b'
select last_insert_id();
last_insert_id()
2
drop table t1;
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);
insert into t1(b)values(5);
insert into t1(b)values(6);
insert into t1(b)values(7);
select * from t1 order by b;
a	b
1	1
200	2
0	3
201	4
202	5
203	6
204	7
alter table t1 modify b mediumint;
select * from t1 order by b;
a	b
1	1
200	2
0	3
201	4
202	5
203	6
204	7
create table t2 (a int);
insert t2 values (1),(2);
alter table t2 add b int auto_increment primary key;
select * from t2;
a	b
1	1
2	2
drop table t2;
delete from t1 where a=0;
update t1 set a=0 where b=5;
select * from t1 order by b;
a	b
1	1
200	2
201	4
0	5
203	6
204	7
delete from t1 where a=0;
update t1 set a=NULL where b=6;
ERROR 23000: Column 'a' cannot be null
update t1 set a=300 where b=7;
SET SQL_MODE='';
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;
a	b
1	1
200	2
201	4
203	6
300	7
301	8
400	9
401	10
402	11
403	12
404	13
405	14
delete from t1 where a=0;
update t1 set a=0 where b=12;
select * from t1 order by b;
a	b
1	1
200	2
201	4
203	6
300	7
301	8
400	9
401	10
402	11
0	12
404	13
405	14
delete from t1 where a=0;
update t1 set a=NULL where b=13;
ERROR 23000: Column 'a' cannot be null
update t1 set a=500 where b=14;
select * from t1 order by b;
a	b
1	1
200	2
201	4
203	6
300	7
301	8
400	9
401	10
402	11
404	13
500	14
drop table t1;
create table t1 (a bigint);
insert into t1 values (1), (2), (3), (NULL), (NULL);
alter table t1 modify a bigint not null auto_increment primary key;
select * from t1;
a
1
2
3
4
5
drop table t1;
create table t1 (a bigint);
insert into t1 values (1), (2), (3), (0), (0);
alter table t1 modify a bigint not null auto_increment primary key;
select * from t1;
a
1
2
3
4
5
drop table t1;
create table t1 (a bigint);
insert into t1 values (0), (1), (2), (3);
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
alter table t1 modify a bigint not null auto_increment primary key;
set sql_mode= '';
select * from t1;
a
0
1
2
3
drop table t1;
create table t1 (a int auto_increment primary key , b int null);
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
insert into t1 values (0,1),(1,2),(2,3);
select * from t1;
a	b
0	1
1	2
2	3
set sql_mode= '';
alter table t1 modify b varchar(255);
insert into t1 values (0,4);
select * from t1;
a	b
0	1
1	2
2	3
3	4
drop table t1;
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
INSERT INTO t1 (b) VALUES ('aaaa');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1 (b) VALUES ('');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1 (b) VALUES ('bbbb');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
t1_name VARCHAR(255) DEFAULT NULL,
t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
KEY (t1_name),
PRIMARY KEY (t1_id)
) AUTO_INCREMENT = 1000;
INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');
SELECT * from t1;
t1_name	t1_id
MySQL	1000
MySQL	1001
MySQL	1002
SHOW CREATE TABLE `t1`;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1_name` varchar(255) DEFAULT NULL,
  `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t1_id`),
  KEY `t1_name` (`t1_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
DROP TABLE `t1`;
create table t1(a int not null auto_increment primary key);
create table t2(a int not null auto_increment primary key, t1a int);
insert into t1 values(NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
insert into t1 values (NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID());
insert into t1 values (NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
select * from t2;
a	t1a
1	1
2	1
3	2
4	2
5	2
6	3
7	3
8	3
9	3
drop table t1, t2;
End of 4.1 tests
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
insert into t1 (b) values (1);
replace into t1 (b) values (2), (1), (3);
select * from t1;
a	b
3	1
2	2
4	3
truncate table t1;
insert into t1 (b) values (1);
replace into t1 (b) values (2);
replace into t1 (b) values (1);
replace into t1 (b) values (3);
select * from t1;
a	b
3	1
2	2
4	3
drop table t1;
create table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val));
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
insert into t1 (val) values ('1'),('2');
ERROR 23000: Duplicate entry '1' for key 'val'
select * from t1;
rowid	val
3	1
4	2
drop table t1;
create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);
select * from t1;
a	val
2	1
3	1
drop table t1;
CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
INSERT INTO t1 VALUES(0, 0);
INSERT INTO t1 VALUES(1, 1);
ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
DROP TABLE t1;
create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
insert into t1 values(null,1,1,now());
insert into t1 values(null,0,0,null);
replace into t1 values(null,1,0,null);
select last_insert_id();
last_insert_id()
3
drop table t1;

1 by brian clean slate	1	drop table if exists t1;
	2	drop table if exists t2;
	3	SET SQL_WARNINGS=1;
	4	create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
	5	insert into t1 values (1,1),(NULL,3),(NULL,4);
	6	delete from t1 where a=4;
	7	insert into t1 values (NULL,5),(NULL,6);
	8	select * from t1;
	9	a b
	10	1 1
	11	3 3
	12	5 5
	13	6 6
	14	delete from t1 where a=6;
	15	replace t1 values (3,1);
	16	ALTER TABLE t1 add c int;
	17	replace t1 values (3,3,3);
	18	insert into t1 values (NULL,7,7);
	19	update t1 set a=8,b=b+1,c=c+1 where a=7;
	20	insert into t1 values (NULL,9,9);
	21	select * from t1;
	22	a b c
	23	1 1 NULL
	24	3 3 3
	25	5 5 NULL
	26	8 8 8
	27	9 9 9
	28	drop table t1;
	29	create table t1 (
	30	skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
	31	sval char(20)
	32	);
	33	insert into t1 values (NULL, "hello");
	34	insert into t1 values (NULL, "hey");
	35	select * from t1;
	36	skey sval
	37	1 hello
	38	2 hey
	39	select _rowid,t1._rowid,skey,sval from t1;
	40	_rowid _rowid skey sval
	41	1 1 1 hello
	42	2 2 2 hey
	43	drop table t1;
	44	create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
	45	insert into t1 values ("a",1),("b",2),("a",2),("c",1);
	46	insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
	47	insert into t1 (a) values ("a"),("b"),("c"),("d");
	48	insert into t1 (a) values ('k'),('d');
	49	insert into t1 (a) values ("a");
	50	insert into t1 values ("d",last_insert_id());
	51	select * from t1;
	52	a b
	53	a 1
	54	a 2
	55	a 3
	56	a 4
	57	a 5
	58	b 2
	59	b 3
	60	b 4
	61	c 1
	62	c 2
	63	c 3
	64	d 1
65	d 2
66	d 5
67	e 1
68	k 1
69	drop table t1;
70	create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
71	insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
72	select * from t1;
73	ordid ord
74	1 sdj
75	2 sdj
76	drop table t1;
77	create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
78	insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
79	select * from t1;
80	ordid ord
81	1 abc
82	2 abc
83	3 abc
84	1 sdj
85	2 sdj
86	3 sdj
87	1 zzz
88	drop table t1;
89	create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
90	create table t2 (sid char(20), id int(2));
91	insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
92	insert into t1 select * from t2;
93	select * from t1;
94	sid id
95	skr 1
96	skr 2
97	test 1
98	drop table t1,t2;
99	create table t1 (a int not null primary key auto_increment);
100	insert into t1 values (0);
101	update t1 set a=0;
102	select * from t1;
103	a
104	0
105	check table t1;
106	Table Op Msg_type Msg_text
107	test.t1 check warning Found row where the auto_increment column has the value 0
108	test.t1 check status OK
109	drop table t1;
110	create table t1 (a int not null auto_increment primary key);
111	insert into t1 values (NULL);
112	insert into t1 values (-1);
113	select last_insert_id();
114	last_insert_id()
115	1
116	insert into t1 values (NULL);
117	select * from t1;
118	a
119	-1
120	1
121	2
122	drop table t1;
123	create table t1 (a int not null auto_increment primary key) /!40102 engine=heap /;
124	insert into t1 values (NULL);
125	insert into t1 values (-1);
126	select last_insert_id();
127	last_insert_id()
128	1
129	insert into t1 values (NULL);
130	select * from t1;
131	a
132	1
133	-1
134	2
135	drop table t1;
136	create table t1 (i tinyint unsigned not null auto_increment primary key);
137	insert into t1 set i = 254;
138	insert into t1 set i = null;
139	select last_insert_id();
140	last_insert_id()
141	255
142	explain extended select last_insert_id();
143	id select_type table type possible_keys key key_len ref rows filtered Extra
144	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
145	Warnings:
146	Note 1003 select last_insert_id() AS `last_insert_id()`
147	insert into t1 set i = 254;
148	ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
149	select last_insert_id();
150	last_insert_id()
151	255
152	insert into t1 set i = null;
153	ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
154	select last_insert_id();
155	last_insert_id()
156	255
157	drop table t1;
158	create table t1 (i tinyint unsigned not null auto_increment, key (i));
159	insert into t1 set i = 254;
160	insert into t1 set i = null;
161	select last_insert_id();
162	last_insert_id()
163	255
164	insert into t1 set i = null;
165	Warnings:
166	Warning 1264 Out of range value for column 'i' at row 1
167	select last_insert_id();
168	last_insert_id()
169	255
170	drop table t1;
171	create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
172	insert into t1 values (NULL, 10);
173	select last_insert_id();
174	last_insert_id()
175	1
176	insert into t1 values (NULL, 15);
177	select last_insert_id();
178	last_insert_id()
179	2
180	insert into t1 values (NULL, 10);
181	ERROR 23000: Duplicate entry '10' for key 'b'
182	select last_insert_id();
183	last_insert_id()
184	2
185	drop table t1;
186	create table t1(a int auto_increment,b int null,primary key(a));
187	SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
188	insert into t1(a,b)values(NULL,1);
189	insert into t1(a,b)values(200,2);
190	insert into t1(a,b)values(0,3);
191	insert into t1(b)values(4);
192	insert into t1(b)values(5);
193	insert into t1(b)values(6);
194	insert into t1(b)values(7);
195	select * from t1 order by b;
196	a b
197	1 1
198	200 2
199	0 3
200	201 4
201	202 5
202	203 6
203	204 7
204	alter table t1 modify b mediumint;
205	select * from t1 order by b;
206	a b
207	1 1
208	200 2
209	0 3
210	201 4
211	202 5
212	203 6
213	204 7
214	create table t2 (a int);
215	insert t2 values (1),(2);
216	alter table t2 add b int auto_increment primary key;
217	select * from t2;
218	a b
219	1 1
220	2 2
221	drop table t2;
222	delete from t1 where a=0;
223	update t1 set a=0 where b=5;
224	select * from t1 order by b;
225	a b
226	1 1
227	200 2
228	201 4
229	0 5
230	203 6
231	204 7
232	delete from t1 where a=0;
233	update t1 set a=NULL where b=6;
234	ERROR 23000: Column 'a' cannot be null
235	update t1 set a=300 where b=7;
236	SET SQL_MODE='';
237	insert into t1(a,b)values(NULL,8);
238	insert into t1(a,b)values(400,9);
239	insert into t1(a,b)values(0,10);
240	insert into t1(b)values(11);
241	insert into t1(b)values(12);
242	insert into t1(b)values(13);
243	insert into t1(b)values(14);
244	select * from t1 order by b;
245	a b
246	1 1
247	200 2
248	201 4
249	203 6
250	300 7
251	301 8
252	400 9
253	401 10
254	402 11
255	403 12
256	404 13
257	405 14
258	delete from t1 where a=0;
259	update t1 set a=0 where b=12;
260	select * from t1 order by b;
261	a b
262	1 1
263	200 2
264	201 4
265	203 6
266	300 7
267	301 8
268	400 9
269	401 10
270	402 11
271	0 12
272	404 13
273	405 14
274	delete from t1 where a=0;
275	update t1 set a=NULL where b=13;
276	ERROR 23000: Column 'a' cannot be null
277	update t1 set a=500 where b=14;
278	select * from t1 order by b;
279	a b
280	1 1
281	200 2
282	201 4
283	203 6
284	300 7
285	301 8
286	400 9
287	401 10
288	402 11
289	404 13
290	500 14
291	drop table t1;
292	create table t1 (a bigint);
293	insert into t1 values (1), (2), (3), (NULL), (NULL);
294	alter table t1 modify a bigint not null auto_increment primary key;
295	select * from t1;
296	a
297	1
298	2
299	3
300	4
301	5
302	drop table t1;
303	create table t1 (a bigint);
304	insert into t1 values (1), (2), (3), (0), (0);
305	alter table t1 modify a bigint not null auto_increment primary key;
306	select * from t1;
307	a
308	1
309	2
310	3
311	4
312	5
313	drop table t1;
314	create table t1 (a bigint);
315	insert into t1 values (0), (1), (2), (3);
316	set sql_mode=NO_AUTO_VALUE_ON_ZERO;
317	alter table t1 modify a bigint not null auto_increment primary key;
318	set sql_mode= '';
319	select * from t1;
320	a
321	0
322	1
323	2
324	3
325	drop table t1;
326	create table t1 (a int auto_increment primary key , b int null);
327	set sql_mode=NO_AUTO_VALUE_ON_ZERO;
328	insert into t1 values (0,1),(1,2),(2,3);
329	select * from t1;
330	a b
331	0 1
332	1 2
333	2 3
334	set sql_mode= '';
335	alter table t1 modify b varchar(255);
336	insert into t1 values (0,4);
337	select * from t1;
338	a b
339	0 1
340	1 2
341	2 3
342	3 4
343	drop table t1;
344	CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
345	INSERT INTO t1 (b) VALUES ('aaaa');
346	CHECK TABLE t1;
347	Table Op Msg_type Msg_text
348	test.t1 check status OK
349	INSERT INTO t1 (b) VALUES ('');
350	CHECK TABLE t1;
351	Table Op Msg_type Msg_text
352	test.t1 check status OK
353	INSERT INTO t1 (b) VALUES ('bbbb');
354	CHECK TABLE t1;
355	Table Op Msg_type Msg_text
356	test.t1 check status OK
357	DROP TABLE IF EXISTS t1;
358	CREATE TABLE `t1` (
359	t1_name VARCHAR(255) DEFAULT NULL,
360	t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
361	KEY (t1_name),
362	PRIMARY KEY (t1_id)
363	) AUTO_INCREMENT = 1000;
364	INSERT INTO t1 (t1_name) VALUES('MySQL');
365	INSERT INTO t1 (t1_name) VALUES('MySQL');
366	INSERT INTO t1 (t1_name) VALUES('MySQL');
367	SELECT * from t1;
368	t1_name t1_id
369	MySQL 1000
370	MySQL 1001
371	MySQL 1002
372	SHOW CREATE TABLE `t1`;
373	Table Create Table
374	t1 CREATE TABLE `t1` (
375	`t1_name` varchar(255) DEFAULT NULL,
376	`t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
377	PRIMARY KEY (`t1_id`),
378	KEY `t1_name` (`t1_name`)
379	) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
380	DROP TABLE `t1`;
381	create table t1(a int not null auto_increment primary key);
382	create table t2(a int not null auto_increment primary key, t1a int);
383	insert into t1 values(NULL);
384	insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
385	insert into t1 values (NULL);
386	insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
387	(NULL, LAST_INSERT_ID());
388	insert into t1 values (NULL);
389	insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
390	(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
391	select * from t2;
392	a t1a
393	1 1
394	2 1
395	3 2
396	4 2
397	5 2
398	6 3
399	7 3
400	8 3
401	9 3
402	drop table t1, t2;
403	End of 4.1 tests
404	CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
405	insert into t1 (b) values (1);
406	replace into t1 (b) values (2), (1), (3);
407	select * from t1;
408	a b
409	3 1
410	2 2
411	4 3
412	truncate table t1;
413	insert into t1 (b) values (1);
414	replace into t1 (b) values (2);
415	replace into t1 (b) values (1);
416	replace into t1 (b) values (3);
417	select * from t1;
418	a b
419	3 1
420	2 2
421	4 3
422	drop table t1;
423	create table t1 (rowid int not null auto_increment, val int not null,primary
424	key (rowid), unique(val));
425	replace into t1 (val) values ('1'),('2');
426	replace into t1 (val) values ('1'),('2');
427	insert into t1 (val) values ('1'),('2');
428	ERROR 23000: Duplicate entry '1' for key 'val'
429	select * from t1;
430	rowid val
431	3 1
432	4 2
433	drop table t1;
434	create table t1 (a int not null auto_increment primary key, val int);
435	insert into t1 (val) values (1);
436	update t1 set a=2 where a=1;
437	insert into t1 (val) values (1);
438	select * from t1;
439	a val
440	2 1
441	3 1
442	drop table t1;
443	CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
444	INSERT INTO t1 VALUES(0, 0);
445	INSERT INTO t1 VALUES(1, 1);
446	ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
447	ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
448	DROP TABLE t1;
449	create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
450	insert into t1 values(null,1,1,now());
451	insert into t1 values(null,0,0,null);
452	replace into t1 values(null,1,0,null);
453	select last_insert_id();
454	last_insert_id()
455	3
456	drop table t1;