4 files changed, 55 insertions, 14 deletions
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 69eb64f08ea..2ac73fe7662 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
 create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
 insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
 create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
@@ -636,16 +636,35 @@ ff1	ff2
 drop table t1, t2;
 create table t1 (a int unique);
 create table t2 (a int, b int);
+create table t3 (c int, d int);
 insert into t1 values (1),(2);
 insert into t2 values (1,2);
+insert into t3 values (1,6),(3,7);
 select * from t1;
 a
 1
 2
-insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
+insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
 select * from t1;
 a
 2
 3
-drop table t1;
-drop table t2;
+insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
+select * from t1;
+a
+3
+5
+insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
+select * from t1;
+a
+1
+5
+10
+insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
+insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
+ERROR 23000: Column 'a' in field list is ambiguous
+insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
+ERROR 42S02: Unknown table 't2' in field list
+insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
+ERROR 42S02: Unknown table 't2' in field list
+drop table t1,t2,t3;
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 2143538469b..150f4ef26c7 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -143,7 +143,7 @@ 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=c+100;
+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
@@ -158,6 +158,8 @@ a	b	c
 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
@@ -174,7 +176,7 @@ select * from t1;
 a
 1
 2
-insert ignore into t1 select a from t1 on duplicate key update a=a+1 ;
+insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ;
 select * from t1;
 a
 1
@@ -185,5 +187,7 @@ a
 2
 3
 insert into t1 select a from t1 on duplicate key update a=a+1 ;
-ERROR 23000: Duplicate entry '3' for key 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;
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 7402940fa52..67799873b73 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -3,7 +3,7 @@
 #
 
 --disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
 --enable_warnings
 
 create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
@@ -182,10 +182,24 @@ drop table t1, t2;
 #
 create table t1 (a int unique);
 create table t2 (a int, b int);
+create table t3 (c int, d int);
 insert into t1 values (1),(2);
 insert into t2 values (1,2);
+insert into t3 values (1,6),(3,7);
 select * from t1;
-insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
+insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
 select * from t1;
-drop table t1;
-drop table t2; 
+insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
+select * from t1;
+insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
+select * from t1;
+insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
+
+#Some error cases
+--error 1052
+insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
+--error 1109
+insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
+--error 1109
+insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index f5857840588..3d6297d8d7a 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -72,11 +72,13 @@ 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=c+100;
+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;
@@ -89,10 +91,12 @@ 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 on duplicate key update a=a+1 ;
+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 1062
+--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;