~drizzle-trunk/drizzle/development : contents of mysql-test/suite/rpl/r/rpl_insert

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

#
# Setup
#
use test;
drop table if exists t1, t2, t3;
#
# See if queries that use both auto_increment and LAST_INSERT_ID()
# are replicated well
#
# We also check how the foreign_key_check variable is replicated
#
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create table t1(a int auto_increment, key(a));
create table t2(b int auto_increment, c int, key(b));
insert into t1 values (1),(2),(3);
insert into t1 values (null);
insert into t2 values (null,last_insert_id());
select * from t1 ORDER BY a;
a
1
2
3
4
select * from t2 ORDER BY b;
b	c
1	4
drop table t1;
drop table t2;
create table t1(a int auto_increment, key(a)) engine=innodb;
create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=innodb;
SET FOREIGN_KEY_CHECKS=0;
insert into t1 values (10);
insert into t1 values (null),(null),(null);
insert into t2 values (5,0);
insert into t2 values (null,last_insert_id());
SET FOREIGN_KEY_CHECKS=1;
select * from t1;
a
10
11
12
13
select * from t2;
b	c
5	0
6	11
#
# check if INSERT SELECT in auto_increment is well replicated (bug #490)
#
drop table t2;
drop table t1;
create table t1(a int auto_increment, key(a));
create table t2(b int auto_increment, c int, key(b));
insert into t1 values (10);
insert into t1 values (null),(null),(null);
insert into t2 values (5,0);
insert into t2 (c) select * from t1 ORDER BY a;
select * from t2 ORDER BY b;
b	c
5	0
6	10
7	11
8	12
9	13
select * from t1 ORDER BY a;
a
10
11
12
13
select * from t2 ORDER BY b;
b	c
5	0
6	10
7	11
8	12
9	13
drop table t1;
drop table t2;
#
# Bug#8412: Error codes reported in binary log for CHARACTER SET,
#           FOREIGN_KEY_CHECKS
#
SET TIMESTAMP=1000000000;
CREATE TABLE t1 ( a INT UNIQUE );
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);
Got one of the listed errors
drop table t1;
#
# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
#
create table t1(a int auto_increment, key(a));
create table t2(a int);
insert into t1 (a) values (null);
insert into t2 (a) select a from t1 where a is null;
insert into t2 (a) select a from t1 where a is null;
select * from t2;
a
1
select * from t2;
a
1
drop table t1;
drop table t2;
#
# End of 4.1 tests
#
#
# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
#
# The solution is not to reset last_insert_id on enter to sub-statement.
#
drop function if exists bug15728;
drop function if exists bug15728_insert;
drop table if exists t1, t2;
create table t1 (
id int not null auto_increment,
last_id int,
primary key (id)
);
create function bug15728() returns int(11)
return last_insert_id();
insert into t1 (last_id) values (0);
insert into t1 (last_id) values (last_insert_id());
insert into t1 (last_id) values (bug15728());
create table t2 (
id int not null auto_increment,
last_id int,
primary key (id)
);
create function bug15728_insert() returns int(11) modifies sql data
begin
insert into t2 (last_id) values (bug15728());
return bug15728();
end|
create trigger t1_bi before insert on t1 for each row
begin
declare res int;
select bug15728_insert() into res;
set NEW.last_id = res;
end|
insert into t1 (last_id) values (0);
drop trigger t1_bi;
select last_insert_id();
last_insert_id()
4
select bug15728_insert();
bug15728_insert()
2
select last_insert_id();
last_insert_id()
4
insert into t1 (last_id) values (bug15728());
select last_insert_id();
last_insert_id()
5
drop procedure if exists foo;
create procedure foo()
begin
declare res int;
insert into t2 (last_id) values (bug15728());
insert into t1 (last_id) values (bug15728());
end|
call foo();
select * from t1;
id	last_id
1	0
2	1
3	2
4	1
5	4
6	3
select * from t2;
id	last_id
1	3
2	4
3	5
select * from t1;
id	last_id
1	0
2	1
3	2
4	1
5	4
6	3
select * from t2;
id	last_id
1	3
2	4
3	5
drop function bug15728;
drop function bug15728_insert;
drop table t1,t2;
drop procedure foo;
create table t1 (n int primary key auto_increment not null,
b int, unique(b));
set sql_log_bin=0;
insert into t1 values(null,100);
replace into t1 values(null,50),(null,100),(null,150);
select * from t1 order by n;
n	b
2	50
3	100
4	150
truncate table t1;
set sql_log_bin=1;
insert into t1 values(null,100);
select * from t1 order by n;
n	b
1	100
insert into t1 values(null,200),(null,300);
delete from t1 where b <> 100;
select * from t1 order by n;
n	b
1	100
replace into t1 values(null,100),(null,350);
select * from t1 order by n;
n	b
2	100
3	350
select * from t1 order by n;
n	b
2	100
3	350
insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
select * from t1 order by n;
n	b
2	100
4	400
1000	350
1001	600
select * from t1 order by n;
n	b
2	100
4	400
1000	350
1001	600
drop table t1;
create table t1 (n int primary key auto_increment not null,
b int, unique(b));
insert into t1 values(null,100);
select * from t1 order by n;
n	b
1	100
insert into t1 values(null,200),(null,300);
delete from t1 where b <> 100;
select * from t1 order by n;
n	b
1	100
insert into t1 values(null,100),(null,350) on duplicate key update n=2;
select * from t1 order by n;
n	b
2	100
3	350
select * from t1 order by n;
n	b
2	100
3	350
drop table t1;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
UNIQUE(b));
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
SELECT * FROM t1;
a	b
1	10
2	2
SELECT * FROM t1;
a	b
1	10
2	2
drop table t1;
CREATE TABLE t1 (
id bigint(20) unsigned NOT NULL auto_increment,
field_1 int(10) unsigned NOT NULL,
field_2 varchar(255) NOT NULL,
field_3 varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY field_1 (field_1, field_2)
);
CREATE TABLE t2 (
field_a int(10) unsigned NOT NULL,
field_b varchar(255) NOT NULL,
field_c varchar(255) NOT NULL
);
INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
SELECT * FROM t1;
id	field_1	field_2	field_3
1	1	a	1a
2	2	b	2b
3	3	c	3c
4	4	d	4d
5	5	e	5e
6	6	f	6f
SELECT * FROM t1;
id	field_1	field_2	field_3
1	1	a	1a
2	2	b	2b
3	3	c	3c
4	4	d	4d
5	5	e	5e
6	6	f	6f
drop table t1, t2;
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1, t2;
SELECT LAST_INSERT_ID(0);
LAST_INSERT_ID(0)
0
CREATE TABLE t1 (
id INT NOT NULL DEFAULT 0,
last_id INT,
PRIMARY KEY (id)
);
CREATE TABLE t2 (
id INT NOT NULL AUTO_INCREMENT,
last_id INT,
PRIMARY KEY (id)
);
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
END|
CALL p1();
SELECT * FROM t1;
id	last_id
0	1
SELECT * FROM t2;
id	last_id
1	0
SELECT * FROM t1;
id	last_id
0	1
SELECT * FROM t2;
id	last_id
1	0
DROP PROCEDURE p1;
DROP TABLE t1, t2;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP FUNCTION IF EXISTS f3;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
j INT DEFAULT 0
);
CREATE TABLE t2 (i INT);
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1 (i) VALUES (NULL);
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
INSERT INTO t1 (i) VALUES (NULL), (NULL);
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
END |
CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
BEGIN
INSERT INTO t1 (i) VALUES (NULL);
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
INSERT INTO t1 (i) VALUES (NULL), (NULL);
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
RETURN 0;
END |
CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
RETURN LAST_INSERT_ID() |
CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
BEGIN
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
RETURN 0;
END |
INSERT INTO t1 VALUES (NULL, -1);
CALL p1();
SELECT f1();
f1()
0
INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
INSERT INTO t1 VALUES (NULL, f2());
INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
(NULL, @@LAST_INSERT_ID);
INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
UPDATE t1 SET j= -1 WHERE i IS NULL;
INSERT INTO t1 (i) VALUES (NULL);
INSERT INTO t1 (i) VALUES (NULL);
SELECT f3();
f3()
0
SELECT * FROM t1;
i	j
1	-1
2	0
3	0
4	0
5	0
6	0
7	0
8	3
9	3
10	3
11	3
12	3
13	8
14	13
15	5
16	13
17	-1
18	14
19	0
20	0
SELECT * FROM t2;
i
2
3
5
6
19
SELECT * FROM t1;
i	j
1	-1
2	0
3	0
4	0
5	0
6	0
7	0
8	3
9	3
10	3
11	3
12	3
13	8
14	13
15	5
16	13
17	-1
18	14
19	0
20	0
SELECT * FROM t2;
i
2
3
5
6
19
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION f3;
DROP TABLE t1, t2;
#
# End of 5.0 tests
#
create table t2 (
id int not null auto_increment,
last_id int,
primary key (id)
);
truncate table t2;
create table t1 (id tinyint primary key);
create function insid() returns int
begin
insert into t2 (last_id) values (0);
return 0;
end|
set sql_log_bin=0;
insert into t2 (id) values(1),(2),(3);
delete from t2;
set sql_log_bin=1;
select insid();
insid()
0
set sql_log_bin=0;
insert into t2 (id) values(5),(6),(7);
delete from t2 where id>=5;
set sql_log_bin=1;
insert into t1 select insid();
select * from t1;
id
0
select * from t2;
id	last_id
4	0
8	0
select * from t1;
id
0
select * from t2;
id	last_id
4	0
8	0
drop table t1;
drop function insid;
truncate table t2;
create table t1 (n int primary key auto_increment not null,
b int, unique(b));
create procedure foo()
begin
insert into t1 values(null,10);
insert ignore into t1 values(null,10);
insert ignore into t1 values(null,10);
insert into t2 values(null,3);
end|
call foo();
select * from t1;
n	b
1	10
select * from t2;
id	last_id
1	3
select * from t1;
n	b
1	10
select * from t2;
id	last_id
1	3
drop table t1, t2;
drop procedure foo;

