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

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

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
INSERT t1 VALUES (1,2,10), (3,4,20);
INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	30
INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	130
INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	1020
5	6	130
INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000;
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4;
ERROR 23000: Duplicate entry '4' for key 'b'
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
TRUNCATE TABLE t1;
INSERT t1 VALUES (1,2,10), (3,4,20);
INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	6	30
8	9	60
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	0	30
8	9	60
INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
SELECT *, VALUES(a) FROM t1;
a	b	c	VALUES(a)
1	2	10	NULL
3	4	127	NULL
5	0	30	NULL
8	9	60	NULL
2	1	11	NULL
explain extended SELECT *, VALUES(a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1`
explain extended select * from t1 where values(a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where values(`test`.`t1`.`a`)
DROP TABLE t1;
create table t1(a int primary key, b int);
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
select * from t1;
a	b
1	1
2	2
3	3
4	4
5	5
insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18)
on duplicate key update b=b+10;
affected rows: 7
info: Records: 5  Duplicates: 2  Warnings: 0
select * from t1;
a	b
1	1
2	2
3	3
4	14
5	15
6	16
7	17
8	18
replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29);
affected rows: 9
info: Records: 5  Duplicates: 4  Warnings: 0
select * from t1;
a	b
1	1
2	2
3	3
4	14
5	25
6	26
7	27
8	28
9	29
drop table t1;
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
INSERT t1 VALUES (1,2,10), (3,4,20);
INSERT t1 SELECT 5,6,30 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	30
INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	130
INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	1020
5	6	130
INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
ERROR 23000: Duplicate entry '4' for key 'b'
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
TRUNCATE TABLE t1;
INSERT t1 VALUES (1,2,10), (3,4,20);
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1);
INSERT t2 VALUES (2,1,11,2), (7,4,40,2);
INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=t1.c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	6	30
8	9	60
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	0	30
8	9	60
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
ERROR 23000: Column 'c' in field list is ambiguous
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
SELECT *, VALUES(a) FROM t1;
a	b	c	VALUES(a)
1	2	10	NULL
3	4	127	NULL
5	0	30	NULL
8	9	60	NULL
2	1	11	NULL
DROP TABLE t1;
DROP TABLE t2;
create table t1 (a int not null unique) engine=myisam;
insert into t1 values (1),(2);
insert ignore into t1 select 1 on duplicate key update a=2;
select * from t1;
a
1
2
insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ;
select * from t1;
a
1
3
insert into t1 select 1 on duplicate key update a=2;
select * from t1;
a
2
3
insert into t1 select a from t1 on duplicate key update a=a+1 ;
ERROR 23000: Column 'a' in field list is ambiguous
insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
ERROR 23000: Column 't1.a' in field list is ambiguous
drop table t1;
CREATE TABLE t1 (
a BIGINT(20) NOT NULL DEFAULT 0,
PRIMARY KEY  (a)
) ENGINE=MyISAM;
INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ;
DROP TABLE t1;
CREATE TABLE t1
(
a   BIGINT UNSIGNED,
b   BIGINT UNSIGNED,
PRIMARY KEY (a)
);
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
IF(VALUES(b) > t1.b, VALUES(b), t1.b);
SELECT * FROM t1;
a	b
45	1
INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b =
IF(VALUES(b) > t1.b, VALUES(b), t1.b);
SELECT * FROM t1;
a	b
45	2
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = 
IF(VALUES(b) > t1.b, VALUES(b), t1.b);
SELECT * FROM t1;
a	b
45	2
DROP TABLE t1;
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
INSERT INTO t1 SELECT 1, j;
ERROR 42S22: Unknown column 'j' in 'field list'
DROP TABLE t1;
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
CREATE TABLE t2 (a INT, b INT);
CREATE TABLE t3 (a INT, c INT);
INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
ON DUPLICATE KEY UPDATE j= a;
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t2 VALUES (1), (3);
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
ERROR 42S22: Unknown column 'a' in 'field list'
DROP TABLE t1,t2;
SET SQL_MODE = 'TRADITIONAL';
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
INSERT INTO t1 (a) VALUES (1);
ERROR HY000: Field 'b' doesn't have a default value
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
ERROR HY000: Field 'b' doesn't have a default value
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
ERROR HY000: Field 'b' doesn't have a default value
SELECT * FROM t1;
a	b
DROP TABLE t1;
CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
f2 VARCHAR(5) NOT NULL UNIQUE);
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1
DROP TABLE t1;
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `t1` (
`id` int(11) PRIMARY KEY auto_increment,
`f1` varchar(10) NOT NULL UNIQUE
);
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1
SELECT * FROM t1;
id	f1
1	test1
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT * FROM t1;
id	f1
1	test1
2	test2
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
2
SELECT * FROM t1;
id	f1
1	test1
2	test2
INSERT IGNORE INTO t1 (f1) VALUES ("test3")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3
SELECT * FROM t1;
id	f1
1	test1
2	test2
3	test3
DROP TABLE t1;
CREATE TABLE `t1` (
`id` int(11) PRIMARY KEY auto_increment,
`f1` varchar(10) NOT NULL UNIQUE
);
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1
SELECT * FROM t1;
id	f1
1	test1
INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
2
SELECT * FROM t1;
id	f1
1	test1
2	test4
DROP TABLE t1;
CREATE TABLE `t1` (
`id` int(11) PRIMARY KEY auto_increment,
`f1` varchar(10) NOT NULL UNIQUE,
tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
);
INSERT INTO t1 (f1) VALUES ("test1");
SELECT id, f1 FROM t1;
id	f1
1	test1
REPLACE INTO t1 VALUES (0,"test1",null);
SELECT id, f1 FROM t1;
id	f1
0	test1
DROP TABLE t1;
SET SQL_MODE='';
CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 CHAR(1) UNIQUE KEY,
cnt INT DEFAULT 1
);
INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C');
SELECT * FROM t1;
id	c1	cnt
1	A	1
2	B	1
3	C	1
INSERT  INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
id	c1	cnt
1	A	2
2	B	1
3	C	1
4	X	1
5	Y	1
6	Z	1
DROP TABLE t1;
CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 INT NOT NULL,
cnt INT DEFAULT 1
);
INSERT INTO t1 (id,c1) VALUES (1,10);
SELECT * FROM t1;
id	c1	cnt
1	10	1
CREATE TABLE t2 (id INT, c1 INT);
INSERT INTO t2 VALUES (1,NULL), (2,2);
INSERT INTO t1 (id,c1) SELECT 1,NULL
ON DUPLICATE KEY UPDATE c1=NULL;
ERROR 23000: Column 'c1' cannot be null
SELECT * FROM t1;
id	c1	cnt
1	10	1
INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
Warnings:
Warning	1048	Column 'c1' cannot be null
Error	1048	Column 'c1' cannot be null
SELECT * FROM t1;
id	c1	cnt
1	0	2
INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
Warnings:
Warning	1048	Column 'c1' cannot be null
Error	1048	Column 'c1' cannot be null
SELECT * FROM t1;
id	c1	cnt
1	0	3
2	2	1
DROP TABLE t1;
create table t1(f1 int primary key,
f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP);
insert into t1(f1) values(1);
select @stamp1:=f2 from t1;
@stamp1:=f2
#
insert into t1(f1) values(1) on duplicate key update f1=1;
select @stamp2:=f2 from t1;
@stamp2:=f2
#
select if( @stamp1 = @stamp2, "correct", "wrong");
if( @stamp1 = @stamp2, "correct", "wrong")
correct
drop table t1;

1 by brian clean slate	1	DROP TABLE IF EXISTS t1, t2;
	2	CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
	3	INSERT t1 VALUES (1,2,10), (3,4,20);
	4	INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE c=c+100;
	5	SELECT * FROM t1;
	6	a b c
	7	1 2 10
	8	3 4 20
	9	5 6 30
	10	INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100;
	11	SELECT * FROM t1;
	12	a b c
	13	1 2 10
	14	3 4 20
	15	5 6 130
	16	INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000;
	17	SELECT * FROM t1;
	18	a b c
	19	1 2 10
	20	3 4 1020
	21	5 6 130
	22	INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000;
	23	SELECT * FROM t1;
	24	a b c
	25	1 2 10010
	26	3 4 1020
	27	5 6 130
	28	INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4;
	29	ERROR 23000: Duplicate entry '4' for key 'b'
	30	SELECT * FROM t1;
	31	a b c
	32	1 2 10010
	33	3 4 1020
	34	5 6 130
	35	TRUNCATE TABLE t1;
	36	INSERT t1 VALUES (1,2,10), (3,4,20);
	37	INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100;
	38	SELECT * FROM t1;
	39	a b c
	40	1 2 10
	41	3 4 120
	42	5 6 30
	43	8 9 60
	44	INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
	45	SELECT * FROM t1;
	46	a b c
	47	1 2 10
	48	3 4 120
	49	5 0 30
	50	8 9 60
	51	INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
	52	SELECT *, VALUES(a) FROM t1;
	53	a b c VALUES(a)
	54	1 2 10 NULL
	55	3 4 127 NULL
	56	5 0 30 NULL
	57	8 9 60 NULL
	58	2 1 11 NULL
	59	explain extended SELECT *, VALUES(a) FROM t1;
	60	id select_type table type possible_keys key key_len ref rows filtered Extra
	61	1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00
	62	Warnings:
	63	Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1`
	64	explain extended select * from t1 where values(a);
65	id select_type table type possible_keys key key_len ref rows filtered Extra
66	1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where
67	Warnings:
68	Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where values(`test`.`t1`.`a`)
69	DROP TABLE t1;
70	create table t1(a int primary key, b int);
71	insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
72	select * from t1;
73	a b
74	1 1
75	2 2
76	3 3
77	4 4
78	5 5
79	insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18)
80	on duplicate key update b=b+10;
81	affected rows: 7
82	info: Records: 5 Duplicates: 2 Warnings: 0
83	select * from t1;
84	a b
85	1 1
86	2 2
87	3 3
88	4 14
89	5 15
90	6 16
91	7 17
92	8 18
93	replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29);
94	affected rows: 9
95	info: Records: 5 Duplicates: 4 Warnings: 0
96	select * from t1;
97	a b
98	1 1
99	2 2
100	3 3
101	4 14
102	5 25
103	6 26
104	7 27
105	8 28
106	9 29
107	drop table t1;
108	CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
109	INSERT t1 VALUES (1,2,10), (3,4,20);
110	INSERT t1 SELECT 5,6,30 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
111	SELECT * FROM t1;
112	a b c
113	1 2 10
114	3 4 20
115	5 6 30
116	INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
117	SELECT * FROM t1;
118	a b c
119	1 2 10
120	3 4 20
121	5 6 130
122	INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
123	SELECT * FROM t1;
124	a b c
125	1 2 10
126	3 4 1020
127	5 6 130
128	INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
129	SELECT * FROM t1;
130	a b c
131	1 2 10010
132	3 4 1020
133	5 6 130
134	INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
135	ERROR 23000: Duplicate entry '4' for key 'b'
136	SELECT * FROM t1;
137	a b c
138	1 2 10010
139	3 4 1020
140	5 6 130
141	TRUNCATE TABLE t1;
142	INSERT t1 VALUES (1,2,10), (3,4,20);
143	CREATE TABLE t2 (a INT, b INT, c INT, d INT);
144	INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1);
145	INSERT t2 VALUES (2,1,11,2), (7,4,40,2);
146	INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=t1.c+100;
147	SELECT * FROM t1;
148	a b c
149	1 2 10
150	3 4 120
151	5 6 30
152	8 9 60
153	INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
154	SELECT * FROM t1;
155	a b c
156	1 2 10
157	3 4 120
158	5 0 30
159	8 9 60
160	INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
161	ERROR 23000: Column 'c' in field list is ambiguous
162	INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
163	SELECT *, VALUES(a) FROM t1;
164	a b c VALUES(a)
165	1 2 10 NULL
166	3 4 127 NULL
167	5 0 30 NULL
168	8 9 60 NULL
169	2 1 11 NULL
170	DROP TABLE t1;
171	DROP TABLE t2;
172	create table t1 (a int not null unique) engine=myisam;
173	insert into t1 values (1),(2);
174	insert ignore into t1 select 1 on duplicate key update a=2;
175	select * from t1;
176	a
177	1
178	2
179	insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ;
180	select * from t1;
181	a
182	1
183	3
184	insert into t1 select 1 on duplicate key update a=2;
185	select * from t1;
186	a
187	2
188	3
189	insert into t1 select a from t1 on duplicate key update a=a+1 ;
190	ERROR 23000: Column 'a' in field list is ambiguous
191	insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
192	ERROR 23000: Column 't1.a' in field list is ambiguous
193	drop table t1;
194	CREATE TABLE t1 (
195	a BIGINT(20) NOT NULL DEFAULT 0,
196	PRIMARY KEY (a)
197	) ENGINE=MyISAM;
198	INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ;
199	DROP TABLE t1;
200	CREATE TABLE t1
201	(
202	a BIGINT UNSIGNED,
203	b BIGINT UNSIGNED,
204	PRIMARY KEY (a)
205	);
206	INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
207	IF(VALUES(b) > t1.b, VALUES(b), t1.b);
208	SELECT * FROM t1;
209	a b
210	45 1
211	INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b =
212	IF(VALUES(b) > t1.b, VALUES(b), t1.b);
213	SELECT * FROM t1;
214	a b
215	45 2
216	INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
217	IF(VALUES(b) > t1.b, VALUES(b), t1.b);
218	SELECT * FROM t1;
219	a b
220	45 2
221	DROP TABLE t1;
222	CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
223	INSERT INTO t1 SELECT 1, j;
224	ERROR 42S22: Unknown column 'j' in 'field list'
225	DROP TABLE t1;
226	CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
227	CREATE TABLE t2 (a INT, b INT);
228	CREATE TABLE t3 (a INT, c INT);
229	INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3
230	ON DUPLICATE KEY UPDATE j= a;
231	DROP TABLE t1,t2,t3;
232	CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
233	CREATE TABLE t2 (a INT);
234	INSERT INTO t1 VALUES (1, 1);
235	INSERT INTO t2 VALUES (1), (3);
236	INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
237	ERROR 42S22: Unknown column 'a' in 'field list'
238	DROP TABLE t1,t2;
239	SET SQL_MODE = 'TRADITIONAL';
240	CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
241	INSERT INTO t1 (a) VALUES (1);
242	ERROR HY000: Field 'b' doesn't have a default value
243	INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
244	ERROR HY000: Field 'b' doesn't have a default value
245	INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
246	ERROR HY000: Field 'b' doesn't have a default value
247	SELECT * FROM t1;
248	a b
249	DROP TABLE t1;
250	CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
251	f2 VARCHAR(5) NOT NULL UNIQUE);
252	INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
253	SELECT LAST_INSERT_ID();
254	LAST_INSERT_ID()
255	1
256	INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
257	SELECT LAST_INSERT_ID();
258	LAST_INSERT_ID()
259	1
260	DROP TABLE t1;
261	SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
262	CREATE TABLE `t1` (
263	`id` int(11) PRIMARY KEY auto_increment,
264	`f1` varchar(10) NOT NULL UNIQUE
265	);
266	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
267	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
268	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
269	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
270	SELECT LAST_INSERT_ID();
271	LAST_INSERT_ID()
272	1
273	SELECT * FROM t1;
274	id f1
275	1 test1
276	INSERT IGNORE INTO t1 (f1) VALUES ("test2")
277	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
278	SELECT * FROM t1;
279	id f1
280	1 test1
281	2 test2
282	INSERT IGNORE INTO t1 (f1) VALUES ("test2")
283	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
284	SELECT LAST_INSERT_ID();
285	LAST_INSERT_ID()
286	2
287	SELECT * FROM t1;
288	id f1
289	1 test1
290	2 test2
291	INSERT IGNORE INTO t1 (f1) VALUES ("test3")
292	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
293	SELECT LAST_INSERT_ID();
294	LAST_INSERT_ID()
295	3
296	SELECT * FROM t1;
297	id f1
298	1 test1
299	2 test2
300	3 test3
301	DROP TABLE t1;
302	CREATE TABLE `t1` (
303	`id` int(11) PRIMARY KEY auto_increment,
304	`f1` varchar(10) NOT NULL UNIQUE
305	);
306	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
307	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
308	SELECT LAST_INSERT_ID();
309	LAST_INSERT_ID()
310	1
311	SELECT * FROM t1;
312	id f1
313	1 test1
314	INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
315	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
316	SELECT LAST_INSERT_ID();
317	LAST_INSERT_ID()
318	2
319	SELECT * FROM t1;
320	id f1
321	1 test1
322	2 test4
323	DROP TABLE t1;
324	CREATE TABLE `t1` (
325	`id` int(11) PRIMARY KEY auto_increment,
326	`f1` varchar(10) NOT NULL UNIQUE,
327	tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
328	);
329	INSERT INTO t1 (f1) VALUES ("test1");
330	SELECT id, f1 FROM t1;
331	id f1
332	1 test1
333	REPLACE INTO t1 VALUES (0,"test1",null);
334	SELECT id, f1 FROM t1;
335	id f1
336	0 test1
337	DROP TABLE t1;
338	SET SQL_MODE='';
339	CREATE TABLE t1 (
340	id INT AUTO_INCREMENT PRIMARY KEY,
341	c1 CHAR(1) UNIQUE KEY,
342	cnt INT DEFAULT 1
343	);
344	INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C');
345	SELECT * FROM t1;
346	id c1 cnt
347	1 A 1
348	2 B 1
349	3 C 1
350	INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
351	ON DUPLICATE KEY UPDATE cnt=cnt+1;
352	SELECT * FROM t1;
353	id c1 cnt
354	1 A 2
355	2 B 1
356	3 C 1
357	4 X 1
358	5 Y 1
359	6 Z 1
360	DROP TABLE t1;
361	CREATE TABLE t1 (
362	id INT AUTO_INCREMENT PRIMARY KEY,
363	c1 INT NOT NULL,
364	cnt INT DEFAULT 1
365	);
366	INSERT INTO t1 (id,c1) VALUES (1,10);
367	SELECT * FROM t1;
368	id c1 cnt
369	1 10 1
370	CREATE TABLE t2 (id INT, c1 INT);
371	INSERT INTO t2 VALUES (1,NULL), (2,2);
372	INSERT INTO t1 (id,c1) SELECT 1,NULL
373	ON DUPLICATE KEY UPDATE c1=NULL;
374	ERROR 23000: Column 'c1' cannot be null
375	SELECT * FROM t1;
376	id c1 cnt
377	1 10 1
378	INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
379	ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
380	Warnings:
381	Warning 1048 Column 'c1' cannot be null
382	Error 1048 Column 'c1' cannot be null
383	SELECT * FROM t1;
384	id c1 cnt
385	1 0 2
386	INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
387	ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
388	Warnings:
389	Warning 1048 Column 'c1' cannot be null
390	Error 1048 Column 'c1' cannot be null
391	SELECT * FROM t1;
392	id c1 cnt
393	1 0 3
394	2 2 1
395	DROP TABLE t1;
396	create table t1(f1 int primary key,
397	f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP);
398	insert into t1(f1) values(1);
399	select @stamp1:=f2 from t1;
400	@stamp1:=f2
401	#
402	insert into t1(f1) values(1) on duplicate key update f1=1;
403	select @stamp2:=f2 from t1;
404	@stamp2:=f2
405	#
406	select if( @stamp1 = @stamp2, "correct", "wrong");
407	if( @stamp1 = @stamp2, "correct", "wrong")
408	correct
409	drop table t1;