--disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings 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; INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000; SELECT * FROM t1; INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000; SELECT * FROM t1; -- error ER_DUP_ENTRY INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4; SELECT * FROM t1; 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; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; explain extended SELECT *, VALUES(a) FROM t1; explain extended select * from t1 where values(a); DROP TABLE t1; # # test for Bug #2709 "Affected Rows for ON DUPL.KEY undocumented, # perhaps illogical" # 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; --enable_info insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18) on duplicate key update b=b+10; --disable_info select * from t1; enable_info; replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29); disable_info; select * from t1; drop table t1; # WorkLog #2274 - enable INSERT .. SELECT .. UPDATE syntax # Same tests as beginning of this test except that insert source # is a result from a select statement # 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; INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000; SELECT * FROM t1; INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000; SELECT * FROM t1; -- error ER_DUP_ENTRY INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4; SELECT * FROM t1; TRUNCATE TABLE t1; INSERT t1 VALUES (1,2,10), (3,4,20); CREATE TABLE t2 (a INT, b INT, c INT, d INT); # column names deliberately clash with columns in t1 (Bug#8147) 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; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; --error 1052 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); 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; DROP TABLE t1; DROP TABLE t2; # # Bug#9725 - "disapearing query/hang" and "unknown error" with "on duplicate key update" # INSERT INGORE...UPDATE gives bad error or breaks protocol. # 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; insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ; select * from t1; insert into t1 select 1 on duplicate key update a=2; select * from t1; --error 1052 insert into t1 select a from t1 on duplicate key update a=a+1 ; --error 1052 insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ; drop table t1; # # Bug#10109 - INSERT .. SELECT ... ON DUPLICATE KEY UPDATE fails # Bogus "Duplicate columns" error message # 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; # # Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES # # End of 4.1 tests 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; INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = IF(VALUES(b) > t1.b, VALUES(b), t1.b); SELECT * FROM t1; INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = IF(VALUES(b) > t1.b, VALUES(b), t1.b); SELECT * FROM t1; DROP TABLE t1; # # Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving. # CREATE TABLE t1 (i INT PRIMARY KEY, j INT); --error ER_BAD_FIELD_ERROR INSERT INTO t1 SELECT 1, j; 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); --error ER_BAD_FIELD_ERROR INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; DROP TABLE t1,t2; # # Bug #26261: Missing default value isn't noticed in # insert ... on duplicate key update # SET SQL_MODE = 'TRADITIONAL'; CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL); --error 1364 INSERT INTO t1 (a) VALUES (1); --error 1364 INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b; --error 1364 INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b; SELECT * FROM t1; DROP TABLE t1; # # Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were # touched but not actually changed. # 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(); INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); SELECT LAST_INSERT_ID(); DROP TABLE t1; # # Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the # NO_AUTO_VALUE_ON_ZERO mode. # 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(); SELECT * FROM t1; INSERT IGNORE INTO t1 (f1) VALUES ("test2") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT * FROM t1; INSERT IGNORE INTO t1 (f1) VALUES ("test2") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); SELECT * FROM t1; INSERT IGNORE INTO t1 (f1) VALUES ("test3") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); SELECT * FROM t1; 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(); SELECT * FROM t1; INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); SELECT * FROM t1; 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; REPLACE INTO t1 VALUES (0,"test1",null); SELECT id, f1 FROM t1; DROP TABLE t1; SET SQL_MODE=''; # # Bug#27954: multi-row INSERT ... ON DUPLICATE with duplicated # row at the first place into table with AUTO_INCREMENT and # additional UNIQUE key. # 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; INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; DROP TABLE t1; # # Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE # with erroneous UPDATE: NOT NULL field with NULL value. # 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; CREATE TABLE t2 (id INT, c1 INT); INSERT INTO t2 VALUES (1,NULL), (2,2); --error 1048 INSERT INTO t1 (id,c1) SELECT 1,NULL ON DUPLICATE KEY UPDATE c1=NULL; SELECT * FROM t1; INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; SELECT * FROM t1; INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2 ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; SELECT * FROM t1; DROP TABLE t1; # # Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it # shouldn't. # create table t1(f1 int primary key, f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP); insert into t1(f1) values(1); --replace_column 1 # select @stamp1:=f2 from t1; --sleep 2 insert into t1(f1) values(1) on duplicate key update f1=1; --replace_column 1 # select @stamp2:=f2 from t1; select if( @stamp1 = @stamp2, "correct", "wrong"); drop table t1; # # Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates # --echo connection: default set low_priority_updates=1; --disable_warnings drop table if exists t1; --enable_warnings create table t1 (a int, b int, unique key t1$a (a)); lock table t1 read; connect (update,localhost,root,,); connection update; --echo connection: update set low_priority_updates=1; show variables like 'low_priority_updates'; let $ID= `select connection_id()`; --send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2; connection default; # we must wait till the insert opens and locks the table let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and id = $ID; --source include/wait_condition.inc connect (select,localhost,root,,); --echo connection: select select * from t1; connection default; --echo connection: default select * from t1; connection default; disconnect update; disconnect select; drop table t1; set low_priority_updates=default;