1 by brian clean slate	1	#
	2	# Setup
	3	#
	4	use test;
	5	drop table if exists t1, t2, t3;
	6	#
	7	# See if queries that use both auto_increment and LAST_INSERT_ID()
	8	# are replicated well
	9	#
	10	# We also check how the foreign_key_check variable is replicated
	11	#
	12	stop slave;
	13	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	14	reset master;
	15	reset slave;
	16	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	17	start slave;
	18	create table t1(a int auto_increment, key(a));
	19	create table t2(b int auto_increment, c int, key(b));
	20	insert into t1 values (1),(2),(3);
	21	insert into t1 values (null);
	22	insert into t2 values (null,last_insert_id());
	23	select * from t1 ORDER BY a;
	24	a
	25	1
	26	2
	27	3
	28	4
	29	select * from t2 ORDER BY b;
	30	b c
	31	1 4
	32	drop table t1;
	33	drop table t2;
	34	create table t1(a int auto_increment, key(a)) engine=innodb;
	35	create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=innodb;
	36	SET FOREIGN_KEY_CHECKS=0;
	37	insert into t1 values (10);
	38	insert into t1 values (null),(null),(null);
	39	insert into t2 values (5,0);
	40	insert into t2 values (null,last_insert_id());
	41	SET FOREIGN_KEY_CHECKS=1;
	42	select * from t1;
	43	a
	44	10
	45	11
	46	12
	47	13
	48	select * from t2;
	49	b c
	50	5 0
	51	6 11
	52	#
	53	# check if INSERT SELECT in auto_increment is well replicated (bug #490)
	54	#
	55	drop table t2;
	56	drop table t1;
	57	create table t1(a int auto_increment, key(a));
	58	create table t2(b int auto_increment, c int, key(b));
	59	insert into t1 values (10);
	60	insert into t1 values (null),(null),(null);
	61	insert into t2 values (5,0);
	62	insert into t2 (c) select * from t1 ORDER BY a;
	63	select * from t2 ORDER BY b;
	64	b c
