Better bug fix for:

#9728  'Decreased functionality in "on duplicate key update
#8147  'a column proclaimed ambigous in INSERT ... SELECT .. ON DUPLICATE'

This ensures fields are uniquely qualified and also that one can't update other tables in the ON DUPLICATE KEY UPDATE part


mysql-test/r/insert_select.result:
  More tests for bug #9728 and #8147
mysql-test/r/insert_update.result:
  Updated tests after changing how INSERT ... SELECT .. ON DUPLICATE KEY works
mysql-test/t/insert_select.test:
  More tests for bug #9728 and #8147
mysql-test/t/insert_update.test:
  Updated tests after changing how INSERT ... SELECT .. ON DUPLICATE KEY works
mysys/my_access.c:
  Cleanup (shorter loop variable names)
sql/ha_ndbcluster.cc:
  Indentation fixes
sql/item.cc:
  Remove item_flags
sql/item.h:
  Remove item_flags
sql/mysql_priv.h:
  New arguments to mysql_prepare_insert
sql/sql_base.cc:
  Remove old fix for bug #8147
sql/sql_insert.cc:
  Extend mysql_prepare_insert() with new field list for tables that can be used in the values port of ON DUPLICATE KEY UPDATE
sql/sql_parse.cc:
  Revert fix for #9728
  Allow one to use other tables in ON DUPLICATE_KEY for INSERT ... SELECT if there is no GROUP BY clause
sql/sql_prepare.cc:
  New arguments to mysql_prepare_insert
sql/sql_yacc.yy:
  Revert bug fix for #9728

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;