~drizzle-trunk/drizzle/development : contents of tests/r/insert

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

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 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 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 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 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 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 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,
b   BIGINT,
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;
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;
CREATE TABLE `t1` (
`id` int 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
3	test2
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3
SELECT * FROM t1;
id	f1
1	test1
3	test2
INSERT IGNORE INTO t1 (f1) VALUES ("test3")
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5
SELECT * FROM t1;
id	f1
1	test1
3	test2
5	test3
DROP TABLE t1;
CREATE TABLE `t1` (
`id` int 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 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
2	test1
DROP TABLE t1;
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);
520.1.16 by Brian Aker More test updates (one ulong fix)	110	INSERT t1 SELECT 5,6,30 ON DUPLICATE KEY UPDATE c=c+100;
1 by brian clean slate	111	SELECT * FROM t1;
	112	a b c
	113	1 2 10
	114	3 4 20
	115	5 6 30
520.1.16 by Brian Aker More test updates (one ulong fix)	116	INSERT t1 SELECT 5,7,40 ON DUPLICATE KEY UPDATE c=c+100;
1 by brian clean slate	117	SELECT * FROM t1;
	118	a b c
	119	1 2 10
	120	3 4 20
	121	5 6 130
520.1.16 by Brian Aker More test updates (one ulong fix)	122	INSERT t1 SELECT 8,4,50 ON DUPLICATE KEY UPDATE c=c+1000;
1 by brian clean slate	123	SELECT * FROM t1;
	124	a b c
	125	1 2 10
	126	3 4 1020
	127	5 6 130
520.1.16 by Brian Aker More test updates (one ulong fix)	128	INSERT t1 SELECT 1,4,60 ON DUPLICATE KEY UPDATE c=c+10000;
1 by brian clean slate	129	SELECT * FROM t1;
	130	a b c
	131	1 2 10010
	132	3 4 1020
	133	5 6 130
520.1.16 by Brian Aker More test updates (one ulong fix)	134	INSERT t1 SELECT 1,9,70 ON DUPLICATE KEY UPDATE c=c+100000, b=4;
1 by brian clean slate	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 (
520.1.16 by Brian Aker More test updates (one ulong fix)	195	a BIGINT NOT NULL DEFAULT 0,
1 by brian clean slate	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	(
520.1.16 by Brian Aker More test updates (one ulong fix)	202	a BIGINT,
520.1.16 by Brian Aker More test updates (one ulong fix)	203	b BIGINT,
1 by brian clean slate	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	CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
	240	INSERT INTO t1 (a) VALUES (1);
	241	ERROR HY000: Field 'b' doesn't have a default value
	242	INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
	243	ERROR HY000: Field 'b' doesn't have a default value
	244	INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
	245	ERROR HY000: Field 'b' doesn't have a default value
	246	SELECT * FROM t1;
	247	a b
	248	DROP TABLE t1;
	249	CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
	250	f2 VARCHAR(5) NOT NULL UNIQUE);
	251	INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
	252	SELECT LAST_INSERT_ID();
	253	LAST_INSERT_ID()
	254	1
	255	INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
	256	SELECT LAST_INSERT_ID();
	257	LAST_INSERT_ID()
	258	1
	259	DROP TABLE t1;
	260	CREATE TABLE `t1` (
520.1.16 by Brian Aker More test updates (one ulong fix)	261	`id` int PRIMARY KEY auto_increment,
1 by brian clean slate	262	`f1` varchar(10) NOT NULL UNIQUE
	263	);
	264	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
	265	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	266	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
	267	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	268	SELECT LAST_INSERT_ID();
	269	LAST_INSERT_ID()
	270	1
	271	SELECT * FROM t1;
	272	id f1
	273	1 test1
	274	INSERT IGNORE INTO t1 (f1) VALUES ("test2")
	275	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	276	SELECT * FROM t1;
	277	id f1
	278	1 test1
520.1.16 by Brian Aker More test updates (one ulong fix)	279	3 test2
1 by brian clean slate	280	INSERT IGNORE INTO t1 (f1) VALUES ("test2")
	281	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	282	SELECT LAST_INSERT_ID();
	283	LAST_INSERT_ID()
520.1.16 by Brian Aker More test updates (one ulong fix)	284	3
1 by brian clean slate	285	SELECT * FROM t1;
	286	id f1
	287	1 test1
520.1.16 by Brian Aker More test updates (one ulong fix)	288	3 test2
1 by brian clean slate	289	INSERT IGNORE INTO t1 (f1) VALUES ("test3")
	290	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	291	SELECT LAST_INSERT_ID();
	292	LAST_INSERT_ID()
520.1.16 by Brian Aker More test updates (one ulong fix)	293	5
1 by brian clean slate	294	SELECT * FROM t1;
	295	id f1
	296	1 test1
520.1.16 by Brian Aker More test updates (one ulong fix)	297	3 test2
520.1.16 by Brian Aker More test updates (one ulong fix)	298	5 test3
1 by brian clean slate	299	DROP TABLE t1;
1 by brian clean slate	300	CREATE TABLE `t1` (
520.1.16 by Brian Aker More test updates (one ulong fix)	301	`id` int PRIMARY KEY auto_increment,
1 by brian clean slate	302	`f1` varchar(10) NOT NULL UNIQUE
	303	);
	304	INSERT IGNORE INTO t1 (f1) VALUES ("test1")
	305	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	306	SELECT LAST_INSERT_ID();
	307	LAST_INSERT_ID()
	308	1
	309	SELECT * FROM t1;
	310	id f1
	311	1 test1
	312	INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
	313	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
	314	SELECT LAST_INSERT_ID();
	315	LAST_INSERT_ID()
	316	2
	317	SELECT * FROM t1;
	318	id f1
	319	1 test1
	320	2 test4
	321	DROP TABLE t1;
	322	CREATE TABLE `t1` (
520.1.16 by Brian Aker More test updates (one ulong fix)	323	`id` int PRIMARY KEY auto_increment,
1 by brian clean slate	324	`f1` varchar(10) NOT NULL UNIQUE,
	325	tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
	326	);
	327	INSERT INTO t1 (f1) VALUES ("test1");
	328	SELECT id, f1 FROM t1;
	329	id f1
	330	1 test1
	331	REPLACE INTO t1 VALUES (0,"test1",null);
	332	SELECT id, f1 FROM t1;
	333	id f1
520.1.16 by Brian Aker More test updates (one ulong fix)	334	2 test1
1 by brian clean slate	335	DROP TABLE t1;
	336	CREATE TABLE t1 (
	337	id INT AUTO_INCREMENT PRIMARY KEY,
	338	c1 CHAR(1) UNIQUE KEY,
	339	cnt INT DEFAULT 1
	340	);
	341	INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C');
	342	SELECT * FROM t1;
	343	id c1 cnt
	344	1 A 1
	345	2 B 1
	346	3 C 1
	347	INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
	348	ON DUPLICATE KEY UPDATE cnt=cnt+1;
	349	SELECT * FROM t1;
	350	id c1 cnt
	351	1 A 2
	352	2 B 1
	353	3 C 1
	354	4 X 1
	355	5 Y 1
	356	6 Z 1
	357	DROP TABLE t1;
	358	CREATE TABLE t1 (
	359	id INT AUTO_INCREMENT PRIMARY KEY,
	360	c1 INT NOT NULL,
	361	cnt INT DEFAULT 1
	362	);
	363	INSERT INTO t1 (id,c1) VALUES (1,10);
	364	SELECT * FROM t1;
	365	id c1 cnt
	366	1 10 1
	367	CREATE TABLE t2 (id INT, c1 INT);
	368	INSERT INTO t2 VALUES (1,NULL), (2,2);
	369	INSERT INTO t1 (id,c1) SELECT 1,NULL
	370	ON DUPLICATE KEY UPDATE c1=NULL;
	371	ERROR 23000: Column 'c1' cannot be null
	372	SELECT * FROM t1;
	373	id c1 cnt
	374	1 10 1
	375	INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
	376	ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
	377	Warnings:
	378	Warning 1048 Column 'c1' cannot be null
	379	Error 1048 Column 'c1' cannot be null
	380	SELECT * FROM t1;
	381	id c1 cnt
	382	1 0 2
	383	INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
	384	ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
	385	Warnings:
	386	Warning 1048 Column 'c1' cannot be null
	387	Error 1048 Column 'c1' cannot be null
	388	SELECT * FROM t1;
	389	id c1 cnt
	390	1 0 3
	391	2 2 1
	392	DROP TABLE t1;
	393	create table t1(f1 int primary key,
	394	f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP);
	395	insert into t1(f1) values(1);
	396	select @stamp1:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	397	@stamp1:=f2
1 by brian clean slate	398	#
	399	insert into t1(f1) values(1) on duplicate key update f1=1;
	400	select @stamp2:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	401	@stamp2:=f2
1 by brian clean slate	402	#
	403	select if( @stamp1 = @stamp2, "correct", "wrong");
	404	if( @stamp1 = @stamp2, "correct", "wrong")
	405	correct
	406	drop table t1;