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 't1.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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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
Warnings:
Warning	1681	'VALUES function' is deprecated and will be removed in a future release.
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain SELECT *, VALUES(a) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Warning	1#81	'VALUES function' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,NULL AS `VALUES(a)` from `test`.`t1`
explain select * from t1 where values(a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Warning	1681	'VALUES function' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where false
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 't1.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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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
Warnings:
Warning	1681	'VALUES function' is deprecated and will be removed in a future release.
DROP TABLE t1;
DROP TABLE t2;
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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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,
tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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
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
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
#
Warnings:
#	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
insert into t1(f1) values(1) on duplicate key update f1=1;
select @stamp2:=f2 from t1;
@stamp2:=f2
#
Warnings:
#	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select if( @stamp1 = @stamp2, "correct", "wrong");
if( @stamp1 = @stamp2, "correct", "wrong")
correct
drop table t1;
# Bug 21774967: MYSQL ACCEPTS NON-ASCII IN ASCII COLUMNS
CREATE TABLE t1(
a CHAR(20) CHARACTER SET ascii,
b VARCHAR(20) CHARACTER SET ascii,
c TEXT(20) CHARACTER SET ascii
);
CREATE TABLE t2(
a CHAR(20) CHARACTER SET ascii COLLATE ascii_general_ci,
b VARCHAR(20) CHARACTER SET ascii COLLATE ascii_general_ci,
c TEXT(20) CHARACTER SET ascii COLLATE ascii_general_ci
);
CREATE TABLE t3(
a CHAR(20) CHARACTER SET ascii COLLATE ascii_bin,
b VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin,
c TEXT(20) CHARACTER SET ascii COLLATE ascii_bin
);
SET SQL_MODE="STRICT_TRANS_TABLES";
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO t1 values(x'6162', x'6162', x'6162');
UPDATE t1 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 1
INSERT INTO t2 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO t2 values(x'6162', x'6162', x'6162');
UPDATE t2 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 1
INSERT INTO t3 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO t3 values(x'6162', x'6162', x'6162');
UPDATE t3 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 1
SET SQL_MODE="";
INSERT INTO t1 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO t1 values(x'6162', x'6162', x'6162');
UPDATE t1 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 3
INSERT INTO t2 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO t2 values(x'6162', x'6162', x'6162');
UPDATE t2 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 3
INSERT INTO t3 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO t3 values(x'6162', x'6162', x'6162');
UPDATE t3 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 3
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE VIEW v3 AS SELECT * FROM t3;
SET SQL_MODE="STRICT_TRANS_TABLES";
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO v1 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO v1 values(x'6162', x'6162', x'6162');
UPDATE v1 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 4
INSERT INTO v2 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO v2 values(x'6162', x'6162', x'6162');
UPDATE v2 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 4
INSERT INTO v3 values(x'8142', x'8142', x'8142');
ERROR HY000: Incorrect string value: '\x81B' for column 'a' at row 1
INSERT INTO v3 values(x'6162', x'6162', x'6162');
UPDATE v3 SET a=x'8243' where a=x'6162';
ERROR HY000: Incorrect string value: '\x82C' for column 'a' at row 4
SET SQL_MODE="";
INSERT INTO v1 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO v1 values(x'6162', x'6162', x'6162');
UPDATE v1 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 4
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 6
INSERT INTO v2 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO v2 values(x'6162', x'6162', x'6162');
UPDATE v2 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 4
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 6
INSERT INTO v3 values(x'8142', x'8142', x'8142');
Warnings:
Warning	1366	Incorrect string value: '\x81B' for column 'a' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'b' at row 1
Warning	1366	Incorrect string value: '\x81B' for column 'c' at row 1
INSERT INTO v3 values(x'6162', x'6162', x'6162');
UPDATE v3 SET a=x'8243' where a=x'6162';
Warnings:
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 4
Warning	1366	Incorrect string value: '\x82C' for column 'a' at row 6
DROP VIEW v1;
DROP VIEW v2;
DROP VIEW v3;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
CREATE TABLE t_latin1(
a CHAR(20) CHARACTER SET latin1,
b VARCHAR(20) CHARACTER SET latin1,
c TEXT(20) CHARACTER SET latin1
);
CREATE TABLE t_gb2312(
a CHAR(20) CHARACTER SET gb2312,
b VARCHAR(20) CHARACTER SET gb2312,
c TEXT(20) CHARACTER SET gb2312
);
CREATE TABLE t_utf8(
a CHAR(20) CHARACTER SET utf8,
b VARCHAR(20) CHARACTER SET utf8,
c TEXT(20) CHARACTER SET utf8
);
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
SET SQL_MODE="STRICT_TRANS_TABLES";
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t_latin1 values(x'f242', x'f242', x'f242');
UPDATE t_latin1 SET a=x'f343' where a=x'f242';
INSERT INTO t_gb2312 values(x'e5ac', x'e5ac', x'e5ac');
UPDATE t_gb2312 SET a=x'e6af' where a=x'e5ac';
INSERT INTO t_utf8 values(x'e4b8ad', x'e4b8ad', x'e4b8ad');
INSERT INTO t_utf8 values(x'f4b8ad', x'f4b8ad', x'f4b8ad');
ERROR HY000: Incorrect string value: '\xF4\xB8\xAD' for column 'a' at row 1
UPDATE t_utf8 SET a=x'e69687' where a=x'e4b8ad';
UPDATE t_utf8 SET a=x'f69687' where a=x'e69687';
ERROR HY000: Incorrect string value: '\xF6\x96\x87' for column 'a' at row 1
SET SQL_MODE="";
INSERT INTO t_latin1 values(x'f242', x'f242', x'f242');
UPDATE t_latin1 SET a=x'f343' where a=x'f242';
INSERT INTO t_gb2312 values(x'e5ac', x'e5ac', x'e5ac');
UPDATE t_gb2312 SET a=x'e6af' where a=x'e5ac';
INSERT INTO t_utf8 values(x'e4b8ad', x'e4b8ad', x'e4b8ad');
INSERT INTO t_utf8 values(x'f4b8ad', x'f4b8ad', x'f4b8ad');
Warnings:
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'a' at row 1
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'b' at row 1
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'c' at row 1
UPDATE t_utf8 SET a=x'e69687' where a=x'e4b8ad';
UPDATE t_utf8 SET a=x'f69687' where a=x'e69687';
Warnings:
Warning	1366	Incorrect string value: '\xF6\x96\x87' for column 'a' at row 1
Warning	1366	Incorrect string value: '\xF6\x96\x87' for column 'a' at row 2
CREATE VIEW v_latin1 AS SELECT * FROM t_latin1;
CREATE VIEW v_gb2312 AS SELECT * FROM t_gb2312;
CREATE VIEW v_utf8 AS SELECT * FROM t_utf8;
SET SQL_MODE="STRICT_TRANS_TABLES";
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO v_latin1 values(x'f242', x'f242', x'f242');
UPDATE v_latin1 SET a=x'f343' where a=x'f242';
INSERT INTO v_gb2312 values(x'e5ac', x'e5ac', x'e5ac');
UPDATE v_gb2312 SET a=x'e6af' where a=x'e5ac';
INSERT INTO v_utf8 values(x'e4b8ad', x'e4b8ad', x'e4b8ad');
INSERT INTO v_utf8 values(x'f4b8ad', x'f4b8ad', x'f4b8ad');
ERROR HY000: Incorrect string value: '\xF4\xB8\xAD' for column 'a' at row 1
UPDATE v_utf8 SET a=x'e69687' where a=x'e4b8ad';
UPDATE v_utf8 SET a=x'f69687' where a=x'e69687';
ERROR HY000: Incorrect string value: '\xF6\x96\x87' for column 'a' at row 4
SET SQL_MODE="";
INSERT INTO v_latin1 values(x'f242', x'f242', x'f242');
UPDATE v_latin1 SET a=x'f343' where a=x'f242';
INSERT INTO v_gb2312 values(x'e5ac', x'e5ac', x'e5ac');
UPDATE v_gb2312 SET a=x'e6af' where a=x'e5ac';
INSERT INTO v_utf8 values(x'e4b8ad', x'e4b8ad', x'e4b8ad');
INSERT INTO v_utf8 values(x'f4b8ad', x'f4b8ad', x'f4b8ad');
Warnings:
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'a' at row 1
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'b' at row 1
Warning	1366	Incorrect string value: '\xF4\xB8\xAD' for column 'c' at row 1
UPDATE v_utf8 SET a=x'e69687' where a=x'e4b8ad';
UPDATE v_utf8 SET a=x'f69687' where a=x'e69687';
Warnings:
Warning	1366	Incorrect string value: '\xF6\x96\x87' for column 'a' at row 4
Warning	1366	Incorrect string value: '\xF6\x96\x87' for column 'a' at row 5
DROP VIEW v_latin1;
DROP VIEW v_gb2312;
DROP VIEW v_utf8;
DROP TABLE t_latin1;
DROP TABLE t_gb2312;
DROP TABLE t_utf8;
SET SQL_MODE=DEFAULT;
# WL#5094: Refactor DML statements
# Semantic changes in INSERT ... ON DUPLICATE KEY
CREATE TABLE t0 (k INTEGER PRIMARY KEY);
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (3);
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
# Allowed: Reference column from a single table
INSERT INTO t0
SELECT a FROM t1
ON DUPLICATE KEY UPDATE k= a + t1.a + 10;
SELECT * FROM t0;
k
3
12
14
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
# Allowed: Reference column from a join over multiple tables
INSERT INTO t0
SELECT t1.a FROM t1 JOIN t2 ON t1.a=t2.a
ON DUPLICATE KEY UPDATE k= t1.a + t2.a + 10;
SELECT * FROM t0;
k
2
3
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
INSERT INTO t0
SELECT a FROM t1 JOIN t2 USING (a)
ON DUPLICATE KEY UPDATE k= t1.a + t2.a + 10;
SELECT * FROM t0;
k
2
3
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
INSERT INTO t0
SELECT a FROM t1 LEFT JOIN t2 USING (a)
ON DUPLICATE KEY UPDATE k= a + 10;
SELECT * FROM t0;
k
3
11
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
INSERT INTO t0
SELECT DISTINCT a FROM t1
ON DUPLICATE KEY UPDATE k= a + 10;
SELECT * FROM t0;
k
3
11
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
# Allowed: Wrap a distinct query in a derived table
INSERT INTO t0
SELECT a FROM (SELECT DISTINCT a FROM t1) AS dt
ON DUPLICATE KEY UPDATE k= a + 10;
SELECT * FROM t0;
k
3
11
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
# Not allowed: Reference column from an explicitly grouped query
INSERT INTO t0
SELECT a FROM t1 GROUP BY a
ON DUPLICATE KEY UPDATE k= a + 10;
ERROR 42S22: Unknown column 'a' in 'field list'
# Not allowed: Reference column from an implicitly grouped query
INSERT INTO t0
SELECT SUM(a) FROM t1
ON DUPLICATE KEY UPDATE k= a + 10;
ERROR 42S22: Unknown column 'a' in 'field list'
# Allowed: Wrap a grouped query in a derived table
INSERT INTO t0
SELECT a FROM (SELECT a FROM t1 GROUP BY a) AS dt
ON DUPLICATE KEY UPDATE k= a + 10;
SELECT * FROM t0;
k
3
11
12
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
# Not allowed: Reference column from a UNION query
INSERT INTO t0
SELECT a FROM t1 UNION SELECT a FROM t2
ON DUPLICATE KEY UPDATE k= a + 10;
ERROR 42S22: Unknown column 'a' in 'field list'
# Allowed:: Wrap a UNION query in a derived table
INSERT INTO t0
SELECT a
FROM (SELECT a, COUNT(*) AS c FROM t1 GROUP BY a
UNION
SELECT a, COUNT(*) AS c FROM t2 GROUP BY a) AS dt
ON DUPLICATE KEY UPDATE k= dt.a + dt.c + 10;
SELECT * FROM t0;
k
12
13
14
DELETE FROM t0;
INSERT INTO t0 SELECT a FROM t1 UNION SELECT a FROM t2;
DROP TABLE t0, t1, t2;
# Bug#25526439: Assertion failed: is_fixed_or_outer_ref(this)
CREATE TABLE t1 (
a INTEGER NOT NULL
);
INSERT INTO t1 VALUES(0);
CREATE TABLE t2 (
d INTEGER
);
# Query from bug report
INSERT INTO t1(a) VALUES (1)
ON DUPLICATE KEY UPDATE a= (SELECT d FROM t2 GROUP BY 1);
SELECT * FROM t1;
a
0
1
# Similar query with a simple query block
INSERT INTO t1(a) SELECT 1
ON DUPLICATE KEY UPDATE a= (SELECT d FROM t2 GROUP BY 1);
SELECT * FROM t1;
a
0
1
1
# Similar query with a UNION
INSERT INTO t1(a) SELECT 1 UNION SELECT 2
ON DUPLICATE KEY UPDATE a= (SELECT d FROM t2 GROUP BY 1);
SELECT * FROM t1;
a
0
1
1
1
2
DROP TABLE t1, t2;
# Bug#25071305: Assertion failed: first_execution ||
#               !tl->is_view_or_derived() || tl->is_merged()
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(b INTEGER);
INSERT INTO t2 VALUES (1),(1);
INSERT INTO t1(a) VALUES (1)
ON DUPLICATE KEY UPDATE a= (SELECT b FROM (SELECT b FROM t2) AS w);
DROP TABLE t1, t2;
# Bug#24716127: Incorrect behavior by insert statement with
#               "on duplicate key update"
CREATE TABLE t1(a INTEGER, b INTEGER, PRIMARY KEY(a,b));
CREATE TABLE t2(c2 INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE t3(c3 INTEGER NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
INSERT INTO t1 VALUES (1,1)
ON DUPLICATE KEY UPDATE a= (SELECT c2
FROM t2 JOIN t3 ON c3 = c2
WHERE c2 = 1);
SELECT * FROM t1;
a	b
1	1
INSERT INTO t1
SELECT 1, 1 FROM t2
ON DUPLICATE KEY UPDATE a= t2.c2 + 100, b= t2.c2 + 100;
SELECT * FROM t1;
a	b
1	1
101	101
DROP TABLE t1, t2, t3;
#
# Bug#28995498 INSERT IS FINE BUT "ON DUPLICATE UPDATE" UPDATING WRONG DATA
#
CREATE TABLE t1 (pk VARCHAR(10) PRIMARY KEY, col VARCHAR(10));
INSERT INTO t1 VALUES (1 , "Carmen" ),(2 , "Martin" );
INSERT INTO t1 SELECT * FROM t1 AS source
ON DUPLICATE KEY UPDATE t1.col = source.col;
SELECT * FROM t1 ;
pk	col
1	Carmen
2	Martin
DROP TABLE t1;
# WL#6312: Referencing new row in INSERT .. VALUES .. ON DUPLICATE KEY UPDATE.
CREATE TABLE t0(a INT PRIMARY KEY, b INT);
CREATE TABLE t1(x INT PRIMARY KEY, y INT);
# Allowed: Referencing VALUES from the update list.
INSERT INTO t0 VALUES (1, 3), (2, 3) AS n(a, b)
ON DUPLICATE KEY UPDATE b= t0.b + n.b;
SELECT * FROM t0;
a	b
1	3
2	3
INSERT INTO t0 VALUES (1, 3), (2, 3) AS n(a, b)
ON DUPLICATE KEY UPDATE b= t0.b + n.b;
SELECT * FROM t0;
a	b
1	6
2	6
# Allowed: Not naming columns for VALUES table.
INSERT INTO t0 VALUES (1, 5), (2, 7) AS n
ON DUPLICATE KEY UPDATE b= t0.b + n.b;
SELECT * FROM t0;
a	b
1	11
2	13
# Not allowed: Naming the VALUES table the same as the table inserted into.
INSERT INTO t0 VALUES (1, 5), (2, 7) AS t0
ON DUPLICATE KEY UPDATE b= t0.a;
ERROR 42000: Not unique table/alias: 't0'
# Not allowed: Naming multiple VALUES columns the same.
INSERT INTO t0 VALUES (1, 5), (2, 7) AS n(a, a)
ON DUPLICATE KEY UPDATE b= t0.a;
ERROR 42S21: Duplicate column name 'a'
# Not allowed: Unequal number of columns in VALUES table and inserted rows.
INSERT INTO t0 VALUES (1, 5), (2, 7) AS n(a)
ON DUPLICATE KEY UPDATE b= t0.a;
ERROR HY000: In definition of view, derived table or common table expression, SELECT list and column names list have different column counts
# Allowed: Referencing VALUES table from within a subquery.
INSERT INTO t1 VALUES (1, 50), (2, 100);
INSERT INTO t0 VALUES (1, 10), (2, 20) AS n
ON DUPLICATE KEY UPDATE b= (SELECT y FROM t1 WHERE x = n.a);
SELECT * FROM t0;
a	b
1	50
2	100
# Allowed: Overriding the VALUES table name from within a subquery.
INSERT INTO t0 VALUES (1, 10) AS n(a, b)
ON DUPLICATE KEY UPDATE b= 20 + (SELECT n.y FROM t1 AS n WHERE n.x = t0.a);
SELECT * FROM t0;
a	b
1	70
2	100
# Allowed: VALUES table name overriding table names.
CREATE TABLE n(a INT, b INT);
INSERT INTO t0 VALUES (1, 10) AS n(a, b)
ON DUPLICATE KEY UPDATE b= n.b;
SELECT * FROM t0;
a	b
1	10
2	100
# Allowed: FROM clause in subquery overriding VALUES table name.
INSERT INTO n VALUES (1, 50);
INSERT INTO t0 VALUES (1, 20) AS n(a, b)
ON DUPLICATE KEY UPDATE b= (SELECT n.b FROM n);
SELECT * FROM t0;
a	b
1	50
2	100
# Allowed: Discrepancy between specified insert columns and column names of VALUES table.
CREATE TABLE t2(a INT, b INT PRIMARY KEY, c INT);
INSERT INTO t2 VALUES (1, 10, 100);
INSERT INTO t2(b, c) VALUES (10, 20) AS n(a, b)
ON DUPLICATE KEY UPDATE c= n.b;
SELECT * FROM t2;
a	b	c
1	10	20
# Allowed: Using the «INSERT .. SET» syntax.
DROP TABLE t0;
CREATE TABLE t0(a INT PRIMARY KEY, b INT);
INSERT INTO t0 VALUES (1, 10);
INSERT INTO t0 SET a=1, b=20 AS n
ON DUPLICATE KEY UPDATE b= n.b;
SELECT * FROM t0;
a	b
1	20
# Allowed: Referring to the VALUES table from inside functions.
DROP TABLE t1;
CREATE TABLE t1(a BIGINT UNSIGNED PRIMARY KEY, b BIGINT UNSIGNED);
INSERT INTO t1 VALUES (45, 1) AS n
ON DUPLICATE KEY UPDATE b= IF(n.b > t1.b, n.b, t1.b);
SELECT * FROM t1;
a	b
45	1
INSERT INTO t1 VALUES (45, 2) AS n
ON DUPLICATE KEY UPDATE b= IF(n.b > t1.b, n.b, t1.b);
SELECT * FROM t1;
a	b
45	2
INSERT INTO t1 VALUES (45, 1) AS n
ON DUPLICATE KEY UPDATE b= IF(n.b > t1.b, n.b, t1.b);
SELECT * FROM t1;
a	b
45	2
# Allowed: Using ODKU when inserting into a view.
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 3, b INT);
CREATE VIEW v AS SELECT b FROM t1;
INSERT INTO t1 VALUES(3, 2);
INSERT INTO v VALUES(3) AS n
ON DUPLICATE KEY UPDATE b= n.b;
SELECT * FROM t1;
a	b
3	3
SELECT * FROM v;
b
3
# Allowed: Creating a VALUES alias with no ODKU statement.
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1, 10) as n;
SELECT * FROM t1;
a	b
1	10
# Not allowed: Referencing the VALUES table inside the VALUES clause.
INSERT INTO t1 VALUES(n.a, 10) as n
ON DUPLICATE KEY UPDATE b= n.b;
ERROR 42S22: Unknown column 'n.a' in 'field list'
# If a table reference within an update expression does not find the
# column in the VALUES table, it should look further in outer contexts.
DROP TABLE n;
CREATE TABLE n(x INT, y INT);
DROP TABLE t0;
CREATE TABLE t0(a INT PRIMARY KEY, b INT);
DELETE FROM t0;
INSERT INTO t0 VALUES(1, 10);
INSERT INTO n VALUES(1, 11);
INSERT INTO t0 VALUES(1, 19) as n(a, b)
ON DUPLICATE KEY UPDATE b= (SELECT n.y FROM n);
SELECT * FROM t0;
a	b
1	11
# Allowed: Referring to the old row of the insert table from within an
# ODKU update expression.
DELETE FROM t0;
INSERT INTO t0 VALUES(1, 10);
INSERT INTO t0 VALUES(2, 20);
INSERT INTO t0 VALUES(2, 29) as n
ON DUPLICATE KEY UPDATE b= t0.b+1;
SELECT * FROM t0;
a	b
1	10
2	21
DROP TABLE t0;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE n;
DROP VIEW v;
# WL#6312: Referencing new row in INSERT .. VALUES .. ON DUPLICATE KEY UPDATE.
# Compare old and new syntax (VALUES() vs. AS new) for regressions.
# binlog_unsafe.test. More than one unique key.
# Old syntax.
CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY);
INSERT INTO insert_2_keys values (1, 1);
INSERT INTO insert_2_keys VALUES (1, 2)
ON DUPLICATE KEY UPDATE a= VALUES(a) + 10, b= VALUES(b) + 10;
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM insert_2_keys;
a	b
11	12
DROP TABLE insert_2_keys;
# New syntax.
CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY);
INSERT INTO insert_2_keys values (1, 1);
INSERT INTO insert_2_keys VALUES (1, 2) AS n
ON DUPLICATE KEY UPDATE a= n.a + 10, b= n.b + 10;
SELECT * FROM insert_2_keys;
a	b
11	12
DROP TABLE insert_2_keys;
# json_functions.inc. JSON should work with INSERT .. ON DUPLICATE KEY UPDATE.
# Old syntax.
CREATE TABLE t(id INT PRIMARY KEY, j JSON);
INSERT INTO t VALUES (1, '[1]')
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("a", VALUES(j));
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t;
id	j
1	[1]
INSERT INTO t VALUES (1, '[1,2]')
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("ab", VALUES(j));
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t;
id	j
1	{"ab": [1, 2]}
INSERT INTO t VALUES (1, '[1,2,3]')
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("abc", VALUES(j));
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t;
id	j
1	{"abc": [1, 2, 3]}
DROP TABLE t;
# New syntax.
CREATE TABLE t(id INT PRIMARY KEY, j JSON);
INSERT INTO t VALUES (1, '[1]') AS n
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("a", n.j);
SELECT * FROM t;
id	j
1	[1]
INSERT INTO t VALUES (1, '[1,2]') AS n
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("ab", n.j);
SELECT * FROM t;
id	j
1	{"ab": [1, 2]}
INSERT INTO t VALUES (1, '[1,2,3]') AS n
ON DUPLICATE KEY UPDATE j = JSON_OBJECT("abc", n.j);
SELECT * FROM t;
id	j
1	{"abc": [1, 2, 3]}
DROP TABLE t;
# errors.test. Subquery with VALUES table reference.
# Old syntax.
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t2(a, b) VALUES (1, 0);
INSERT INTO t2(a, b) VALUES (1, 0)
ON DUPLICATE KEY UPDATE b= (SELECT VALUES(a) + 2 FROM t1);
Warnings:
Warning	1287	The VALUES function is deprecated and will be removed in a future release. It always returns NULL in this context. If you meant to access a value from the VALUES clause of the INSERT statement, consider using an alias (INSERT INTO ... VALUES (...) AS alias) and reference alias.col instead of VALUES(col) in the ON DUPLICATE KEY UPDATE clause.
SELECT * FROM t2;
a	b
1	NULL
DROP TABLE t1;
DROP TABLE t2;
# New syntax.
CREATE TABLE t1 (a INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t2(a, b) VALUES (1, 0);
INSERT INTO t2(a, b) VALUES (1, 0) AS n
ON DUPLICATE KEY UPDATE b= (SELECT n.a + 2 FROM t1);
SELECT * FROM t2;
a	b
1	3
DROP TABLE t1;
DROP TABLE t2;
# func_test.test. New syntax should work inside functions.
# Old syntax.
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (1, 3)
ON DUPLICATE KEY UPDATE b= GREATEST(b, VALUES(b));
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t1;
a	b
1	3
DROP TABLE t1;
# New syntax.
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (1, 3) AS n
ON DUPLICATE KEY UPDATE b= GREATEST(t1.b, n.b);
SELECT * FROM t1;
a	b
1	3
DROP TABLE t1;
# type_blob-bug13901905_myisam.test. INSERT .. SET syntax should work with blobs.
# Old syntax.
CREATE TABLE t1 (a INT, b BLOB, UNIQUE KEY(a));
INSERT INTO t1 SET b='11', a=0
ON DUPLICATE KEY UPDATE b= VALUES(a), a= values(b);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
INSERT INTO t1 SET b='11', a=0
ON DUPLICATE KEY UPDATE b= VALUES(a), a= values(b);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t1;
a	b
11	0
DROP TABLE t1;
# New syntax
CREATE TABLE t1 (a INT, b BLOB, UNIQUE KEY(a));
INSERT INTO t1 SET b='11', a=0 AS n
ON DUPLICATE KEY UPDATE b= n.a, a= n.b;
INSERT INTO t1 SET b='11', a=0 AS n
ON DUPLICATE KEY UPDATE b= n.a, a= n.b;
SELECT * FROM t1;
a	b
11	0
DROP TABLE t1;
# insert-bug25361251.test. Text fields should work.
# Old syntax.
CREATE TABLE t1(id INT NOT NULL, text1 TEXT, text2 TEXT, PRIMARY KEY (id));
INSERT INTO t1 VALUES (0, "x",  "x"), (1, "y",  "y");
INSERT INTO t1 (id, text1, text2) VALUES (0, "x", "y")
ON DUPLICATE KEY UPDATE text1 = VALUES(text1), text2 = VALUES(text2);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t1;
id	text1	text2
0	x	y
1	y	y
DROP TABLE t1;
# New syntax.
CREATE TABLE t1(id INT NOT NULL, text1 TEXT, text2 TEXT, PRIMARY KEY (id));
INSERT INTO t1 VALUES (0, "x",  "x"), (1, "y",  "y");
INSERT INTO t1 (id, text1, text2) VALUES (0, "x", "y") AS n
ON DUPLICATE KEY UPDATE text1 = n.text1, text2 = n.text2;
SELECT * FROM t1;
id	text1	text2
0	x	y
1	y	y
DROP TABLE t1;
# Bug#30051303: SERVER CRASHED WHILE RUNNING INSERT ON DUPLICATE KEY
#               UPDATE OVER VIEW
CREATE TABLE t0(a INT PRIMARY KEY, b INT, c INT);
CREATE VIEW v AS SELECT t0.a AS va, t0.b AS vb, t0.c AS vc FROM t0;
INSERT INTO v(va, vb, vc) VALUES(1, 10, 100) AS n
ON DUPLICATE KEY UPDATE vc= 199;
SELECT * FROM t0;
a	b	c
1	10	100
DROP TABLE t0;
DROP VIEW v;