65	5 0
66	6 10
67	7 11
68	8 12
69	9 13
70	select * from t1 ORDER BY a;
71	a
72	10
73	11
74	12
75	13
76	select * from t2 ORDER BY b;
77	b c
78	5 0
79	6 10
80	7 11
81	8 12
82	9 13
83	drop table t1;
84	drop table t2;
85	#
86	# Bug#8412: Error codes reported in binary log for CHARACTER SET,
87	# FOREIGN_KEY_CHECKS
88	#
89	SET TIMESTAMP=1000000000;
90	CREATE TABLE t1 ( a INT UNIQUE );
91	SET FOREIGN_KEY_CHECKS=0;
92	INSERT INTO t1 VALUES (1),(1);
93	Got one of the listed errors
94	drop table t1;
95	#
96	# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
97	#
98	create table t1(a int auto_increment, key(a));
99	create table t2(a int);
100	insert into t1 (a) values (null);
101	insert into t2 (a) select a from t1 where a is null;
102	insert into t2 (a) select a from t1 where a is null;
103	select * from t2;
104	a
105	1
106	select * from t2;
107	a
108	1
109	drop table t1;
110	drop table t2;
111	#
112	# End of 4.1 tests
113	#
114	#
115	# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
116	#
117	# The solution is not to reset last_insert_id on enter to sub-statement.
118	#
119	drop function if exists bug15728;
120	drop function if exists bug15728_insert;
121	drop table if exists t1, t2;
122	create table t1 (
123	id int not null auto_increment,
124	last_id int,
125	primary key (id)
126	);
127	create function bug15728() returns int(11)
128	return last_insert_id();
129	insert into t1 (last_id) values (0);
130	insert into t1 (last_id) values (last_insert_id());
131	insert into t1 (last_id) values (bug15728());
132	create table t2 (
133	id int not null auto_increment,
134	last_id int,
135	primary key (id)
136	);
137	create function bug15728_insert() returns int(11) modifies sql data
138	begin
139	insert into t2 (last_id) values (bug15728());
140	return bug15728();
141	end\|
142	create trigger t1_bi before insert on t1 for each row
143	begin
144	declare res int;
145	select bug15728_insert() into res;
146	set NEW.last_id = res;
147	end\|
148	insert into t1 (last_id) values (0);
149	drop trigger t1_bi;
150	select last_insert_id();
151	last_insert_id()
152	4
153	select bug15728_insert();
154	bug15728_insert()
155	2
156	select last_insert_id();
157	last_insert_id()
158	4
159	insert into t1 (last_id) values (bug15728());
160	select last_insert_id();
161	last_insert_id()
162	5
163	drop procedure if exists foo;
164	create procedure foo()
165	begin
166	declare res int;
167	insert into t2 (last_id) values (bug15728());
168	insert into t1 (last_id) values (bug15728());
169	end\|
170	call foo();
171	select * from t1;
172	id last_id
173	1 0
174	2 1
175	3 2
176	4 1
177	5 4
178	6 3
179	select * from t2;
180	id last_id
181	1 3
182	2 4
183	3 5
184	select * from t1;
185	id last_id
186	1 0
187	2 1
188	3 2
189	4 1
190	5 4
191	6 3
192	select * from t2;
193	id last_id
194	1 3
195	2 4
196	3 5
197	drop function bug15728;
198	drop function bug15728_insert;
199	drop table t1,t2;
200	drop procedure foo;
201	create table t1 (n int primary key auto_increment not null,
202	b int, unique(b));
203	set sql_log_bin=0;
204	insert into t1 values(null,100);
205	replace into t1 values(null,50),(null,100),(null,150);
206	select * from t1 order by n;
207	n b
208	2 50
209	3 100
210	4 150
211	truncate table t1;
212	set sql_log_bin=1;
213	insert into t1 values(null,100);
214	select * from t1 order by n;
215	n b
216	1 100
217	insert into t1 values(null,200),(null,300);
218	delete from t1 where b <> 100;
219	select * from t1 order by n;
220	n b
221	1 100
222	replace into t1 values(null,100),(null,350);
223	select * from t1 order by n;
224	n b
225	2 100
226	3 350
227	select * from t1 order by n;
228	n b
229	2 100
230	3 350
231	insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
232	select * from t1 order by n;
233	n b
234	2 100
235	4 400
236	1000 350
237	1001 600
238	select * from t1 order by n;
239	n b
240	2 100
241	4 400
242	1000 350
243	1001 600
244	drop table t1;
245	create table t1 (n int primary key auto_increment not null,
246	b int, unique(b));
247	insert into t1 values(null,100);
248	select * from t1 order by n;
249	n b
250	1 100
251	insert into t1 values(null,200),(null,300);
252	delete from t1 where b <> 100;
253	select * from t1 order by n;
254	n b
255	1 100
256	insert into t1 values(null,100),(null,350) on duplicate key update n=2;
257	select * from t1 order by n;
258	n b
259	2 100
260	3 350
261	select * from t1 order by n;
262	n b
263	2 100
264	3 350
265	drop table t1;
266	CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
267	UNIQUE(b));
268	INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
269	SELECT * FROM t1;
270	a b
271	1 10
272	2 2
273	SELECT * FROM t1;
274	a b
275	1 10
276	2 2
277	drop table t1;
278	CREATE TABLE t1 (
279	id bigint(20) unsigned NOT NULL auto_increment,
280	field_1 int(10) unsigned NOT NULL,
281	field_2 varchar(255) NOT NULL,
282	field_3 varchar(255) NOT NULL,
283	PRIMARY KEY (id),
284	UNIQUE KEY field_1 (field_1, field_2)
285	);
286	CREATE TABLE t2 (
287	field_a int(10) unsigned NOT NULL,
288	field_b varchar(255) NOT NULL,
289	field_c varchar(255) NOT NULL
290	);
291	INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
292	INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
293	INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
294	INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
295	INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
296	INSERT INTO t1 (field_1, field_2, field_3)
297	SELECT t2.field_a, t2.field_b, t2.field_c
298	FROM t2
299	ON DUPLICATE KEY UPDATE
300	t1.field_3 = t2.field_c;
301	INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
302	INSERT INTO t1 (field_1, field_2, field_3)
303	SELECT t2.field_a, t2.field_b, t2.field_c
304	FROM t2
305	ON DUPLICATE KEY UPDATE
306	t1.field_3 = t2.field_c;
307	SELECT * FROM t1;
308	id field_1 field_2 field_3
309	1 1 a 1a
310	2 2 b 2b
311	3 3 c 3c
312	4 4 d 4d
313	5 5 e 5e
314	6 6 f 6f
315	SELECT * FROM t1;
316	id field_1 field_2 field_3
317	1 1 a 1a
318	2 2 b 2b
319	3 3 c 3c
320	4 4 d 4d
321	5 5 e 5e
322	6 6 f 6f
323	drop table t1, t2;
324	DROP PROCEDURE IF EXISTS p1;
325	DROP TABLE IF EXISTS t1, t2;
326	SELECT LAST_INSERT_ID(0);
327	LAST_INSERT_ID(0)
328	0
329	CREATE TABLE t1 (
330	id INT NOT NULL DEFAULT 0,
331	last_id INT,
332	PRIMARY KEY (id)
333	);
334	CREATE TABLE t2 (
335	id INT NOT NULL AUTO_INCREMENT,
336	last_id INT,
337	PRIMARY KEY (id)
338	);
339	CREATE PROCEDURE p1()
340	BEGIN
341	INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
342	INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
343	END\|
344	CALL p1();
345	SELECT * FROM t1;
346	id last_id
347	0 1
348	SELECT * FROM t2;
349	id last_id
350	1 0
351	SELECT * FROM t1;
352	id last_id
353	0 1
354	SELECT * FROM t2;
355	id last_id
356	1 0
357	DROP PROCEDURE p1;
358	DROP TABLE t1, t2;
359	DROP PROCEDURE IF EXISTS p1;
360	DROP FUNCTION IF EXISTS f1;
361	DROP FUNCTION IF EXISTS f2;
362	DROP FUNCTION IF EXISTS f3;
363	DROP TABLE IF EXISTS t1, t2;
364	CREATE TABLE t1 (
365	i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
366	j INT DEFAULT 0
367	);
368	CREATE TABLE t2 (i INT);
369	CREATE PROCEDURE p1()
370	BEGIN
371	INSERT INTO t1 (i) VALUES (NULL);
372	INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
373	INSERT INTO t1 (i) VALUES (NULL), (NULL);
374	INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
375	END \|
376	CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
377	BEGIN
378	INSERT INTO t1 (i) VALUES (NULL);
379	INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
380	INSERT INTO t1 (i) VALUES (NULL), (NULL);
381	INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
382	RETURN 0;
383	END \|
384	CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
385	RETURN LAST_INSERT_ID() \|
386	CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
387	BEGIN
388	INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
389	RETURN 0;
390	END \|
391	INSERT INTO t1 VALUES (NULL, -1);
392	CALL p1();
393	SELECT f1();
394	f1()
395	0
396	INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
397	(NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
398	INSERT INTO t1 VALUES (NULL, f2());
399	INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
400	(NULL, @@LAST_INSERT_ID);
401	INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
402	UPDATE t1 SET j= -1 WHERE i IS NULL;
403	INSERT INTO t1 (i) VALUES (NULL);
404	INSERT INTO t1 (i) VALUES (NULL);
405	SELECT f3();
406	f3()
407	0
408	SELECT * FROM t1;
409	i j
410	1 -1
411	2 0
412	3 0
413	4 0
414	5 0
415	6 0
416	7 0
417	8 3
418	9 3
419	10 3
420	11 3
421	12 3
422	13 8
423	14 13
424	15 5
425	16 13
426	17 -1
427	18 14
428	19 0
429	20 0
430	SELECT * FROM t2;
431	i
432	2
433	3
434	5
435	6
436	19
437	SELECT * FROM t1;
438	i j
439	1 -1
440	2 0
441	3 0
442	4 0
443	5 0
444	6 0
445	7 0
446	8 3
447	9 3
448	10 3
449	11 3
450	12 3
451	13 8
452	14 13
453	15 5
454	16 13
455	17 -1
456	18 14
457	19 0
458	20 0
459	SELECT * FROM t2;
460	i
461	2
462	3
463	5
464	6
465	19
466	DROP PROCEDURE p1;
467	DROP FUNCTION f1;
468	DROP FUNCTION f2;
469	DROP FUNCTION f3;
470	DROP TABLE t1, t2;
471	#
472	# End of 5.0 tests
473	#
474	create table t2 (
475	id int not null auto_increment,
476	last_id int,
477	primary key (id)
478	);
479	truncate table t2;
480	create table t1 (id tinyint primary key);
481	create function insid() returns int
482	begin
483	insert into t2 (last_id) values (0);
484	return 0;
485	end\|
486	set sql_log_bin=0;
487	insert into t2 (id) values(1),(2),(3);
488	delete from t2;
489	set sql_log_bin=1;
490	select insid();
491	insid()
492	0
493	set sql_log_bin=0;
494	insert into t2 (id) values(5),(6),(7);
495	delete from t2 where id>=5;
496	set sql_log_bin=1;
497	insert into t1 select insid();
498	select * from t1;
499	id
500	0
501	select * from t2;
502	id last_id
503	4 0
504	8 0
505	select * from t1;
506	id
507	0
508	select * from t2;
509	id last_id
510	4 0
511	8 0
512	drop table t1;
513	drop function insid;
514	truncate table t2;
515	create table t1 (n int primary key auto_increment not null,
516	b int, unique(b));
517	create procedure foo()
518	begin
519	insert into t1 values(null,10);
520	insert ignore into t1 values(null,10);
521	insert ignore into t1 values(null,10);
522	insert into t2 values(null,3);
523	end\|
524	call foo();
525	select * from t1;
526	n b
527	1 10
528	select * from t2;
529	id last_id
530	1 3
531	select * from t1;
532	n b
533	1 10
534	select * from t2;
535	id last_id
536	1 3
537	drop table t1, t2;
538	drop procedure foo;