5
drop table if exists t1, t2, t3;
7
# See if queries that use both auto_increment and LAST_INSERT_ID()
10
# We also check how the foreign_key_check variable is replicated
13
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
16
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
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;
29
select * from t2 ORDER BY b;
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;
53
# check if INSERT SELECT in auto_increment is well replicated (bug #490)
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;
70
select * from t1 ORDER BY a;
76
select * from t2 ORDER BY b;
86
# Bug#8412: Error codes reported in binary log for CHARACTER SET,
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
96
# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
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;
115
# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
117
# The solution is not to reset last_insert_id on enter to sub-statement.
119
drop function if exists bug15728;
120
drop function if exists bug15728_insert;
121
drop table if exists t1, t2;
123
id int not null auto_increment,
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());
133
id int not null auto_increment,
137
create function bug15728_insert() returns int(11) modifies sql data
139
insert into t2 (last_id) values (bug15728());
142
create trigger t1_bi before insert on t1 for each row
145
select bug15728_insert() into res;
146
set NEW.last_id = res;
148
insert into t1 (last_id) values (0);
150
select last_insert_id();
153
select bug15728_insert();
156
select last_insert_id();
159
insert into t1 (last_id) values (bug15728());
160
select last_insert_id();
163
drop procedure if exists foo;
164
create procedure foo()
167
insert into t2 (last_id) values (bug15728());
168
insert into t1 (last_id) values (bug15728());
197
drop function bug15728;
198
drop function bug15728_insert;
201
create table t1 (n int primary key auto_increment not null,
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;
213
insert into t1 values(null,100);
214
select * from t1 order by n;
217
insert into t1 values(null,200),(null,300);
218
delete from t1 where b <> 100;
219
select * from t1 order by n;
222
replace into t1 values(null,100),(null,350);
223
select * from t1 order by n;
227
select * from t1 order by n;
231
insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
232
select * from t1 order by n;
238
select * from t1 order by n;
245
create table t1 (n int primary key auto_increment not null,
247
insert into t1 values(null,100);
248
select * from t1 order by n;
251
insert into t1 values(null,200),(null,300);
252
delete from t1 where b <> 100;
253
select * from t1 order by n;
256
insert into t1 values(null,100),(null,350) on duplicate key update n=2;
257
select * from t1 order by n;
261
select * from t1 order by n;
266
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
268
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
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,
284
UNIQUE KEY field_1 (field_1, field_2)
287
field_a int(10) unsigned NOT NULL,
288
field_b varchar(255) NOT NULL,
289
field_c varchar(255) NOT NULL
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
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
305
ON DUPLICATE KEY UPDATE
306
t1.field_3 = t2.field_c;
308
id field_1 field_2 field_3
316
id field_1 field_2 field_3
324
DROP PROCEDURE IF EXISTS p1;
325
DROP TABLE IF EXISTS t1, t2;
326
SELECT LAST_INSERT_ID(0);
330
id INT NOT NULL DEFAULT 0,
335
id INT NOT NULL AUTO_INCREMENT,
339
CREATE PROCEDURE p1()
341
INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
342
INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
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;
365
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
368
CREATE TABLE t2 (i INT);
369
CREATE PROCEDURE p1()
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());
376
CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
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());
384
CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
385
RETURN LAST_INSERT_ID() |
386
CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
388
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
391
INSERT INTO t1 VALUES (NULL, -1);
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);
475
id int not null auto_increment,
480
create table t1 (id tinyint primary key);
481
create function insid() returns int
483
insert into t2 (last_id) values (0);
487
insert into t2 (id) values(1),(2),(3);
494
insert into t2 (id) values(5),(6),(7);
495
delete from t2 where id>=5;
497
insert into t1 select insid();
515
create table t1 (n int primary key auto_increment not null,
517
create procedure foo()
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);