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 *, VALUE(a) FROM t1;
a b c VALUE(a)
1 2 10 NULL
3 4 127 NULL
5 0 30 NULL
8 9 60 NULL
2 1 11 NULL
explain extended SELECT *, VALUE(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`,value(`test`.`t1`.`a`) AS `VALUE(a)` from `test`.`t1`
explain extended select * from t1 where value(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 value(`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 UPDATE is ambiguous
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
SELECT *, VALUE(a) FROM t1;
a b c VALUE(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;
Warnings:
Warning 1062 Duplicate entry '2' for key 'a'
select * from t1;
a
1
2
insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ;
Warnings:
Warning 1062 Duplicate entry '2' for key 'a'
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 UPDATE is ambiguous
insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
ERROR 23000: Column 't1.a' in UPDATE 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 'SELECT'
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 'UPDATE'
DROP TABLE t1,t2;
SET SQL_MODE = 'TRADITIONAL';
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
INSERT INTO t1 VALUES (1,1);
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 = a;
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
SELECT * FROM t1;
a b
1 1
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
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
SELECT * FROM t1;
id c1 cnt
1 0 3
2 2 1
DROP TABLE t1;
DROP TABLE t2;
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;
#
# MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases
#
set timestamp=unix_timestamp('2000-10-20 0:0:0');
create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp
default current_timestamp on update current_timestamp);
insert t1 (pk, val) values(1, 'val1');
select * from t1;
pk val ts
1 val1 2000-10-20 00:00:00
set timestamp=unix_timestamp('2000-10-20 1:0:0');
insert t1 (pk, val) select 2, 'val3' union select 3, 'val4'
    on duplicate key update ts=now();
select * from t1;
pk val ts
1 val1 2000-10-20 00:00:00
2 val3 2000-10-20 01:00:00
3 val4 2000-10-20 01:00:00
set timestamp=unix_timestamp('2000-10-20 2:0:0');
insert t1 (pk, val) select 1, 'val1' union select 4, 'val2'
    on duplicate key update ts=now();
select * from t1;
pk val ts
1 val1 2000-10-20 02:00:00
2 val3 2000-10-20 01:00:00
3 val4 2000-10-20 01:00:00
4 val2 2000-10-20 02:00:00
set timestamp=unix_timestamp('2000-10-20 3:0:0');
insert t1 (pk, val) select 5, 'val1' union select 1, 'val2'
    on duplicate key update ts=now();
select * from t1;
pk val ts
1 val1 2000-10-20 03:00:00
2 val3 2000-10-20 01:00:00
3 val4 2000-10-20 01:00:00
4 val2 2000-10-20 02:00:00
5 val1 2000-10-20 03:00:00
drop table t1;
set timestamp=default;
# End of 10.4 tests
#
# MDEV-38096 Server crashes after INSERT.. ON duplicate KEY UPDATE i = DEFAULT;
#
create sequence s;
create table t1 (i varchar(10) default nextval(s), j text, key(i));
insert into t1 set i =  (value (j)) on duplicate key update i = default;
insert into t1 set i =  (value (j)) on duplicate key update i = default;
drop table t1;
create table t1 (i varchar(10) default nextval(s), j text, key(i)) engine=innodb;
insert into t1 set i =  (value (j)) on duplicate key update i = default;
insert into t1 set i =  (value (j)) on duplicate key update i = default;
drop table t1;
create table t1 (i varchar(10) default nextval(s), j varchar(10), key(i)) engine=myisam;
insert into t1 set i =  (value (j)) on duplicate key update i = default;
insert into t1 set i =  (value (j)) on duplicate key update i = default;
drop table t1;
create table t1 (j text default 5, i varchar(10) as (j), key(i));
insert into t1 set j =  (value (j)) on duplicate key update i = default;
insert into t1 set j =  (value (j)) on duplicate key update i = default;
drop table t1;
drop sequence s;
# End of 10.11 tests